ODBC | Configuring A DSN On Windows

The DSN Settings

After clicking btn:[Finish] on the previous screen, you are presented with a form into which you will enter the parameters for a connection and will be able to test that they all work.

Figure 1. Setting up parameters for the DSN

Table 1. Parameters for the DSN Configuration
Parameter	Entry
Data Source Name (DSN)	REQUIRED.A unique, meaningful name indicating the type of connection or its use.Make it brief as you can expand the narrative elsewhere.Examples: “Connect from FbEmbed” or “ConnectFbServer”
Description	Optional.Can be used to provide more details about the data source.
Database	REQUIRED.Full address of the database, as required for an embedded or network connection.If the connection is remote, it can be in TCP/IP or WNET format.TCP/IP is recommended.Firebird database aliases are supported.Refer to Connection Examples.
Client	May be required.Local path to the Firebird client library.For embedded connections to a sub-V.3 Windows server, it can point to the copy of `fbembed.dll` in the application directory;otherwise, point it to where you have located the bitness-compatible Firebird remote client library unless you are certain the correct library will be found automatically in a system location.
Database Account	Optional, since login credentials can be captured during connection to a Firebird database.If it is not configured, the ODBC interface will prompt for a user ID (UID or USER) at connection time.
Password	Optional, since login credentials can be captured during connection to a Firebird database.If it is configured, it should be the password for the supplied User ID.Otherwise, the ODBC interface will prompt for a password (PWD or PASSWORD) at connection time.Any password configured is encrypted automatically and saved in `odbc.ini`.Storing the password here should not be a security risk.
Role	Optional.If it is defined and the login is by SYSDBA, role is ignored;otherwise, the login credentials, whether stored or captured at connection, must have been granted that role prior to the login attempt.
Character Set	May be blank.Sets the default character set of the client.
Options (set here in DSN or specify dynamically)
Transaction parameters
Read (default write)	Transactions are read/write by default.Check to make transactions read-only.
Nowait (default wait)	The transaction will wait if it encounters a lock conflict.Check to have the transaction return an error immediately upon encountering a lock conflict.
Lock timeout	When a transaction is set for WAIT conflict resolution, express the length of time in seconds until the lock times out and a lock conflict error is returned (`isc_lock_timeout`).
Other optional parameters
Dialect	SQL dialect for the client to use in accessing the database.The only valid options for Firebird are 1 or 3.Note, Dialect 1 is not compatible with quoted identifiers;Dialect 3 will not accept strings delimited by double quotes.
Quoted Identifier	Causes pairs of double quotes to be treated solely as delimiters of case-sensitive object identifiers.Attempts to pass double quotes as string delimiters will be treated as errors in both dialects.Note, double-quoted strings have always been illegal in Dialect 3.
Sensitive Identifier	This option affects the way the client treats the property `SQL_IDENTIFIER_CASE`.`SQL_IC_UPPER` (value=1) is the default, treating all identifiers as stored in upper case characters.Check to select `SQL_IC_SENSITIVE` (value=3) to have the interface treat all identifiers that are not in all upper case as though they were case-sensitive.This is not recommended!For an explanation, see Note (1) below.
Autoquoted Identifier	Default is `NO`.The effect of checking this is to change the setting to `YES`.In that case, every identifier in every statement will be double-quoted automatically.The need to set this on would be highly unusual and would need to be well understood to avoid non-stop errors.
SCHEMA options	Drop-down list offering three options for treatment of SQL schemas, which Firebird does not support.Normally, leave this at the default setting menu:Set null field SCHEMA[].For some details, see Note (2) below.

Note

Note (1) regarding “Sensitive identifier”

If this setting is checked on, it would cause this statement

SELECT A.Test_Field FROM Mixed_Caps_Table A
ORDER BY A.Test_Field

to be converted to

SELECT A."Test_Field" FROM "Mixed_Caps_Table" A
ORDER BY A."Test_Field"

The following would result in a wrong conversion:

Select A.Test_Field From Mixed_Caps_Table A
Order By A.Test_Field

gets converted to

"Select" A."Test_Field" "From" "Mixed_Caps_Table" A
"Order" "By" A."Test_Field"

Note

Note (2) regarding SCHEMA settings

Some applications generate SQL statements automatically, based on user inquiries, on the assumption that the target database supports namespaces and SQL SCHEMAs.For example,

select SYSDBA.COUNTRY,SYSDBA.CURRENCY from SYSDBA.COUNTRY

or

select * from SYSDBA.COUNTRY

This selection of schema settings attempts to prevent clashes with applications that do this kind of thing.The drop-down list offers the three variants:

Set null field SCHEMA
Remove SCHEMA from SQL query
Use full SCHEMA

menu:Set null field SCHEMA[] is the default, causing the SCHEMA element to be set NULL whenever it is specified as part of a query.The result is a query that Firebird can process.

menu:Remove SCHEMA from SQL query[] filters the namespace references from the statement whenever the SQLExecDirect command receives a request such as

select SYSDBA.COUNTRY,SYSDBA.CURRENCY from SYSDBA.COUNTRY

transforming it before passing it to the API as

select COUNTRY,CURRENCY from COUNTRY

menu:Use full SCHEMA[]] is reserved for a future in which Firebird has the capability to process these concepts itself — perhaps in Firebird 4.In that event, the driver will have no need to screen out these constructions.

Click on the btn:[Test connection] button to confirm that your configuration is good:

Figure 2. Testing the Configuration

If all is well, click btn:[OK], return to the main form and save the configuration by clicking btn:[OK] there, too.

The Services Button

The Services button launches a number of server management utilities through a GUI management console.It is described later in The Services Interface.

Configuring a DSN on Linux

Pavel Cisar

Configuration depends on the Linux distribution but, somewhere in /etc or /etc/unixODBC, should be two files named odbc.ini and odbcinst.ini.

Add to odbcinst.ini:

[Firebird]
Description     = InterBase/Firebird ODBC Driver
Driver          = /usr/local/lib64/libOdbcFb.so
Setup           = /usr/local/lib64/libOdbcFb.so
Threading       = 1
FileUsage       = 1
CPTimeout       =
CPReuse         =

Add to odbc.ini:

[employee]
Description     = Firebird
Driver          = Firebird
Dbname          = localhost:/opt/firebird/examples/empbuild/employee.fdb
User            = SYSDBA
Password        = masterkey
Role            =
CharacterSet    =
ReadOnly        = No
NoWait          = No

Testing the Configuration

UnixODBC has a tool named ISQL (not to be confused with Firebird’s tool of the same name!) that you can use to test the connection, as follows:

isql -v employee

If you have connection problems, make sure the directory where you placed the Firebird ODBC shared library, e.g. /usr/local/lib64/libOdbcFb.so, is on the system loadable library path.If not you could set:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc

or, more simply,

export LD_LIBRARY_PATH=/usr/lib/odbc

If you still have problems, the next thing is to try an strace to try to identify them:

strace -o output.txt isql -v employee

The DSN Settings

The DSN Settings

The Services Button

docnext count = 2

Configuring a DSN on Linux

Testing the Configuration