WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WIL Extenders
ODBC
plus
Sample Code

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Using WildCards in an ODBC SQL Statement

Question:

I would like an end-user to provide part of a country name and have this search a very large Access database and return the record(s) it matches. I have it working if I provide the SQL statement with an exact match on the whole name (LIKE "ALBANIA"). But, if I use a wildcard for a partial name query (LIKE "AL*"), which works in an Access query, the script produces a qExecDirect failed with -1. I can't seem to get surrounding quotes and the * wildcard to work together.

Here's the code so far (most of which is from the online tech database here): 

AddExtender("wwodb34I.dll")

DSN = "City"
tablename = "Codes"

SQL = "SELECT Country FROM Codes WHERE Country LIKE 'al*'" 

;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
henv = qAllocEnv()
If henv == -1
;THIS RETURNS LAST CODE SET BY LAST SQL FUNCTION
retcode = qLastCode() 
Message("qAllocEnv failed", retcode)
Exit
Endif

;ALLOCATES A SQL CONNECTION HANDLE - MAXIMUM 
;OF 10 OPEN CONNECTION HANDLES
hdbc = qAllocConnect(henv) 
If hdbc == -1
retcode = qLastCode()
Message("qAllocConnect failed", retcode)
Exit
Endif

;CONNECTS TO THE DATA SOURCE
retcode = qConnect(hdbc, DSN , "", "") 
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qConnect failed", retcode)
Exit
Endif

;THIS ALLOCATES A SQL STATEMENT HANDLE
hstmt = qAllocStmt(hdbc) 
If hstmt == -1
retcode = qLastCode()
Message("qAllocStmt failed", retcode)
Exit
Endif


;THE FOLLOWING EXECUTES AN SQL STATEMENT.
;THE COLUMNS BELOW ARE ACTUAL COLUMN NAMES IN YOUR DATA SOURCE, ;E.G.,"LASTNAME":
retcode = qExecDirect(hstmt, SQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qExecDirect failed", retcode)
Exit
Endif


columnstr = ""
While @TRUE
;FETCHES A ROW OF DATA FROM A RESULT SET
retcode = qFetch(hstmt) 
If retcode == @qNoData Then Break
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qFetch failed", retcode)
Exit
Endif

numcol = qNumRsltCol(hstmt)
rowstr = ""
For column = 1 to numcol
;RETRIEVES THE VALUE OF A COLUMN IN THE CURRENT ROW 
;OF A RESULT SET
retcode = qGetData(hstmt, column, "columndata", 80)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qGetData failed", retcode)
Exit
Endif 
rowstr = StrCat(rowstr, @tab,columndata)
Next
rowstr = StrTrim(rowstr)
columnstr = StrCat(columnstr, @lf,rowstr)
EndWhile

;Reformat for display
columnstr = StrSub(columnstr,2,-1)
Message(StrCat("Data in the table ",tablename), columnstr) 

;SQL_DROP - RESETS OR FREES AN SQL STATEMENT HANDLE
qFreeStmt(hstmt, 1)
;CLOSES A CONNECTION TO A DATA SOURCE
qDisconnect(hdbc)
;FREES AN SQL CONNECTION HANDLE
qFreeConnect(hdbc)
;FREES AN SQL ENVIRONMENT HANDLE
qFreeEnv(henv)

Answer:

The SQL statement looks basically ok. But after further debugging we found that using a % instead of * for a wildcard resolved the issue.

See: http://support.microsoft.com/default.aspx?scid=kb;en-us;199163 

AddExtender("wwodb34I.dll")

DSN = "City"
tablename = "Codes"
SQL = "SELECT Country FROM Codes WHERE Country LIKE 'albani_'" ; '_' Matches a single character
;SQL = "SELECT Country FROM Codes WHERE Country LIKE 'al%%'" ; '%'Matches one or more characters


;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
henv = qAllocEnv()
If henv == -1
;THIS RETURNS LAST CODE SET BY LAST SQL FUNCTION
retcode = qLastCode() 
Message("qAllocEnv failed", retcode)
Exit
Endif

;ALLOCATES A SQL CONNECTION HANDLE - MAXIMUM 
;OF 10 OPEN CONNECTION HANDLES
hdbc = qAllocConnect(henv) 
If hdbc == -1
retcode = qLastCode()
Message("qAllocConnect failed", retcode)
Exit
Endif

;CONNECTS TO THE DATA SOURCE
retcode = qConnect(hdbc, DSN , "", "") 
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qConnect failed", retcode)
Exit
Endif

;THIS ALLOCATES A SQL STATEMENT HANDLE
hstmt = qAllocStmt(hdbc) 
If hstmt == -1
retcode = qLastCode()
Message("qAllocStmt failed", retcode)
Exit
Endif


;THE FOLLOWING EXECUTES AN SQL STATEMENT.
;THE COLUMNS BELOW ARE ACTUAL COLUMN NAMES IN YOUR DATA SOURCE, ;E.G.,"LASTNAME":
retcode = qExecDirect(hstmt, SQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qExecDirect failed", qError(hstmt,2))
Exit
Endif


columnstr = ""
While @TRUE
;FETCHES A ROW OF DATA FROM A RESULT SET
retcode = qFetch(hstmt) 
If retcode == @qNoData Then Break
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qFetch failed", retcode)
Exit
Endif

numcol = qNumRsltCol(hstmt)
rowstr = ""
For column = 1 to numcol
;RETRIEVES THE VALUE OF A COLUMN IN THE CURRENT ROW 
;OF A RESULT SET
retcode = qGetData(hstmt, column, "columndata", 80)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qGetData failed", retcode)
Exit
Endif 
rowstr = StrCat(rowstr, @tab,columndata)
Next
rowstr = StrTrim(rowstr)
columnstr = StrCat(columnstr, @lf,rowstr)
EndWhile

;Reformat for display
columnstr = StrSub(columnstr,2,-1)
Message(StrCat("Data in the table ",tablename), columnstr) 

;SQL_DROP - RESETS OR FREES AN SQL STATEMENT HANDLE
qFreeStmt(hstmt, 1)
;CLOSES A CONNECTION TO A DATA SOURCE
qDisconnect(hdbc)
;FREES AN SQL CONNECTION HANDLE
qFreeConnect(hdbc)
;FREES AN SQL ENVIRONMENT HANDLE
qFreeEnv(henv)


Article ID:   W15849

File Created: 2004:03:30:15:41:08
Last Updated: 2004:03:30:15:41:08