CODBC Class Examples

[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: [Select]

' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code: [Select]

' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code: [Select]

' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code: [Select]

' ########################################################################################
' 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
' ========================================================================================


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Columns.bas
' Contents: CODBC class example
' Demonstrates the use of the Columns 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"

' ========================================================================================
' Displays the data
' ========================================================================================
SUB ShowColumnsData (BYVAL pCon AS IOdbcConnection, BYREF szInTableName AS ASCIIZ)

   #REGISTER NONE   ' // Binded variables can't be register variables

   LOCAL cbbytes AS LONG
   LOCAL szCatalogName AS ASCIIZ * 256     '  1. Catalog name
   LOCAL szSchemaName AS ASCIIZ * 256      '  2. Schema name
   LOCAL szTableName AS ASCIIZ * 129       '  3. Table name
   LOCAL szColumnName AS ASCIIZ * 129      '  4. Column name
   LOCAL iDataType AS INTEGER              '  5. SQL data type
   LOCAL szTypeName AS ASCIIZ * 129        '  6. Data-source dependent data type
   LOCAL lColumnSize AS LONG               '  7. Column size
   LOCAL lBufferLength AS LONG             '  8. Length in bytes of data transferred
   LOCAL iDecimalDigits AS INTEGER         '  9. Decimal digits
   LOCAL iNumPrecRadix AS INTEGER          ' 10. Numeric precision radix
   LOCAL iNullable AS INTEGER              ' 11. Indicates with certainty if a column can accept nulls
   LOCAL szRemarks AS ASCIIZ * 256         ' 12. A description of the column
   LOCAL szColumnDefault AS ASCIIZ * 129   ' 13. Default value of the column
   LOCAL iSQLDataType AS INTEGER           ' 14. SQL data type as it appears in the SQL_DESC_TYPE record field in the IRD
   LOCAL iDatetimeSubtypeCode AS INTEGER   ' 15. The subtype code for datetime and interval data types
   LOCAL lCharOctetLength AS LONG          ' 16. The maximun length in bytes of a character or binary data type
   LOCAL lOrdinalPosition AS LONG          ' 17. The ordinal position of the column in the table
   LOCAL szIsNullable AS ASCIIZ * 4        ' 18. Indicates with certainty if a column cannot accept nulls

   IF ISNOTHING(pCon) THEN EXIT SUB

   ' // Allocate an statement handle
   LOCAL pStmt AS IOdbcStatement
   pStmt = pCon.Statement("Statement1")
   IF ISNOTHING(pStmt) THEN EXIT SUB

   TRY
      pStmt.Columns("", "", szInTableName, "")
      pStmt.BindColToString ( 1, szCatalogName, SIZEOF(szCatalogName), cbBytes)
      pStmt.BindColToString ( 2, szSchemaName, SIZEOF(szSchemaName), cbbytes)
      pStmt.BindColToString ( 3, szTableName, SIZEOF(szTableName), cbbytes)
      pStmt.BindColToString ( 4, szColumnName, SIZEOF(szColumnName), cbbytes)
      pStmt.BindColToInteger( 5, iDataType, cbbytes)
      pStmt.BindColToString ( 6, szTypeName, SIZEOF(szTypeName), cbbytes)
      pStmt.BindColToLong   ( 7, lColumnSize, cbbytes)
      pStmt.BindColToLong   ( 8, lBufferLength, cbbytes)
      pStmt.BindColToInteger( 9, iDecimalDigits, cbbytes)
      pStmt.BindColToInteger(10, iNumPrecRadix, cbbytes)
      pStmt.BindColToInteger(11, iNullable, cbbytes)
      pStmt.BindColToString (12, szRemarks, SIZEOF(szRemarks), cbbytes)
      pStmt.BindColToString (13, szColumnDefault, SIZEOF(szColumnDefault), cbbytes)
      pStmt.BindColToInteger(14, iSQLDataType, cbbytes)
      pStmt.BindColToInteger(15, iDatetimeSubtypeCode, cbbytes)
      pStmt.BindColToLong   (16, lCharOctetLength, cbbytes)
      pStmt.BindColToLong   (17, lOrdinalPosition, cbbytes)
      pStmt.BindColToString (18, szIsNullable, SIZEOF(szIsNullable), cbbytes)
      ' // 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 "----------------------------------"
         PRINT "Catalog name: " szCatalogName
         PRINT "Schema name: " szSchemaName
         PRINT "Table name: " szTableName
         PRINT "Column name " szColumnName
         PRINT "Data type: " iDataType
         PRINT "Type name: " szTypeName
         PRINT "Column size: " lColumnSize
         PRINT "Buffer length: " lBufferLength
         PRINT "Decimal digits: " iDecimalDigits
         PRINT "Numeric precision radix: " iNumPrecRadix
         PRINT "Can accept nulls: " iNullable
         PRINT "Remarks: " szRemarks
         PRINT "Column default: " szColumnDefault
         PRINT "IRD SQL data type: " iSqlDataType
         PRINT "Datetime subtype code: " iDateTimeSubtypeCOde
         PRINT "Character octet length: " lCharOctetLength
         PRINT "Ordinal position: " lOrdinalPosition
         PRINT "Cannot accept nulls: " szIsNullable
         PRINT "----------------------------------"
         WAITKEY$
         CLS
      LOOP
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

END SUB
' ========================================================================================

' ========================================================================================
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Display the data
      ShowColumnsData (pCon, "Authors")
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_DeleteRecord.bas
' Contents: CODBC class example
' Demonstrates the use of the DeleteRecord 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement handle
      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 * FROM Authors WHERE Au_Id=999")
      ' // Fetch the record
      pstmt.Fetch
      ' // Delet the record
      pStmt.DeleteRecord
      STDOUT "Record deleted"
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_DescribeCol.bas
' Contents: CODBC class example
' Demonstrates the use of the DescribeCol 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement handle
      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")
      ' -------------------------------------------------------------------------------------
      ' Use DescribeCol to retrieve information about column 2
      ' -------------------------------------------------------------------------------------
      LOCAL strColName AS STRING
      LOCAL iNameLength AS INTEGER
      LOCAL iDataType AS INTEGER
      LOCAL dwColumnSize AS DWORD
      LOCAL iDecimalDigits AS INTEGER
      LOCAL iNullable AS INTEGER
      pStmt.DescribeCol(2, strColName, iDataType, dwColumnSize, iDecimalDigits, iNullable)
      PRINT "Column name: " & strColName
      PRINT "Name length: " & FORMAT$(iNameLength)
      PRINT "Data type: " & FORMAT$(iDataType)
      PRINT "Column size: " & FORMAT$(dwColumnSize)
      PRINT "Decimal digits: " & FORMAT$(iDecimalDigits)
      PRINT "Nullable: " & FORMAT$(iNullable) & " - " & IIF$(iNullable, "TRUE", "FALSE")
      ' -------------------------------------------------------------------------------------
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Drivers.bas
' Contents: CODBC class example
' Demonstrates the use of the Drivers 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.
' ########################################################################################

' SED_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

   LOCAL wDirection AS WORD
   LOCAL strDriverAttributes AS STRING
   LOCAL strDriverDesc AS STRING

   TRY
      wDirection = %SQL_FETCH_FIRST
      DO
         strDriverAttributes = ""
         IF SQL_SUCCEEDED(pOdbc.Drivers(wDirection, strDriverDesc, strDriverAttributes)) = 0 THEN EXIT DO
         PRINT "----------------------"
         PRINT "Driver description: " strDriverDesc
         PRINT "Driver attributes: " strDriverAttributes
         PRINT "----------------------"
         wDirection = %SQL_FETCH_NEXT
         WAITKEY$
      LOOP
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Drivers.bas
' Contents: CODBC class example
' Demonstrates the use of the EndTran 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Set manual commit
      pCon.SetAutocommitOff
      ' // Allocate an statement handle
      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     = 998               : cbAuID     = SIZEOF(lAuId)
      szAuthor  = "Edgar Allan Poe" : cbAuthor   = LEN(szAuthor)
      iYearBorn = 1809              : cbYearBorn = SIZEOF(iYearBorn)
      ' // Add the record
      pStmt.AddRecord
      STDOUT "Record added"
      ' // Commit the transaction
      ' pCon.CommitDbcTran
      ' PRINT "Commit succeeded"
      ' or Rollbacks it because this is a test
      pCon.RollbackTran
      PRINT "Rollback succeeded"
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_GetDescField.bas
' Contents: CODBC class example
' Demonstrates the use of the DescFieldXXX properties.
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement handle
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Titles ORDER BY Title")
      ' // Get the current setting or values of the descriptor record for the 9th field ("Price")
      PRINT pStmt.GetImpRowDescFieldName(9)
      PRINT pStmt.GetImpRowDescFieldPrecision(9)
      PRINT pStmt.GetImpRowDescFieldOctetLength(9)
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Drivers.bas
' Contents: CODBC class example
' Demonstrates the use of the DescRecXXX properties.
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement handle
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Generate a result set
      pStmt.ExecDirect ("SELECT TOP 20 * FROM Titles ORDER BY Title")
      ' // Get the current setting or values of the descriptor record for the 9th field ("Price")
      LOCAL strName AS STRING, StringLength AS INTEGER
      LOCAL nType AS INTEGER, nSubType AS INTEGER, nLength AS LONG
      LOCAL nPrecision AS INTEGER, nScale AS INTEGER, nNullable AS INTEGER
      pStmt.GetImpRowDescRec(9, strName, StringLength, nType, nSubType, nLength, nPrecision, nScale, nNullable)
      PRINT "Name: " strName
      PRINT "String length: " StringLength
      PRINT "Type: " nType
      PRINT "Subtype: " nSubType
      PRINT "Length: " nLength
      PRINT "Precision: " nPrecision
      PRINT "Scale: " nScale
      PRINT "Nullable: " nNullable
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_GetTypeInfo.bas
' Contents: CODBC class example
' Demonstrates the use of the GetTypeInfo 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"

' ========================================================================================
' Shows the data
' ========================================================================================
SUB ShowTypeInfoData (BYVAL pCon AS IOdbcConnection, BYVAL iInDataType AS INTEGER)

   #REGISTER NONE   ' // Binded variables can't be register variables

   LOCAL cbbytes AS LONG
   LOCAL szTypeName AS ASCIIZ * 129
   LOCAL iDataType AS INTEGER
   LOCAL lColumnSize AS LONG
   LOCAL szIntervalPrefix AS ASCIIZ * 129
   LOCAL szIntervalSuffix AS ASCIIZ * 129
   LOCAL szCreateParams AS ASCIIZ * 129
   LOCAL iNullable AS INTEGER
   LOCAL iCaseSensitive AS INTEGER
   LOCAL iSearchable AS INTEGER
   LOCAL iUnsignedAttribute AS INTEGER
   LOCAL iFixedPrecScale AS INTEGER
   LOCAL iAutoUniqueValue AS INTEGER
   LOCAL szLocalTypeName AS ASCIIZ * 129
   LOCAL iMinimumScale AS INTEGER
   LOCAL iMaximumScale AS INTEGER
   LOCAL iSqlDataType AS INTEGER
   LOCAL iSqlDatetimeSub AS INTEGER
   LOCAL lNumPrecRadix AS LONG
   LOCAL iIntervalPrecision AS INTEGER

   IF ISNOTHING(pCon) THEN EXIT SUB

   ' // Allocate an statement handle
   LOCAL pStmt AS IOdbcStatement
   pStmt = pCon.Statement("Statement1")
   IF ISNOTHING(pStmt) THEN EXIT SUB

   TRY
      pStmt.GetTypeInfo(iInDataType)
      pStmt.BindColToString ( 1, szTypeName, SIZEOF(szTypeName), cbbytes)
      pStmt.BindColToInteger( 2, iDataType, cbbytes)
      pStmt.BindColToLong   ( 3, lColumnSize, cbbytes)
      pStmt.BindColToString ( 4, szIntervalPrefix, SIZEOF(szIntervalPrefix), cbbytes)
      pStmt.BindColToString ( 5, szIntervalSuffix, SIZEOF(szIntervalSuffix), cbbytes)
      pStmt.BindColToString ( 6, szCreateParams, SIZEOF(szCreateParams), cbbytes)
      pStmt.BindColToInteger( 7, iNullable, cbbytes)
      pStmt.BindColToInteger( 8, iCasesensitive, cbbytes)
      pStmt.BindColToInteger( 9, iSearchable, cbbytes)
      pStmt.BindColToInteger(10, iUnsignedAttribute, cbbytes)
      pStmt.BindColToInteger(11, iFixedPrecScale, cbbytes)
      pStmt.BindColToInteger(12, iAutoUniqueValue, cbbytes)
      pStmt.BindColToString (13, szLocalTypeName, SIZEOF(szLocalTypeName), cbbytes)
      pStmt.BindColToInteger(14, iMinimumScale, cbbytes)
      pStmt.BindColToInteger(15, iMaximumScale, cbbytes)
      pStmt.BindColToInteger(16, iSqlDataType, cbbytes)
      pStmt.BindColToInteger(17, iSqlDateTimeSub, cbbytes)
      pStmt.BindColToLong   (18, lNumPrecRadix, cbbytes)
      pStmt.BindColToInteger(19, iIntervalPrecision, cbbytes)
      DO
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         PRINT "----------------------------------"
         PRINT "Type name: " szTypeName
         PRINT "Data type: " iDataType
         PRINT "Column size: " lColumnSize
         PRINT "Interval prefix: " szIntervalPrefix
         PRINT "Interval suffix: " szIntervalSuffix
         PRINT "Create params: " szCreateParams
         PRINT "Nullable: " iNullable
         PRINT "Case sensitive: " iCaseSensitive
         PRINT "Searchable: " iSearchable
         PRINT "Unsigned attribute: " iUnsignedAttribute
         PRINT "Fixed prec scale: " iFixedPrecScale
         PRINT "Auto unique value: " iAutoUniqueValue
         PRINT "Local type name: " szLocalTypeName
         PRINT "Minimum scale: " iMinimumScale
         PRINT "Maximum scale: " iMaximumScale
         PRINT "SQL data type: " iSqlDataType
         PRINT "SQL Datetime sub: " iSqlDatetimeSub
         PRINT "Num prec radix: " lNumPrecRadix
         PRINT "Interval precision: " iIntervalPrecision
         PRINT "----------------------------------"
         WAITKEY$
         CLS
      LOOP
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

END SUB
' ========================================================================================

' ========================================================================================
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Show the type info data
      ShowTypeInfoData (pCon, %SQL_ALL_TYPES)
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_NumResultCols.bas
' Contents: CODBC class example
' This example demostrates the use of the Columns and NumResultCols methods.
' It shows a way to create a recordset and parse its contents if we only knew the name of
' the database and the table.
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' -------------------------------------------------------------------------------------
      ' Retrieve the column names
      ' -------------------------------------------------------------------------------------
      ' // Allocate an statement object
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Get a recordset with the column names
      pStmt.Columns("", "", "Authors", "")
      ' // Binds the variable
      STATIC szBuf AS ASCIIZ * 256
      STATIC cbbytes AS LONG
      STATIC szColumnName AS ASCIIZ * 129
      STATIC idx AS LONG
      pStmt.BindColToString(4, szColumnName, SIZEOF(szColumnName), cbbytes)
      DO
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         ' // Skip column 0, reserved to bookmarks
         IF idx <> 0 THEN PRINT "Column name: " szColumnName
         INCR idx
      LOOP
      ' // Destroy the statement object
      pCon.RemoveStatement("Statement1")
      pStmt = NOTHING
      ' -------------------------------------------------------------------------------------
      ' // Allocate an statement object
      pStmt = pCon.Statement("Statement2")
      ' // 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
      PRINT "Number of columns: " & STR$(numCols)
      IF numCols = 0 THEN EXIT FUNCTION
      ' // Parse the result set
      DO
         ' // Fetch the record
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         ' // Retrieve and display the values of the columns
         FOR idx = 1 TO numCols
            PRINT pStmt.GetDataString(idx) & " ";
         NEXT
         PRINT
      LOOP
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Prepare.bas
' Contents: CODBC class example
' Demonstrates how to update a record using a prepared statement.
' 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

   ' // Create a connection
   LOCAL pCon AS IOdbcConnection
   pCon = pOdbc.Connection("Connection1")
   IF ISNOTHING(pCon) THEN EXIT FUNCTION

   TRY
      ' // Open the database
      pCon.OpenDatabase("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=biblio.mdb;UID=;PWD=;")
      ' // Allocate an statement handle
      LOCAL pStmt AS IOdbcStatement
      pStmt = pCon.Statement("Statement1")
      ' // Cursor type
      pStmt.SetMultiuserKeysetCursor
      ' // Prepare the statement
      pStmt.Prepare("UPDATE Authors SET Author=? WHERE Au_ID=?")
      ' // Bind the columns
      ' // Note: If you want to use local variables, make sure they aren't register variables using #REGISTER NONE
      STATIC szAuthor AS ASCIIZ * 256, cbAuthor AS LONG
      STATIC lAuId, cbAuId AS LONG
      pStmt.BindParameter(1, %SQL_PARAM_INPUT, %SQL_C_CHAR, %SQL_CHAR, 255, 0, VARPTR(szAuthor), SIZEOF(szAuthor), cbAuthor)
      pStmt.BindParameter(2, %SQL_PARAM_INPUT, %SQL_C_LONG, %SQL_INTEGER, 4, 0, VARPTR(lAuId), 0, cbAuID)
      ' // Fill the parameter value
      lAuId = 999 : cbAuId = 4
      szAuthor = "William Shakespeare" : cbAuthor = LEN(szAuthor)
      ' // Execute the prepared statement
      pStmt.Execute
      STDOUT "Record updated"
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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