Re: 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

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/

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(at)edlsystems(dot)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
>
>