Firebird ODBC ConfigurationDeveloping with the Firebird ODBC/JDBC Driver

The ODBC/JDBC driver attempts to connect a client to the Firebird server according to a set of attributes that default to those provided by the DSN definition.Those stored attributes can be — and usually are — overridden by parameters passed by the application or read from a file (FILEDSN) when it prepares to connect.

Connection Parameters

The connection parameters for the driver comprise a list of strings in the form KEYWORD=value, separated by semicolons (‘;’).The following table enumerates the keywords with their verbose meanings and, where it is not obvious, their possible values.

Table 1. Keywords for Connection Attributes
Keyword	Description	More Information
`UID`	Database account, i.e. username
`USER`	Database account, i.e. username
`PWD`	Password
`PASSWORD`	Password
`ROLE`	Role
`DSN`	Data source name
`DRIVER`	Driver name	e.g., the string `Firebird/InterBase(r) driver`.Defaults to the driver defined in the DSN.
`DBNAME`	Database	Full path to the database as seen by the server, including IP address \| server name[/port] for a remote connection.Defaults to the database defined in the DSN.
`DATABASE`	Database
`CLIENT`	Local path to the required `fbclient` library	May be needed if the connection is to be via an embedded server library located in an application folder.
`CHARSET`	Client-side default character set	Should be the same as the default character set of the database, if possible;or one that is known to be codepage-compatible.
`CHARACTERSET`	Client-side default character set
`READONLY`	Read-only	Set transactions in this connection to be read-only.The default is read/write.
`NOWAIT`	No wait	Set transactions in this connection to have NO WAIT lock resolution.The default is WAIT.
`LOCKTIMEOUT`	Set the lock timeout on WAIT transaction	Pass the number of seconds to elapse after encountering a lock conflict until a transaction is to return an error.Not valid if the transaction is set for NO WAIT resolution.
`DIALECT`	Set SQL dialect	Only 1 or 3 is valid.Normally this would have been set in the DSN.It must match the dialect of the database.
`QUOTED`	Set on quoted identifiers	If set in the DSN, the setting should be correct, i.e. already `ON` or `OFF`.
`SENSITIVE`	Set on case-sensitive identifiers	If set in the DSN, the setting should be correct, i.e. already `ON` or `OFF`.
`AUTOQUOTED`	Set on auto-quoting identifiers	If set in the DSN, the setting should be correct, i.e. already `ON` or `OFF`.
`USESCHEMA`	Set on “use schema”	If set in the DSN, the setting should be correct.
`SAFETHREAD`	Safe threading
`FILEDSN`	File DSN	Path to a file where the attribute strings from a previous connection are stored.If this string is present, the contents of the file will take priority over the main DSN.
`SAVEDSN`	Save DSN	Path to a file where the attribute strings from this connection, if successful, are to be stored.The password will be saved in encrypted format.

docnext count = 4

Read Sequence of the Keys

The ODBC function SQLDriverConnect gives priority to the attributes defined in the connection string, only fetching those stored in the DSN, or in a cited FILEDSN, to fill in any gaps.

Connection Examples

Some examples of connection strings for applications that use the ODBC function SQLDriverConnect:

Open("DSN=myDb;")

Here, the function is expected to read everything it needs from the DSN.User name and password are not supplied in the string.If they are not present in the DSN, either

it will use the environment variables ISC_PASSWORD and ISC_USER if they are are set; otherwise
it will prompt the user for the login credentials

Open("DSN=myDb; UID=MCSSITE; PWD=mcssite;")

The function should have what it needs to make this connection, provided the user name and password are authenticated by the server.

Open("DSN=myDb; UID=MCSSITE; PWD=mcssite; DBNAME=172.17.2.10:/usr/local/db/myDb.fdb;")

Open("DSN=myDb; UID=MCSSITE; PWD=mcssite; DBNAME=myserver:/usr/local/db/myDb.fdb;")

The DBNAME key points to the server IP address in the first example, with the path to the database file in the POSIX format.The second example is making the same connection, using the server’s host name instead of the IP address.

Three examples including the DRIVER attribute in the string:

Open("DRIVER=Firebird/InterBase(r) driver; DBNAME=172.17.2.10:/usr/local/db/myDb.fdb;")

Open("DRIVER=Firebird/InterBase(r) driver; UID=MCSSITE; PWD=mcssite; DBNAME=172.17.2.10:/usr/local/db/myDb.fdb;")

Open("DRIVER=Firebird/InterBase(r) driver; UID=MCSSITE; PWD=mcssite; DBNAME=dummy;")

In the last example, a local connection using a database alias in place of the database file path.Of course, the alias must be present in aliases.conf in the root directory of the Firebird server (or, for Firebird 3 and up, in databases.conf).

Using the server IP address and specifying an alternative port, with the target database on a POSIX server;and the same using the server’s host name instead:

172.17.2.10/3051:/usr/local/db/myDb.fdb

myserver/3051:/usr/local/db/myDb.fdb

Using the server IP address, with the target database on a Windows server;and the same using the server’s host name instead:

172.17.2.10:c:\db\myDb.fdb

myserver:c:\db\myDb.fdb

Using the server IP address and specifying an alternative port, with the target database on a Windows server;and the same using the server’s host name instead:

172.17.2.10/3051:c:\db\myDb.fdb

myserver/3051:c:\db\myDb.fdb

Using TCP/IP local loopback, using the local loopback IP address on a POSIX server;and the same using the local loopback host name localhost:

127.0.0.1:/usr/local/db/myDb.fdb

localhost:/usr/local/db/myDb.fdb

The same things on a Windows server:

127.0.0.1:c:\db\myDb.fdb

localhost:c:\db\myDb.fdb

DBNAME for Embedded Connections

The DBNAME value for embedded connections and for the “Windows Local” (XNET) style of connection uses just the file path or alias, without host name, IP address or any port number.

Note

From Firebird 3 on, the way we conceptualise non-network connections on all platforms is more unified than for the earlier versions.However, from the point of view of the ODBC/JDBC driver, the expression of the DBNAME value has not changed, regardless of the platform on which we are making our embedded connection.

Local connection on a Windows server using first the file path and next an alias:

DBNAME=C:\db\myDb.fdb

DBNAME=C:dummy

On a POSIX server:

DBNAME=/usr/local/db/myDb.fdb

DBNAME=dummy

DBNAME Using Aliases

It is strongly recommended to define and use aliases to simplify life for you and your users.It makes your DBNAME values completely neutral to the filesystem and so much less cumbersome.In our last pairs of examples, the same alias was used on both Windows and POSIX.The one on the Windows server would be defined thus:

dummy = C:\db\myDb.fdb

while, on the Linux server, it would be defined thus:

dummy = /usr/local/db/myDb.fdb

Connecting To Firebird From Applications

Connecting to Firebird from Applications