Wilson WindowWare Tech Support

WinBatch WinBatch+Compiler WebBatch
Home | Tech Database | Tech BBS | White Papers | Purchase

ODBC with Access Examples 

Keywords:   odbc access
Below are two routines which I use as a generic basis for working with ACCESS and Oracle Tables.

Both work via an intermediary INI file which holds Column settings.

Hope these are helpful to others building ODBC apps with Winbatch.

GETCOLS.WBT: 

; ///////////////////////////////////////////////////////////////////
; // Generic Winbatch Script To Obtain Information on Columns in   //
; // a Table via the ODBC Extender.  I wrote it to accept 4        //
; // parameters:                                                   //
; // param1 = INI File name to hold information, i.e. MyIni        //
; //          The .ini extension is added and the file is either   //
; //          created in the current dir or Opened there           //
; // param2 = INI Section to Create/Update DEFAULTS to [Main]      //
; // param3 = DSN         DEFAULTS TO pop up selection box         //
; // param4 = Table Name  DEFAULTS TO pop up selection box         //
; //                                                               //
; // An ODBC Environment, Connection and Statement Handle are set  //
; // An SQL Statement SELECT * FROM %param4% is Created            //
; // An SQLPrepare() operation is performed, this will not         //
; // actually extract the data but will allow the Table To         //
; // be interrogated for Columns in the Table                      //
; //                                                               //
; // for later binding of columns, just open the ini file and      //
; // create a loop to get values based on ColNumber= entry         //
; //                                                               //
; // USAGE: getcols Sample AccessData RESORTS RESORTS              //
; //                                                               //
; // Creates/writes to an INI file names Sample, a Section         //
; // called AccessData Using DSN=RESORTS, Table=Resorts            //
; //                                                               //
; // NOTE: for DSN's with UID/Password, you can add 2 more param   //
; //                                                               //
; //             Stan Littlefield, March 05, 2000                  //
; ///////////////////////////////////////////////////////////////////

AddExtender("wwodb34I.dll")

IF IsDefined(param1)==@NO
   message("Required Parameter Missing","Looking For INI File Name w/Optional Section,DSN,Table")
   exit
Endif

BoxOpen("ODBC Inquiry.....","Gathering Column Information for Table:%param4%")

IF IsDefined(param2)==@NO  ; set to DEFAULT
   param2="Main"
Endif

;general section to set up handles
henv = qAllocEnv()
If henv == -1
  retcode = qLastCode()  
  Message("Could Not Create ODBC Environment", retcode)
  Exit
Endif

hdbc = qAllocConnect(henv) 
If hdbc == -1
  retcode = qLastCode()
  Message("qAllocConnect failed", retcode)
  Exit
Endif

If IsDefined(param3)==@NO ;no DSN sent as parm, so offer to select 1
   gosub GetDSN
   IF param3=="NULL"
      qFreeConnect(hdbc)
      qFreeEnv(henv)
      exit
   Endif
Endif


retcode = qConnect(hdbc, "%param3%", "", "")
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("Connection To Data Source Failed", retcode)
  Exit
Endif

hstmt = qAllocStmt(hdbc)
If hstmt != 0
  retcode = qLastCode()
  Message("qAllocStmt failed", retcode)
  Exit
Endif

If IsDefined(param4)==@NO ;no Table sent as parm, so offer to select 1
   gosub GetTable
   IF param4=="NULL"
      qDisconnect(hdbc)
      qFreeConnect(hdbc)
      qFreeEnv(henv)
      exit
   Endif
Endif

