IT-Consultant: José Roca (PBWIN 10+/PBCC 6+) (Archive only) > Windows API Programming
CODBC Class Examples
José Roca:
--- Code: ---' ########################################################################################
' 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
' ========================================================================================
--- End code ---
José Roca:
--- Code: ---' ########################################################################################
' 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
' ========================================================================================
--- End code ---
José Roca:
--- Code: ---' ########################################################################################
' 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
' ========================================================================================
--- End code ---
José Roca:
--- Code: ---' ########################################################################################
' 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
' ========================================================================================
--- End code ---
José Roca:
--- Code: ---' ########################################################################################
' 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
' ========================================================================================
--- End code ---
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version