pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPattern matching fun via ODBC
2+ messages / 1 participants
[nested] [flat]
* Pattern matching fun via ODBC
@ 1999-12-20 07:09 Mike Mascari <mascarm@mascari.com>
0 siblings, 0 replies; 2+ messages in thread
From: Mike Mascari @ 1999-12-20 07:09 UTC (permalink / raw)
To: pgsql-interfaces
Hello,
I just thought I should bring this to the attention of the
mailing list. About a week ago I posted a problem I was
having with Microsoft Access 97 and the PostgreSQL ODBC
driver (6.40.0006) with respect to the use of a LIKE
expression (to which no one responded, BTW). A query such as
this:
SELECT workorders.workorder, workorders.workorderno,
equipment.assetno, equipment.controlno
FROM workorders, equipment
WHERE equipment.assetno LIKE '%214%'
AND workorders.equipment=equipment.equipment
ORDER BY workorders.workorder;
gets re-written by Access as this:
"SELECT "workorders"."workorder","equipment"."equipment"
FROM "equipment","workorders"
WHERE (("equipment"."assetno" = '%214%' )
AND ("workorders"."equipment" = "equipment"."equipment" ) )
ORDER BY "workorders"."workorder"
which is obviously NOT equivalent. I don't know whether to
blame Access 97 or the ODBC driver, but I'm leaning toward
Microsoft. Anyway, in order to get around this, I used the
following query:
SELECT workorders.workorder, workorders.workorderno,
equipment.assetno, equipment.controlno
FROM workorders, equipment
WHERE InStr(equipment.assetno, '214') > 0
AND workorders.equipment=equipment.equipment
ORDER BY workorders.workorder;
This was rewritten by Access 97 as:
"SELECT "workorders"."workorder","equipment"."equipment"
FROM "workorders","equipment"
WHERE (({fn locate('214' ,"equipment"."assetno" ,1)}> 0 )
AND ("workorders"."equipment" = "equipment"."equipment" ) )
ORDER BY "workorders"."workorder"
Well, this is a problem. Because, according to my ODBC 2.0
specs, LOCATE() should only contain 2 parameters as in:
LOCATE(string1, string2)
not three. I also see that the ODBC driver is translating fn
LOCATE ODBC client calls into strpos() calls on the server.
In Convert.c:
/* How to map ODBC scalar functions {fn func(args)} to
Postgres */
/* This is just a simple substitution */
char *mapFuncs[][2] = {
{ "CONCAT", "textcat" },
{ "LCASE", "lower" },
{ "LOCATE", "strpos" },
{ "LENGTH", "textlen" },
{ "LTRIM", "ltrim" },
Unfortunately, PostgreSQL doesn't have a strpos() routine of
the type:
int4 strpos(text, text, int4)
so I created one and everything now works. But I was just
wondering what (if anything) should be done about it. It
would be really simple to add a strpos() function to the
system catalog which takes a starting character parameter,
but should that be PostgreSQL's responsibility? The thing
is, I know of no other way to perform simple %pattern% type
matching via ODBC without using pass-through queries.
Any comments?
Mike Mascari
^ permalink raw reply [nested|flat] 2+ messages in thread
* [INTERFACES] Pattern matching fun via ODBC
@ 1999-12-20 13:17 Mike Mascari <mascarm@mascari.com>
0 siblings, 0 replies; 2+ messages in thread
From: Mike Mascari @ 1999-12-20 13:17 UTC (permalink / raw)
To: pgsql-interfaces
>David C Hartwig Jr wrote:
>
> Mike Mascari wrote:
>
> > Hello,
> >
> > SELECT workorders.workorder, workorders.workorderno,
> > equipment.assetno, equipment.controlno
> > FROM workorders, equipment
> > WHERE equipment.assetno LIKE '%214%'
> > AND workorders.equipment=equipment.equipment
> > ORDER BY workorders.workorder;
> >
> > gets re-written by Access as this:
> >
> > "SELECT "workorders"."workorder","equipment"."equipment"
> > FROM "equipment","workorders"
> > WHERE (("equipment"."assetno" = '%214%' )
> > AND ("workorders"."equipment" = "equipment"."equipment" ) )
> > ORDER BY "workorders"."workorder"
>
> I don't have Access 97 installed on any of my systems at this time, so
> I cam not verify what I am about to say. But I seem to recall that
> Access uses asterisk (*) as the wild card character. I think it is
> the MS Jet Engine that is transforming your statements. I believe the
> Jet sees the percent (%) character as just another character and so in
> its infinite wisdom optimizes for you - transforming the LIKE to an
> equal (=). Pretty thoughtful of them. :>(
>
> I believe (in the context of a LIKE expression) the Jet will transform
> your asterisks (*) to percents (%).
Unbelievable!
You're absolutely correct. I had tried using asterisks with
the equality operator, but I had never thought to use both
LIKE and asterisks. Wow! Thanks...
Mike Mascari
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-12-20 13:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-20 07:09 Pattern matching fun via ODBC Mike Mascari <mascarm@mascari.com>
1999-12-20 13:17 [INTERFACES] Pattern matching fun via ODBC Mike Mascari <mascarm@mascari.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox