Thread: MS Access to PostgreSQL
I have recently migrated from MS Access to PostgreSQL.Previously I had
a SQL command
ResultSet aGroupResultSet = aGroupPathStmt.executeQuery(
"SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE
\"fullpath\" Like '" +
aPath + "'");
where aPath was equal to 'folder\another folder\%'.
The field to be edited stores the full path in the format
'folder\folder1\folder2' and so on...
The purpose being to change all groups at this level of the
hieracarchy and below, this was achieved using the '%' in Access, this
however doesn't seem to work in PostgreSQL, it doesn't error out but
it just seems to see the '%' as a normal character.
How can this be done in PostgreSQL?
Many Thanks,
B
--- William Shatner <shatner.william@gmail.com> wrote: > I have recently migrated from MS Access to > PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = > aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM > \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the > format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level > of the > hieracarchy and below, this was achieved using the > '%' in Access, this > however doesn't seem to work in PostgreSQL, it > doesn't error out but > it just seems to see the '%' as a normal character. > > How can this be done in PostgreSQL? > I suspect that, unlike Access, PostgreSQL will intrepret C-style escape sequences (ie: \r, \n, \t) so you'll need to properly escape the backslash in aPath like so: folder1\\folder2\\folder3 Regards, Shelby Cain __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
On Fri, Mar 11, 2005 at 05:19:35PM +0000, William Shatner wrote: > I have recently migrated from MS Access to PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level of the > hieracarchy and below, this was achieved using the '%' in Access, this > however doesn't seem to work in PostgreSQL, it doesn't error out but > it just seems to see the '%' as a normal character. You're running into problems with \ being the escape character in string constants and again in patterns. There's some discussion of this in the "Pattern Matching" section of the "Functions and Operators" chapter in the documentation: http://www.postgresql.org/docs/8.0/interactive/functions-matching.html Here are some ways to make it work (dollar quoting available only in 8.0 and later): fullpath LIKE 'folder\\\\another folder\\\\%' fullpath LIKE 'folder\\another folder\\%' ESCAPE '' fullpath LIKE $$folder\\another folder\\%$$ fullpath LIKE $$folder\another folder\%$$ ESCAPE '' -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi Shelby,
I don't think the problem isn't with the backslashes. They are escaped and
seem to be working fine e.g. to insert a backslash in Access I had to use
one escape character ('\\') whereas in PostgreSQL four backslashes ('\\\\')
are required. The line that inserts the % is as follows...
String aPath = group.getPath() + aOldGroupName + "\\%";
It just doesn't seem to be having the same effect in PostgreSQL as in
Access.
B
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Shelby Cain
Sent: 11 March 2005 17:32
To: William Shatner; pgsql-novice@postgresql.org;
pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] [GENERAL] MS Access to PostgreSQL
--- William Shatner <shatner.william@gmail.com> wrote:
> I have recently migrated from MS Access to
> PostgreSQL.Previously I had
> a SQL command
>
> ResultSet aGroupResultSet =
> aGroupPathStmt.executeQuery(
> "SELECT \"groupID\",\"fullpath\" FROM
> \"groups\" WHERE
> \"fullpath\" Like '" +
> aPath + "'");
>
>
>
> where aPath was equal to 'folder\another folder\%'.
>
> The field to be edited stores the full path in the
> format
> 'folder\folder1\folder2' and so on...
> The purpose being to change all groups at this level
> of the
> hieracarchy and below, this was achieved using the
> '%' in Access, this
> however doesn't seem to work in PostgreSQL, it
> doesn't error out but
> it just seems to see the '%' as a normal character.
>
> How can this be done in PostgreSQL?
>
I suspect that, unlike Access, PostgreSQL will
intrepret C-style escape sequences (ie: \r, \n, \t) so
you'll need to properly escape the backslash in aPath
like so:
folder1\\folder2\\folder3
Regards,
Shelby Cain
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Fri, 2005-03-11 at 17:52 +0000, Brian Gunning wrote:
[rearranged quoting]
> From: pgsql-jdbc-owner@postgresql.org
> --- William Shatner <shatner.william@gmail.com> wrote:
> > I have recently migrated from MS Access to
> > PostgreSQL.Previously I had
> > a SQL command
> >
> > ResultSet aGroupResultSet =
> > aGroupPathStmt.executeQuery(
> > "SELECT \"groupID\",\"fullpath\" FROM
> > \"groups\" WHERE
> > \"fullpath\" Like '" +
> > aPath + "'");
> >
> >
> > where aPath was equal to 'folder\another folder\%'.
> >
> I don't think the problem isn't with the backslashes. They are escaped and
> seem to be working fine e.g. to insert a backslash in Access I had to use
> one escape character ('\\') whereas in PostgreSQL four backslashes ('\\\\')
> are required. The line that inserts the % is as follows...
>
> String aPath = group.getPath() + aOldGroupName + "\\%";
are the backslashes in group.getPath() + aOldGroupName escaped ?
does aGroupPathStmt.executeQuery() escape backslashes ?
did you print the SQL statement to screen or logfile to make sure
what is actually sent to executeQuery() ?
does your language support placeholders ? this kind of problems are
usually easier to deal with with them.
gnari
William Shatner wrote: > I have recently migrated from MS Access to PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > > > where aPath was equal to 'folder\another folder\%'. > <snip> Are you sure? In MS-Access JET engine it uses the '*' character instead of the '%' one as a "like" wildcard. Two things you can try... If you have attached the table "groups" in MS-Access and are using it through JET (as the code you provided would suggest) then try changing the "%" character to "*" - the JET engine will convert that to % for you, whereas it may escape the "%" character you have supplied to keep the behaviour the same as JET. The other possibility is to use the "dbPassThrough" parameter and execute it as a pass through query, here the SQL is sent to the PostgreSQL engine unchanged. All in all I am sure this is an MS-Access problem rather than a Postgres one. Eddy
Hi Edward, Michael Fuhr's solution worked (Mar 11). I had to write a method to insert four backslashes into the the path been searched for. For example if the stored path in the DB was folder1\folder2\folder3\ in order for PostgreSQL to serach against this i had to search for path LIKE folder1\\\\folder2\\\\folder3\\\\%. Thanks to all for help and suggestions. WS On Sun, 20 Mar 2005 00:21:00 +0000, Edward Macnaghten <eddy@edlsystems.com> wrote: > William Shatner wrote: > > I have recently migrated from MS Access to PostgreSQL.Previously I had > > a SQL command > > > > ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( > > "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE > > \"fullpath\" Like '" + > > aPath + "'"); > > > > > > > > where aPath was equal to 'folder\another folder\%'. > > > > <snip> > > Are you sure? In MS-Access JET engine it uses the '*' character instead > of the '%' one as a "like" wildcard. > > Two things you can try... If you have attached the table "groups" in > MS-Access and are using it through JET (as the code you provided would > suggest) then try changing the "%" character to "*" - the JET engine > will convert that to % for you, whereas it may escape the "%" character > you have supplied to keep the behaviour the same as JET. > > The other possibility is to use the "dbPassThrough" parameter and > execute it as a pass through query, here the SQL is sent to the > PostgreSQL engine unchanged. > > All in all I am sure this is an MS-Access problem rather than a Postgres > one. > > Eddy > >