My 1st attempt at SQL in Winbatch

[mcvpjd3]

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...

Code Select Expand


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.

[JTaylor]

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

[Deana]

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:

Code (winbatch) Select Expand

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:

Code (winbatch) Select Expand


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)

[mcvpjd3]

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

[Deana]

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.

Code (winbatch) Select Expand


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
EndIf

Add the code run again, then post the value displayed in the SQL Provider Error message.

[mcvpjd3]

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.

[Deana]

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