Message-ID: <385DD647.FE504F2B@mascari.com>
Date: Mon, 20 Dec 1999 02:09:59 -0500
From: Mike Mascari <mascarm@mascari.com>
Organization: Mascari Development Inc
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: Pattern matching fun via ODBC
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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