Pattern matching fun via ODBC
От | Mike Mascari |
---|---|
Тема | Pattern matching fun via ODBC |
Дата | |
Msg-id | 385DD647.FE504F2B@mascari.com обсуждение исходный текст |
Список | 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
В списке pgsql-interfaces по дате отправления: