Главная страница | назад





Article #25639: Connecting via ODBC using Delphi or C++ Builder

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