ODBCConn: Difference between revisions
(→Windows: reworked as three options were so similar, and added the Start..Run.. way) |
(→Connecting to an ODBC data source: added bit about ODBC without DSNs) |
||
Line 96: | Line 96: | ||
=== The ODBC connection string === | === The ODBC connection string === | ||
Before going to the details of this wrapper, you must first have a basic understanding of how an ODBC data source is identified. | Before going to the details of this wrapper, you must first have a basic understanding of how an ODBC data source is identified. | ||
First of all, an ODBC driver manager provides | First of all, an ODBC driver manager provides alternative ways to make DSN shortcuts for a set of parameters: | ||
* DSN ('''D'''ata'''S'''ource '''N'''ame): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator or by manually editing the <code>odbc.ini</code> file (or registry). | * DSN ('''D'''ata'''S'''ource '''N'''ame): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator or by manually editing the <code>odbc.ini</code> file (or registry). | ||
* File DSN: a file which contains connection parameters. An ODBC Data Source Administrator usually allows you to create File DSNs from the GUI. | * File DSN: a file which contains connection parameters. An ODBC Data Source Administrator usually allows you to create File DSNs from the GUI. | ||
Line 107: | Line 107: | ||
All other parameters are driver dependent. | All other parameters are driver dependent. | ||
Please refer to the documentation of the specific driver to learn more about available parameters and their names! | Please refer to the documentation of the specific driver to learn more about available parameters and their names! | ||
=== Without a DSN === | |||
You may also connect via ODBC without using a DSN, simply supplying all the details in the Connection String that you would otherwise set up in a DSN (driver,server,database,login details). This more direct method avoids the need to set up a DSN when installing your application on a new machine. | |||
=== TODBCConnection properties === | === TODBCConnection properties === |
Revision as of 11:23, 21 September 2012
│
English (en) │
español (es) │
français (fr) │
日本語 (ja) │
The ODBCConn unit implements an SQLdb connection to ODBC data sources.
Overview
ODBC
ODBC (Open Database Connectivity) is a technology that allows one to connect to a whole variety of databases using a single API, the ODBC API.
There are ODBC implementations for various platforms and there are drivers for most Database Management Systems. Official ODBC documentation can be found at the MSDN ODBC site.
TODBCConnection
FreePascal ships with ODBC headers; they are in the odbcsql
and odbcsqldyn
units.
TODBCConnection
is an TSQLConnection
descendant providing a nice OOP wrapper for ODBC using the SQLdb framework.
In Lazarus, you can find the TODBCConnection
component on the SQLdb component tab.
You can also use the TODBCConnection
component in your code by adding ODBCConn
to your uses clause.
What has been implemented:
- executing queries and retrieving result sets
- most field types, including blobs
- query parameters (string and integer types)
- preparing queries
- UpdateIndexDefs (so you can use ApplyUpdates)
What is left to be implemented:
- proper transaction support; currently each connection corresponds to one transaction
- some field types
- SQL_TYPE_UTC* (these are mentioned in the ODBC docs, but seem not to be used in implementations)
- SQL_INTERVAL_* (what would be the corresponding TFieldType?)
- SQL_GUID (TGUIDField was not implemented, until recently)
Why use ODBC?
FreePascal ships with components for connecting to several databases, such as MySQL, PostGreSQL, Firebird, Oracle, etc.
For those databases missing from the list, like MS Access and MS-SQL, ODBC is an acceptable and well established solution. (Developers using vb.net/C# are recommended to use SQLClient rather than OLEDB or ODBC for the ultimate in performance, but here in Lazarus ODBC will work adequately and reliably)
The TODBCConnection component was developed originally to circumvent the strict MySQL license for applications that are not GPLed or do not obey MySQL AB's FLOSS exception.
Installing ODBC and ODBC Drivers
Before you can connect to your database using ODBC, you need to install
- an ODBC Driver Manager
- an ODBC driver specific to the DBMS you want to connect to
The ODBC Driver Manager
Windows
Windows has an ODBC Driver Manager built in, which allows DSNs to be set up, and other configuration. It is found in the Control Panel, in later Windows versions with categorised sections it was moved into the Administrative Tools area. Or you may simply click the Start button and enter ODBC into the Run box (in Windows 7 this appears as "Search programs and files").
Unices
Two popular ODBC Driver Managers for Unix-based platforms are unixODBC and iODBC.
ODBCConn
is known to work with unixODBC; iODBC compatibility still has to be tested.
Debian
For Debian, you can install the unixodbc package:
aptitude install unixodbc
aptitude install unixodbc-bin # if you want some GUI tools
The odbcsqldyn
unit, and hence odbcconn
, will search for a file called libodbc.so
.
It will not accept a file named like libodbc.so.1
or libodbc.so.1.0.0
.
Debian's unixodbc package does not create a symlink with the name /usr/lib/libodbc.so
; you must either
- create the link yourself:
ln -s libodbc.so.1 /usr/lib/libodbc.so
, or - install the unixodbc-dev package, which does create the symlink.
If you installed the unixodbc-bin package, you can run the ODBCConfig
program to configure ODBC drivers and DSNs.
Ubuntu
For Ubuntu, follow the instruction for Debian. Note: the unixodbc-bin package might not be available from the default package repository.
ODBC Drivers
TODO
Connecting to an ODBC data source
The parameters for connecting to an ODBC data source are described in a connection string.
This is a string of the form NAME=VALUE;NAME=VALUE
.
TODBCConnection
provides a wrapper around this connection string.
Some of its properties are mapped to name-value pairs in the connection string, and custom parameters can be specified in the Params
property (which is a TStrings
).
The ODBC connection string
Before going to the details of this wrapper, you must first have a basic understanding of how an ODBC data source is identified. First of all, an ODBC driver manager provides alternative ways to make DSN shortcuts for a set of parameters:
- DSN (DataSource Name): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator or by manually editing the
odbc.ini
file (or registry). - File DSN: a file which contains connection parameters. An ODBC Data Source Administrator usually allows you to create File DSNs from the GUI.
The parameters in a DSN or File DSN can always be combined with additional parameters in the connection string, for example to specify a password.
The ODBC specification defines a few parameters that can be used in a connection string:
- Two special parameters,
DSN
andFILEDSN
, allow one to select a set of pre-defined parameters, as described above. - The
DRIVER
specifies which ODBC driver to use. Obviously, this is a very important parameter. - The
UID
andPWD
parameters are used to supply a username and password.
All other parameters are driver dependent. Please refer to the documentation of the specific driver to learn more about available parameters and their names!
Without a DSN
You may also connect via ODBC without using a DSN, simply supplying all the details in the Connection String that you would otherwise set up in a DSN (driver,server,database,login details). This more direct method avoids the need to set up a DSN when installing your application on a new machine.
TODBCConnection properties
The following table describes the mapping of TODBCConnection
properties to ODBC connection string parameters:
Property | Type | Connection string parameter |
---|---|---|
Driver |
string |
DRIVER
|
DatabaseName |
string |
DSN , not to something like DATABASENAME , which is not part of the ODBC standard
|
FileDSN |
string |
FILEDSN
|
Password |
string |
PWD
|
UserName |
string |
UID
|
HostName |
string |
none; there is no corresponding parameter in the ODBC standard |
Params |
TStrings |
Use this to specify custom parameters. Each item must be of the form NAME=VALUE .
|
Note that TODBCConnection
will take care of escaping parameter values in the connection string (when required).
The LoginPrompt
boolean property is not implemented yet.
It would require finding the correct window handle, so a driver can show a GUI dialog to specify parameters.
Note that this is not controlled by the connection string, but rather by the last parameter to the ODBC API function SQLDriverConnect
.
Examples
In this section, examples are given of connecting to certain DBMSs using their specific ODBC drivers.
Connecting to MySQL
For a reference of supported parameters by the MyODBC driver, see [1].
The driver name differs a bit depending on the platform and MyODBC version; examples are:
{ properties of connection object, i.e. conn: TODBCConnection; }
conn.Driver := 'MySQL'; // (Unix)
conn.Driver := 'MySQL ODBC 3.51 Driver'; // (Windows)
conn.Driver := 'MySQL Connector/ODBC v5'; // (Windows)
// note: driver name doesn't need {} like it does in SQL Connection Strings elsewhere
Other parameters:
conn.UserName := 'myUsername';
conn.Password := 'myPassword';
// conn.Params.Strings set using .add method :
conn.Params.Add('SERVER=example.com');
conn.Params.Add('PORT=3306');
conn.Params.Add('DATABASE=myDatabase');
Connecting to MS Access
On Windows, you can use
{ properties of connection object, i.e. conn: TODBCConnection; }
conn.Driver := 'Microsoft Access Driver (*.mdb)';
// set conn.Params.Strings :
conn.Params.Add('DBQ=C:\path\to\my\database.mdb');
Notes: The MS Access ODBC driver seems not to support the SQLPrimaryKeys
API function, hence UpdateIndexDefs
will fail.
Currently, you must set SQLQuery.UsePrimaryAsKey:=false;
to avoid an exception until code has been added that tests for the availability of the function in the driver.
It seems that a pathname with national (accentuated) characters is not supported.
Connecting to Microsoft SQL Server
See http://www.connectionstrings.com/sql-server#p7 for more details on connection strings.
On Windows, you can use something like this with an existing named DSN:
{ properties of connection object, i.e. conn: TODBCConnection; }
conn.DatabaseName := '<Your_DSN_Name>';
{ Leave all other conn. properties empty }
Without a DSN:
{ properties of connection object, i.e. conn: TODBCConnection; }
conn.Driver := 'SQL Server';
// note: driver name doesn't need {} like it does in SQL Connection Strings elsewhere
{ Leave Hostname and DatabaseName properties empty }
{ set conn.Params.Strings : }
conn.Params.Add('Database=<yourdatabasename>');
conn.Params.Add('Server=.\SQLEXPRESS');
conn.Params.Add('Trusted_Connection=Yes'); // or 'Integrated Security=SSPI'
Note: In this example, we connect to the local machine (server=.), on the instance SQLEXPRESS. You can use a hostname instead of ., and you can omit the instance to connect to the default instance. See MS documentation for details.
Using a trusted connection (also known as integrated security or SSPI) means you login using your Windows user credentials. You can omit the Trusted_Connection line, but then you need to specify user ID (Uid=...) and password (Pwd=...).
Another Microsoft SQL Server example
The example code below selects the rows from a table called 'journal_entries' and displays all the values of column 'journal_entry' in a Memo control called Memo1.
// uses ODBCConn, sqldb;
procedure TForm1.Button1Click(Sender: TObject);
var
S: String;
conn: TODBCConnection;
query: TSQLQuery;
transaction: TSQLTransaction;
begin
conn := TODBCCOnnection.Create(nil);
query := TSQLQuery.Create(nil);
transaction := TSQLTransaction.Create(nil);
try
try
conn.HostName := '127.0.0.1';
conn.DatabaseName := 'diary'; {replace this with the name of your database}
conn.Transaction := transaction;
conn.UserName:= 'db_username';
conn.Password:= '';
query.DataBase := conn;
{ The following line is required, else you get "could not retrieve primary key metadata".
I was really stuck on this until I found http://bugs.freepascal.org/view.php?id=13241 }
query.UsePrimaryKeyAsKey:=false;
query.SQL.Text := 'select * from diary.dbo.journal_entries';
query.Open;
S := '';
while not query.EOF do
begin
S := S + query.FieldByName('journal_entry').AsString + #13#10;
query.Next;
end;
finally
query.Free;
conn.Free;
transaction.Free;
end;
except
on E: Exception do
ShowMessage(E.message);
end;
Memo1.Text:= S;
end;
Debugging ODBC
There are several facilities in ODBC that are helpful when debugging your ODBC application.
Error Messages
Each ODBC API call also returns a succes code; if there is an error, more information can be retrieved using calls to SQLGetDiagRec
. (For the sake of completeness: diagnostic records are also available when an API call returns SQL_SUCCES_WITH_INFO
.)
TODBCConnection
checks the return code of each ODBC call and contructs an EODBCException
.
The message
of this exception consists of:
- a message identifying what the
TODBCConnection
was doing when the error occurred - the return code of the ODBC API call (e.g.
SQL_ERROR
) - a number of diagnostic records (obtained using
SQLGetDiagRec
); each record consists of three fields:- a 5-character error code identifying the error
- a 'native error code'
- a message describing the error
Note: if you get a 'Function sequence error' in the finalization section of the ODBCConn
unit, then you probably did not properly clean up all you queries and connections.
Tracing
Most ODBC managers have a tracing option in which all ODBC API calls are logged to a trace log. This can be very useful for debugging an ODBC application. The ODBC Data Source Administration GUI of both Windows and unixODBC have a tab where you can configure the tracing option.
Of course the trace log is mainly useful for developers that are familiar with the ODBC API, but it can help to identify the problem. Also, you can attach a trace log if you report a problem to the bug tracker.