[Zope] odbc database adapter ... problem with "like"

Phil Harris phil.harris@zope.co.uk
Wed, 24 Nov 1999 17:00:44 -0000


Looks to me as if you are misquoting the sql actually used.  see below

|>-----Original Message-----
|>From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of
|>Thomas Weiner
|>Sent: Wednesday, November 24, 1999 4:47 PM
|>To: Pate, Clarence T
|>Cc: zope@zope.org
|>Subject: Re: [Zope] odbc database adapter ... problem with "like"
|>
|>
|>"Pate, Clarence T" schrieb:
|>>
|>> Hello all!
|>>
|>> Is there a problem when using "LIKE" with the ODBC database
|>adapteor?  If you
|>> notice, the error message says alike instead of like.  Is this
|>normal?  Is
|>> there a better way to perform a look up similar to this?
|>>
|>> Error message in browser:
|>>
|>> Z SQL Method at  /recert / director / sqlDirectorEmployeeByLastName
|>> Error, sql.error: ('37000', -3100, "
|>> [Microsoft][ODBC Microsoft Access 97 Driver]
|>> Syntax error (missing operator) in query expression '(((NAME) alike
|>> ``Pate*``))'.")

Notice the error says (((NAME) alike ``Pate*``)), but see below.

|>>
|>> SQL used: (in ZSQL method)
|>> select DIRECTOR_NAME, USERID, NAME, AUTHORIZED, TELECOMMUTER
|>> from test_directors_names
|>> where (((NAME) like ""Pate*""));

Your sql says (((NAME) like ""Pate*"")), see the difference, the operator
the error is talking about wou;ld be the like statement, but you have alike.

There's your problem.

|>
|>Use % instead of the asterisk:
|>where NAME like 'Pate%'

MS Access in it's infinite wisdom decided to go against the convention of
using '%' as a wildcard and decided to use '*', so the SQL is correct for
Access.

|>
|>hth,
|>thomas
|>

hth

Phil
phil.harris@zope.co.uk