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 simple example

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

  • PBWin ODBC simple example

    ODBC is powerful and flexible but can seem scary at first glance. But its really not that bad when you get used to it. I created some simple wrapper functions I use to make ODBC as simple as a few lines of code.

    Here is a simple example of using them: (note: the connection string can point to all kinds of databases like SQL servers, MS Access DB files, System DNS, etc)
    Code:
    sConnectString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=MyDB.mdb;"
    If DBconnect(sConnectString, hEnv, hConn, sError) then
       sSQL = "Select * FROM Table1 WHERE (((Table1.RecID)<100));"
       IF DBSQLSelect(hConn, sSQL, sHeader, sData, sError) THEN
          AddMyFile "MyReport.txt", sHeader
          AddMyFile "MyReport.txt", sData
       ELSE
          MSGBOX sError,,"Error on SELECT statement"
       END IF
       DBdisconnect hEnv, hConn   'disconnect from DB
    Else
      Msgbox sError,,"Error connecting"
    End if
    See ODBC is not that scary is it?

    Note: To use the following code you will need PB 9 and the include files from Jose Roca found here:
    http://www.jose.it-berater.org/smffo...hp?board=344.0

    For any questions please use this discussion thread:
    http://www.powerbasic.com/support/pb...ad.php?t=41467

    Here is the full example which includes the wrapper functions. Enjoy.

    Code:
    '==========================================================================
    '  ODBC_DB_Example.bas     by William Burns
    '  A simple example of using ODBC to connect to several different types of
    '  databases using the same functions.
    '
    '==========================================================================
    '#DEBUG DISPLAY ON         'unrem this statement for debuging
    #COMPILE EXE
    #DIM ALL
    
    #IF NOT %DEF(%WINAPI)
    #INCLUDE "WIN32API.INC"
    #ENDIF
    
    'the following 2 files can be found at http://www.jose.it-berater.org/smfforum/index.php?board=344.0
    #INCLUDE "Sql.inc"         'API declares created by Jose Roca
    #INCLUDE "Sqlext.inc"      'API declares created by Jose Roca
    
    
    '==========================================================================
    '   Main Application Entry Point
    '==========================================================================
    FUNCTION PBMAIN()
    #REGISTER NONE       'registered vars can cause problems on some DB functions
       LOCAL iUpdated       AS LONG
       LOCAL hEnv           AS DWORD
       LOCAL hConn          AS DWORD
       LOCAL sError         AS STRING
       LOCAL sHeader        AS STRING
       LOCAL sData          AS STRING
       LOCAL sConnectString AS STRING
       LOCAL sSQL           AS STRING
    
       sConnectString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=ScanResults.mdb;"
       'other examples:
       'MS Access DB example string "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\DataFolder\MyAccess.mdb;"
       'SQL server example1 = "DRIVER=SQL Server;DATABASE=MyDBname;Server=MyDBServerNameOrIp;UID=MyId;PWD=MyPass;"
       'SQL server example2 = "Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;"
       'PostgreSQL example string:  "DRIVER={PostgreSQL};DATABASE=MyDBname;Server=MyDBservername;UID=MyId;PWD=MyPass;"
    
       IF DBconnect(sConnectString, hEnv, hConn, sError) THEN
    
          'example of sending an update SQL action statment (same works for INSERT, DROP, DELETE etc)
          sSQL = "UPDATE Table1 SET Table1.OSver='Windows 7' WHERE (((Table1.PC_Name) Like 'WORKPCA4%'));"
          IF DBSQLExec(hConn, sSQL, iUpdated, sError) THEN
             MSGBOX "Updated " & STR$(iUpdated) & " records.",,"UPDATE Worked"
          ELSE
             MSGBOX sError,,"Error on UPDATE statement"
          END IF
    
          'example of a select statement returning the header row and all the data rows
          sSQL = "SELECT Table1.RecID, Table1.PC_Name, Table1.OSver FROM Table1 WHERE (((Table1.RecID)<10)) ORDER BY Table1.PC_Name;"
          IF DBSQLSelect(hConn, sSQL, sHeader, sData, sError) THEN
             AddMyFile "MyReport.txt", sHeader
             AddMyFile "MyReport.txt", sData
          ELSE
             MSGBOX sError,,"Error on SELECT statement"
          END IF
    
          DBdisconnect hEnv, hConn   'disconnect from DB
          MSGBOX "Done",,"Done"
       ELSE
          MSGBOX sError,,"Error connecting"
       END IF
    END FUNCTION
    '==========================================================================
    
    
    '==========================================================================
    '   This is just to make saving to a file easier
    '==========================================================================
    SUB AddMyFile(BYVAL sFileName AS STRING, BYVAL sText AS STRING)
       LOCAL iFile    AS LONG
       TRY
          iFile = FREEFILE
          OPEN sFileName FOR APPEND AS iFile
          PRINT #iFile, sText
          CLOSE iFile
       CATCH
          CLOSE iFile
       END TRY
    END SUB
    '==========================================================================
    
    
    
    'Here are some of my wrapper functions used:
    
    '==========================================================================
    ' DBaddErr() - Pulls error information about SQL
    '==========================================================================
    FUNCTION DBaddErr(hStmt AS DWORD, sError AS STRING) AS LONG
    #REGISTER NONE       'registered vars can cause problems on some DB functions
       LOCAL iLenRet  AS INTEGER
       LOCAL iRecNum  AS INTEGER
       LOCAL iErr     AS LONG
       LOCAL zState   AS ASCIIZ * %MAX_PATH
       LOCAL zMsg     AS ASCIIZ * %MAX_PATH
       iRecNum = 1
       CALL SQLGetDiagRec(%SQL_HANDLE_STMT, hStmt, iRecNum,zState,iErr,zMsg,%MAX_PATH,iLenRet)
       sError = sError + "Error " + FORMAT$(iErr) + "  " + TRIM$(zState) + " " + TRIM$(zMsg)
    END FUNCTION
    
    '==========================================================================
    ' DBconnect() - Connect to the database
    '==========================================================================
    FUNCTION DBconnect(sDB_connect_string AS STRING, hEnv AS DWORD, hConn AS DWORD, sError AS STRING) AS LONG
    #REGISTER NONE       'registered vars can cause problems on some DB functions
       LOCAL iBytes   AS INTEGER
       LOCAL zConnect AS ASCIIZ * 1000
       LOCAL zOut     AS ASCIIZ * 400
       IF SQLAllocHandle(%SQL_HANDLE_ENV,%SQL_NULL_HANDLE,hEnv) => %SQL_SUCCESS THEN  ' ret= %SQL_SUCCESS(0) %SQL_SUCCESS_WITH_INFO(1) %SQL_ERROR(-1) %SQL_INVALID_HANDLE(-2)
          IF SQLSetEnvAttr(hEnv,%SQL_ATTR_ODBC_VERSION,BYVAL %SQL_OV_ODBC3,%SQL_IS_INTEGER) => %SQL_SUCCESS THEN
             IF SQLAllocHandle(%SQL_HANDLE_DBC,hEnv,hConn) => %SQL_SUCCESS THEN
                zConnect = sDB_connect_string
                IF SQLDriverConnect(hConn, 0, zConnect, LEN(zConnect), zOut, 400, iBytes, %SQL_DRIVER_NOPROMPT) => %SQL_SUCCESS THEN
                   FUNCTION = %TRUE
                ELSE
                   sError = sError + "Function DBconnect -SQLDriverConnect() failed." + $CRLF
                   CALL SQLFreeHandle(%SQL_HANDLE_DBC,hConn)
                   CALL SQLFreeHandle(%SQL_HANDLE_ENV,hEnv)
                   hConn = 0
                   hEnv = 0
                END IF
             ELSE
                sError = sError + "Function DBconnect - SQLAllocHandle(%SQL_HANDLE_DBC) failed." + $CRLF
                CALL SQLFreeHandle(%SQL_HANDLE_ENV,hEnv)
                hEnv = 0
             END IF
          ELSE
             sError = sError + "Function DBconnect - SQLSetEnvAttr() failed." + $CRLF
             CALL SQLFreeHandle(%SQL_HANDLE_ENV,hEnv)
             hEnv = 0
          END IF
       ELSE
          sError = sError + "Function DBconnect - SQLAllocHandle(%SQL_HANDLE_ENV) failed." + $CRLF
       END IF
    END FUNCTION
    
    
    
    '==========================================================================
    ' DBdisconnect() - disconnect and free handles
    '==========================================================================
    SUB DBdisconnect(hEnv AS DWORD, hConn AS DWORD)
    #REGISTER NONE
       CALL SQLDisconnect(hConn)
       CALL SQLFreeHandle(%SQL_HANDLE_DBC, hConn)
       CALL SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
       hEnv = 0
       hConn = 0
    END SUB
    
    
    '==========================================================================
    'Run a INSERT, UPDATE or DELETE SQL query
    '==========================================================================
    FUNCTION DBSQLExec(hConn AS DWORD, BYVAL sSQL AS STRING, iUpdated AS LONG, sError AS STRING) AS LONG
    #REGISTER NONE 'registered vars seem to mess up some database items
       LOCAL iRet           AS LONG
       LOCAL hStmt          AS DWORD
       LOCAL zQuery         AS ASCIIZ * 2000
       iUpdated = 0
       zQuery = UCASE$(sSQL)
       IF SQLAllocHandle(%SQL_HANDLE_STMT, hConn, hStmt) => %SQL_SUCCESS THEN
          iRet = SQLExecDirect(hStmt,zQuery,%SQL_NTS)
          IF iRet < %SQL_SUCCESS THEN
             sError = sError + " DBSQLExec() Error SQLExecuteDirect - "
             DBaddErr(hStmt, sError)
          ELSE
             IF SQLRowCount(hStmt, iRet) => %SQL_SUCCESS THEN
                iUpdated = iRet
             END IF
             FUNCTION = %TRUE   'worked
          END IF
          CALL SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
       ELSE
          sError = sError + " DBSQLExec() Error SQLAllocHandle."
       END IF
    END FUNCTION
    '==========================================================================
    
    '==========================================================================
    'Run a SQL Select query
    ' sHeader will return a tab delimited header
    ' sData will return tab delimited rows (each row seperated by CrLf
    '==========================================================================
    FUNCTION DBSQLSelect(hConn AS DWORD, BYVAL sSQL AS STRING, sHeader AS STRING, sData AS STRING, sError AS STRING) AS LONG
    #REGISTER NONE 'registered vars seem to mess up some database items
       LOCAL iRet           AS LONG
       LOCAL iCount         AS LONG
       LOCAL iNumCols       AS INTEGER
       LOCAL iLen           AS INTEGER
       LOCAL iCol           AS LONG
       LOCAL iSize          AS LONG
       LOCAL hStmt          AS DWORD
       LOCAL zBuff          AS ASCIIZ * 265
       LOCAL zQuery         AS ASCIIZ * 2000
       sHeader = ""
       sData = ""
       zQuery = UCASE$(sSQL)
       IF SQLAllocHandle(%SQL_HANDLE_STMT, hConn, hStmt) => %SQL_SUCCESS THEN
          iRet = SQLExecDirect(hStmt,zQuery,%SQL_NTS)
          IF iRet < %SQL_SUCCESS THEN
             sError = sError + " DBSQLSelect() SQLExecuteDirect - "
             DBaddErr(hStmt, sError)
          ELSE
             'first get the number of columns in the result set
             CALL SQLNumResultCols(hStmt, iNumCols)
             IF iNumCols THEN
                'now get the field names for the column headers in our report
                FOR iCount = 1 TO iNumCols
                   iRet = SQLColAttribute(hStmt, iCount, %SQL_DESC_LABEL, BYVAL VARPTR(zBuff), BYVAL 265, iLen, BYVAL %NULL)
                   IF (iRet = %SQL_SUCCESS) OR (iRet = %SQL_SUCCESS_WITH_INFO) THEN
                      sHeader = sHeader & TRIM$(LEFT$(zBuff, iLen)) & $TAB
                   END IF
                NEXT iCount
    
                'now lets read each line from the result set
                DO 'loop to read each row
                   iRet = SQLFetch(hStmt) 'fetch a new row of data
                   IF (iRet = %SQL_SUCCESS) OR (iRet = %SQL_SUCCESS_WITH_INFO) THEN
                      FOR iCol = 1 TO iNumCols   'loop to read each column in each row
                         iRet =  SQLGetData(hStmt, iCol, %SQL_C_CHAR, BYVAL VARPTR(zBuff), BYVAL 265, iSize)
                         IF (iRet = %SQL_SUCCESS) OR (iRet = %SQL_SUCCESS_WITH_INFO) THEN
                            sData = sData & TRIM$(LEFT$(zBuff, iSize)) & $TAB
                         ELSEIF iRet = %SQL_NO_DATA THEN  'done so exit
                            EXIT LOOP
                         ELSE
                            sError = sError + " DBSQLSelect() Error " & STR$(iRet) & " reading - SQLGetData - "
                            DBaddErr(hStmt, sError)
                            EXIT LOOP
                         END IF
                      NEXT iCol
                      sData = TRIM$(sData, $TAB) & $CRLF     'add the row to our return buffer
                   ELSEIF iRet = %SQL_NO_DATA THEN
                      IF sData = "" THEN sError = sError + " DBSQLSelect() No Data returned for SQL: " & sSQL
                      EXIT LOOP
                   ELSE
                      sError = sError + " DBSQLSelect() Error " & STR$(iRet) & " reading - SQLFetch - "
                      DBaddErr(hStmt, sError)
                      EXIT LOOP
                   END IF
                LOOP
                sData = TRIM$(sData, ANY CHR$(9,10,13))   'remove last CrLf
                IF LEN(sData) THEN FUNCTION = %TRUE
             ELSE
                sError = sError + " DBSQLSelect() Num cols = 0 - "
                DBaddErr(hStmt, sError)
             END IF
          END IF
          CALL SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
       ELSE
          sError = sError + " DBSQLSelect() Error SQLAllocHandle."
       END IF
    END FUNCTION
    "I haven't lost my mind... its backed up on tape... I think??" :D
Working...
X