Easysoft ODBC-Firebird Driver User Guide - Configuration

Configuring the Easysoft ODBC-Firebird Driver

In terms of the Easysoft ODBC-Firebird Driver, the client is the machine running the ODBC application with which you want to access data on your Firebird server.

To allow an ODBC application on the client machine to access data on the remote server, you need to create a data source on the client.

This data source uses the Easysoft ODBC-Firebird Driver and specifies the attributes required to connect to the Firebird database on the server.

Before setting up a data source on your client machine, you must have successfully installed the Easysoft ODBC-Firebird Driver on this machine.

Instructions for installing the Easysoft ODBC-Firebird Driver on Windows and Unix platforms are provided in Installation.

Chapter Guide

Configuring the driver

This section outlines how to configure the attribute fields which are available to be set for the Easysoft ODBC-Firebird Driver.

Attribute fields are configured either:

¯ OR ¯

Refer to the section relevant to your platform to continue:

Windows Setup

To connect an ODBC application on a Windows machine to an Firebird database on a remote server:

1.  In Control Panel, double-click Administrative Tools and then Data Sources (ODBC).

 The ODBC Data Source Administrator dialog box is displayed:

Figure 2: The ODBC Data Source Administrator dialog box

2.  Select the User DSN tab to set up a data source that only you can access.

¯ OR ¯

 Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.

3.  Click Add... to add a new data source.

 The Create New Data Source dialog box displays a list of drivers:

Figure 3: The Create New Data Source dialog box

4.  Select Easysoft Firebird ODBC and click Finish.

 The Easysoft ODBC-Firebird Driver DSN Setup dialog box is displayed:

Figure 4: The Easysoft ODBC-Firebird Driver DSN Setup dialog box

 Test allows you to check that the client is able to connect to the specified server data source.

For details of the other attributes that can be set on this dialog box, see Attribute Fields.


64-bit Windows

The Easysoft installer program installs both a 32-bit and a 64-bit version of the Easysoft ODBC-Firebird Driver. If you want to use a 64-bit ODBC application, you need to use the 64-bit Easysoft ODBC-Firebird Driver. If you want to use a 32-bit ODBC application, you need to use the 32-bit Easysoft ODBC-Firebird Driver.

There is both a 32-bit and a 64-bit version of ODBC Administrator. The 64-bit ODBC Administrator is located in Control Panel under Administrative tools. To access the 32-bit ODBC Administrator, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

Easysoft ODBC-Firebird Driver data sources created in the 64-bit ODBC Administrator will specify the 64-bit version of the Easysoft ODBC-Firebird Driver. Easysoft ODBC-Firebird Driver data sources created in the 32-bit ODBC Administrator will specify the 32-bit version of the Easysoft ODBC-Firebird Driver.

System data sources created in the 64-bit ODBC Administrator are only visible to 64-bit applications. If you want to create an Easysoft ODBC-Firebird Driver System data source for use with a 64-bit application, use the 64-bit ODBC Administrator therefore. Likewise, System data sources created in the 32-bit ODBC Administrator are only visible to 32-bit applications.



64-bit Windows

(The reason for this is that System data sources created in the 64-bit ODBC Administrator are stored in a registry key called HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. System data sources created in the 32-bit ODBC Administrator are stored in a registry key called HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI. The WOW64 layer intercepts registry calls to HKEY_LOCAL_MACHINE\Software that are made by 32-bit applications, and then redirects them to the HKEY_LOCAL_MACHINE\Software\WOW6432node key.)

User data sources are visible to both 32-bit and 64-bit applications, irrespective of the version of ODBC Administrator they were created in. If a 64-bit application connects to an Easysoft ODBC-Firebird Driver User data source created in the 32-bit ODBC Administrator, it will load the 64-bit version of the Easysoft ODBC-Firebird Driver. Likewise, a 32-bit application that connects to a 64-bit Easysoft ODBC-Firebird Driver data source will load the 32-bit version of the Easysoft ODBC-Firebird Driver.


