IT-Consultant: José Roca (PBWIN 10+/PBCC 6+) (Archive only) > Windows API Programming

CODBC Class Examples

(1/4) > >>

José Roca:
 
COdbc is a wrapper class on top of ODBC. You must create only one instance of this class in your application because it automatically creates the environment handle and only one ODBC environment handle should be used at any time.

This is not limiting in any way because you can open multiple connections if needed and each connection can allocate multiple statement handles.

Include files:
 
COdbc.inc

Allocates the environment handle and allows to create connection objects. The name of the class is COdbc and the name of the interface IOdbc.
 
COdbcDbc.inc

Implements methods to manage connection objects and allows to allocate statement and descriptor objects. The name of the class is COdbcConnection and the name of the interface IOdbcConnection.
 
COdbcDesc.inc

Implements methods to manage descriptor objects. The name of the class is COdbcDescriptor and the name of the interface IOdbcDescriptor.
 
COdbcStmt.inc

Implements methods to manage statement objects. The name of the class is COdbcStatement and the name of the interface IOdbcStatement.

The main purpose of the class if to ease the use of the ODBC API, that is notoriously difficult to use.

You create an instance of the class using:

DIM pOdbc AS IOdbc
pOdbc = NewOdbc(%SQL_OV_ODBC3_80)
or %SQL_OV_ODBC3 if you aren't using a 3.8 ODBC driver


To create a connection, you call the Connection method of the IOdbc interface:

DIM pCon AS IOdbcConnection
pCon = pOdbc.Connection("Connection1")


You open a connection to a database using the OpenDatabase method with the appropriate connection string, e.g.:

DIM ConStr AS STRING
ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;"
pCon.OpenDatabase(ConStr)

And you create an statement object calling the Statement method of the IOdbcConnection interface.

DIM pStmt AS IOdbcStatement
pStmt = pStmt = pCon.Statement("Statement1")


When the class is destroyed, its Destroy method closes cursors and connections and frees all the handles that have been allocated using the methods provided by the class. Therefore, is not needed to explicitly close the database and free handles. However, in complex applications, you may need to free resources, to disconnect the connection (using the Disconnect method) to reconnect it with different attributes (using the Connect, DriverConnect or BrowseConnect methods), or to close the database (using the CloseDatabase method).

To remove a Connection object, you call the RemoveConnection method of the IOdbc interface:

pOdbc.RemoveConnection("Connection1")

To remove a Descriptor object, you call the RemoveDescriptor method of the IOdbcConnection interface:

pOdbc.RemoveConnection("Descriptor1")

To remove an Statement object, you call the RemoveStatement method of the IOdbcConnection interface:

pOdbc.RemoveConnection("Statement1")

Structured Error Handling

The methods and properties throw an exception when the result of an operation is SQL_ERROR or SQL_INVALID_HANDLE, allowing to be used between TRY / CATCH / END TRY blocks, and rich error information is returned using the standard COM error info interfaces. To retrieve it, call the OdbcOleErrorInfo wrapper function.

José Roca:

--- Code: ---' ########################################################################################
' Microsoft Windows
' File: CODBCEX_AddRecord.bas
' Contents: CODBC class example
' Demonstrates the use of the AddRecord method.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC  -  Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CODBC.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pOdbc AS IOdbc
   pOdbc = NewOdbc(%SQL_OV_ODBC3_80)
   IF ISNOTHING(pOdbc) THEN EXIT FUNCTION

   TRY
      ' // Create a connection object
      LOCAL pCon AS IOdbcConnection
      pCon = pOdbc.Connection("Connection1")
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement object
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Bind the columns
      ' // Note: If you want to use local variables, make sure they aren't register variables using #REGISTER NONE
      STATIC lAuId, cbAuId AS LONG
      pStmt.BindColToLong(1, lAuId, cbAuId)
      STATIC szAuthor AS ASCIIZ * 256, cbAuthor AS LONG
      pStmt.BindColToString(2, szAuthor, SIZEOF(szAuthor), cbAuthor)
      STATIC iYearBorn AS INTEGER, cbYearBorn AS LONG
      pStmt.BindColToInteger(3, iYearBorn, cbYearBorn)
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Authors ORDER BY Author")
      ' // Fill the values of the bounded application variables and its sizes
      lAuId     = 999               : cbAuID     = SIZEOF(lAuId)
      szAuthor  = "Edgar Allan Poe" : cbAuthor   = LEN(szAuthor)
      iYearBorn = 1809              : cbYearBorn = SIZEOF(iYearBorn)
      ' // Add the record
      pStmt.AddRecord
      STDOUT "Record added"
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:

