Issue 231: PYODBC Connection Cursor using LIKE statement not working
Status:  NoFix
Owner: ----
Closed:  Jun 2012
Reported by darryl.k...@gmail.com, Jan 18, 2012
What steps will reproduce the problem?
1.Connect to a MS Access 2007 Database 
2.Execute a connection cursor using the WHERE [fieldname] = 'value';
3.Execute a connection cursor using the WHERE [fieldname] LIKE 'value*'; statement

What is the expected output? What do you see instead?
For the = statement query - i get over 500 rows back, but the like statment using the same table and same field/value pair - i get 0 rows back.  This should give me the same number of rows if not more because of the like statment

What version of the product are you using? On what operating system?
I am using Python 2.6, MS Access 2007, and pyodbc module 2.1.8


Please provide any additional information below.
I am not sure if the "LIKE" part of this is not working or not avaiable.  I figured as long as my SQL string was formed correct it would work.  Below are my 2 full SQL statments. 

EQUALS statement (returns 555 rows)
"SELECT DISTRICT, PROBLEM2, STATUS2, OPEN_CATAGORY, OPENING_ID FROM priority_problems WHERE STATUS2 = 'DN';"

LIKE statement (returns 0 rows)
"SELECT DISTRICT, PROBLEM2, STATUS2, OPEN_CATAGORY, OPENING_ID FROM priority_problems WHERE STATUS2 LIKE 'DN*';"

Thanks


Jan 18, 2012
#1 darryl.k...@gmail.com
I found a partial answer to this problem.  Using the LIKE Statment with the PYODBC connection - you must use a '%' as a wildcard.  Although the '%' wildcard doesn't work when executing a SQL statment in Access manually.  This is very strange why the wildcard value changes in the 2 SQL statments.  '*' works in Access SQL and '%' works in PYODBC SQL.
Jun 23, 2012
Project Member #2 mkleehammer
This is normal.  The correct wildcard is '%', but Access changes it when using the Access UI.

Status: NoFix
Apr 28, 2013
#3 jakub.zi...@seznam.cz
Hi, can you help me?
This code works for me:

cursor.execute("SELECT Zadání FROM tab1 WHERE Zadání Like '%something%';")

But this one doesn't work, and i don't know why.

X='%something%'
cursor.execute("SELECT Zadání FROM tab1 WHERE Zadání Like ?;",X)

Am i doing something wrong?
Btw, great work with that module, you save my school project. ZIZA

Apr 28, 2013
#4 darryl.k...@gmail.com
Not sure what you are trying to do with the ?;" 
But I think you want to try this instead
X = '%something%'
cursor.execute("SELECT Zadani FROM tab1 WHERE Zadani LIKE %s;" % X)
Apr 29, 2013
#5 jakub.zi...@seznam.cz
Yeah, that is what i meant, it works now, Thank you very much for your time.
Dec 16, 2013
#6 dhel...@gmail.com
The "?" is a placeholder for cursor to sanitize inputs from SQL injection attacks I believe, so switching to "% X" could possibly open up a vulnerability.