Connecting via ODBC using Delphi or C++ Builder

By: Borland Staff

Abstract: Step by steps connecting to ODBC

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.


Server Response from: ETNASC03