Announcement

Collapse
No announcement yet.

Prb: PBDLL and ODBC

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Prb: PBDLL and ODBC

    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

  • #2
    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

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

    Comment


    • #3
      Here's some code I used with a UNION

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


      Code:
          ' 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



      Michael Mattias
      Tal Systems Inc. (retired)
      Racine WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        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>
        Patrice Terrier
        www.zapsolution.com
        www.objreader.com
        Addons: GDImage.DLL 32/64-bit (Graphic library), WinLIFT.DLL 32/64-bit (Skin Engine).

        Comment


        • #5
          Do you Have an ex with SQLGetData ?

          Thank in avance


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

          Comment

          Working...
          X