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