Unix Setup

1.  Using the sample DSN entry created during the install as a template, create a DSN entry in the driver manager odbc.ini file. The entry will look something like the following.

 [FB_SAMPLE]

 Driver = FIREBIRD

 Description = Firebird driver

 Database = server:/path/to/database.fdb

 User = test

 Password = easysoft

 With_Schema = 0

 Dialect = 3

 Charset =

 Role =

 Nowait = 0

 OldMetaData = 0

 ExecProc = 0

 DQuote = 0

 UIDOverride = 0

 

2.  Amend the Database, User and Password attributes in the new odbc.inisection to suit your specific user installation.

 For details of the other attributes that can be set in odbc.ini, see Attribute Fields.

3.  The installation will create a odbcinst.ini entry that allows the driver manager to use the Firebird Driver To do this manually, add a entry containing the following entries.

 [FIREBIRD]

 Description = Easysoft Firebird ODBC Driver

 Driver = /usr/local/easysoft/fb/libfbodbc.so

 Setup = /usr/local/easysoft/fb/libfbodbcS.so

 FileUsage = 1

 DontDLClose = 1

4.  Amend the Driver and Setup attributes to the reflect the install directory you chose in step 14.

Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-Firebird Driver in a table showing:

Attributes which are text fields are displayed as "value".

Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.

For example, in the statement:

DIALECT=1|2|3

the value entered may be "1", "2" or "3".

DSN Name

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value
DSN Dialog Box (Windows) DSN
odbc.ini file (Unix) [value]
Connect String DSN=value

DSN Description

Descriptive text which may be retrieved by certain applications to describe the data source.

Interface Value
DSN Dialog Box (Windows) Description
odbc.ini file (Unix) Description=value
Connect String Not Used

Database Name

The connection string passed to the Firebird Connect function.

Interface Value
DSN Dialog Box (Windows) Database
odbc.ini file (Unix) Database=connection_string
Connect String DATABASE=connection_string

The connection string may be entered in several formats, depending on where the Firebird database server is located and the connection method required:


Connection String Format Connection Method Examples
db_file Local c:\program files\firebird\firebird_2_1\examples\empbuild\employee.fdbConnects to the sample Firebird database on Windows. The Firebird database server is installed on the same machine as the Firebird client./opt/firebird/examples/empbuild/employee.fdbConnects to the sample Firebird database on Linux/Unix. The Firebird database server is installed on the same machine as the Firebird client.
server:db_file TCP/IP my_remote_database_server:c:\program files\firebird\firebird_2_1\examples\empbuild\employee.fdbConnects to the sample Firebird database on Windows. The Firebird database server is installed on a different machine (my_remote_database_server) to the Firebird client.192.168.0.10:/opt/firebird/examples/empbuild/employee.fdbConnects to the sample Firebird database on Linux/Unix. The Firebird database server is installed on a different machine (192.168.0.10) to the Firebird client.

The Easysoft ODBC-Firebird Driver may prompt for this attribute if no value is supplied.

The connection string contains only the location of the Firebird database.

For example, on Windows:

DATABASE=c:\program files\firebird\database\employee.fdb

or Linux:

DATABASE=/opt/firebird/p4.fdb

As this type of connection is made in the process context of the client application, it can fail if the privileges of a user do not permit database access (e.g. if the connection is being made by a Web Server).

The client process uses a network protocol to connect to a server process, which then connects to the server database, so avoiding many problems with permissions encountered when connecting to a local database.

The server may be located either on a remote computer, or on the same computer as the client and the communication protocol used may be specified by the contents of the database string.

To establish a connection using TCP/IP, prefix the database with "servername:" (where "servername:" resolves via the normal DNS name lookup to a machine on the network).

It may be possible to use a dotted quad IP address instead of "servername", but this is dependent on the IP protocol stack being used.

For example, to connect via TCP/IP to a server on a Windows machine:

