Problem: This document will cover the followings: * Setting up an ODBC data source * Connecting from Delphi or C++ Builder using the ODBC data source * Displaying SQL Monitor log for the ODBC connection Solution: Setting up an ODBC data source In order to make a connection from 3rd party products using ODBC, an ODBC data source is needed. A data source stores connection information such as user name, password, location of database&etc. This is how to set up a data source: ? Start up ODBC Administrator. ODBC Administrator can be started by: 1) Going to Control Panel and double click on 32bit ODBC. OR 2) Starting the utility called "32bit ODBC Administrator" if you have the ODBC SDK installed. ? Stay at the User Data Source tab and click on Add. This will bring up another window titled "Create New Data Source". ? Pick the ODBC driver that to be used. Pick the InterBase driver which is called "InterBase 5.x driver by Visigenic (*.gdb)" and then click on Finish. This will bring up a new window with the title "InterBase ODBC Configuration". ? Fill in the blank fields in this window: 1) Data Source Name: Make up a name for your data source. 2) Description: This is the description of the data source. It's not required. 3) Network Protocol: Choose the protocol from the drop down list. 4) Database: Fill in the physical full path to the database including the database name to server. 5) Server: Fill in the server name. If you choose the protocol "local", this will default to the local server. 6) Username: Fill in the database user name. 7) Password: Fill in the password corresponding to the above user name. Optional for the new InterSolv ODBC driver for InterBase: 8) Go to the Advanced tab and fill in the CharacterSet and Roles. ? Clicking the OK button will bring back to the main form. You should see the newly added user data source there. Note: A user data source is a data source visible to the user whereas a system data source is visible to the system. Connecting from Delphi using the ODBC data source ODBC connection from Delphi is very similar to connecting using BDE from Delphi. Here is an example of connecting using the Tquery component. This example will also display the results of a sql statement. ? Drop a Tquery, a Tdatasource, and a Tdbgrid component on a Delphi form. ? Set the following properties for the Tquery component: 1) DatabaseName: Pick from the list the data source name you just created in ODBC Administrator. 2) SQL: Input the sql statement to be executed. For example: "select * from table1". 3) Active: Set to True to connect. And supply user name and password on connection. ? Set the following properties for the Tdatasource component: 1) Data Set: Set to the name of the Tquery component, or "query1" in this case. ? Set the following properties for the TDBGrid component: 1) Data Source: Set to the name of the Tdatasource component, or "data source1" in this case. ? Now you can see the returned results from select statement in the dbgrid area. Displaying SQL Monitor Log for the ODBC connection This section will disply the SQL Monitor Log for ODBC and BDE. It will show the difference of the two logs. ? To turn on the SQL Monitor log: Go the Database and choose SQL Monitor. ? Make the Tquery's connection to active from above. And you should see a log generated in the SQL Monitor window. Here is a sample of what it looks like: 1 13:57:29 Log started for: Delphi 4 2 13:57:34 SQL Prepare: InterBase InterSolv Driver (*.g — select * from test_table 3 13:57:34 SQL Execute: InterBase InterSolv Driver (*.g — select * from test_table 4 13:57:34 SQL Vendor: ODBC — SQLAllocStmt 5 13:57:34 SQL Vendor: ODBC — SQLExecDirect 6 13:57:34 SQL Vendor: ODBC — SQLNumResultCols 7 13:57:34 SQL Vendor: ODBC — SQLDescribeCol 8 13:57:34 SQL Vendor: ODBC — SQLDescribeCol 9 13:57:34 SQL Misc: InterBase InterSolv Driver (*.g — Set rowset size 10 13:57:34 SQL Vendor: ODBC — SQLBindCol 11 13:57:34 SQL Vendor: ODBC — SQLBindCol 12 13:57:34 SQL Stmt: InterBase InterSolv Driver (*.g — Fetch 13 13:57:34 SQL Vendor: ODBC — SQLSetStmtOption 14 13:57:34 SQL Vendor: ODBC — SQLExtendedFetch 15 13:57:34 SQL Data Out: InterBase InterSolv Driver (*.g — Column = 1, Name = TESTING, Type = fldTIMESTAMP, Precision = 19, Scale = 0, Data = 3/31/1998 10:42:52:0 16 13:57:34 SQL Data Out: InterBase InterSolv Driver (*.g — Column = 2, Name = TESTDATE, Type = fldTIMESTAMP, Precision = 19, Scale = 0, Data = 3/30/1998 14:4:39:0 17 13:57:34 SQL Stmt: InterBase InterSolv Driver (*.g — Fetch 18 13:57:34 SQL Vendor: ODBC — SQLSetStmtOption 19 13:57:34 SQL Vendor: ODBC — SQLExtendedFetch 20 13:57:34 SQL Stmt: InterBase InterSolv Driver (*.g — EOF 21 13:57:34 SQL Stmt: InterBase InterSolv Driver (*.g — Reset 22 13:57:34 SQL Vendor: ODBC — SQLFreeStmt Here is a sample SQL monitor log using the BDE for the same operation: 1 14:06:45 SQL Prepare: INTRBASE — select * from test_table 2 14:06:45 SQL Vendor: INTRBASE — isc_dsql_allocate_statement 3 14:06:45 SQL Vendor: INTRBASE — isc_start_transaction 4 14:06:45 SQL Vendor: INTRBASE — isc_dsql_prepare 5 14:06:45 SQL Vendor: INTRBASE — isc_dsql_sql_info 6 14:06:45 SQL Vendor: INTRBASE — isc_vax_integer 7 14:06:45 SQL Transact: INTRBASE — XACT (UNKNOWN) 8 14:06:45 SQL Vendor: INTRBASE — isc_commit_retaining 9 14:06:45 SQL Execute: INTRBASE — select * from test_table 10 14:06:45 SQL Vendor: INTRBASE — isc_dsql_execute 11 14:06:45 SQL Stmt: INTRBASE — Fetch 12 14:06:45 SQL Vendor: INTRBASE — isc_dsql_fetch 13 14:06:45 SQL Data Out: INTRBASE — Column = 1, Name = TESTING, Type = fldTIMESTAMP, Precision = 1, Scale = 0, Data = 3/31/1998 10:42:52:0 14 14:06:45 SQL Data Out: INTRBASE — Column = 2, Name = TESTDATE, Type = fldTIMESTAMP, Precision = 1, Scale = 0, Data = 3/30/1998 14:4:39:0 15 14:06:45 SQL Stmt: INTRBASE — Fetch 16 14:06:45 SQL Vendor: INTRBASE — isc_dsql_fetch 17 14:06:45 SQL Stmt: INTRBASE — EOF 18 14:06:45 SQL Stmt: INTRBASE — Reset 19 14:06:45 SQL Vendor: INTRBASE — isc_dsql_free_statement 20 14:06:45 SQL Transact: INTRBASE — XACT Commit 21 14:06:45 SQL Vendor: INTRBASE — isc_commit_transaction Note: What's after SQL Vendor is the layer beyond Delphi. For ODBC, it's ODBC and for BDE is the "INTRBASE". Also pay special attention to the function follows the name. ODBC uses ODBC function calls and where as BDE uses InterBase api function calls.
Last Modified: 27-OCT-00