This is a description of the general flow of ODBC applications. It is unlikely that any application calls all of these functions in exactly this order. However, most applications use some variation of these steps.
Step 1: Connect to the Data Source
The first step in connecting to the data source is to load the Driver Manager and allocate the environment handle with SQLAllocHandle.
The application then registers the version of ODBC to which it conforms by calling SQLSetEnvAttr with the %SQL_ATTR_APP_ODBCVER environment attribute.
Next, the application allocates a connection handle with SQLAllocHandle and connects to the data source with SQLConnect, SQLDriverConnect, or SQLBrowseConnect.
The application then sets any connection attributes, such as whether to manually commit transactions.
Step 2: Initialize the Application
The second step is to initialize the application. Exactly what is done here varies with the application.
At this point, it is common to use SQLGetInfo to discover the capabilities of the driver.
All applications need to allocate a statement handle with SQLAllocHandle, and many applications set statement attributes, such as the cursor type, with SQLSetStmtAttr.
Step 3: Build and Execute an SQL Statement
The third step is to build and execute an SQL statement. The methods used to perform this step are likely to vary tremendously. The application might prompt the user to enter an SQL statement, build an SQL statement based on user input, or use a hard-coded SQL statement.
If the SQL statement contains parameters, the application binds them to application variables by calling SQLBindParameter for each parameter.
After the SQL statement is built and any parameters are bound, the statement is executed with SQLExecDirect. If the statement will be executed multiple times, it can be prepared with SQLPrepare and executed with SQLExecute.
The application might also forgo executing an SQL statement altogether and instead call a function to return a result set containing catalog information, such as the available columns or tables.
The application's next action depends on the type of SQL statement executed.
Step 4a: Fetch the Results
The next step is to fetch the results.
If the statement executed in "Step 3: Build and Execute an SQL Statement" was a SELECT statement or a catalog function, the application first calls SQLNumResultCols to determine the number of columns in the result set. This step is not necessary if the application already knows the number of result set columns, such as when the SQL statement is hard-coded in a vertical or custom application.
Next, the application retrieves the name, data type, precision, and scale of each result set column with SQLDescribeCol. Again, this is not necessary for applications such as vertical and custom applications that already know this information. The application passes this information to SQLBindCol, which binds an application variable to a column in the result set.
The application now calls SQLFetch to retrieve the first row of data and place the data from that row in the variables bound with SQLBindCol. If there is any long data in the row, it then calls SQLGetData to retrieve that data. The application continues to call SQLFetch and SQLGetData to retrieve additional data. After it has finished fetching data, it calls SQLCloseCursor to close the cursor.
The application now returns to "Step 3: Build and Execute an SQL Statement" to execute another statement in the same transaction; or proceeds to "Step 5: Commit the Transaction" to commit or roll back the transaction.
Step 4b: Fetch the Row Count
The next step is to fetch the row count.
If the statement executed in Step 3 was an UPDATE, DELETE, or INSERT statement, the application retrieves the count of affected rows with SQLRowCount.
The application now returns to step 3 to execute another statement in the same transaction or proceeds to step 5 to commit or roll back the transaction.
Step 5: Commit the Transaction
The next step is to commit the transaction.
The fifth step is to call SQLEndTran to commit or roll back the transaction. The application performs this step only if it set the transaction commit mode to manual-commit; if the transaction commit mode is auto-commit, which is the default, the transaction is automatically committed when the statement is executed.
To execute a statement in a new transaction, the application returns to step 3. To disconnect from the data source, the application proceeds to step 6.
Step 6: Disconnect from the Data Source
The final step is to disconnect from the data source. First, the application frees any statement handles by calling SQLFreeHandle.
Next, the application disconnects from the data source with SQLDisconnect and frees the connection handle with SQLFreeHandle.
(http://www.jose.it-berater.org/captures/ODBC_BasicSteps.gif)
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Parse the result set using SQLGetData to retrieve the data
DO
r = SqlFetch(hStmt)
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
r = SQLGetData(hStmt, 1, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
strOutput = szBuf & " "
r = SQLGetData(hStmt, 2, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
strOutput = strOutput & szBuf & " "
r = SQLGetData(hStmt, 3, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
strOutput = strOutput & szBuf
STDOUT strOutput
LOOP
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
Applications can bind as many or as few columns of the result set as they choose, including binding no columns at all. When a row of data is fetched, the driver returns the data for the bound columns to the application.
Data can be retrieved from unbound columns by calling SQLGetData. This is commonly called to retrieve long data, which often exceeds the length of a single buffer and must be retrieved in parts.
Columns can be bound at any time, even after rows have been fetched. However, the new bindings do not take effect until the next time a row is fetched; they are not applied to data from rows already fetched.
A variable remains bound to a column until a different variable is bound to the column, until the column is unbound by calling SQLBindCol with a null pointer as the variable's address, until all columns are unbound by calling SQLFreeStmt with the %SQL_UNBIND option, or until the statement is released. For this reason, the application must be sure that all bound variables remain valid as long as they are bound.
The application binds columns by calling SQLBindCol. This function binds one column at a time.
The following example demonstrates the use of SQLBindCol:
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates the use of SQLBindCol.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Binds the columns
LOCAL cbbytes AS LONG
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL iYearBorn AS INTEGER
SQLBindCol(hStmt, 1, %SQL_C_LONG, lAuId, 0, cbbytes)
SQLBindCol(hStmt, 2, %SQL_C_CHAR, szAuthor, SIZEOF(szAuthor), cbbytes)
SQLBindCol(hStmt, 3, %SQL_C_SHORT, iYearBorn, 0, cbbytes)
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Parse the result set
DO
r = SqlFetch(hStmt)
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
PRINT lAuId " ";
PRINT szAuthor " ";
PRINT iYearBorn
LOOP
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
To insert rows with
SQLBulkOperations, the application does the following:
1. Sets the %SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to insert and places the new data values in the bound application buffers.
2. Sets the value in the length/indicator buffer of each column as necessary. This is the byte length of the data or %SQL_NTS for columns bound to string buffers, the byte length of the data for columns bound to binary buffers, and %SQL_NULL_DATA for any columns to be set to NULL. The application sets the value in the length/indicator buffer of those columns that are to be set to their default (if one exists) or NULL (if one does not) to %SQL_COLUMN_IGNORE.
3. Calls SQLBulkOperations with the Operation argument set to %SQL_ADD.
The following example demonstrates how to add a single record using
SQLBulkOperations:
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates how to add a record using SQLBulkOperations.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Binds the columns
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL iYearBorn AS INTEGER
LOCAL cbAuId AS LONG
LOCAL cbAuthor AS LONG
LOCAL cbYearBorn AS LONG
SQLBindCol(hStmt, 1, %SQL_C_LONG, lAuId, 0, cbAuId)
SQLBindCol(hStmt, 2, %SQL_C_CHAR, szAuthor, SIZEOF(szAuthor), cbAuthor)
SQLBindCol(hStmt, 3, %SQL_C_SHORT, iYearBorn, 0, cbYearBorn)
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' 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)
' Adds the record
r = SQLBulkOperations(hStmt, %SQL_ADD)
IF SQL_SUCCEEDED(r) THEN
PRINT "Record added"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
END iF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
An application can retrieve result set metadata at any time after a statement has been prepared or executed and before the cursor over the result set is closed. Very few applications require result set metadata after the statement is prepared and before it is executed. If possible, applications should wait to retrieve metadata until after the statement is executed, because some data sources cannot return metadata for prepared statements and emulating this capability in the driver is often a slow process.
Metadata is often expensive to retrieve from the data source. Because of this, drivers should cache any metadata they retrieve from the server and hold it for as long as the cursor over the result set is open. Also, applications should request only the metadata they absolutely need.
SQLColAttribute returns descriptor information for a column in a result set. Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.
The following example demonstrates the use of SQLColAttribute to retrieve the names of the columns in a result set:
' ========================================================================================
' Example of use of the SQLColAttribute function.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Retrieves the number of columns
LOCAL NumCols AS INTEGER
r = SQLNumResultCols(hStmt, NumCols)
PRINT "Number of columns: " & STR$(NumCols)
IF NumCols = 0 THEN GOTO Terminate
' Retrieves the names of the fields (columns)
LOCAL idx AS INTEGER
LOCAL szFieldName AS ASCIIZ * 255
LOCAL szFieldNameLen AS INTEGER
FOR idx = 1 TO NumCols
r = SQLColAttribute(hStmt, idx, %SQL_DESC_NAME, _
szFieldName, SIZEOF(szFieldName), _
szFieldNameLen, BYVAL %NULL)
szFieldName = LEFT$(szFieldName, szFieldNameLen)
PRINT "Field #" & FORMAT$(idx) & " name: " & szFieldName
NEXT
' Parses the result set
DO
r = SqlFetch(hStmt)
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
FOR idx = 1 TO NumCols
r = SQLGetData(hStmt, idx, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
PRINT szBuf " ";
NEXT
PRINT
LOOP
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLColumns returns the list of column names in specified tables. The driver returns this information as a result set on the specified StatementHandle.
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Displays the data
' ========================================================================================
SUB ShowColumnsData (BYVAL hDbc AS DWORD, BYREF szInTableName AS ASCIIZ)
LOCAL hStmt AS DWORD
LOCAL cbbytes AS LONG
LOCAL r AS INTEGER
IF ISFALSE hDbc THEN EXIT SUB
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE hStmt THEN EXIT SUB
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
r = SQLColumns(hStmt, _
BYVAL %NULL, 0, _ ' All catalogs
BYVAL %NULL, 0, _ ' All schemas
szInTableName, %SQL_NTS,_ ' Table name
BYVAL %NULL, 0) ' All columns
IF SQL_SUCCEEDED(r) THEN
r = SQLBindCol (hStmt, 1, %SQL_C_CHAR, szCatalogName, SIZEOF(szCatalogName), cbBytes)
r = SQLBindCol (hStmt, 2, %SQL_C_CHAR, szSchemaName, SIZEOF(szSchemaName), cbbytes)
r = SQLBindCol (hStmt, 3, %SQL_C_CHAR, szTableName, SIZEOF(szTableName), cbbytes)
r = SQLBindCol (hStmt, 4, %SQL_C_CHAR, szColumnName, SIZEOF(szColumnName), cbbytes)
r = SQLBindCol (hStmt, 5, %SQL_C_SSHORT, iDataType, 0, cbbytes)
r = SQLBindCol (hStmt, 6, %SQL_C_CHAR, szTypeName, SIZEOF(szTypeName), cbbytes)
r = SQLBindCol (hStmt, 7, %SQL_C_SLONG, lColumnSize, 0, cbbytes)
r = SQLBindCol (hStmt, 8, %SQL_C_SLONG, lBufferLength, 0, cbbytes)
r = SQLBindCol (hStmt, 9, %SQL_C_SSHORT, iDecimalDigits, 0, cbbytes)
r = SQLBindCol (hStmt, 10, %SQL_C_SSHORT, iNumPrecRadix, 0, cbbytes)
r = SQLBindCol (hStmt, 11, %SQL_C_SSHORT, iNullable, 0, cbbytes)
r = SQLBindCol (hStmt, 12, %SQL_C_CHAR, szRemarks, SIZEOF(szRemarks), cbbytes)
r = SQLBindCol (hStmt, 13, %SQL_C_CHAR, szColumnDefault, SIZEOF(szColumnDefault), cbbytes)
r = SQLBindCol (hStmt, 14, %SQL_C_SSHORT, iSQLDataType, 0, cbbytes)
r = SQLBindCol (hStmt, 15, %SQL_C_SSHORT, iDatetimeSubtypeCode, 0, cbbytes)
r = SQLBindCol (hStmt, 16, %SQL_C_SLONG, lCharOctetLength, 0, cbbytes)
r = SQLBindCol (hStmt, 17, %SQL_C_SLONG, lOrdinalPosition, 0, cbbytes)
r = SQLBindCol (hStmt, 18, %SQL_C_CHAR, szIsNullable, SIZEOF(szIsNullable), cbbytes)
DO
r = SQLFetch(hStmt) ' Fetch the data
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
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
END IF
SQLFreeStmt hStmt, %SQL_CLOSE ' Closes the cursor
SQLFreeHandle %SQL_HANDLE_STMT, hStmt ' Frees the statement handle
END SUB
' ========================================================================================
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
ShowColumnsData (hDbc, "Authors")
Terminate:
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
The following example demostrates the use of the SQLColumns as a way to create a recordset and parse its contents if we only knew the name of the database and the table.
' ========================================================================================
' This example demostrates the use of the SQLColumns and SQLNumResultColsfunctions. It
' shows a way to create a recordset and parse its contents if we only knew the name of the
' database and the table.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL cbbytes AS LONG
LOCAL szInTableName AS ASCIIZ * 129
LOCAL szColumnName AS ASCIIZ * 129
LOCAL idx AS LONG
LOCAL NumCols AS INTEGER
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' --- Retrieves the column names ------------------------------------------------------
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
szInTableName = "Authors"
' Retrieves the information
r = SQLColumns(hStmt, _
BYVAL %NULL, 0, _ ' All catalogs
BYVAL %NULL, 0, _ ' All schemas
szInTableName, %SQL_NTS,_ ' Table name
BYVAL %NULL, 0) ' All columns
IF SQL_SUCCEEDED(r) THEN
' Binds the variable
r = SQLBindCol (hStmt, 4, %SQL_C_CHAR, szColumnName, SIZEOF(szColumnName), cbbytes)
DO
r = SQLFetch(hStmt) ' Fetch the data
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
' Skip column 0, reserved to bookmarks
IF idx <> 0 THEN PRINT "Column name: " szColumnName
INCR idx
LOOP
END IF
SQLFreeStmt hStmt, %SQL_CLOSE ' Closes the cursor
SQLFreeHandle %SQL_HANDLE_STMT, hStmt ' Frees the statement handle
hStmt = %NULL
' -------------------------------------------------------------------------------------
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Retrieves the number of columns
r = SQLNumResultCols(hStmt, NumCols)
PRINT "Number of columns: " & STR$(NumCols)
IF NumCols = 0 THEN GOTO Terminate
' Parses the result set using SQLGetData to retrieve the data
DO
r = SqlFetch(hStmt)
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
FOR idx = 1 TO NumCols
r = SQLGetData(hStmt, idx, %SQL_C_CHAR, szBuf, SIZEOF(szBuf), BYVAL %NULL)
PRINT szBuf " ";
NEXT
PRINT
LOOP
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLConfigDataSource adds, modifies, or deletes data sources. The function returns TRUE if it is successful, FALSE if it fails. When SQLConfigDataSource returns FALSE, an associated *pfErrorCode value can be obtained by calling SQLInstallerError.
' ========================================================================================
' This example creates an Access database.
' To create a databse that is compatible with Microsoft Access 2.0 use:
' r = SQLConfigDataSource(0, %ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)", "CREATE_V2DB=new.mdb")
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "odbcinst.inc"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
r = SQLConfigDataSource(0, %ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)", "CREATE_DB=new.mdb")
IF ISTRUE r THEN
PRINT "Database created"
END IF
WAITKEY$
END FUNCTION
' ========================================================================================
SQLCreateDataSource displays a dialog box with which the user can add a data source.
' ========================================================================================
' Demostrates the use of the SQLCreateDataSource function
' ========================================================================================
' SED_PBWIN - Use the PBWIN compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "odbcinst.inc"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION WINMAIN (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS ASCIIZ PTR, BYVAL nCmdShow AS LONG) AS LONG
LOCAL hDlg AS LONG
DIALOG NEW 0, "SQLCreateDataSource Demo", , , 400, 240, %WS_OVERLAPPED OR %WS_THICKFRAME OR %WS_SYSMENU OR _
%WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_VISIBLE OR %DS_CENTER TO hDlg
' For icon from resource, instead use something like, LoadIcon(hInst, 'APPICON')
DIALOG SEND hDlg, %WM_SETICON, %ICON_SMALL, LoadIcon(%Null, BYVAL %IDI_APPLICATION)
DIALOG SEND hDlg, %WM_SETICON, %ICON_BIG, LoadIcon(%Null, BYVAL %IDI_APPLICATION)
CONTROL ADD BUTTON, hDlg, %IDOK, "&Ok", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
CONTROL ADD BUTTON, hDlg, %IDCANCEL, "&Close", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
DIALOG SHOW MODAL hDlg, CALL DlgProc
END FUNCTION
' ========================================================================================
' ========================================================================================
' Main Dialog procedure
' ========================================================================================
CALLBACK FUNCTION DlgProc() AS LONG
LOCAL rc AS RECT
SELECT CASE CBMSG
CASE %WM_SIZE
' Resize the two sample buttons of the Dialog
IF CBWPARAM <> %SIZE_MINIMIZED THEN
GetClientRect CBHNDL, rc
MoveWindow GetDlgItem(CBHNDL, %IDOK), (rc.nRight - rc.nLeft) - 185, (rc.nBottom - rc.nTop) - 35, 75, 23, %True
MoveWindow GetDlgItem(CBHNDL, %IDCANCEL), (rc.nRight - rc.nLeft) - 95, (rc.nBottom - rc.nTop) - 35, 75, 23, %True
DIALOG REDRAW CBHNDL
END IF
CASE %WM_COMMAND
SELECT CASE CBCTL
CASE %IDOK
IF CBCTLMSG = %BN_CLICKED THEN
SQLCreateDataSource(CBHNDL, "")
END IF
CASE %IDCANCEL
IF CBCTLMSG = %BN_CLICKED THEN DIALOG END CBHNDL, 0
END SELECT
END SELECT
END FUNCTION
' ========================================================================================
SQLDescribeCol returns the result descriptor — column name, type, column size, decimal digits, and nullability — for one column in the result set. This information also is available in the fields of the IRD.
An application typically calls SQLDescribeCol after a call to SQLPrepare and before or after the associated call to SQLExecute. An application can also call SQLDescribeCol after a call to SQLExecDirect.
SQLDescribeCol retrieves the column name, type, and length generated by a SELECT statement. If the column is an expression, *ColumnName is either an empty string or a driver-defined name.
The following example demonstrates the use of SQLDescribeCol after a call to SQLExecDirect:
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates the use of the SQLDescribeCol function.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' -------------------------------------------------------------------------------------
' Use SQLDescribeCol to retrieve information about column 2
' -------------------------------------------------------------------------------------
LOCAL szColumnName AS ASCIIZ * 256
LOCAL iNameLength AS INTEGER
LOCAL iDataType AS INTEGER
LOCAL dwColumnSize AS DWORD
LOCAL iDecimalDigits AS INTEGER
LOCAL iNullable AS INTEGER
r = SQLDescribeCol(hStmt, 2, szColumnName, SIZEOF(szColumnName), iNameLength, _
iDataType, dwColumnSize, iDecimalDigits, iNullable)
PRINT "Column name: " & szColumnName
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")
' -------------------------------------------------------------------------------------
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Frees the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLDrivers lists driver descriptions and driver attribute keywords. This function is implemented solely by the Driver Manager.
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL wDirection AS WORD
LOCAL DriverAttributes AS STRING * 255
LOCAL szDriverDescription AS ASCIIZ * 255 ' Driver description
LOCAL cbDriverDescriptionLen AS INTEGER ' Driver description total length
LOCAL szDriverAttributes AS ASCIIZ * 255 ' Driver attributes
LOCAL cbDriverAttributesLen AS INTEGER ' Driver attributes total length
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
wDirection = %SQL_FETCH_FIRST
DO
DriverAttributes = ""
r = SQLDrivers(hEnv, wDirection, szDriverDescription, SIZEOF(szDriverDescription), _
cbDriverDescriptionLen, BYVAL VARPTR(DriverAttributes), LEN(DriverAttributes), cbDriverAttributesLen)
IF r = %SQL_NO_DATA THEN EXIT DO
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_ENV, hEnv, r)
EXIT DO
END IF
REPLACE CHR$(0,0) WITH ";" IN DriverAttributes
REPLACE ANY CHR$(0) WITH ";" IN DriverAttributes
szDriverAttributes = TRIM$(DriverAttributes)
PRINT "----------------------"
PRINT "Driver description: " szDriverDescription
PRINT "Server description len: " cbDriverDescriptionLen
PRINT "Driver attributes: " szDriverAttributes
PRINT "Driver attributes len: " cbDriverAttributesLen
PRINT LEN(szDriverAttributes)
PRINT "----------------------"
wDirection = %SQL_FETCH_NEXT
WAITKEY$
LOOP
Terminate:
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
In manual-commit mode, applications must explicitly complete transactions by calling SQLEndTran to commit them or roll them back. This is the normal transaction mode for most relational databases.
Transactions in ODBC do not have to be explicitly initiated. Instead, a transaction begins implicitly whenever the application starts operating on the database.
Applications specify the transaction mode with the %SQL_ATTR_AUTOCOMMIT connection attribute. By default, ODBC transactions are in auto-commit mode (unless SQLSetConnectAttr and SQLSetConnectOption are not supported, which is unlikely). Switching from manual-commit mode to auto-commit mode automatically commits any open transaction on the connection.
The following example demonstrates how to add a new record deactivating the auto-commit mode with a call to SQLSetConnectAttr, adding the record with a call to SQLBulkOperations and completing the transaction with a call to SQLEndTran:
' ========================================================================================
' Example of use of the ODBC wrapper functions
' Demonstrates the use of SQLEndTran.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Set manual commit
r = SQLSetConnectAttr(hDBC, %SQL_ATTR_AUTOCOMMIT, BYVAL %SQL_AUTOCOMMIT_OFF, %NULL)
' Binds the columns
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL iYearBorn AS INTEGER
LOCAL cbAuId AS LONG
LOCAL cbAuthor AS LONG
LOCAL cbYearBorn AS LONG
SQLBindCol(hStmt, 1, %SQL_C_LONG, lAuId, 0, cbAuId)
SQLBindCol(hStmt, 2, %SQL_C_CHAR, szAuthor, SIZEOF(szAuthor), cbAuthor)
SQLBindCol(hStmt, 3, %SQL_C_SHORT, iYearBorn, 0, cbYearBorn)
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT * FROM Authors ORDER BY Author", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Fills 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)
' Adds the record
r = SQLBulkOperations(hStmt, %SQL_ADD)
IF SQL_SUCCEEDED(r) THEN
PRINT "Record added"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
END iF
' Commits the transaction
r = SQLEndTran(%SQL_HANDLE_DBC, hDbc, %SQL_COMMIT)
IF SQL_SUCCEEDED(r) THEN
PRINT "Commit succeeded"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
END iF
' or Rollbacks it
' r = SQLEndTran(%SQL_HANDLE_DBC, hDbc, %SQL_ROLLBACK)
' IF SQL_SUCCEEDED(r) THEN
' PRINT "Rollback succeeded"
' ELSE
' STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
' END iF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
An application can call SQLGetDescField to obtain a single field of a descriptor record. SQLGetDescField gives the application access to all the descriptor fields defined in ODBC, and to driver-defined fields as well.
The following example demonstrates the use of SQLGetDescField:
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Titles ORDER BY Title", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Get the current setting or values of the descriptor record
' for the 9th field ("Price")
LOCAL hIpd AS DWORD
LOCAL szName AS ASCIIZ * 256
LOCAL cbLength AS LONG
LOCAL Precision AS INTEGER
LOCAL nLength AS LONG
r = SQLGetstmtAttr(hStmt, %SQL_ATTR_IMP_ROW_DESC, hIpd, %SQL_IS_UINTEGER, BYVAL %NULL)
IF ISFALSE hIpd THEN GOTO Terminate
r = SQLGetDescField(hIpd, 9, %SQL_DESC_NAME, szName, SIZEOF(szName), cbLength)
r = SQLGetDescField(hIpd, 9, %SQL_DESC_PRECISION, Precision, %SQL_IS_SMALLINT, cbLength)
r = SQLGetDescField(hIpd, 9, %SQL_DESC_OCTET_LENGTH, nLength, %SQL_IS_INTEGER, cbLength)
PRINT szName
PRINT Precision
PRINT nLength
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLGetDescRec returns the current settings or values of multiple fields of a descriptor record. The fields returned describe the name, data type, and storage of column or parameter data.
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates the use of the SQLGetDescRec function.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szBuf AS ASCIIZ * 256
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT TOP 20 * FROM Titles ORDER BY Title", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Get the current setting or values of the descriptor record
' for the 9th field ("Price")
LOCAL hIpd AS DWORD, StringLengthPtr AS INTEGER, TypePtr AS INTEGER
LOCAL SubTypePtr AS INTEGER, LengthPtr AS LONG, PrecisionPtr AS INTEGER
LOCAL ScalePtr AS INTEGER, NullablePtr AS INTEGER
LOCAL szName AS ASCIIZ * 255
' Gets the handle of the IPD (implicitily allocated descriptor).
r = SQLGetStmtAttr(hStmt, %SQL_ATTR_IMP_ROW_DESC, hIpd, %SQL_IS_UINTEGER, BYVAL %NULL)
IF ISTRUE hIpd THEN
r = SQLGetDescRec(hIpd, 9, szName, SIZEOF(szName), StringLengthPtr, TypePtr, SubTypePtr, _
LengthPtr, PrecisionPtr, ScalePtr, NullablePtr)
IF SQL_SUCCEEDED(r) THEN
PRINT "Name: " szName
PRINT "String length: " StringLengthPtr
PRINT "Type: " TypePtr
PRINT "Subtype: " SubTypePtr
PRINT "Length: " LengthPtr
PRINT "Precision: " PrecisionPtr
PRINT "Scale: " ScalePtr
PRINT "Nullable: " NullablePtr
END IF
END IF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLGetTranslator displays a dialog box from which a user can select a translator.
' ========================================================================================
' Demostrates the use of the SQLGetTranslator function
' ========================================================================================
' SED_PBWIN - Use the PBWIN compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "odbcinst.inc"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION WINMAIN (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS ASCIIZ PTR, BYVAL nCmdShow AS LONG) AS LONG
LOCAL hWndMain AS DWORD
LOCAL hCtl AS DWORD
LOCAL hFont AS DWORD
LOCAL wcex AS WndClassEx
LOCAL szClassName AS ASCIIZ * 80
LOCAL rc AS RECT
LOCAL szCaption AS ASCIIZ * 255
LOCAL nLeft AS LONG
LOCAL nTop AS LONG
LOCAL nWidth AS LONG
LOCAL nHeight AS LONG
hFont = GetStockObject(%ANSI_VAR_FONT)
' Register the window class
szClassName = "MyClassName"
wcex.cbSize = SIZEOF(wcex)
wcex.style = %CS_HREDRAW OR %CS_VREDRAW
wcex.lpfnWndProc = CODEPTR(WndProc)
wcex.cbClsExtra = 0
wcex.cbWndExtra = 0
wcex.hInstance = hInstance
wcex.hCursor = LoadCursor (%NULL, BYVAL %IDC_ARROW)
wcex.hbrBackground = %COLOR_3DFACE + 1
wcex.lpszMenuName = %NULL
wcex.lpszClassName = VARPTR(szClassName)
wcex.hIcon = LoadIcon (%NULL, BYVAL %IDI_APPLICATION) ' Sample, if resource icon: LoadIcon(hInst, "APPICON")
wcex.hIconSm = LoadIcon (%NULL, BYVAL %IDI_APPLICATION) ' Remember to set small icon too..
RegisterClassEx wcex
' Window caption
szCaption = "SQLGetTranslator Demo"
' Retrieve the size of the working area
SystemParametersInfo %SPI_GETWORKAREA, 0, BYVAL VARPTR(rc), 0
' Calculate the position and size of the window
nWidth = (((rc.nRight - rc.nLeft)) + 2) * 0.75 ' 75% of the client screen width
nHeight = (((rc.nBottom - rc.nTop)) + 2) * 0.70 ' 70% of the client screen height
nLeft = ((rc.nRight - rc.nLeft) \ 2) - nWidth \ 2
nTop = ((rc.nBottom - rc.nTop) \ 2) - (nHeight \ 2)
' Create a window using the registered class
hWndMain = CreateWindowEx(%WS_EX_CONTROLPARENT, _ ' extended style
szClassName, _ ' window class name
szCaption, _ ' window caption
%WS_OVERLAPPEDWINDOW OR _
%WS_CLIPCHILDREN, _ ' window style
nLeft, _ ' initial x position
nTop, _ ' initial y position
nWidth, _ ' initial x size
nHeight, _ ' initial y size
%NULL, _ ' parent window handle
0, _ ' window menu handle
hInstance, _ ' program instance handle
BYVAL %NULL) ' creation parameters
hCtl = CreateWindowEx(0, "BUTTON", "&Ok", %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT, _
0, 0, 0, 0, hWndMain, %IDOK, hInstance, BYVAL %NULL)
IF hFont THEN SendMessage hCtl, %WM_SETFONT, hFont, 0
hCtl = CreateWindowEx(0, "BUTTON", "&Close", %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT, _
0, 0, 0, 0, hWndMain, %IDCANCEL, hInstance, BYVAL %NULL)
IF hFont THEN SendMessage hCtl, %WM_SETFONT, hFont, 0
' Show the window
ShowWindow hWndMain, nCmdShow
UpdateWindow hWndMain
' Message handler loop
LOCAL Msg AS tagMsg
WHILE GetMessage(Msg, %NULL, 0, 0)
IF ISFALSE IsDialogMessage(hWndMain, Msg) THEN
TranslateMessage Msg
DispatchMessage Msg
END IF
WEND
FUNCTION = msg.wParam
END FUNCTION
' ========================================================================================
' ========================================================================================
' Main Window procedure
' ========================================================================================
FUNCTION WndProc (BYVAL hWnd AS DWORD, BYVAL wMsg AS DWORD, BYVAL wParam AS DWORD, BYVAL lParam AS LONG) AS LONG
LOCAL rc AS RECT
SELECT CASE wMsg
CASE %WM_SIZE
' Resize the two sample buttons of the dialog
IF wParam <> %SIZE_MINIMIZED THEN
GetClientRect hWnd, rc
MoveWindow GetDlgItem(hWNd, %IDOK), (rc.nRight - rc.nLeft) - 185, (rc.nBottom - rc.nTop) - 35, 75, 23, %TRUE
MoveWindow GetDlgItem(hWNd, %IDCANCEL), (rc.nRight - rc.nLeft) - 95, (rc.nBottom - rc.nTop) - 35, 75, 23, %TRUE
END IF
CASE %WM_COMMAND
SELECT CASE LOWRD(wParam)
CASE %IDOK
IF HIWRD(wParam) = %BN_CLICKED THEN
LOCAL szName AS ASCIIZ * 1025
LOCAL szPath AS ASCIIZ * %MAX_PATH + 1
LOCAL pcbNameOut AS WORD
LOCAL pcbPathOut AS WORD
LOCAL pvOption AS DWORD
FUNCTION = SQLGetTranslator(hWnd, szName, SIZEOF(szName), pcbNameOut, szPath, SIZEOF(szPath), pcbPathOut, pvOption)
szName = LEFT$(szName, pcbNameOut)
szPath = LEFT$(szPath, pcbPathOut)
MSGBOX "Name: " & szName & $CRLF & "Path: " & szPath & $CRLF & "Option:" & STR$(pvOption)
END IF
CASE %IDCANCEL
IF HIWRD(wParam) = %BN_CLICKED THEN
SendMessage hWnd, %WM_DESTROY, wParam, lParam
FUNCTION = 0
EXIT FUNCTION
END IF
END SELECT
CASE %WM_DESTROY
PostQuitMessage 0 ' This function closes the main window
FUNCTION = 0 ' by sending zero to the main message loop
EXIT FUNCTION
END SELECT
FUNCTION = DefWindowProc(hWnd, wMsg, wParam, lParam)
END FUNCTION
' ========================================================================================
SQLGetTypeInfo returns information about data types supported by the data source. The driver returns the information in the form of an SQL result set. The data types are intended for use in Data Definition Language (DDL) statements.
Important Applications must use the type names returned in the TYPE_NAME column of the SQLGetTypeInfo result set in ALTER TABLE and CREATE TABLE statements. SQLGetTypeInfo may return more than one row with the same value in the DATA_TYPE column.
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Shows the data
' ========================================================================================
SUB ShowTypeInfoData (BYVAL hDbc AS DWORD, iInDataType AS INTEGER)
LOCAL hStmt AS DWORD
LOCAL cbbytes AS LONG
LOCAL r AS INTEGER
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 ISFALSE hDbc THEN EXIT SUB
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE hStmt THEN EXIT SUB
r = SQLGetTypeInfo (hStmt, iInDataType)
IF SQL_SUCCEEDED(r) THEN
r = SQLBindCol (hStmt, 1, %SQL_C_CHAR, szTypeName, SIZEOF(szTypeName), cbbytes)
r = SQLBindCol (hStmt, 2, %SQL_C_SSHORT, iDataType, 0, cbbytes)
r = SQLBindcol (hStmt, 3, %SQL_C_SLONG, lColumnSize, 0, cbbytes)
r = SQLBindCol (hStmt, 4, %SQL_C_CHAR, szIntervalPrefix, SIZEOF(szIntervalPrefix), cbbytes)
r = SQLBindCol (hStmt, 5, %SQL_C_CHAR, szIntervalSuffix, SIZEOF(szIntervalSuffix), cbbytes)
r = SQLBindCol (hStmt, 6, %SQL_C_CHAR, szCreateParams, SIZEOF(szCreateParams), cbbytes)
r = SQLBindCOl (hStmt, 7, %SQL_C_SSHORT, iNullable, 0, cbbytes)
r = SQLBindCol (hStmt, 8, %SQL_C_SSHORT, iCasesensitive, 0, cbbytes)
r = SQLBindCol (hStmt, 9, %SQL_C_SSHORT, iSearchable, 0, cbbytes)
r = SQLBindCol (hStmt, 10, %SQL_C_SSHORT, iUnsignedAttribute, 0, cbbytes)
r = SQLBindCol (hStmt, 11, %SQL_C_SSHORT, iFixedPrecScale, 0, cbbytes)
r = SQLBindCol (hStmt, 12, %SQL_C_SSHORT, iAutoUniqueValue, 0, cbbytes)
r = SQLBindCol (hStmt, 13, %SQL_C_CHAR, szLocalTypeName, SIZEOF(szLocalTypeName), cbbytes)
r = SQLBindCol (hStmt, 14, %SQL_C_SSHORT, iMinimumScale, 0, cbbytes)
r = SQLBindCol (hStmt, 15, %SQL_C_SSHORT, iMaximumScale, 0, cbbytes)
r = SQLBindCol (hStmt, 16, %SQL_C_SSHORT, iSqlDataType, 0, cbbytes)
r = SQLBindCol (hStmt, 17, %SQL_C_SSHORT, iSqlDateTimeSub, 0, cbbytes)
r = SQLBindCol (hStmt, 18, %SQL_C_SLONG, lNumPrecRadix, 0, cbbytes)
r = SQLBindCol (hStmt, 19, %SQL_C_SSHORT, iIntervalPrecision, 0, cbbytes)
DO
r = SQLFetch(hStmt) ' Fetch the data
IF ISFALSE SQL_SUCCEEDED(r) 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
END IF
SQLFreeStmt hStmt, %SQL_CLOSE ' Closes the cursor
SQLFreeHandle %SQL_HANDLE_STMT, hStmt ' Frees the statement handle
END SUB
' ========================================================================================
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
ShowTypeInfoData (hDbc, %SQL_ALL_TYPES)
Terminate:
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLManageDataSources displays a dialog box with which users can set up, add, and delete data sources in the system information.
' ========================================================================================
' Demostrates the use of the SQLManageDataSources function
' ========================================================================================
' SED_PBWIN - Use the PBWIN compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "odbcinst.inc"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION WINMAIN (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS ASCIIZ PTR, BYVAL nCmdShow AS LONG) AS LONG
LOCAL hDlg AS LONG
DIALOG NEW 0, "SQLManageDataSources Demo", , , 400, 240, %WS_OVERLAPPED OR %WS_THICKFRAME OR %WS_SYSMENU OR _
%WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_VISIBLE OR %DS_CENTER TO hDlg
' For icon from resource, instead use something like, LoadIcon(hInst, 'APPICON')
DIALOG SEND hDlg, %WM_SETICON, %ICON_SMALL, LoadIcon(%Null, BYVAL %IDI_APPLICATION)
DIALOG SEND hDlg, %WM_SETICON, %ICON_BIG, LoadIcon(%Null, BYVAL %IDI_APPLICATION)
CONTROL ADD BUTTON, hDlg, %IDOK, "&Ok", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
CONTROL ADD BUTTON, hDlg, %IDCANCEL, "&Close", 0, 0, 50, 14, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %BS_FLAT
DIALOG SHOW MODAL hDlg, CALL DlgProc
END FUNCTION
' ========================================================================================
' ========================================================================================
' Main Dialog procedure
' ========================================================================================
CALLBACK FUNCTION DlgProc() AS LONG
LOCAL rc AS RECT
SELECT CASE CBMSG
CASE %WM_SIZE
' Resize the two sample buttons of the dialog
IF CBWPARAM <> %SIZE_MINIMIZED THEN
GetClientRect CBHNDL, rc
MoveWindow GetDlgItem(CBHNDL, %IDOK), (rc.nRight - rc.nLeft) - 185, (rc.nBottom - rc.nTop) - 35, 75, 23, %True
MoveWindow GetDlgItem(CBHNDL, %IDCANCEL), (rc.nRight - rc.nLeft) - 95, (rc.nBottom - rc.nTop) - 35, 75, 23, %True
DIALOG REDRAW CBHNDL
END IF
CASE %WM_COMMAND
SELECT CASE CBCTL
CASE %IDOK
IF CBCTLMSG = %BN_CLICKED THEN
SQLManageDataSources CBHNDL
END IF
CASE %IDCANCEL
IF CBCTLMSG = %BN_CLICKED THEN DIALOG END CBHNDL, 0
END SELECT
END SELECT
END FUNCTION
' ========================================================================================
Prepared execution is an efficient way to execute a statement more than once. The statement is first compiled, or prepared, into an access plan. The access plan is then executed one or more times at a later time.
Prepared execution is faster than direct execution for statements executed more than once, primarily because the statement is compiled only once; statements executed directly are compiled each time they are executed.
Prepared execution should not be used for statements executed a single time. For such statements, it is slightly slower than direct execution because it requires an additional ODBC function call.
To prepare and execute a statement, the application:
1. Calls SQLPrepare and passes it a string containing the SQL statement.
2. Sets the values of any parameters. Parameters can actually be set before or after preparing the statement.
3. Calls SQLExecute and does any additional processing that is necessary, such as fetching data.
4. Repeats steps 2 and 3 as necessary.
The following example demonstrates the use of
SQLPrepare,
SQLBindParameter and
SQLExecute:
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates how to update a record using a prepared statement.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Binds the parameter
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL cbAuId AS LONG
LOCAL cbAuthor AS LONG
r = SQLPrepare(hStmt, "UPDATE Authors SET Author=? WHERE Au_ID=?", %SQL_NTS)
r = SQLBindParameter(hStmt, 1, %SQL_PARAM_INPUT, %SQL_C_CHAR, %SQL_CHAR, 255, 0, szAuthor, SIZEOF(szAuthor), cbAuthor)
r = SQLBindParameter(hStmt, 2, %SQL_PARAM_INPUT, %SQL_C_LONG, %SQL_INTEGER, 4, 0, lAuId, 0, cbAuID)
' Fills the parameter value
lAuId = 999 : cbAuId = SIZEOF(lAuId)
szAuthor = "William Shakespeare" : cbAuthor = LEN(szAuthor)
' Executes the prepared statement
r = SQLExecute(hStmt)
IF SQL_SUCCEEDED(r) THEN
STDOUT "Record updated"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLSetCursorName associates a cursor name with an active statement. If an application does not call SQLSetCursorName, the driver generates cursor names as needed for SQL statement processing.
Cursor names are used only in positioned update and delete statements (for example, UPDATE table-name ...WHERE CURRENT OF cursor-name). If the application does not call SQLSetCursorName to define a cursor name, on execution of a query statement the driver generates a name that begins with the letters SQL_CUR and does not exceed 18 characters in length.
All cursor names within the connection must be unique. The maximum length of a cursor name is defined by the driver. For maximum interoperability, it is recommended that applications limit cursor names to no more than 18 characters. In ODBC 3.x, if a cursor name is a quoted identifier, it is treated in a case-sensitive manner and it can contain characters that the syntax of SQL would not permit or would treat specially, such as blanks or reserved keywords. If a cursor name must be treated in a case-sensitive manner, it must be passed as a quoted identifier.
A cursor name that is set either explicitly or implicitly remains set until the statement with which it is associated is dropped, using SQLFreeHandle. SQLSetCursorName can be called to rename a cursor on a statement as long as the cursor is in an allocated or prepared state.
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates the use of SQLSetCursorName and SQLGetCursorName.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL szCursorName AS ASCIIZ * 256
LOCAL iNameLength AS INTEGER
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Sets the cursor name
r = SQLSetCursorName(hStmt, "MyCursor", %SQL_NTS)
' Gets the cursor name
r = SQLGetCursorName(hStmt, szCursorName, SIZEOF(szCursorName), iNameLength)
PRINT "Cursor name: " & szCursorName
PRINT "Name length: " & FORMAT$(iNameLength)
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
To update rows with
SQLSetPos, the application does the following:
1. Places the new data values in the rowset buffers.
2. Sets the value in the length/indicator buffer of each column as necessary. This is the byte length of the data or %SQL_NTS for columns bound to string buffers, the byte length of the data for columns bound to binary buffers, and %SQL_NULL_DATA for any columns to be set to NULL.
3. Sets the value in the length/indicator buffer of those columns which are not to be updated to %SQL_COLUMN_IGNORE. Although the application can skip this step and resend existing data, this is inefficient and risks sending values to the data source that were truncated when they were read.
4. Calls SQLSetPos with Operation set to %SQL_UPDATE and RowNumber set to the number of the row to update. If RowNumber is 0, all rows in the rowset are updated.
After
SQLSetPos returns, the current row is set to the updated row.
The folllowing example demonstrates the use of
SQLSetPos to update a record:
' ========================================================================================
' Example of use of the ODBC raw API functions
' Demonstrates how to update a record using SQLSetPos.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Binds the columns
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL iYearBorn AS INTEGER
LOCAL cbAuId AS LONG
LOCAL cbAuthor AS LONG
LOCAL cbYearBorn AS LONG
SQLBindCol(hStmt, 1, %SQL_C_LONG, lAuId, 0, cbAuId)
SQLBindCol(hStmt, 2, %SQL_C_CHAR, szAuthor, SIZEOF(szAuthor), cbAuthor)
SQLBindCol(hStmt, 3, %SQL_C_SHORT, iYearBorn, 0, cbYearBorn)
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT * FROM Authors WHERE Au_Id=999", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Fetches the record
r = SQLFetch(hStmt)
IF SQL_SUCCEEDED(r) THEN
' 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 = SIZEOF(iYearBorn)
' Updates the record
r = SQLSetPos(hStmt, 1, %SQL_UPDATE, %SQL_LOCK_NO_CHANGE)
IF SQL_SUCCEEDED(r) THEN
STDOUT "Record updated"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
END IF
END IF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
The delete operation of SQLSetPos makes the data source delete one or more selected rows of a table. To delete rows with SQLSetPos, the application calls SQLSetPos with Operation set to %SQL_DELETE and RowNumber set to the number of the row to delete. If RowNumber is 0, all rows in the rowset are deleted.
After SQLSetPos returns, the deleted row is the current row and its status is %SQL_ROW_DELETED. The row cannot be used in any further positioned operations, such as calls to SQLGetData or SQLSetPos.
When deleting all rows of the rowset (RowNumber is equal to 0), the application can prevent the driver from deleting certain rows by using the row operation array, in the same way as for the update operation of SQLSetPos.
Every row that is deleted should be a row that exists in the result set. If the application buffers were filled by fetching and if a row status array has been maintained, its values at each of these row positions should not be %SQL_ROW_DELETED, %SQL_ROW_ERROR, or %SQL_ROW_NOROW.
The following example demonstrates how to delete a single record using SQLSetPos.
' ========================================================================================
' Example of use of the ODBC wrapper functions
' Demonstrates how to delete a record with SQLSetPos.
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
LOCAL strOutput AS STRING
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
' Allocates an statement handle
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hStmt THEN GOTO Terminate
' Binds the columns
LOCAL lAuId AS LONG
LOCAL szAuthor AS ASCIIZ * 256
LOCAL iYearBorn AS INTEGER
LOCAL cbAuId AS LONG
LOCAL cbAuthor AS LONG
LOCAL cbYearBorn AS LONG
SQLBindCol(hStmt, 1, %SQL_C_LONG, lAuId, 0, cbAuId)
SQLBindCol(hStmt, 2, %SQL_C_CHAR, szAuthor, SIZEOF(szAuthor), cbAuthor)
SQLBindCol(hStmt, 3, %SQL_C_SHORT, iYearBorn, 0, cbYearBorn)
' Cursor type
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CURSOR_TYPE, BYVAL %SQL_CURSOR_KEYSET_DRIVEN, %SQL_IS_UINTEGER)
' Optimistic concurrency
r = SQLSetStmtAttr(hStmt, %SQL_ATTR_CONCURRENCY, BYVAL %SQL_CONCUR_VALUES, BYVAL %SQL_IS_UINTEGER)
' Generates a result set
r = SQLExecDirect (hStmt, "SELECT * FROM Authors WHERE Au_Id=999", %SQL_NTS)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
GOTO Terminate
END IF
' Fetches the record and deletes it
r = SQLFetch(hStmt)
IF SQL_SUCCEEDED(r) THEN
' Deletes the record
r = SQLSetPos(hStmt, 1, %SQL_DELETE, %SQL_LOCK_NO_CHANGE)
IF SQL_SUCCEEDED(r) THEN
STDOUT "Record deleted"
ELSE
STDOUT SQLGetErrorInfo(%SQL_HANDLE_STMT, hStmt, r)
END IF
END IF
Terminate:
' Closes the cursor
IF hStmt THEN SQLCloseCursor(hStmt)
' Closes the statement handle
IF hStmt THEN SQLFreeHandle(%SQL_HANDLE_STMT, hStmt)
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
Applications call SQLGetDiagRec or SQLGetDiagField to retrieve diagnostic information. These functions accept an environment, connection, statement, or descriptor handle and return diagnostics from the function that last used that handle. The diagnostics logged on a particular handle are discarded when a new function is called using that handle. If the function returned multiple diagnostic records, the application calls these functions multiple times; the total number of status records is retrieved by calling SQLGetDiagField for the header record (record 0) with the %SQL_DIAG_NUMBER option.
Applications retrieve individual diagnostic fields by calling SQLGetDiagField and specifying the field to retrieve. Certain diagnostic fields do not have any meaning for certain types of handles. For a list of diagnostic fields and their meanings, see the SQLGetDiagField function description.
Applications retrieve the SQLSTATE, native error code, and diagnostic message in a single call by calling SQLGetDiagRec; SQLGetDiagRec cannot be used to retrieve information from the header record.
The following function, included in ODBCAPI352.INC, uses SQLGetDiagRec and SQLGetDiagField to retrieve error information:
FUNCTION SQLGetErrorInfo (BYVAL HandleType AS INTEGER, BYVAL hndl AS DWORD, OPTIONAL BYVAL iErrorCode AS INTEGER) AS STRING
LOCAL iResult AS INTEGER
LOCAL szSqlState AS ASCIIZ * 6
LOCAL lNativeError AS LONG
LOCAL szErrMsg AS ASCIIZ * %SQL_MAX_MESSAGE_LENGTH + 1
LOCAL strErrMsg AS STRING
LOCAL cbbytes AS INTEGER
LOCAL ErrorCount AS LONG
LOCAL i AS LONG
iResult = SQLGetDiagField(HandleType, hndl, 0, %SQL_DIAG_NUMBER, ErrorCount, %SQL_IS_INTEGER, cbbytes)
IF ErrorCount THEN
FOR i = 1 TO ErrorCount
iResult = SQLGetDiagRec(HandleType, hndl, 1, szSqlState, lNativeError, szErrMsg, SIZEOF(szErrMsg), cbbytes)
IF iResult <> %SQL_SUCCESS AND iResult <> %SQL_SUCCESS_WITH_INFO THEN EXIT FOR
strErrMsg = strErrMsg & "SqlState: " & szSqlState & $CRLF & _
"Native error: " & FORMAT$(lNativeError) & $CRLF & szErrMsg
NEXT
ELSEIF iErrorCode THEN
SELECT CASE iErrorCode
CASE -1 : strErrMsg = "SQL error" ' "SQL_ERROR"
CASE 2 : strErrMsg = "Still executing" ' "SQL_STILL_EXECUTING"
CASE -2 : strErrMsg = "Invalid handle" '"SQL_INVALID_HANDLE"
CASE 99 : strErrMsg = "Need data" ' "SQL_NEED_DATA"
CASE 100 : strErrMsg = "No data" '"SQL_NO_DATA"
CASE ELSE
strErrMsg = "Error " & FORMAT$(iErrorCode)
END SELECT
END IF
FUNCTION = strErrMsg
END FUNCTION
SQLStatistics retrieves a list of statistics about a single table and the indexes associated with the table. The driver returns the information as a result set.ç
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Shows the data
' ========================================================================================
SUB ShowStatisticsData (BYVAL hDbc AS DWORD, BYREF szInTableName AS ASCIIZ, BYVAL iUnique AS INTEGER, BYVAL iCardinality AS INTEGER)
LOCAL hStmt AS DWORD
LOCAL cbbytes AS LONG
LOCAL r AS INTEGER
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 ISFALSE hDbc THEN EXIT SUB
IF LEN(szIntableName) = 0 THEN EXIT SUB
IF iCardinality > 1 THEN iCardinality = 0
IF iUnique > 1 THEN iUnique = 1
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE hStmt THEN EXIT SUB
r = SQLStatistics(hStmt, _
BYVAL %NULL, 0, _ ' All catalogs
BYVAL %NULL, 0, _ ' All schemas
szInTableName, %SQL_NTS,_ ' Table name
iUnique, iCardinality)
IF SQL_SUCCEEDED(r) THEN
r = SQLBindCol (hStmt, 1, %SQL_C_CHAR, szTableCatalogName, SIZEOF(szTableCatalogName), cbBytes)
r = SQLBindCol (hStmt, 2, %SQL_C_CHAR, szTableSchemaName, SIZEOF(szTableSchemaName), cbbytes)
r = SQLBindCol (hStmt, 3, %SQL_C_CHAR, szTableName, SIZEOF(szTableName), cbbytes)
r = SQLBindCol (hStmt, 4, %SQL_C_SSHORT, iNonUnique, 0, cbbytes)
r = SQLBindCol (hStmt, 5, %SQL_C_CHAR, szIndexQualifier, SIZEOF(szIndexQualifier), cbbytes)
r = SQLBindCol (hStmt, 6, %SQL_C_CHAR, szIndexName, SIZEOF(szIndexName), cbbytes)
r = SQLBindCol (hStmt, 7, %SQL_C_SSHORT, iInfoType, 0, cbbytes)
r = SQLBindCol (hStmt, 8, %SQL_C_SSHORT, iOrdinalPosition, 0, cbbytes)
r = SQLBindCol (hStmt, 9, %SQL_C_CHAR, szColumnName, SIZEOF(szColumnName), cbbytes)
r = SQLBindCol (hStmt, 10, %SQL_C_CHAR, szAscOrDesc, SIZEOF(szAscOrDesc), cbbytes)
r = SQLBindCol (hStmt, 11, %SQL_C_SLONG, lCardinality, 0, cbbytes)
r = SQLBindCol (hStmt, 12, %SQL_C_SLONG, lPages, 0, cbbytes)
r = SQLBindCol (hStmt, 13, %SQL_C_CHAR, szFilterCondition, SIZEOF(szFilterCondition), cbbytes)
DO
r = SQLFetch(hStmt) ' Fetch the data
IF ISFALSE SQL_SUCCEEDED(r) 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
END IF
SQLFreeStmt hStmt, %SQL_CLOSE ' Closes the cursor
SQLFreeHandle %SQL_HANDLE_STMT, hStmt ' Frees the statement handle
END SUB
' ========================================================================================
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
ShowStatisticsData hDbc, "Authors", %SQL_INDEX_ALL, %SQL_ENSURE
Terminate:
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================
SQLTables returns the list of table, catalog, or schema names, and table types, stored in a specific data source. The driver returns the information as a result set.
' ========================================================================================
' Example of use of the ODBC raw API functions
' ========================================================================================
' SED_PBCC - Use the PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "ODBC.INC"
' ========================================================================================
' Shows the data
' ========================================================================================
SUB ShowTablesData (BYVAL hDbc AS DWORD, BYREF szInTableTypes AS ASCIIZ)
LOCAL hStmt AS DWORD
LOCAL cbbytes AS LONG
LOCAL r AS INTEGER
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 ISFALSE hDbc THEN EXIT SUB
r = SQLAllocHandle (%SQL_HANDLE_STMT, hDbc, hStmt)
IF ISFALSE hStmt THEN EXIT SUB
IF LEN(szInTableTypes) THEN
r = SQLTables (hStmt, _
BYVAL %NULL, 0, _ ' All catalogues
BYVAL %NULL, 0, _ ' All schemas
BYVAL %NULL, 0,_ ' All tables
szInTableTypes, %SQL_NTS) ' Table types
ELSE
r = SQLTables (hStmt, _
BYVAL %NULL, 0, _ ' All catalogues
BYVAL %NULL, 0, _ ' All schemas
BYVAL %NULL, 0,_ ' All tables
BYVAL %NULL, 0) ' All table types
END IF
IF SQL_SUCCEEDED(r) THEN
r = SQLBindCol (hStmt, 1, %SQL_C_CHAR, szTableCatalogName, SIZEOF(szTableCatalogName), cbbytes)
r = SQLBindCol (hStmt, 2, %SQL_C_CHAR, szTableSchemaName, SIZEOF(szTableSchemaName), cbbytes)
r = SQLBindCol (hStmt, 3, %SQL_C_CHAR, szTableName, SIZEOF(szTableName), cbbytes)
r = SQLBindCol (hStmt, 4, %SQL_C_CHAR, szTableType, SIZEOF(szTableType), cbbytes)
r = SQLBindCol (hStmt, 5, %SQL_C_CHAR, szRemarks, SIZEOF(szRemarks), cbbytes)
DO
r = SQLFetch(hStmt) ' Fetch the data
IF ISFALSE SQL_SUCCEEDED(r) THEN EXIT DO
PRINT "----------------------------------"
PRINT "szTableCatalogName: " szTableCatalogName
PRINT "szTableSchemaName: " szTableSchemaName
PRINT "szTableName: " szTableName
PRINT "szTableType: " szTableType
PRINT "szRemarks: " szRemarks
PRINT "----------------------------------"
WAITKEY$
CLS
LOOP
END IF
SQLFreeStmt hStmt, %SQL_CLOSE ' Closes the cursor
SQLFreeHandle %SQL_HANDLE_STMT, hStmt ' Frees the statement handle
END SUB
' ========================================================================================
' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN
LOCAL r AS INTEGER
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL szInConnectionString AS ASCIIZ * 1025
LOCAL szOutConnectionString AS ASCIIZ * 1025
' Allocates the environment handle
r = SQLAllocHandle (%SQL_HANDLE_ENV, %SQL_NULL_HENV, hEnv)
IF ISFALSE SQL_SUCCEEDED(r) OR ISFALSE hEnv THEN EXIT FUNCTION
' Tells to the driver manager that is an application that uses the ODBC driver 3.x
r = SQLSetEnvAttr (hEnv, %SQL_ATTR_ODBC_VERSION, BYVAL %SQL_OV_ODBC3, %SQL_IS_INTEGER)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Allocates the connection handle
r = SQLAllocHandle (%SQL_HANDLE_DBC, hEnv, hDbc)
IF ISFALSE SQL_SUCCEEDED(r) THEN GOTO Terminate
' Connection string
szInConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=biblio.mdb;UID=;PWD=;"
' Connects with the ODBC driver
r = SQLDriverConnect (hDbc, _
%HWND_DESKTOP, _
szInConnectionString, _
LEN(szInConnectionString), _
szOutConnectionString, _
SIZEOF (szOutConnectionString), _
BYVAL %NULL, _
%SQL_DRIVER_COMPLETE)
' Check for errors
IF ISFALSE SQL_SUCCEEDED(r) THEN
STDOUT SQLGetErrorInfo(%SQL_HANDLE_DBC, hDbc, r)
GOTO Terminate
END IF
ShowTablesData (hDbc, "TABLE")
Terminate:
' Closes the connection
IF hDbc THEN
SQLDisconnect(hDbc)
SQLFreeHandle (%SQL_HANDLE_DBC, hDbc)
END IF
' Frees the environment handle
IF hEnv THEN SQLFreeHandle(%SQL_HANDLE_ENV, hEnv)
WAITKEY$
END FUNCTION
' ========================================================================================