DATABASE=servername:c:\program files\firebird\examples\database\employee.fdb

or Linux:

DATABASE=servername:/opt/firebird/p4.fdb

DATABASE=\\servername\c:\program files\firebird\database\employee.fdb

User Name

The name of the user that will be supplied to Firebird to authenticate the connection.

Interface Value
DSN Dialog Box (Windows) User Name
odbc.ini file (Unix) User=value
Connect String UID=value

Note that this attribute must contain a valid user name for the Firebird server database.

If a value is specified in the connect string then any entry in the DSN will be ignored (see UID Override).

The Easysoft ODBC-Firebird Driver may prompt for this attribute if no value is supplied.

Password

The password supplied to Firebird to authenticate the connection

Interface Value
DSN Dialog Box (Windows) Password
odbc.ini file (Unix) Password=value
Connect String PWD=value

If a value is specified in the connect string then any entry in the DSN will be ignored (see UID Override).

The Easysoft ODBC-Firebird Driver may prompt for this attribute if no value is specified.

Role

An optional Firebird role by which a user can connect.

Interface Value
DSN Dialog Box (Windows) Role
odbc.ini file (Unix) Role=value
Connect String ROLE=value

See the Firebird documentation for more details on the use of roles.

Schema Information

Specifies that owner and schema information should be returned from metadata calls.

Interface Value
DSN Dialog Box (Windows) With Schema
odbc.ini file (Unix) With_Schema=0|1
Connect String SCHEMA=0|1

The Firebird metadata store contains a value for the Owner or Schema of a table.

So, for example, if the following CREATE TABLE statement was executed while logged in as USR1:

CREATE TABLE TEST1 (I INTEGER NOT NULL PRIMARY KEY)

a table called TEST1 would be created and its schema would be set to USR1.

However, Firebird does not have the ability to use this information in later SQL statements, so for instance

SELECT * FROM USR1.TEST1

would fail with a syntax error.

To avoid this, the default behavior of the Easysoft ODBC-Firebird Driver is to not return this information from metadata calls (such as SQLTables) unless this attribute is selected.

Selecting this attribute will cause most applications to fail when used with the Easysoft ODBC-Firebird Driver.

Deadlocks

Instructs the Easysoft ODBC-Firebird Driver to report update conflicts.

Interface Value
DSN Dialog Box (Windows) No Wait
odbc.ini file (Unix) Nowait=0|1
Connect String NOWAIT=0|1

Firebird uses a transaction model that avoids deadlocks between conflicting updates from concurrent applications.

This is achieved by pausing the transaction within any secondary application until the active transaction within a primary application has finished and then executing that secondary transaction.

By default, the Easysoft ODBC-Firebird Driver will also act in this manner, but selecting this attribute will return an error message reporting the update conflict.

SQL Dialect

The SQL dialect passed to the server when processing SQL queries.

Interface Value
DSN Dialog Box (Windows) Dialect
odbc.ini file (Unix) Dialect=1|2|3
Connect String DIALECT=1|2|3

For more details consult the Firebird documentation detailing the difference between dialects.

See Leave Quotes for details of how amending the value of this attribute may also affect the way in which the Easysoft ODBC-Firebird Driver converts certain SQL strings.

Metadata Precision

Indicates to the Easysoft ODBC-Firebird Driver not to use the precision field in metadata queries.

Interface Value
DSN Dialog Box (Windows) Pre IB6 MetaData
odbc.ini file (Unix) OldMetaData=0|1
Connect String OLDMETADATA=0|1

In current versions of InterBase and Firebird, the metadata describing NUMERIC columns contains both a precision and scale value, so that, for instance, a column created as

NUMERIC (10, 2)

would have a precision of 10 and a scale of 2.

However, in versions of InterBase prior to version 6, this column would have been stored in a double precision field, which cannot supply a fixed precision value, so the metadata for this version contains a column length only (8 in the case of a double precision field), rather than the precision value.