--- Code: ---' ########################################################################################
' Microsoft Windows
' File: CODBCEX_BasicSteps.bas
' Contents: CODBC class example
' Demonstrates the basic steps to use the CODBC class to connect to a database, execute a
' query and fetch the results.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC  -  Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CODBC.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pOdbc AS IOdbc
   pOdbc = NewOdbc(%SQL_OV_ODBC3_80)
   IF ISNOTHING(pOdbc) THEN EXIT FUNCTION

   TRY
      ' // Create a connection object
      LOCAL pCon AS IOdbcConnection
      pCon = pOdbc.Connection("Connection1")
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement object
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Authors ORDER BY Author")
      ' // Parse the result set
      LOCAL strOutput AS STRING
      DO
         ' // Fetch the record
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         ' // Get the values of the columns and display them
         strOutput = ""
         strOutput += pStmt.GetDataString(1) & " "
         strOutput += pStmt.GetDataString(2) & " "
         strOutput += pStmt.GetDataString(3)
         STDOUT strOutput
         ' // Note: Instead of retrieving the data by ordinal,
         ' // you can also do it by column name.
'         strOutput = ""
'         strOutput += pStmt.GetDataString("Au_ID") & " "
'         strOutput += pStmt.GetDataString("Author") & " "
'         strOutput += pStmt.GetDataString("Year Born")
'         STDOUT strOutput
      LOOP
   CATCH
      ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:

--- Code: ---' ########################################################################################
' Microsoft Windows
' File: CODBCEX_BindCol.bas
' Contents: CODBC class example
' Demonstrates the use of the BindCol methods.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC  -  Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CODBC.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pOdbc AS IOdbc
   pOdbc = NewOdbc(%SQL_OV_ODBC3_80)
   IF ISNOTHING(pOdbc) THEN EXIT FUNCTION

   TRY
      ' // Create a connection object
      LOCAL pCon AS IOdbcConnection
      pCon = pOdbc.Connection("Connection1")
      IF ISNOTHING(pCon) THEN EXIT FUNCTION
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement object
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Bind the columns
      ' // Note: If you want to use local variables, make sure they aren't register variables using #REGISTER NONE
      STATIC lAuId, cbAuId AS LONG
      pStmt.BindColToLong(1, lAuId, cbAuId)
      STATIC szAuthor AS ASCIIZ * 256, cbAuthor AS LONG
      pStmt.BindColToString(2, szAuthor, SIZEOF(szAuthor), cbAuthor)
      STATIC iYearBorn AS INTEGER, cbYearBorn AS LONG
      pStmt.BindColToInteger(3, iYearBorn, cbYearBorn)
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Authors ORDER BY Author")
      ' // Parse the result set
      DO
         ' // Fetch the record
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         ' // Get the values of the columns and display them
         PRINT lAuId " ";
         PRINT szAuthor " ";
         PRINT iYearBorn
      LOOP
   CATCH
      ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

José Roca:

--- Code: ---' ########################################################################################
' Microsoft Windows
' File: CODBCEX_ColAttribute.bas
' Contents: CODBC class example
' Demonstrates the use of the ColName property.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

' CSED_PBCC  -  Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "CODBC.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   ' // Create an instance of the class
   LOCAL pOdbc AS IOdbc
   pOdbc = NewOdbc(%SQL_OV_ODBC3_80)
   IF ISNOTHING(pOdbc) THEN EXIT FUNCTION

   TRY
      ' // Create a connection object
      LOCAL pCon AS IOdbcConnection
      pCon = pOdbc.Connection("Connection1")
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement object
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Authors ORDER BY Author")
      ' // Retrieve the number of columns
      LOCAL numCols AS INTEGER
      numCols = pStmt.NumResultCols
      STDOUT "Number of columns:" & STR$(numCols)
      IF numCols = 0 THEN
         WAITKEY$
         EXIT FUNCTION
      END IF
      ' // Retrieve the names of the fields (columns)
      LOCAL idx AS INTEGER
      FOR idx = 1 TO numCols
         STDOUT "Field #" & FORMAT$(idx) & " name: " & pStmt.ColName(idx)
      NEXT
      ' // Parse the result set
      DO
         ' // Fetch the record
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         ' // Get the values of the columns and display them
         FOR idx = 1 TO numCols
            STDOUT pStmt.GetDataString(idx)
         NEXT
      LOOP
   CATCH
      ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================

--- End code ---

Navigation

[0] Message Index

[#] Next page