Announcement

Collapse

Forum Guidelines

This forum is for finished source code that is working properly. If you have questions about this or any other source code, please post it in one of the Discussion Forums, not here.
See more
See less

ODBC Sample

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

  • ODBC Sample

    Hi there,

    source below shows how to open and read from a access database via ODBC. The Sql include files are form PB ftp site.

    Best rgds
    Ralph

    Code:
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' file : access.bas
    ' open and read a access table
    ' sql32.inc & sqlext32.inc are from pb's ftp server (file: odbc30.zip)
    
    $COMPILE EXE
    $DIM ALL
    $INCLUDE  "WIN32API.INC"
    $INCLUDE  "SQL32.INC"
    $INCLUDE  "SQLEXT32.INC"
    
    
    FUNCTION PBMAIN()
        '' ODBC Session
        LOCAL a_hEnv AS DWORD
        LOCAL a_hDBC AS DWORD
        LOCAL hstmt  AS DWORD
        LOCAL ret AS LONG
        
        '' Data Fields
        LOCAL field1   AS ASCIIZ * 7
        LOCAL field2   AS ASCIIZ * 26
        LOCAL cbValue1 AS LONG
        LOCAL cbValue2 AS LONG
    
    ' 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 = SQLConnect(a_hDBC, "ATIME", %SQL_NTS, "Admin", %SQL_NTS, "", %SQL_NTS)
    
    ' Create a statement to select data
        Ret = SQLAllocStmt(a_hDBC, hstmt)
        ret = SQLExecDirect(hstmt, "Select Nr, Name FROM abteilung" , %SQL_NTS)
        IF ret = %SQL_ERROR OR _
           ret = %SQL_SUCCESS_WITH_INFO THEN MSGBOX STR$(hstmt),0,"Error in SQLExecDirect"
        
    ' Bind the parameters for the select query
        Ret = SQLBindCol(hstmt, 1, %SQL_C_CHAR, field1, SIZEOF(field1), cbValue1 )
        Ret = SQLBindCol(hstmt, 2, %SQL_C_CHAR, field2, SIZEOF(field2), cbValue2 )
        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
        Ret = SQLFetch(hstmt)
        IF ret = %SQL_ERROR OR ret = %SQL_SUCCESS_WITH_INFO THEN
            MSGBOX STR$(Ret),0,"Error in SQLFetch"
        ELSE
            MSGBOX "Field 1 : " & field1 & $CRLF & _
                   "Field 2 : " & field2, _
                   0, "SQLFetch got data"
        END IF
    
    ' 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


  • #2
    Thank you! More!

    Regards
    Peter

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

    Comment


    • #3
      Peter --

      sorry i've no more ODBC samples for Access.
      But i have some ODBC DB2 samples to connect to a AS400.

      Shall i post them ?

      Ralph

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

      Comment


      • #4
        Yes, Please. I'm interested in any example.

        Regards
        Peter

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

        Comment


        • #5
          I'd be *VERY* interested in taking a look at those...



          Scott

          ------------------
          Scott
          mailto:[email protected][email protected]</A>
          Scott Turchin
          MCSE, MCP+I
          http://www.tngbbs.com
          ----------------------
          True Karate-do is this: that in daily life, one's mind and body be trained and developed in a spirit of humility; and that in critical times, one be devoted utterly to the cause of justice. -Gichin Funakoshi

          Comment


          • #6
            Ok !

            Try this snippet :

            Code:
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' file : db2_test.bas
            ' open, R/W and close db2
            ' sql32.inc & sqlext32.inc are from pb's ftp server (file: odbc30.zip)
            ' based on IBM's db2 sample
            
            $COMPILE EXE
            $DIM ALL
            $INCLUDE  "WIN32API.INC"
            $INCLUDE  "SQL32.INC"
            $INCLUDE  "SQLEXT32.INC"
            
            
            FUNCTION PBMAIN()
                DIM a_hEnv AS DWORD
                DIM a_hDBC AS DWORD
                DIM s_District AS DWORD
                DIM u_District AS DWORD
            
                DIM aToken AS LONG
                DIM Ret AS INTEGER
            
                DIM s_parm1 AS ASCIIZ * 256
                DIM s_parm2 AS INTEGER
                DIM s_parm3 AS ASCIIZ * 256
                DIM s_parm4 AS INTEGER
                DIM aDTax AS SINGLE
                DIM aNextOrder AS INTEGER
                DIM bNextOrder AS INTEGER
                DIM cbValue1 AS LONG
                DIM cbValue2 AS LONG
                DIM cbValue3 AS LONG
                DIM cbValue4 AS LONG
                DIM cbValue5 AS LONG
                DIM i AS INTEGER
            
            ' Variables that hold the length of the parameters
                cbValue1 = %SQL_NTS
                cbValue2 = 0
                cbValue3 = %SQL_NTS
                cbValue4 = 0
                cbValue5 = 0
            
            ' Connect to an AS/400
                Ret = SQLAllocEnv(a_hEnv)                      ' Allocates the SQL environment
                Ret = SQLAllocConnect(a_hEnv, a_hDBC)          ' Allocates connection
                Ret = SQLConnect(a_hDBC, "BANANA", %SQL_NTS, "SPEED", %SQL_NTS, "SPEED2", %SQL_NTS)
            
            ' Create a prepared statement to select data
                Ret = SQLAllocStmt(a_hDBC, s_District)
                Ret = SQLSetStmtOption(s_District, %SQL_CONCURRENCY, %SQL_CONCUR_READ_ONLY)
                Ret = SQLPrepare(s_District, "Select DTAX, DNXTOR from QUSER.DSTRCT where (DWID=? and DID=?)", %SQL_NTS)
            
            ' Bind the parameters for the select query
                Ret = SQLBindParameter(s_District, 1, %SQL_PARAM_INPUT, %SQL_C_CHAR, %SQL_CHAR, 4, 0, s_parm1, 0, cbValue1)
                Ret = SQLBindParameter(s_District, 2, %SQL_PARAM_INPUT, %SQL_C_SSHORT, %SQL_INTEGER, 3, 0, s_parm2, 0, cbValue2)
                Ret = SQLBindCol(s_District, 1, %SQL_C_FLOAT, aDTax, 0, 0&)
                Ret = SQLBindCol(s_District, 2, %SQL_C_SSHORT, aNextOrder, 0, 0&)
            
            ' Create a prepared statement to update data
                Ret = SQLAllocStmt(a_hDBC, u_District)
                Ret = SQLPrepare(u_District, "Update DSTRCT set DNXTOR=? where (DWID=? and DID=?)" , %SQL_NTS)
            ' Bind the parameters for the Update query
                Ret = SQLBindParameter(u_District, 1, %SQL_PARAM_INPUT, %SQL_C_SSHORT, %SQL_INTEGER, 0, 0, bNextOrder, 0, cbValue5)
                Ret = SQLBindParameter(u_District, 2, %SQL_PARAM_INPUT, %SQL_C_CHAR, %SQL_CHAR, 4, 0, s_parm3, 0, cbValue3)
                Ret = SQLBindParameter(u_District, 3, %SQL_PARAM_INPUT, %SQL_C_SSHORT, %SQL_INTEGER, 3, 0, s_parm4, 0, cbValue4)
            
            
            ' This loop will increment a field in ten different rows
                FOR i = 1 TO 10
            
            ' Set the parameters
                    s_parm2 = i     ' District
                    s_parm4 = i     ' District
            
            ' Execute the select query.
                    Ret = SQLExecute(s_District)
            ' Fetch results.
                    Ret = SQLFetch(s_District)
                    Ret = SQLFreeStmt(s_District, %SQL_CLOSE)
            
            ' Do some processing of this row...
            
            ' Then increment the field
                    bNextOrder = aNextOrder + 1
            
            ' Execute the update
                    Ret = SQLExecute(u_District)
                    Ret = SQLFreeStmt(u_District, %SQL_CLOSE)
                NEXT
            
            ' Free the ODBC resources
                Ret = SQLFreeStmt(s_District, %SQL_DROP)
                Ret = SQLFreeStmt(u_District, %SQL_DROP)
                Ret = SQLDisconnect(a_hDBC)        ' Disconnecting and deallocating.
                Ret = SQLFreeConnect(a_hDBC)
                Ret = SQLFreeEnv(a_hEnv)
                
            END FUNCTION
            ------------------

            Comment

            Working...
            X