Skip to main content

JDBC tutorial

JDBC quick start tutorial for Java programmers

This tutorial helps Java programmers use the JDBC API to interact with FairCom servers.

Note

The <root> directory for this driver is <faircom>\drivers\sql.jdbc\

  1. Install and start up the FairCom server if it is not currently running.

  2. Optionally read the FairCom JDBC API introduction.

  3. Get started immediately with the source code found in the <faircom>\drivers\sql.jdbc\tutorials directory, or proceed with the following instructions.

FairCom JDBC API introduction

The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and the FairCom SQL Database Engine. The JDBC API provides a call-level API for FairCom SQL-based database access, which then allows you to use the Java programming language to exploit "Write Once, Run Anywhere" capabilities for applications that require access to enterprise data. An application written in the Java programming language alleviates the challenge of writing different applications to run on different platforms. For a closer look see the JDBC Developers Guide.

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.

Compile your project

You must compile the tutorial programs to run on Windows or Linux.

Command-Line compile for Windows

Compile and run the JDBC SQL tutorials from the Microsoft Windows command prompt.

  1. If the FairCom Database Engine is not already running on your machine, start the server.

  2. Run set from the command prompt to review and set your current settings.

    • The PATH should be set to include the path to your Oracle JDK by using a command something like this, but referencing the proper JDK on your machine:

      set JDKDIR="C:\Program Files\Java\jdk1.7.0_07"

      set PATH=%PATH%;%JDKDIR%\bin;%JDKDIR%\jre\bin;

    • The CLASSPATH should be set for your JDK and for the FairCom SQL JDBC Driver by using a command something like this, but referencing the proper JDK on your machine:

      set CLASSPATH="C:\Program Files\Java\jdk1.7.0_07"\jre\lib\rt.jar;

    • The CLASSPATH should also include the c-treeEDGE SQL JDK JAR file and the proper “tutorials” folder found in your <faircom> installation directory by using a command like this but referencing your c-treeEDGE installation directory.

      set CLASSPATH=%CLASSPATH%;C:\FairCom\V*\win32\lib\sql.jdbc\ctreeJDBC.jar;<faircom>\tools\guitools.java\lib\ctreeJDBC.jar;<faircom>\drivers\sql.jdbc\tutorials

  3. Compile and run the tutorial samples using a batch file or from the command-line .

    Batch file:

    Use the<faircom>\drivers\sql.jdbc\tutorials\cmdline\BuildTutorials.bat file to automatically build and then run the tutorials. The executable files with debugging information are placed in the <faircom>\drivers\sql.jdbc\tutorials directory.

    Command-line:

    Compile the tutorials from the command-line:

    javac JDBC_Tutorial1.java
javac JDBC_Tutorial2.java 
javac JDBC_Tutorial3.java 
javac JDBC_Tutorial4.java

    Run the compiled tutorials from the command-line:

    java JDBC_Tutorial1 
java JDBC_Tutorial2 
java JDBC_Tutorial3 
java JDBC_Tutorial4

    To fully observe JDBC in action, you can single-step through the code with a debugger (jdb):

    jdb JDBC_Tutorial1 
jdb JDBC_Tutorial2 
jdb JDBC_Tutorial3 
jdb JDBC_Tutorial4

  4. The output from the first tutorial project should appear similar to the following:

    INIT
    Logon to server...
DEFINE
    Open table...
    Add fields...
    Create table...
MANAGE
    Delete records...
    Add records...
    Display records...
        1000    Bryan Williams
        1001    Michael Jordan
        1002    Joshua Brown
        1003    Keyon Dooling
DONE
    Close table...
    Logout...
Press <ENTER> key to exit . . .

Troubleshooting

The examples below illustrate some of the errors that occur when these environment variables are not set properly. You may need to adjust the path to match your installation.

SQL Exception: 26003 - Connection refused: connect

The most common cause of this error is that the FairCom Database Engine is not running. Remember that the FairCom evaluation license times out after 3 hours, so the server might have exited and needs to be re-started. See Start the FairCom server.

JDK not found! Please set environment for the Java JDK

In this example we do not have the JDKDIR and PATH environment variable set properly, so it cannot find the Java JDK<faircom>\drivers\sql.jdbc\tutorials\cmdline>BuildTutorials.bat

'javac' is not recognized as an internal or external command, operable program or batch file

In this example we do not have the Java environment variables set properly in our PATH. <faircom>\drivers\sql.jdbc\tutorials>javac JDBC_Tutorial1.java

*** Execution aborted ***
INIT
Exception: ctree.jdbc.ctreeDriver
*** Execution aborted ***
Press <ENTER> key to exit...

In this example we do not have the CLASSPATH pointing to the FairCom SQL JDBC Driver directory. <faircom>\drivers\sql.jdbc\tutorials>java JDBC_Tutorial1

Error: Could not find or load main class JDBC_Tutorial1.class

In this example we attempt to execute a tutorial with the .class extension. Simply call the sample without this extension. Another source of this error is leaving the “drivers\sql.jdbc\tutorials” folder out of the CLASSPATH. <faircom>\drivers\sql.jdbc\tutorials>java JDBC_Tutorial1.class

Command-Line compile for Linux

This section explains the steps to compile and run the JDBC SQL tutorials from a Linux shell.

If the FairCom Database Engine is not already running on your machine, start the server.

Compiling

To compile the tutorials from the Linux command line, follow these steps:

  1. CD into the proper folder:

    drivers/sql.jdbc/tutorials/cmdline

  2. Execute make:

    >make

  3. If make works, it prints:

    The jdbc tutorials are ready to run.

    Type 'make run' to run the tutorials.

Running

