Author Topic: ODBC Flat API Examples  (Read 13500 times)

0 Members and 1 Guest are viewing this topic.

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLGetTypeInfo
« Reply #15 on: August 21, 2011, 09:06:27 PM »
 
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.

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLManageDataSources
« Reply #16 on: August 21, 2011, 09:07:44 PM »
 
SQLManageDataSources displays a dialog box with which users can set up, add, and delete data sources in the system information.

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: Prepared Execution
« Reply #17 on: August 21, 2011, 09:09:05 PM »
 
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:

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLSetCursorName
« Reply #18 on: August 21, 2011, 09:10:15 PM »
 
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.

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: Updating Rows in the Rowset with SQLSetPos
« Reply #19 on: August 21, 2011, 09:11:26 PM »
 
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:

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================
« Last Edit: August 21, 2011, 09:15:23 PM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: Deleting Rows in the Rowset with SQLSetPos
« Reply #20 on: August 21, 2011, 09:12:25 PM »
 
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.

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLGetDiagRec and SQLGetDiagField
« Reply #21 on: August 21, 2011, 09:14:10 PM »
 
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:

Code: [Select]
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

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLStatistics
« Reply #22 on: August 21, 2011, 09:16:50 PM »
 
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.ç

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ODBC Example: SQLTables
« Reply #23 on: August 21, 2011, 09:18:04 PM »
 
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.

Code: [Select]
' ========================================================================================
' 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
' ========================================================================================