The Easysoft ODBC-Firebird Driver will convert the length value into a precision value, but in this case it would not be a 10, but an 18, this being the maximum precision that could be stored in the field.

In most cases the Easysoft ODBC-Firebird Driver will attempt to determine if the metadata contains a precision value without help, but there are some InterBase version 6 databases that do not have the precision value.

Selecting this attribute indicates to the Easysoft ODBC-Firebird Driver not to use the precision field in metadata queries.

This attribute is cleared by default, which is correct in most cases, but it should be selected if problems are encountered with the SQLColumns ODBC API.

Execute Proc

Whether stored procedures should be called by using a Select or an Execute statement. If you are calling an Executable procedure, enable Execute Proc (check the attribute or set it to 1). Otherwise, leave Execute Proc set its default value OFF (set to 0 or clear)

Interface Value
DSN Dialog Box (Windows) Execute Proc
odbc.ini file (Unix) ExecProc=0|1
Connect String EXECPROC=0|1
.

There are two types of Firebird procedure:

Execute Proc allows applications to call either type of procedure by using the standard ODBC escape sequence. For example:

{call myselectproc('myarg1', 'myarg2')}

{call myexecutableproc('myarg')}

When Execute Proc is disabled (the default), the Easysoft ODBC-Firebird Driver converts procedure calls to use the Select statement method. For example:

select * from myselectproc('myarg1', 'myarg2')

The called procedure then generates a result set that can be accessed in the same way as any other result set.

When Execute Proc is enabled, the Easysoft ODBC-Firebird Driver converts procedure calls to use the Execute Procedure method. For example:

execute procedure myexecutableproc('myarg')

The called procedure does not return a result set. It may return data if at least one of the procedure arguments has been defined as an output parameter into which values can be placed.

The Easysoft ODBC-Firebird Driver will generate a virtual result set consisting of a single row that contains any output values from the procedure. Unless the driver does this, applications that call SQLFetch following an executable procedure call can cause the Firebird server process to fail.

If an application uses a direct Firebird stored procedure call (rather than the ODBC escape sequence), the Easysoft ODBC-Firebird Driver will adapt to match that usage when Execute Proc is enabled.

Leave Quotes

Instructs the Easysoft ODBC-Firebird Driver to strip double quotes from any SQL string if the SQL Dialect attribute (see SQL Dialect) is set to 1 or 2.

Interface Value
DSN Dialog Box (Windows) Leave Quotes
odbc.ini file (Unix) DQuote=0|1
Connect String DQUOTE=0|1

Although SQL92 makes use of both single and double quotes in SQL strings, they have different meanings.

Given the SQL statement

SELECT * from TABLE WHERE

"COLUMN NAME" = 'COLUMN VALUE'

the result set would contain all the rows where the column with a name of "COLUMN NAME" contains the value 'COLUMN VALUE'.

Double quotes are used to quote identifiers (in this case column names) and single quotes are used to create character literals (normally values with a type of SQL_VARCHAR) containing printable characters.

Firebird treats quotes in this way when using Dialect 3, but double and single quotes are treated the same manner when using Dialect 1 or Dialect 2, so that the expression

WHERE "COLUMN NAME" = 'COLUMN VALUE'

would be identical to

WHERE 'COLUMN NAME' = 'COLUMN VALUE'

which would always evaluate to false.

This can cause problems with applications that use quotes around column and table names, because even though the Easysoft ODBC-Firebird Driver is queried as to the character to use for quoted identifiers, at least one application (Microsoft Access) fails if the application is instructed not to use any character for this purpose.

To overcome this issue, if the SQL Dialect attribute is set to 1 or 2 then the Easysoft ODBC-Firebird Driver will strip double quotes from any SQL string by default.

This default operation may be disabled by selecting this attribute.

UID Override

Allows the user and password information passed in as part of the connect string in the SQLDriverConnect function and the user and password used in the SQLConnect function to be overridden by the values in the DSN or .ini file.



