Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
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