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