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.
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:
Refer to the section relevant to your platform to continue:
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:
2. Select the User DSN tab to set up a data source that only you can access.
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:
4. Select Easysoft Firebird ODBC and click Finish.
The Easysoft ODBC-Firebird Driver DSN Setup dialog box is displayed:
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.
| 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. |
| (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. |
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.
Database = server:/path/to/database.fdb
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.
Description = Easysoft Firebird ODBC Driver
Driver = /usr/local/easysoft/fb/libfbodbc.so
Setup = /usr/local/easysoft/fb/libfbodbcS.so
4. Amend the Driver and Setup attributes to the reflect the install directory you chose in step 14.
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:
the value entered may be "1", "2" or "3".
The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.
| DSN Dialog Box (Windows) | DSN |
| odbc.ini file (Unix) | [value] |
| Connect String | DSN=value |
Descriptive text which may be retrieved by certain applications to describe the data source.
| DSN Dialog Box (Windows) | Description |
| odbc.ini file (Unix) | Description=value |
| Connect String | Not Used |
The connection string passed to the Firebird Connect function.
| 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:
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.
DATABASE=c:\program files\firebird\database\employee.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
DATABASE=servername:/opt/firebird/p4.fdb
DATABASE=\\servername\c:\program files\firebird\database\employee.fdb
The name of the user that will be supplied to Firebird to authenticate the connection.
| 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.
The password supplied to Firebird to authenticate the connection
| 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.
An optional Firebird role by which a user can connect.
| 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.
Specifies that owner and schema information should be returned from metadata calls.
| 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
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.
Instructs the Easysoft ODBC-Firebird Driver to report update conflicts.
| 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.
The SQL dialect passed to the server when processing SQL queries.
| 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.
Indicates to the Easysoft ODBC-Firebird Driver not to use the precision field in metadata queries.
| 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
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.
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)
| 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.
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.
| 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.
"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'
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.
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.
| 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.
Restricts a database connection to read-only access.
| 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.
Allows a default Firebird character set to be specified for the database connection.
| 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).
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
| 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.
Returns the default column type from the SQLColumns statement.
| 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.
Causes the database connection to use a different default isolation method for transactions.
| 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.
| 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.
Use a Commit statement instead of a Commit_Retaining statement when writing to a database.
| 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.
Returns VARCHAR data in the same format as CHAR data (i.e. they are not right space truncated).
| DSN Dialog Box (Windows) | Pad Varchar |
| odbc.ini file (Unix) | PadVarchar=0|1 |
| Connect String | PADVARCHAR=0|1 |
Controls the schema data returned from metadata calls.
| 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.
Control the precision information that the driver returns for NUMERIC fields
| 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.
Alters the method used to convert between UNICODE and ANSI data
| 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.
Select the DEFAULT datatype for wide character conversion
| 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.
Select the code page to use when mapping a character string to a wide character (Unicode) string.
| 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.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
