Use qGetData to Get all Records into an Array

 Keywords: qGetData to recieve retrieve records fields


;***************************************************************************
;**    
;** DATA GRABBER SCRIPT
;** 
;** Purpose:	Read all data from a datasources table into an array
;** Inputs: 	Prompts user to choose a table
;** Outputs:	NONE 
;** Revisions: 10000 
;** 
;***************************************************************************


;***************************************************************************
;** DEFINE THE FOLLOWING VARIABLES TO MEET YOUR NEEDS 
;**  
;***************************************************************************
dsn = "Northwind"  ;Datasource that points to the Access Sample Northwind.MDB
userid = ""
pswd =""
MAX_RECSIZE = 256
;***************************************************************************


AddExtender("wwodb34i.dll")
ver = qVersionInfo(0)

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

;***************************************************************************
;FILLS A RESULT SET W/ A LIST OF TABLE NAMES 
;Prompts user to choose a table
;***************************************************************************
retcode = qTables(hstmt, @qNull, @qNull, @qNull, @qNull) 
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qTables failed", retcode)
  Exit
Endif
;BINDS A COLUMN IN A RESULT SET TO A VARIABLE THAT YOU NAME HERE
retcode = qBindCol(hstmt, 3, "tablename", 80) 
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qBindCol failed", retcode)
  Exit
Endif
tables = ""
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
  tables = StrCat(tables,@TAB,tablename)
EndWhile
tables = StrTrim(tables)
;SQL_CLOSE  - FREES A SQL CONNECTION
qFreeStmt(hstmt, 0)
;SQL_UNBIND
qFreeStmt(hstmt, 2)
:askagain
table = AskItemList("Choose a table",tables,@tab,@unsorted,@single)
if table == "" then goto askagain


;***************************************************************************
;FILLS A RESULT SET WITH A LIST OF COLUMN NAMES
;***************************************************************************
SQL = 'SELECT * FROM "%table%"'
;-----------------------------------------------
;Get number of columns
;-----------------------------------------------
retcode = qExecDirect(hstmt,SQL )
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
	Message("qExecDirect failed", qError(hstmt,2))
	Exit
Endif
columncount=qNumRsltCol(hstmt)
;-----------------------------------------------
;Get number of rows
;-----------------------------------------------
rowcount = 0
While @TRUE
Result = qFetch(hstmt)
If Result == @qNoData Then Break
rowcount = rowcount + 1
EndWhile
message(StrCat("Number of Columns ",columncount),StrCat("Number of Rows ",rowcount))
;SQL_CLOSE - RESETS OR FREES A SQL STATEMENT HANDLE 
qFreeStmt(hstmt, 0)  ; SQL_CLOSE  


;***************************************************************************
;CREATE ARRAY TO STORE THE DATA
;***************************************************************************
ReturnArray = ArrDimension(columncount,rowcount)


;***************************************************************************
;THE FOLLOWING EXECUTES AN SQL STATEMENT.
;select all columns from table specified
;and writes all the data to an array
;***************************************************************************
retcode = qExecDirect(hstmt, SQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
	Message("qExecDirect failed", qError(hstmt,2))
	Exit
Endif
row = 0
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", qerror(hstmt,2))
		Exit
	Endif
	;GET DATA FOR EACH COLUMN IN THIS ROW
	For col = 1 to columncount
		retcode = qGetData(hstmt,col,"dataitem",MAX_RECSIZE)
		If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
			Message("qGetData failed", qError(hstmt,2))
			Exit
		Endif
		;message("data",dataitem)
		ReturnArray[col-1,row] = dataitem
	Next
	row = row + 1
EndWhile

;***************************************************************************
;  Used for Debugging
;***************************************************************************
title = "To View the Database Array"
desc = StrCat("In order to view the contents of the array in memory. This script can be run in debug",@CRLF)
desc = StrCat(desc,"mode from WinBatch Studio. When this message appears, simply press",@CRLF)
desc = StrCat(desc,"OK to this msg, then double click the array in the WATCH window",@CRLF,@CRLF)
desc = StrCat(desc,"Note: If the array is very large, it may take a moment to load")
message(title,desc)
BreakPoint()
						

;SQL_CLOSE  - CLOSE THE CURSOR
qFreeStmt(hstmt, 0)  
;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)


;DISPLAYS THE ODBC EXTENDER VERSION
Message("SQL Test (extender version %ver%)", "Done")

Article ID:   W15061

File Created: 2002:09:05:13:49:24

Last Updated: 2002:09:05:13:49:24

Database Search

TechHome

Use qGetData to Get all Records into an Array