CODBC Class Examples

[José Roca]

Code: [Select]

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

   ' // 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
      ' // Sets the cursor name
      pStmt.CursorName = "MyCursor"
      ' // Gets the cursor name
      PRINT "Cursor name: " & pStmt.CursorName
   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_Statistics.bas
' Contents: CODBC class example
' Demonstrates the use of the Statistics 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 ShowStatisticsData (BYVAL pCon AS IOdbcConnection, BYREF szInTableName AS ASCIIZ, BYVAL iUnique AS INTEGER, BYVAL iCardinality AS INTEGER)

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

   LOCAL cbbytes AS LONG
   LOCAL szTableCatalogName AS ASCIIZ * 256
   LOCAL szTableSchemaName AS ASCIIZ * 256
   LOCAL szTableName AS ASCIIZ * 129
   LOCAL iNonUnique AS INTEGER
   LOCAL szIndexQualifier AS ASCIIZ * 129
   LOCAL szIndexName AS ASCIIZ * 129
   LOCAL iInfoType AS INTEGER
   LOCAL iOrdinalPosition AS INTEGER
   LOCAL szColumnName AS ASCIIZ * 129
   LOCAL szAscOrDesc AS ASCIIZ * 2
   LOCAL lCardinality AS LONG
   LOCAL lPages AS LONG
   LOCAL szFilterCondition AS ASCIIZ * 129

   IF ISNOTHING(pCon) THEN EXIT SUB
   IF LEN(szIntableName) = 0 THEN EXIT SUB
   IF iCardinality > 1 THEN iCardinality = 0
   IF iUnique > 1 THEN iUnique = 1

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

   TRY
      pStmt.Statistics("", "", szInTableName, iUnique, iCardinality)
      pStmt.BindColToString ( 1, szTableCatalogName, SIZEOF(szTableCatalogName), cbBytes)
      pStmt.BindColToString ( 2, szTableSchemaName, SIZEOF(szTableSchemaName), cbbytes)
      pStmt.BindColToString ( 3, szTableName, SIZEOF(szTableName), cbbytes)
      pStmt.BindColToInteger( 4, iNonUnique, cbbytes)
      pStmt.BindColToString ( 5, szIndexQualifier, SIZEOF(szIndexQualifier), cbbytes)
      pStmt.BindColToString ( 6, szIndexName, SIZEOF(szIndexName), cbbytes)
      pStmt.BindColToInteger( 7, iInfoType, cbbytes)
      pStmt.BindColToInteger( 8, iOrdinalPosition, cbbytes)
      pStmt.BindColToString ( 9, szColumnName, SIZEOF(szColumnName), cbbytes)
      pStmt.BindColToString (10, szAscOrDesc, SIZEOF(szAscOrDesc), cbbytes)
      pStmt.BindColToLong   (11, lCardinality, cbbytes)
      pStmt.BindColToLong   (12, lPages, cbbytes)
      pStmt.BindColToString (13, szFilterCondition, SIZEOF(szFilterCondition), cbbytes)
      DO
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         PRINT "----------------------------------"
         PRINT "Table catalog name: " szTableCatalogName
         PRINT "Table schema name: " szTableSchemaName
         PRINT "Table name: " szTableName
         PRINT "Non unique: " iNonUnique
         PRINT "Index qualifier: " szIndexQualifier
         PRINT "Index name: " szIndexName
         PRINT "Info type: " iInfoType
         PRINT "Ordinal position: " iOrdinalPosition
         PRINT "Column name: " szColumnName
         PRINT "Asc or desc: " szAscOrDesc
         PRINT "Cardinality: " lCardinality
         PRINT "Pages: " lPages
         PRINT "Filter condition: " szFilterCondition
         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
      ShowStatisticsData (pCon, "Authors", %SQL_INDEX_ALL, %SQL_ENSURE)
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_Tables.bas
' Contents: CODBC class example
' Demonstrates the use of the Tables 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 ShowTablesData (BYVAL pCon AS IOdbcConnection, BYREF szInTableTypes AS ASCIIZ)

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

   LOCAL cbbytes AS LONG
   LOCAL szTableCatalogName AS ASCIIZ * 256
   LOCAL szTableSchemaName AS ASCIIZ * 256
   LOCAL szTableName AS ASCIIZ * 129
   LOCAL szTableType AS ASCIIZ * 129
   LOCAL szRemarks AS ASCIIZ * 256

   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.Tables("", "", "", szInTableTypes)
      pStmt.BindColToString(1, szTableCatalogName, SIZEOF(szTableCatalogName), cbbytes)
      pStmt.BindColToString(2, szTableSchemaName, SIZEOF(szTableSchemaName), cbbytes)
      pStmt.BindColToString(3, szTableName, SIZEOF(szTableName), cbbytes)
      pStmt.BindColToString(4, szTableType, SIZEOF(szTableType), cbbytes)
      pStmt.BindColToString(5, szRemarks, SIZEOF(szRemarks), cbbytes)
      DO
         IF ISFALSE pStmt.Fetch THEN EXIT DO
         PRINT "----------------------------------"
         PRINT "szTableCatalogName: " szTableCatalogName
         PRINT "szTableSchemaName: " szTableSchemaName
         PRINT "szTableName: " szTableName
         PRINT "szTableType: " szTableType
         PRINT "szRemarks: " szRemarks
         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
      ShowTablesData (pCon, "TABLE")
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   WAITKEY$

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


[José Roca]

Code: [Select]

' ########################################################################################
' Microsoft Windows
' File: CODBCEX_UpdRecord.bas
' Contents: CODBC class example
' Demonstrates how to update a record using UpdateRecord.
' 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")
      ' // Fetches the record
      pstmt.Fetch
      ' // Fills the values of the bounded application variables and its sizes
      cbAuId = %SQL_COLUMN_IGNORE  ' Ignore the Au_Id column in the update
      szAuthor = "Félix Lope de Vega Carpio" : cbAuthor = LEN(szAuthor)
      iYearBorn = 1562 : cbYearBorn = 4
      ' // Updates the record
      pStmt.UpdateRecord(1)
      STDOUT "Record updated"
   CATCH
     ' // Display error information
      STDOUT OdbcOleErrorInfo(OBJRESULT)
      WAITKEY$
   END TRY

   ' // Destroy the class
   pOdbc = NOTHING

   WAITKEY$

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