How can I create DATA TYPE on the fly :
$COMPILE EXE
$INCLUDE "WIN32API.INC"
$INCLUDE "SQL32.INC"
$INCLUDE "SQLEXT32.INC"
Type ColumnDescription
ColumnName AS String * 255
DataType AS INTEGER
ColumnSize AS DWORD
DecimalDigits AS INTEGER
Nullable AS INTEGER
End Type
FUNCTION MAIN() as long
'' ODBC Session
Dim a_hEnv AS DWORD
Dim a_hDBC AS DWORD
Dim hstmt AS DWORD
Dim ret AS LONG
'' Data Fields
Dim field1 AS ASCIIZ * 255
Dim field2 AS ASCIIZ * 255
Dim ConnStrOut AS ASCIIZ * 255
Dim ConnStrOutLen as integer
Dim cbValue1 AS LONG
Dim cbValue2 AS LONG
Dim Nbcol as integer
Dim DSN as String
Dim ColName as ASCIIZ * 255
Dim i as integer
Dim NameLength as integer
Dim MaxFieldLength as DWORD
Dim msg as String
DSN="DSN=MyDSN;UID=root;PWD=;"
' Variables that hold the length of the parameters
cbValue1 = %SQL_NTS
cbValue2 = %SQL_NTS
' Connect via ODBC
Ret = SQLAllocEnv(a_hEnv) ' Allocates the SQL environment
Ret = SQLAllocConnect(a_hEnv, a_hDBC) ' Allocates connection
Ret = SQLDriverConnect(a_hDBC,byval %NULL,bycopy DSN, %SQL_NTS, ConnStrOut, sizeof(ConnStrOut), ConnStrOutLen, %SQL_DRIVER_NOPROMPT)
' Create a statement to select data
Ret = SQLAllocStmt(a_hDBC, hstmt)
ret = SQLExecDirect(hstmt, "Select * FROM MyTable" , %SQL_NTS)
IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN MSGBOX STR$(hstmt),0,"Error in SQLExecDirect"
ret = SQLNumResultCols(hstmt,Nbcol)
Dim field(Nbcol) as ColumnDescription
Dim cbValue(Nbcol) as long
For i=1 to Nbcol
cbValue(i) = %SQL_NTS
ret = SQLDescribeCol(hstmt,i,ColName,sizeof(ColName), NameLength, field(i).DataType, field(i).ColumnSize, field(i).DecimalDigits, field(i).Nullable)
field(i).ColumnName =trim$(ColName)
' OK I have my Column Name
MsgBox str$(i) & " " & field(i).ColumnName,0,"col"
Next i
' problem : create datatype on the fly
ReDim FieldValue(Nbcol) as asciiz * 65000
For i=1 to Nbcol
Ret = SQLBindCol(hstmt, i, field(i).DataType, FieldValue(i), SIZEOF(FieldValue(i)), cbValue(i))
Next i
' Bind the parameters for the select query
IF ret = %SQL_ERROR OR Ret = %SQL_SUCCESS_WITH_INFO THEN MSGBOX STR$(Ret),0,"Error in SQLBindCol"
' Read a record and set bookmark to next one
While SQLFetch(hstmt)= %SQL_SUCCESS
msg=""
For i=1 to Nbcol
msg=msg & trim$(field(i).ColumnName) & " : " & trim$(FieldValue(i)) & chr$(13,10)
Next i
' OK for text field, other NO !!! Data isn't good format
MSGBOX msg, 0, "SQLFetch got data"
loop
' Free the ODBC resources
Ret = SQLFreeStmt(hstmt, %SQL_DROP)
Ret = SQLDisconnect(a_hDBC) ' Disconnecting and deallocating.
Ret = SQLFreeConnect(a_hDBC)
Ret = SQLFreeEnv(a_hEnv)
END FUNCTION
------------------
Bien à vous
Philippe
www.devparadise.com
$COMPILE EXE
$INCLUDE "WIN32API.INC"
$INCLUDE "SQL32.INC"
$INCLUDE "SQLEXT32.INC"
Type ColumnDescription
ColumnName AS String * 255
DataType AS INTEGER
ColumnSize AS DWORD
DecimalDigits AS INTEGER
Nullable AS INTEGER
End Type
FUNCTION MAIN() as long
'' ODBC Session
Dim a_hEnv AS DWORD
Dim a_hDBC AS DWORD
Dim hstmt AS DWORD
Dim ret AS LONG
'' Data Fields
Dim field1 AS ASCIIZ * 255
Dim field2 AS ASCIIZ * 255
Dim ConnStrOut AS ASCIIZ * 255
Dim ConnStrOutLen as integer
Dim cbValue1 AS LONG
Dim cbValue2 AS LONG
Dim Nbcol as integer
Dim DSN as String
Dim ColName as ASCIIZ * 255
Dim i as integer
Dim NameLength as integer
Dim MaxFieldLength as DWORD
Dim msg as String
DSN="DSN=MyDSN;UID=root;PWD=;"
' Variables that hold the length of the parameters
cbValue1 = %SQL_NTS
cbValue2 = %SQL_NTS
' Connect via ODBC
Ret = SQLAllocEnv(a_hEnv) ' Allocates the SQL environment
Ret = SQLAllocConnect(a_hEnv, a_hDBC) ' Allocates connection
Ret = SQLDriverConnect(a_hDBC,byval %NULL,bycopy DSN, %SQL_NTS, ConnStrOut, sizeof(ConnStrOut), ConnStrOutLen, %SQL_DRIVER_NOPROMPT)
' Create a statement to select data
Ret = SQLAllocStmt(a_hDBC, hstmt)
ret = SQLExecDirect(hstmt, "Select * FROM MyTable" , %SQL_NTS)
IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN MSGBOX STR$(hstmt),0,"Error in SQLExecDirect"
ret = SQLNumResultCols(hstmt,Nbcol)
Dim field(Nbcol) as ColumnDescription
Dim cbValue(Nbcol) as long
For i=1 to Nbcol
cbValue(i) = %SQL_NTS
ret = SQLDescribeCol(hstmt,i,ColName,sizeof(ColName), NameLength, field(i).DataType, field(i).ColumnSize, field(i).DecimalDigits, field(i).Nullable)
field(i).ColumnName =trim$(ColName)
' OK I have my Column Name
MsgBox str$(i) & " " & field(i).ColumnName,0,"col"
Next i
' problem : create datatype on the fly
ReDim FieldValue(Nbcol) as asciiz * 65000
For i=1 to Nbcol
Ret = SQLBindCol(hstmt, i, field(i).DataType, FieldValue(i), SIZEOF(FieldValue(i)), cbValue(i))
Next i
' Bind the parameters for the select query
IF ret = %SQL_ERROR OR Ret = %SQL_SUCCESS_WITH_INFO THEN MSGBOX STR$(Ret),0,"Error in SQLBindCol"
' Read a record and set bookmark to next one
While SQLFetch(hstmt)= %SQL_SUCCESS
msg=""
For i=1 to Nbcol
msg=msg & trim$(field(i).ColumnName) & " : " & trim$(FieldValue(i)) & chr$(13,10)
Next i
' OK for text field, other NO !!! Data isn't good format
MSGBOX msg, 0, "SQLFetch got data"
loop
' Free the ODBC resources
Ret = SQLFreeStmt(hstmt, %SQL_DROP)
Ret = SQLDisconnect(a_hDBC) ' Disconnecting and deallocating.
Ret = SQLFreeConnect(a_hDBC)
Ret = SQLFreeEnv(a_hEnv)
END FUNCTION
------------------
Bien à vous
Philippe
www.devparadise.com
Comment