Be sure the FairCom Database Engine is running.

Run the tutorials from the Linux command line. Follow these steps from the same folder you compiled from:

  1. Type the following:

    >make run

    The makefile runs each tutorial one-at-a-time.

  2. Press <Enter> when prompted after each tutorial is run.

Output

The output of the first tutorial project should appear similar to the following:

INIT
    Logon to server...
DEFINE
    Open table...
    Add fields...
    Create table...
MANAGE
    Delete records...
    Add records...
    Display records...
        1000    Bryan Williams
        1001    Michael Jordan
        1002    Joshua Brown
        1003    Keyon Dooling
DONE
    Close table...
    Logout...
Press <ENTER> key to exit . . .

Compiling and Running the Tutorials “by Hand”

To compile and run the tutorials individually, without using the batch file, execute the following from the shell (in the parent folder - <faircom>/drivers/sql.jdbc/tutorials):

javac JDBC_Tutorial1.java 
javac JDBC_Tutorial2.java 
javac JDBC_Tutorial3.java 
javac JDBC_Tutorial4.java

To execute the tutorials without using the batch file, run them as follows:

java -cp .:../ctreeJDBC.jar JDBC_Tutorial1 
java -cp .:../ctreeJDBC.jar JDBC_Tutorial2 
java -cp .:../ctreeJDBC.jar JDBC_Tutorial3 
java -cp .:../ctreeJDBC.jar JDBC_Tutorial4

Troubleshooting

This section lists some of the errors that can occur when compiling and running these tutorials.

SQL Exception: 26003 - Connection refused (Connection refused)

The most common cause of this runtime error is that the FairCom Database Engine is not running on your machine. Remember that the FairCom evaluation license times out after 3 hours, so the server might have exited and you need to start the FairCom server.

Tutorial walkthrough

This quick start has four programs to demonstrate how to create tables and indexes, insert, update, delete, and lock records, query across multiple tables, and process multiple operations as a single atomic transaction.

The source code can be found in <faircom>\drivers\sql.jdbc\tutorials\

Note

These tutorials create tables and indexes outside a database as independent data files in the <faircom>\data directory. Thus, they are invisible in SQL, the JSON DB API, and the FairCom Data Explorer application. In addition, these tutorials do not delete these files and records when they finish running. These files include custmast.dat, custmast.idx, custordr.dat, custordr.idx, invent.dat, invent.idx, itemmast.dat, itemmast.idx, ordritem.dat, and ordritem.idx.

Tutorial 1: delete and insert records into a table

<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial1.java

This tutorial shows the basic use of the FairCom JDBC Interface. It creates a single customer table and inserts records into it. It then reads, displays, and deletes the records.

It organizes the work into four functions:

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates a customer master table named custmast.

  3. Manage()

    1. Adds records.

    2. Reads and displays the records.

    3. Deletes records.

  4. Done()

    Disconnects from FairCom Database Engine.

Tutorial 2: use indexes to lookup records across tables

<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial2.java

This tutorial expands the number of tables. It defines key columns/fields and creates specific indexes for each table to form a relational model database. It creates a simple order system with customer, order, order detail, and product tables then inserts records in the tables and runs a query that lists each customer's name and the total amount of each order.

tut2.svg

It organizes the work into four functions:

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    1. Adds records to each of these tables.

    2. Lists each customer's name and the total amount of each order.

  4. Done()

    Disconnects from FairCom Database Engine.

Tutorial 3: lock and update records

<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial3.java

This tutorial shows how to lock records so that other concurrent processes cannot interfere with them. It begins by deleting preexisting records in the customer table. It then inserts new records and pauses after it places a lock on a record. You can then run another instance of this tutorial, which will automatically block while waiting for the first instance to release its lock. Press Enter in the first instance to remove the lock and allow the second instance to continue.

It organizes the work into four functions:

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates a custmast table with a primary index.

  3. Manage()

    1. Deletes preexisting records.

    2. Adds records.

    3. Reads and displays the records.

    4. Creates a session-wide write lock that locks all subsequently accessed records to prevent them from being modified.

    5. Finds a record and updates it. It automatically locks the record.

    6. Pauses until the user presses ENTER. When the user has pressed ENTER, it releases the session lock.

    7. Reads and displays the records.

  4. Done()

    Disconnects from FairCom Database Engine.

Note

When you run a second instance of this tutorial, it immediately begins deleting all preexisting records. When the delete operation encounters the record locked by the first instance, the delete operation automatically blocks and will not return until the lock is released in the first instance. Once you release the lock in the first instance by pressing ENTER, the second instance deletes the record and continues its process.

Tutorial 4: use transactions

<faircom>\drivers\sql.jdbc\tutorials\JDBC_Tutorial4.java

A transaction treats multiple operations as if they were a single atomic operation. In an atomic transaction, either all operations succeed or none succeed. You can start a transaction and execute multiple operations. You can then commit the transaction or roll it back.

This tutorial shows you how to process transactions in a simple order system with customer, order, order detail, and product tables. It shows you how to use transactions in a variety of use cases.

It organizes the work into four functions:

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    1. In a transaction, it deletes existing records and inserts new records in the custmast and itemmast tables.

    2. In a transaction, it deletes existing records in the custordr and ordritem detail tables. 

    3. In a loop that inserts multiple records, it uses transactions and transaction savepoints to commit good records and roll back bad records that it inserts in the custordr and ordritem tables. Because of bad data, only 2 of 3 orders and 3 of 6 ordered items are inserted.

    4. Reads and displays the newly inserted records in the custordr table.

    5. Reads and displays the newly inserted records in the ordritem table.

  4. Done()

    Disconnects from FairCom Database Engine.

See also