Basic Connection String Attributes and Defaults

The FairCom Database provides a variety of interfaces. This document provides the connection strings required for establishing a connection through some of the most popular interfaces:

The following are basic FairCom DB parameters and definitions used in all drivers and interfaces described below.

FairCom DB Interactive SQL - ISQL

>isql  -u <user>  -p <password>   <connection-string>

A standard FairCom DB SQL connection string consists of tcp-port@machine-name:database-name

Example

>isql  -u ADMIN -p ADMIN  6597@localhost:ctreeSQL

TLS/SSL Example

>isql -u admin -p ADMIN ssl:6597@localhost:ctreesql

FairCom DB ODBC Data Sources

Invoke the ODBC Data Source Administrator. Select FairCom DB SQL from the list of installed system DSN drivers and fill in the dialog fields as shown below:

• Data Source Name (DSN) - Local name for FairCom DB SQL data source
• Description - Optional descriptive text.
• Host, Database, User ID, and Password - The driver uses these values if the application does not supply them. If no you leave these blank, the user is prompted when the application connects.
• Service
• Default Fetch Size - Size (in bytes) used to fetch multiple rows from the server reducing network requests resulting in performance gains. Connection string attribute FETCH_SIZE=[ bytes ] *

Default - 5000

• Default Query Timeout - Query Timeout (in seconds) Connection string attribute: QUERY_TIMEOUT=[ number of seconds ] *
• Preserve Cursor - Yes or No depending on your application requirements.
• Client Character Set - Client character set.
• Options - Optional FairCom DB SQL specific parameters (Refer to online documentation).

* Connection string settings take precedence over defined DSN values.

ADO.NET Connection String

ADO.NET connection strings are passed into CtreeSqlConnection objects as semicolon-separated Name-Value pairs. The Name portion of the Name-Value pair is not case-sensitive.

The following table lists Name-Value pairs used in connection strings for the FairCom DB SQL ADO.NET Data Provider.

You can use any of the synonyms as a replacement for the Name portion of the pair to remain cross-compatible with usage of other database products. The most common synonyms are shown below.

The minimum required are User ID and Password. If other values are not specified, default values will be assigned as noted.
 

Example

A typical connection string for the FairCom DB ADO.NET Data Provider would be composed of:

"User ID=ADMIN;Password=ADMIN;database=ctreeSQL;server=localhost;port=6597”

A minimal connection string can be:

"User=ADMIN;Password=ADMIN"

TLS/SSL Examples

"UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Server=localhost;Service=6597;ssl=basic";

"UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com";

"Database=ctreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;sslcert=support.faircom.com;ClientSSLCert=ADMIN";

JDBC URL Connection String

Beginning with FairCom DB V11.2 and FairCom RTG V2, the connection string is in the following format:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The valid values for param are:

• characterEncoding - Replace encoding with a valid Java encoding name (e.g., US‑ASCII, ISO‑8859-1, UTF‑8, etc.).
• password
• User
• ssl - Values of basic (no peer certificate authentication) or peerAuthentication (server certificate authentication)

When peerAuthentication is requested, the client’s trust store must contain the server’s certificate as shown in the example below.

The tutorials use a connection string that is set for the default configuration:

"jdbc:ctree://localhost:6597/ctreeSQL", "ADMIN", "ADMIN"

TLS/SSL Examples

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=basic");

System.setProperty("javax.net.ssl.trustStore","TrustStore.key");

System.setProperty("javax.net.ssl.trustStorePassword","mypassword""");

Connection c = getConnection("jdbc:ctree://localhost:6597/ctreeSQL?ssl=peerAuthentication");

For backward compatibility, the older format ("jdbc:ctree:6597@localhost:ctreeSQL", "ADMIN", "ADMIN") is still supported but should be considered deprecated.

Adding FairCom DB JDBC to a Third-Party JDBC Tool

The FairCom DB JDBC API can be used with third-party applications such as ReadyAPI application from SmartBear. The steps below explain how to install and configure the FairCom DB JDBC Driver to work with this application.

You will need to know how to load a new JDBC Driver within the ReadyAPI application, as explained on their website: https://support.smartbear.com/readyapi/docs/testing/data-driven/drivers/install/index.html

Steps 1 and 2 from the link shown above are accomplished by copying the ctreeJDBC.jar file to the ReadyAPI bin/ext directory, such as:

C:\Program Files\SmartBear\ReadyAPI-2.4.0\bin\ext

Make sure you have Administrator privileges to be able to copy there.

You can find the ctreeJDBC.jar file in your FairCom DB PRO package in this default location: \FairCom\v11.5.0\winX64\lib\sql.jdbc

Once you restart the ReadyAPI, you can configure the FairCom DB JDBC Driver as shown below:

This corresponds to the following FairCom DB JDBC connection string:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The image below shows a select * from one of the FairCom DB sample tables: