Prb: PBDLL and ODBC

[Philippe PETIT]

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

[Florent Heyworth]

Use UNIONs for this kind of work - either dynamically allocated
or DIMmed. Note that if you only want char values SqlBind
the columns as SQL_C_CHAR and you'll get the CHAR conversion -
otherwise a UNION is the way to go.

Cheers

Florent

------------------

[Michael Mattias]

Here's some code I used with a UNION

(NOTE THIS IS IN PIECES AND WILL NOT COMPILE 'AS IS')

    ' data for use with SQLGetInfo
    UNION InfoValueUnion
        udw      AS DWORD
        uInteger AS INTEGER
        psz      AS ASCIIZ  PTR
        uLong    AS LONG
    END UNION

    .....
    LOCAL InfoValue  AS InfoValueUnion    ' pointer to either a numeric or character buffer, depending on infotype
    ...
    ret = SQLGetInfo(hDBC, %SQL_TXN_CAPABLE, BYVAL VARPTR(InfoValue), SIZEOF(InfoValue.uInteger), StringLength)
        MSGBOX "SQLGetInfo Status =" & STR$(ret)
       SELECT CASE ret
         CASE %SQL_SUCCESS, %SQL_SUCCESS_WITH_INFO
            ' For SQL_TXN_CAPABLE, InfoValueptr points to an SQLSMALLINT
            SELECT CASE InfoValue.uInteger    
                   CASE %SQL_TC_NONE
                       MSGBOX "SQL_TC_NONE"
                   CASE %SQL_TC_DML
                       MSGBOX "SQL_TC_DML"
                   CASE %SQL_TC_DDL_COMMIT
                       MSGBOX "SQL_TC_DDL_COMMIT"
                   CASE %SQL_TC_DDL_IGNORE
                       MSGBOX "SQL_TC_DDL_IGNORE
                   CASE %SQL_TC_ALL
                       MSGBOX "SQL_TC_ALL"    '<<< WHAT THE MS ACCESS DRIVER 4.0 SUPPORTS 5/04/01
                   CASE ELSE
                       MSGBOX "OTHER, value =" & STR$(InfoValue.uinteger)
            END SELECT
         CASE ELSE      ' bad error!
            ret=SQLGetDiagRec (%SQL_HANDLE_DBC,_
                          hDBC, _
                          SQLRecNo, _
                          SQLState,_
                          NativeErrorCode,_
                          SqlStateMessage, _
                          SqlStateMessageSize,_
                          SqlActualMessageSize)
            MSGBOX "SQLState=" & SQLState & $CRLF & SqlStateMessage
      END SELECT
   END IF

MCM

[Patrice Terrier]

Salut Philippe,

Il y a bien longtemps qu'on ne t'a pas vu sur ce forum,
que deviens tu?



------------------
Patrice Terrier
mailto[email protected][email protected]</A>

[Philippe PETIT]

Do you Have an ex with SQLGetData ?

Thank in avance


------------------
Bien à vous
Philippe
www.devparadise.com