Interface Value
DSN Dialog Box (Windows) UID Override
odbc.ini file (Unix) UIDOverride=0|1
Connect String Not Used

This functionality is useful as some applications (e.g. Seagate Crystal Reports in server mode) will pass the Windows (rather than the Firebird) user name and password to the Easysoft ODBC-Firebird Driver.

Normal use would not require the selecting of this attribute.

Read Only

Restricts a database connection to read-only access.

Interface Value
DSN Dialog Box (Windows) Read Only
odbc.ini file (Unix) ReadOnly=0|1
Connect String READONLY=0|1

Only Select statements are permitted and no Update, Insert or Delete statements are allowed.

Character Set

Allows a default Firebird character set to be specified for the database connection.

Interface Value
DSN Dialog Box (Windows) Character Set
odbc.ini file (Unix) Charset=value
Connect String CHARSET=value

See the Firebird documentation for a list of valid character sets (such as UNICODE_FSS).

Commit Select

Enable Commit Select (check the attribute or set it to 1), if you are calling a Select procedure that executes an Insert, Update or Delete statement

Interface Value
DSN Dialog Box (Windows) Commit Select
odbc.ini file (Unix) CommitSelect=0|1
Connect String COMMITSELECT=0|1
.

When Commit Select is disabled (the default), the Easysoft ODBC-Firebird Driver automatically commits Insert, Update or Delete statements when in auto-commit mode, but does not commit Select statements. Firebird Select procedures are called from a Select statement and can contain Insert, Update or Delete statements (or call other procedures that contain these statements). Enabling Commit Select forces the Easysoft ODBC-Firebird Driver to call a Commit statement after each call to a Select statement when auto-commit mode is on. This ensures that updates made by Select procedures are committed.

With Default

Returns the default column type from the SQLColumns statement.

Interface Value
DSN Dialog Box (Windows) With Default
odbc.ini file (Unix) WithDefault=0|1
Connect String WITHDEFAULT=0|1

However, this may not provide usable information, as the BLOB that comes back is the internal BLR representation of the default value.

Transaction Isolation

Causes the database connection to use a different default isolation method for transactions.

Interface Value
DSN Dialog Box (Windows) Txn Isolation
odbc.ini file (Unix) TxnMode=0|2|8
Connect String TXNMODE=0|2|8

the database default; in Firebird terms, either isc_tpb_read or isc_tpb_write, depending on mode

in Firebird terms, isc_tpb_read_committed and isc_tpb_rec_version

in Firebird terms, isc_tpb_consistency

Any non-zero value specified in this attribute will override any isolation method specified in the calling application.


NB

The default value for the Easysoft ODBC-Firebird Driver is set to 2 (SQL_TXN_READ_COMMITTED). This is NOT the same as the database default, which is 0 (SQL_TXN_REPEATABLE_READ).


The default isolation method can also be set via the ODBC API, but this attribute provides a way of setting it without altering the calling client application.

Flush Commit

Use a Commit statement instead of a Commit_Retaining statement when writing to a database.

Interface Value
DSN Dialog Box (Windows) Flush Commit
odbc.ini file (Unix) FlushCommit=0|1
Connect String FLUSHCOMMIT=0|1

Forces a Commit after a Select statement if required (e.g. with a Select statement from a stored procedure that does a update).

Note that although this ensures that transactions are restarted, the visibility of altered rows in other connections can be affected.

Pad Varchar

Returns VARCHAR data in the same format as CHAR data (i.e. they are not right space truncated).

Interface Value
DSN Dialog Box (Windows) Pad Varchar
odbc.ini file (Unix) PadVarchar=0|1
Connect String PADVARCHAR=0|1

Null Schema

Controls the schema data returned from metadata calls.

Interface Value
DSN Dialog Box (Windows) Null Schema
odbc.ini file (Unix) NullSchema=0|1
Connect String NULLSCHEMA=0|1

