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.

ODBC and SQL 

Keywords: 	 ODBC and SQL

Question #1:

Hope you can help.

I have regulary written stuff to update Access Databases and have code that is working. I have now changed the connection over to SQL. Everything works in terms of creating & updating records.

The issue I have is the qGetData is now not working, always returns -1. The code below worked 100% when retrieving from Access. Can you see why it fails for SQL considering no coding changes have occured.

Is there something different about retrieving from SQL.

I have added a message statement in the code below. This is where it fails, any ideas ? 

sql = StrCat("select * from LASTUSED where AccountName = '",tplogin, "'")
hstmt = qAllocStmt(hdbc) 
retcode = qExecDirect(hstmt, sql)

If (retcode != @qSuccess) && (retcode != @qSuccessInfo) then goto
USERCHK_DB

retcode = qBindCol(hstmt, 1, "Aclogin", 30) 
retcode = qBindCol(hstmt, 5, "lastID", 20)
retcode = qFetch(hstmt) 
If retcode <> @qNoData Then 
retcode = qGetData(hstmt, 5, "lastid", 20)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
message("unable to retrieve",retcode)
LASTID="01"
else
lastid=lastid+1
Endif
else
LastID="01"
endif

Answer:

You may be able to get more information by executing further error checking. The ODBC Function qError, which returns error information back from the ODBC driver, should help.

Try the following... 

sql = StrCat("select * from LASTUSED where AccountName = '",tplogin, "'")
hstmt = qAllocStmt(hdbc) 
retcode = qExecDirect(hstmt, sql)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo) then goto USERCHK_DB
retcode = qBindCol(hstmt, 1, "Aclogin", 30) 
retcode = qBindCol(hstmt, 5, "lastID", 20)
retcode = qFetch(hstmt)

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
   Reterror=qError( hstmt,2)
   Message("qFetch failed", Reterror)
   Exit
Endif

If retcode <> @qNoData
   retcode = qGetData(hstmt, 5, "lastid", 20)
   If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
      Reterror=qError( hstmt,2)
      Message("qGetData failed", Reterror)
      exit
   Endif
Endif
Message("Debugging","Finished")
Exit

Question (cont'd):

Thank for this.

I added this qError and got the following error:- 

S1002 [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

Do you know what this means ?

Answer:

Hmmm, that error probably means.... The value specified for the argument Column Number was greater than the number of columns in the result set.

A stratgey you might try is to issue: 

sql = StrCat("select COUNT(*) from LASTUSED where AccountName = '",tplogin, "'")

If the COUNT is 0, then no use going any further with trying to format columns or retrieve data; this is also a good method to help determine wheher to later INSERT or UPDATE a row.

Resolution:

Thank you very much for the info. The problem in the end was a coding issue. I needed the qFreeStmt after my first search. 
Article ID:   W14428

Filename:   ODBC and SQL.txt
File Created: 2001:09:06:11:13:42
Last Updated: 2001:09:06:11:13:42