odbc database adapter ... problem with "like"
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*``))'.") SQL used: (in ZSQL method) select DIRECTOR_NAME, USERID, NAME, AUTHORIZED, TELECOMMUTER from test_directors_names where (((NAME) like ""Pate*"")); Valid Access SQL: SELECT nac_payroll_users_director_rc.USERID, nac_payroll_users_director_rc.NAME, nac_payroll_users_director_rc.DIRECTOR_NAME, nac_payroll_users_director_rc.AUTHORIZED, nac_payroll_users_director_rc.TELECOMMUTER FROM nac_payroll_users_director_rc WHERE (((nac_payroll_users_director_rc.NAME) like "Pate*")); Clarence T. Pate
"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*``))'.")
SQL used: (in ZSQL method) select DIRECTOR_NAME, USERID, NAME, AUTHORIZED, TELECOMMUTER from test_directors_names where (((NAME) like ""Pate*""));
Use % instead of the asterisk: where NAME like 'Pate%' hth, thomas
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
Phil Harris schrieb:
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
[...]
|> |>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.
You are of course right concerning Access' use of wildcards. But in ZSQL-Methods only the '%' gives me the expected results - the '*' as wildcard will be ignored. I use Access 97 as database and ODBC 4.00. regards, thomas
participants (3)
-
Pate, Clarence T -
Phil Harris -
Thomas Weiner