;create or write Initial Entries to INI File
IniFile = strcat(".\","%param1%",".ini")
IniWritePvt("%param2%","DSN","%param3%",IniFile)
IniWritePvt("%param2%","Table","%param4%",IniFile)


cSQL= strcat("SELECT * FROM ","%param4%")
IniWritePvt("%param2%","SQL",cSQL,IniFile)


;use the Extender's qSpecial Function
handle  = qSpecial(2,hstmt,0,0,0)
cDLL    = qSpecial(1,0,0,0,0)
nLen    = strlen(cSQL)
x       = DllCall(cDLL, long:"SQLPrepare",long:handle,lpstr:cSQL,long:nLen)

numcols = qNumRsltCol(hstmt)

IniWritePvt("%param2%","Colnumber",numcols,IniFile)

;now gather column info
Drop(cSQL)
For i=1 To numcols

   cColName   = BinaryAlloc(128)
   BinaryEODSet(cColName,128)

   nColScale  = BinaryAlloc(32)
   BinaryEODSet(nColScale,32)
   BinaryPoke4(nColScale,0,0)

   nLength    = BinaryAlloc(32)
   BinaryEODSet(nLength,32)
   BinaryPoke4(nLength,0,0)

   nColNull   = BinaryAlloc(32)
   BinaryEODSet(nColNull,32)
   BinaryPoke4(nColNull,0,2)

   nColType   = BinaryAlloc(32)
   BinaryEODSet(nColType,32)
   BinaryPoke4(nColType,0,0)

   nColDef    = BinaryAlloc(32)
   BinaryEODSet(nColDef,32)
   BinaryPoke4(nColDef,0,0)

   ;NOTE: here I use a dll call to SQLDescribeCol
   ;I could have used the qColumn which is built into the
   ;present ODBC Extender, but I wanted to obtain information
   ;about the result set - this is important in cases where
   ;you might use transformation or scalar functions on table data,
   ;==============================================================
   ;below is the dllcall() with all parameters described
   ;==============================================================
   ;x= DllCall(cDLL, long:"SQLDescribeCol",
   ;long:handle,         = true statement handle
   ;long:nCol,           = column number
   ;lpbinary:cColName,   = string to return Column Name
   ;long:128,            = Maximum length of Column Name
   ;lpbinary:nLength,    = Actual length returned
   ;lpbinary:nColType,   = Column Type, i.e. 1=SQL_CHAR
   ;lpbinary:nColDef,    = Precision or Size of Data
   ;lpbinary:nColScale,  = Scale
   ;lpbinary:nColNull)   = can column contain Null Values

   x= DllCall(cDLL, long:"SQLDescribeCol",long:handle,long:i,lpbinary:cColName,long:128,lpbinary:nLength,lpbinary:nColType,lpbinary:nColDef,lpbinary:nColScale,lpbinary:nColNull)

   ;store values in INI File
   IniWritePvt("%param2%",strcat("ColName",i),BinaryPeekStr(cColName,0,BinaryEodGet(cColName)),IniFile)

   ;interpret the Column Type
   cType = "SQL_DEFAULT"
   n     = BinaryPeek4(nColtype,0)

   Switch n
      Case 1
         cType= "SQL_CHAR"     ;Note, I am using standard ODBC values
         break
      Case 12
         cType= "SQL_VARCHAR"
         break
      Case -1
         cType= "SQL_LONGVARCHAR"
         break
      Case 2
         cType= "SQL_NUMERIC"
         break
      Case 3
         cType= "SQL_DECIMAL"
         break
      Case 4
         cType= "SQL_INTEGER"
         break
      Case 5
         cType= "SQL_SMALLINT"
         break
      Case 6
         cType= "SQL_FLOAT"
         break
      Case 7
         cType= "SQL_REAL"
         break
      Case 8
         cType= "SQL_DOUBLE"
         break
      Case -5
         cType= "SQL_BIGINT"
         break
      Case -6
         cType= "SQL_TINYINT"
         break
      Case 9
         cType= "SQL_DATE"
         break
      Case 10
         cType= "SQL_TIME"
         break
      Case -7
         cType= "SQL_BIT"

   EndSwitch

   IniWritePvt("%param2%",strcat("ColType",i),"%cType%",IniFile)
   IniWritePvt("%param2%",strcat("ColScale",i),BinaryPeek4(nColScale,0),IniFile)
   IniWritePvt("%param2%",strcat("ColPrecision",i),BinaryPeek4(nColDef,0),IniFile)

   cType = "Unknown"
   n     = BinaryPeek4(nColNull,0)
   Switch n
      Case 0
         cType= "No"
         break
      Case 1
         cType= "Yes"     ;Note, I am using standard ODBC values
   EndSwitch

   IniWritePvt("%param2%",strcat("AcceptNulls",i),"%cType%",IniFile)

   Drop(x,n,cType)

   ;free buffers
   cColName   = BinaryFree(cColName)
   nColScale  = BinaryFree(nColScale)
   nLength    = BinaryFree(nLength)
   nColNull   = BinaryFree(nColNull)
   nColType   = BinaryFree(nColType)
   nColDef    = BinaryFree(nColDef)

Next

qFreeStmt(hstmt, 1)
qDisconnect(hdbc)
qFreeConnect(hdbc)
qFreeEnv(henv)

boxshut()
exit

:Gettable

hstmt1 = qAllocStmt(hdbc)

If hstmt1 == -1
  Display(2,"SQL Operation Status","Unable To Acquire Statement Handle")
  param4="NULL"
  Return
Endif


retcode = qTables(hstmt1, @qNull, @qNull, @qNull, "Table")

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Display(2,"SQL Operation Status","Unable To Load Tables")
  param4="NULL"
  Return
Endif

tablenamebuf = BinaryAlloc(100)
retcode = qBindCol(hstmt1, 3, "TABLENAMEBUF", 80)
retcode = qBindCol(hstmt1, 4, "TABLETYPE", 80)

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Display(2,"SQL Operation Status","Unable To Bind tables")
  param4="NULL"
  Return
Endif
tables = ""

While @TRUE
  retcode = qFetch(hstmt1)
  If retcode == @qNoData Then Break
  If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
     gotTable=@TRUE
     Display(2,"SQL Operation Status","Unable To Fetch Table Data")
     Return
  Endif
  tablename = BinaryPeekStr(tablenamebuf, 0, BinaryEodget(tablenamebuf))
  tables = StrCat(tables, tablename, @TAB, "(", tabletype, ")", @LF)
EndWhile
BinaryFree(tablenamebuf)

param4=askitemlist("Highlight Table and Click OK",tables,@LF,@UNSORTED,@SINGLE)

;Must Be a Legitimate Table
IF ( StrIndexNC( StrUpper(param4),"(TABLE)",1,@FWDSCAN) == 0 )
   Display(2,"SQL Operation Status","Invalid Table Selected")
   param4="NULL"
   Return
Endif

param4 = StrSub(param4,1,StrIndexNC(param4,@TAB,1,@FWDSCAN)-1)

qFreeStmt(hstmt1, 1)
drop(tables,tablename)
Return

:GetDSN
datasources = ""
For i = 1 To 999
   If i == 1
      direction = @qFirst
   Else
      direction = @qNext
   Endif
   datasource = qDataSources(henv, direction)
   If datasource == ""
      retcode = qLastCode()
      If retcode == @qNoData Then Break
         Message("Could Not Display ODBC Data Sources", retcode)
         Exit
   Endif
   datasources = StrCat(datasources, datasource, @LF)
Next

DataSource=askitemlist("Data sources",datasources,@lf,@UNSORTED,@SINGLE)


; ///////////////////////////////////////////////////////////////////
; // Take the Next Section Out if You Want To Allow Any DSN To Be  //
; // Selected,or modify to accept only what you want.              //
; ///////////////////////////////////////////////////////////////////
IF StrIndexNC( StrUpper(DataSource),".MDB",1,@FWDSCAN) == 0
   Message("DSN Selected...", "WAS NOT AN ACCESS DATABASE")
   param3="NULL"
   Return
Endif

;Strip off just the DSN Name
param3 = StrSub(DataSource,1,StrIndexNC(DataSource,@TAB,1,@FWDSCAN)-1)
drop(DataSource,dataSources,direction)
Return

FETCH.WBT 

; ///////////////////////////////////////////////////////////////////
; // Generic Winbatch Script To Issue a SELECT Statment and Bind   //
; // Columns.  The script contains its 'data' from INI entries     //
; // from a file created by the GetCols script.                    //
; //                                                               //
; // INI File looks like below                                     //
; //                                                               //
; // [Main]                        INI Section                     //
; // DSN=RESORTS                                                   //
; // Table=resorts                                                 //
; // SQL=SELECT * FROM resorts                                     //
; // Colnumber=8                   Number of Columns to Bind       //
; // ColName1=COMPANY              Column Name                     //
; // ColType1=SQL_VARCHAR          Column Type                     //
; // ColScale1=0                   Decimal Precision               //
; // ColPrecision1=35              Column Width                    //
; // AcceptNulls1=Yes                                              //
; //                                                               //
; // param1 = Full Path and File Name, for example                 //
; //          .\MyIni.Ini  for Current Directory                   //
; //                                                               //
; // param2 = INI Section to Create/Update DEFAULTS to [Main]      //
; //                                                               //
; //                                                               //
; // USAGE: fetch temp Main                                        //
; //                                                               //
; // OUTPUT: I chose to write data out to a comma-delimited text   //
; //         file, although you might normally insert into another //
; //         data source.                                          //
; //                                                               //
; //             Stan Littlefield, March 05, 2000                  //
; ///////////////////////////////////////////////////////////////////

AddExtender("wwodb34I.dll")

IF IsDefined(param2)==@NO  ; set to DEFAULT
   param2="Main"
Endif

IF IsDefined(param1)==@NO
   param1=AskFileName("Select INI File",".\","INI Files|*.ini|","*.ini",1)
Endif

IF ( StrIndexNC( StrUpper(param1),".INI",1,@FWDSCAN) == 0 )
   Display(2,"Cancelling Script","INI File Not Selected")
   Exit
Endif

cDSN   = IniReadPvt("%param2%","DSN","NULL","%param1%")
cTable = IniReadPvt("%param2%","Table","NULL","%param1%")
cSQL   = IniReadPvt("%param2%","SQL","NULL","%param1%")
nCols  = Int( IniReadPvt("%param2%","Colnumber","0","%param1%") )

BoxOpen("Fetching Data ",cSQL)


;general section to set up handles
henv = qAllocEnv()
If henv == -1
  retcode = qLastCode()  
  Message("Could Not Create ODBC Environment", retcode)
  Exit
Endif

hdbc = qAllocConnect(henv) 
If hdbc == -1
  retcode = qLastCode()
  Message("qAllocConnect failed", retcode)
  Exit
Endif

retcode = qConnect(hdbc, "%cDSN%", "", "")
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("Connection To Data Source Failed", retcode)
  Exit
Endif

hstmt = qAllocStmt(hdbc)
If hstmt != 0
  retcode = qLastCode()
  Message("qAllocStmt failed", retcode)
  Exit
Endif

; ///////////////////////////////////////////////////////////////////
; // Test If Query Will Return Valid Data prior to issuing         //
; // the SQL and Binding Columns                                   //
; ///////////////////////////////////////////////////////////////////
cSQL1 =strcat("SELECT COUNT(*) FROM ",ctable)
retcode = qExecDirect(hstmt,cSQL1)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
   Display(2,"Determining If Table Has Data","Unable To Execute SQL Command")
   Goto Cleanup
Else
   rc  = BinaryAlloc(20)
   retcode = qFetch(hstmt)
   retcode = qGetData(hstmt,1,"rc",20)
   n  = Int( BinaryPeekStr(rc,0,BinaryEodGet(rc)) )
   binaryfree(rc)
   IF n<1
      Display(2,"Abandoning Query","Will Produce Empty RowSet")
      gosub Cleanup
      exit
   Endif
Endif
drop(cSQL1,rc,n,retcode)
qFreeStmt(hstmt, 1)
hstmt = qAllocStmt(hdbc)


retcode = qExecDirect(hstmt,cSQL)

For i = 1 To nCols
   nBufLen    = Int( IniReadPvt("%param2%",strcat("ColPrecision",i),"100","%param1%") )
   cCol%i%    = BinaryAlloc(nBufLen)
   retcode    = qBindCol(hstmt,i,"cCol%i%",nBufLen)
Next


cOutPut = strcat(cTable,".TXT")
handle  = FileOpen("%cOutPut%","WRITE")
BoxText(strcat("Writing Data Out To ",cOutPut))


While @TRUE
   retcode = qFetch(hstmt)
   If retcode == @qNoData Then Break
      If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
         Message("qFetch failed", retcode)
         Exit
      Endif

   cOutPut =""
   For i = 1 To nCols
      cOutPut = strcat(cOutPut,BinaryPeekStr(cCol%i%, 0, BinaryEodget(cCol%i%)) )
      If i<nCols
         cOutPut = strcat(cOutPut,",")
      Endif
   Next
   FileWrite(handle,cOutPut)

EndWhile

For i = 1 To nCols
   BinaryFree(cCol%i%)
Next

FileClose(handle)


:Cleanup
qFreeStmt(hstmt, 1)
qDisconnect(hdbc)
qFreeConnect(hdbc)
qFreeEnv(henv)
BoxShut()

exit


Article ID:   W14542

Filename:   ODBC with Access.txt