Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: odbc access
Both work via an intermediary INI file which holds Column settings.
Hope these are helpful to others building ODBC apps with Winbatch.
; /////////////////////////////////////////////////////////////////// ; // 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
; /////////////////////////////////////////////////////////////////// ; // 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
File Created: 2001:11:05:13:12:14
Last Updated: 2001:11:05:13:12:14