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
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    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:
    Login
    http://www.jose.it-berater.org/smfforum/index.php?board=344.0
    Login


    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
    Tags: database, odbc, sql, wrapper function
Previous template Next
Working...
X

We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, and to analyze site activity. For additional details, refer to our Privacy Policy.

By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also acknowledge that this forum may be hosted outside your country and you consent to the collection, storage, and processing of your data in the country where this forum is hosted.

I Agree