Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
Sample Code
- Create an SQL Server DSN
- DSN less connection
- Get Row Count From Resultset
- Get Rowset Size
- ODBC Example
- ODBC with Access
- Retrieve All Columns From the Last Row of the table
- To Obtain Information on Columns
- Use qGetData to Get all Records into an Array
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: To Obtain Information on Columns in a Table
SAMPLE CODE: To Obtain Information on Columns in a Table
; ///////////////////////////////////////////////////////////////////
; // 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 //
; // param3 = DSN //
; // param4 = Table Name //
; // //
; // 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, February 11, 2000 //
; ///////////////////////////////////////////////////////////////////
AddExtender("wwodb34I.dll")
IF IsDefined(param1)==@NO ;not the greatest error checking
message("Parameters Missing","Please Supply INIFile,Section,DSN,Table")
exit
Endif
BoxOpen("ODBC Inquiry.....","Gathering Column Information for Table:%param4%")
;create of write to INI File
IniFile = strcat(".\","%param1%",".ini")
IniWritePvt("%param2%","DSN","%param3%",IniFile)
IniWritePvt("%param2%","Table","%param4%",IniFile)
;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
;Now, connect to DSN
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
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)
;here is a commented version of the upcoming dllcall()
;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)
message("Column Type %i%",n)
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)
message("Null Values for %i%",n)
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
Article ID: W14333
Filename: To Obtain Information on Columns.txt
File Created: 2001:03:01:15:43:14
Last Updated: 2001:03:01:15:43:14