ODBC Flat API Examples

[José Roca]

 
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.

[José Roca]

Code Select Expand


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


[José Roca]

 

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:

Code Select Expand


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


[José Roca]

 
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:

Code Select Expand


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


[José Roca]

 
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:

Code Select Expand


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


[José Roca]

SQLColumns returns the list of column names in specified tables. The driver returns this information as a result set on the specified StatementHandle.

Code Select Expand


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


[José Roca]

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.

Code Select Expand


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


[José Roca]

 
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.

Code Select Expand


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


[José Roca]

 
SQLCreateDataSource displays a dialog box with which the user can add a data source.

Code Select Expand


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


[José Roca]

 
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:

Code Select Expand


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


[José Roca]

SQLDrivers lists driver descriptions and driver attribute keywords. This function is implemented solely by the Driver Manager.

Code Select Expand


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


[José Roca]

 
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:

Code Select Expand


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


[José Roca]

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:

Code Select Expand


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


[José Roca]

 
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.

Code Select Expand


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


[José Roca]

 
SQLGetTranslator displays a dialog box from which a user can select a translator.

Code Select Expand


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