ODBC Example Code from Help File

Keywords:      ODBC examples

---

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

;SQL_LOGIN_TIMEOUT - SETS OPTIONS FOR SQL CONNECTIONS
qSetConnOpt(hdbc, 103, 5, 0)  

datasources = ""

For i = 1 To 999
  If i == 1
    direction = @qFirst
  Else
    direction = @qNext
  Endif

  ;RETURNS DATA SOURCE NAMES & DESCS FROM A LIST OF AVAILABLE
  ;DATASOURCES
  datasource = qDataSources(henv, direction) 

  If datasource == ""
    retcode = qLastCode()
    If retcode == @qNoData Then Break
    Message("qDataSources failed", retcode)
    Exit
  Endif

  datasources = StrCat(datasources, datasource, @LF)
Next

Message("Data sources", datasources)

;CONNECTS TO THE "SAMPLE" DATA SOURCE
retcode = qConnect(hdbc, "SAMPLE", "", "") 

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 STORED IN A
;SPECIFIED DATASOURCE
retcode = qTables(hstmt, @qNull, @qNull, @qNull, "Table") 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qTables failed", retcode)
  Exit
Endif

tablenamebuf = BinaryAlloc(100)

;BINDS A COLUMN IN A RESULT SET TO A VARIABLE THAT YOU NAME HERE
retcode = qBindCol(hstmt, 3, "tablenamebuf", 80) 
retcode = qBindCol(hstmt, 4, "tabletype", 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

  tablename = BinaryPeekStr(tablenamebuf, 0, BinaryEodget(tablenamebuf))

  tables = StrCat(tables, tablename, @TAB, "(", tabletype, ")", @LF)
EndWhile

BinaryFree(tablenamebuf)

;DISPLAY TABLES IN THE "SAMPLE" DATA SOURCE
Message("Tables in 'SAMPLE'", tables)

;SQL_CLOSE  - FREES A SQL CONNECTION
qFreeStmt(hstmt, 0)
;SQL_UNBIND
qFreeStmt(hstmt, 2)

;--------------------- NEW DATASOURCE EXAMPLE---------------------

;FILLS A RESULT SET WITH A LIST OF COLUMN NAMES IN SPECIFIED ;TABLES FROM "SAMPLE" DATA SOURCE
retcode = qColumns(hstmt, @qNull, @qNull, "data.txt", @qNull) 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qColumns failed", retcode)
  Exit
Endif

;BINDS A COLUMN IN A RESULT SET TO A VARIABLE THAT YOU NAME HERE
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

;DISPLAY COLUMNS IN THE "SAMPLE" DATA SOURCE
Message("Columns in 'SAMPLE'", columns) 

;SQL_CLOSE - RESETS OR FREES A SQL STATEMENT HANDLE 
;(SET WITH QALLOC)
qFreeStmt(hstmt, 0)  
qFreeStmt(hstmt, 2)  ; SQL_UNBIND

;THE FOLLOWING EXECUTES AN SQL STATEMENT.
;THE COLUMNS BELOW ARE ACTUAL COLUMN NAMES IN YOUR DATA SOURCE, ;E.G.,"LASTNAME":
retcode = qExecDirect(hstmt, "SELECT LASTNAME, FIRSTNAME, WORKPHONE, ADDRESS1 FROM DATA.TXT")

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qExecDirect failed", retcode)
  Exit
Endif

names = ""

lastnamebuf = BinaryAlloc(100)

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
  retcode = qGetData(hstmt, 1, "lastnamebuf", 80)
  retcode = qGetData(hstmt, 2, "firstname", 80)
  retcode = qGetData(hstmt, 3, "workphone", 80)
  retcode = qGetData(hstmt, 4, "address1", 80)

  If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
    Message("qGetData failed", retcode)
    Exit
  Endif

  lastname = BinaryPeekStr(lastnamebuf, 0, BinaryEodGet(lastnamebuf))

  If (retcode == @qSuccessInfo)	 ;if the field was too small
    msg = qError(hstmt, 2)
    Message("qGetData error/status (%lastname%, %firstname%)", msg)
  Endif

  names = StrCat(names, lastname, ", ", firstname, @TAB, "(", workphone,")", @TAB, "(", address1, ")", @LF)
EndWhile

BinaryFree(lastnamebuf)

;DISPLAY NAMES IN "EMPLOYEE" DATA SOURCE
Message("Names in 'Sample'", names) 

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

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

---

Article ID:   W12539

Filename:   ODBC Example.txt