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.

How to Do a Simple Sql Query

 Keywords:

Question:

I am a newbie and all I want to do with winbatch is
  1. open and odbc connection to an informix db
  2. execute a simple select * from members query
  3. show it in a window
I have looked at the help and its greek to me, please help.

Answer:

Here is some sample code that uses the ODBC Extender. Hope this helps get you started: 
DSN = "Sample"
tablename = "MyTable"
SQL = StrCat("SELECT * FROM ", tablename)


AddExtender("wwodb34I.dll")

;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

  ;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

  columnstr = StrCat(columnstr, @tab,columndata)
EndWhile

;Reformat for display
columnstr = StrTrim(columnstr)
columnstr = StrReplace(columnstr,@tab,@lf)
Message(StrCat("Data in column ", column), 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)

User Response:

That works but what if you want to do a "select * from xxx" and you want to see all the columns but you don't know how many there are? Answer: You can determine the columns using the function qNumRsltCol.

Here is some code that retrieves all the columns. 

DSN = "Sample"
tablename = "MyTable"
SQL = StrCat("SELECT * FROM ", tablename)


AddExtender("wwodb34I.dll")

;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)





Article ID:   W15845

File Created: 2012:11:26:09:51:06
Last Updated: 2012:11:26:09:51:06