This option alters the way the driver returns schema information in the metadata call, and is used in conjunction with the With Schema option.

The ODBC specification states that for ODBC drivers that support schema (owner) information for the tables, but the current database does not have that information, the Schema field in the ODBC metadata fields (SQLTables, SQLColumns, etc.) should contain a empty string. And for those drivers that do not support the use of a schema, a NULL should be returned in those fields.

This option allows the selection of which of these two types of result the application requires. If the Null Schema option is set, then a NULL is returned, otherwise an empty string.

This option is normally only required with a few application, using the Driver under Microsoft SQL Server as a "Linked Table" is one such application.

Fix Precision

Control the precision information that the driver returns for NUMERIC fields

Interface Value
DSN Dialog Box (Windows) Fix Precision
odbc.ini file (Unix) FixPrecision=0|1
Connect String FIXPRECISION=0|1

Under normal use, the Firebird Driver will return the precision and scale of fixed precision data types, for example, NUMERIC( 10, 3 ).

However, while Firebird allows such a definition, it will actually allow larger numbers to be stored in the field, effectively ignoring the precision value.

Because of this, if a application used the above field, the driver will report a precision of 10 and a scale of 3, so the application will expect to only extract fields that conform to this specification. However in some cases, if the value in the database exceeds this precision, the application will report an error.

This option allows the behavior to be altered, such that the ODBC driver reports a precision that is the maximum possible precision that could be stored in the field, overriding the actual stored precision. This prevents applications reporting a error under these conditions.

Simple UNICODE

Alters the method used to convert between UNICODE and ANSI data

Interface Value
DSN Dialog Box (Windows) Simple UNICODE
odbc.ini file (Unix) SimpleUnicode=0|1
Connect String SIMPLEUNICODE=0|1

Under normal conditions, the Firebird Driver uses the operating system provided conversion routine to change the Firebird representation of UNICODE (UTF8) to the ODBC UNICODE standard (UCS-16). However it is possible to store single character values in a Firebird database, that is not a valid UTF8 character sequence. In this case, the operating system routine will not be able to perform the conversion, and will not give the expected result.

This option prevents the driver from using the conversion provided by the Operating System, but instead to use a simple conversion that will allow non UTF-8 data to be correctly converted to UCS-16. This simple conversion cannot correctly convert true UTF8 data, so the use of this option is governed by the actual data stored.

WCHAR Default to CHAR

Select the DEFAULT datatype for wide character conversion

Interface Value
DSN Dialog Box (Windows) Default SQLCHAR
odbc.ini file (Unix) WCharDefaultC=0|1
Connect String WCHARDEFAULTC=0|1

The ODBC specification allows applications to request that data is returned in a number of forms, and for each SQL datatype there is a DEFAULT type. The specification states, that for a wide character field, the default return type is a SQL_WCHAR, a UNICODE representation.

However, some Microsoft applications do not expect this. It seems that Microsoft Access has been created expecting the default return from a wide character field to be a SQL_CHAR, the ANSI representation.

Microsoft Access only operates in this way, if the field in question is in the primary key of the table, and the symptoms of this are the table opens, but displays #deleted for all the fields.

This option allows the driver to be configured to use the conversion that Microsoft Access expects. It should be remembered that this is enabling non-standard driver behavior, so this may cause problems with other application. If this is the case, create a separate DSN with this option set for use only with Access.

WCMB Mode

Select the code page to use when mapping a character string to a wide character (Unicode) string.

Interface Value
DSN Dialog Box (Windows) WCMB Mode
Connect String WCMB=value

The Windows version of the Easysoft ODBC-Firebird Driver uses the MultiByteToWideChar and WideCharToMultiByte functions to map character strings to and from wide character strings. Use WCMB Mode to specify the code page to use when doing this conversion. Refer to the Windows documentation for MultiByteToWideChar and WideCharToMultiByte for the meanings of the possible WCMB Mode values.