Hi folks, I'm trying to write a simple application in Winbatch to insert a line into a SQL table on an MS SQL server. I've copied the relevant code from the help files and configured my ODBC as appropriate. It all works apart from the Insert command. When I run that I get a return code of -1 and nothing inserted into the table. If I copy the command to the SQL server management studio and run it, it inserts into the database fine. The message section at the end does bring back the columns and types, so the connection is fine. The code is as follows...
AddExtender("wwodb44I.dll")
;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
AddExtender("wwodb44i.dll")
henv = qAllocEnv()
If henv == -1
retcode = qLastCode()
Message("qAllocEnv failed", retcode)
Exit
EndIf
hdbc = qAllocConnect(henv)
If hdbc == -1
retcode = qLastCode()
Message("qAllocConnect failed", retcode)
Exit
EndIf
retcode = qConnect(hdbc, "MYODBCConnect", "myid", "mypassword")
If (retcode != @QSUCCESS) && (retcode != @QSUCCESSINFO)
Message("qConnect failed", retcode)
Exit
EndIf
hstmt = qAllocStmt(hdbc)
If hstmt == -1
retcode = qLastCode()
Message("qAllocStmt failed", retcode)
Exit
EndIf
retcode = qColumns(hstmt, @QNULL, @QNULL, "zNews", @QNULL)
If (retcode != @QSUCCESS) && (retcode != @QSUCCESSINFO)
Message("qColumns failed", retcode)
Exit
EndIf
retcode = qBindCol(hstmt, 4, "colname", 80)
retcode = qBindCol(hstmt, 6, "coltype", 80)
retcode = qBindCol(hstmt, 8, "collength", 80)
If (retcode != @QSUCCESS) && (retcode != @QSUCCESSINFO)
Message("qBindCol failed", retcode)
Exit
EndIf
COLUMNS = ""
While @TRUE
retcode = qFetch(hstmt)
If retcode == @QNODATA Then Break
If (retcode != @QSUCCESS) && (retcode != @QSUCCESSINFO)
Message("qFetch failed", retcode)
Exit
EndIf
COLUMNS = StrCat(COLUMNS, colname, @TAB, "(", coltype, ")", @TAB, "[", collength, "]", @LF)
EndWhile
sql="INSERT INTO dbo.zNews (NewsID,NewsExpiryDate,ProductLicenseType,NewsTimestamp,Severity,ProductVersion,Revision,IsDeleted) VALUES ('123457','2015-04-25 11:15:39.000','ZCM_EVAL,ZCM_ACTIVE','2011-04-25 11:15:39.000','NORMAL','10.3',1,0);"
retcode = qExecDirect(hstmt, sql)
Message("Columns in 'Sample'", COLUMNS)
qFreeStmt(hstmt, 0) ; SQL_CLOSE
qDisconnect(hdbc)
qFreeConnect(hdbc)
qFreeEnv(henv)
exit
Any help appreciated.
I would recommend that you look for an example using ADO/OLEDB in the Tech Database and go that route, rather than using the ODBC Extender.
Jim
In the code you posted you were binding to and obtaining the columns before executing a SQL statement. After you have successfully executed an SQL statement, you will need to fetch the data using the function qFetch. In this case, qFetch fetches a row of data from the result set, created by qExecDirect.
IMPORTANT: I recommend adding the following code AFTER the qExecDirect:
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
ret=qError(hstmt,2)
Message("SQL Provider Error", ret)
Exit
EndIf
Here is an UNDEBUGGED code sample that should help:
AddExtender("wwodb44i.dll")
datasrc = "MYODBCConnect"
userid = "myid"
pswd = "mypassword"
;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 "SAMPLE" DATA SOURCE (with the data source name 'ContactDSN')
retcode = qConnect(hdbc, datasrc, userid, pswd)
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.
sql="INSERT INTO dbo.zNews (NewsID,NewsExpiryDate,ProductLicenseType,NewsTimestamp,Severity,ProductVersion,Revision,IsDeleted) VALUES ('123457','2015-04-25 11:15:39.000','ZCM_EVAL,ZCM_ACTIVE','2011-04-25 11:15:39.000','NORMAL','10.3',1,0);"
retcode = qExecDirect(hstmt, sql)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qExecDirect failed", retcode)
Exit
Endif
counter=0
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
;RETRIEVES THE VALUE OF A COLUMN IN THE CURRENT ROW
;OF A RESULT SET
column = 1
retcode = qGetData(hstmt, column, "columndata", 80)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Message("qGetData failed", retcode)
Exit
Endif
counter=counter+1
columnstr = StrCat(columnstr, @tab,columndata)
EndWhile
;Reformat for display
columnstr = StrTrim(columnstr)
columnstr = StrReplace(columnstr,@tab,@lf)
Message(StrCat("Data in column ", column), columnstr)
Message("NUMBER OF ROWS IN RESULT SET",counter)
;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)
Thanks for this, I've copied it as is (amended for my SQL server) and still getting a retcode of -1 after the qExecDirect command to run the insert.
The qAllocEnv, qAllocConnect.... commands all work, it's just trying to do this Insert command that fails.
Thanks
Quote from: mcvpjd3 on June 26, 2014, 10:23:03 AM
Thanks for this, I've copied it as is (amended for my SQL server) and still getting a retcode of -1 after the qExecDirect command to run the insert.
The qAllocEnv, qAllocConnect.... commands all work, it's just trying to do this Insert command that fails.
Thanks
Sorry you will need to add the qError statement to get the Provider error.
sql="INSERT INTO dbo.zNews (NewsID,NewsExpiryDate,ProductLicenseType,NewsTimestamp,Severity,ProductVersion,Revision,IsDeleted) VALUES ('123457','2015-04-25 11:15:39.000','ZCM_EVAL,ZCM_ACTIVE','2011-04-25 11:15:39.000','NORMAL','10.3',1,0);"
retcode = qExecDirect(hstmt, sql)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
ret=qError(hstmt,2)
Message("SQL Provider Error", ret)
Exit
EndIfAdd the code run again, then post the value displayed in the SQL Provider Error message.
My Bad... The insert did work, it was the qFetch after that that gave the -1
The reason for the qFetch failing is an error:
24000 [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Thanks for the help.
Quote from: mcvpjd3 on June 26, 2014, 12:50:58 PM
My Bad... The insert did work, it was the qFetch after that that gave the -1
The reason for the qFetch failing is an error:
24000 [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Thanks for the help.
You cannot reuse the Statement Handle when the cursor is in use in this memory table. Take a look at the function qFreeStmt. Try one of the following to free up a stmt handle: ;SQL_CLOSE - FREES A SQL CONNECTION
qFreeStmt(hstmt, 0)
;or
;SQL_UNBIND
qFreeStmt(hstmt, 2)
Reference: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WIL~Extenders/ODBC+Invalid~Cursor~State~Error.txt