SOLID JDBC Driver Programmer's Guide

SOLID JDBC Driver
Programmer's Guide

This document gives a brief overview on writing applications on SOLID JDBC Driver. The document's goal is to provide reader sufficient knowledge for connecting SOLID Server through JDBC and completing the basic database operations. The reader is expected to have the basic knowledge of following subjects

JAVA programming language
JAVA development environments
SQL database language.
relational database technology, SOLID Server in particular
JDBC in general

This document contains a general description of the services provided by JDBC and some code examples using JDBC explained.

Contents

Background
Using SOLID JDBC Driver
Running SQL Statements on JDBC
Special notes about SOLID Server and JDBC

BACKGROUND

The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java.

JDBC drivers can either be entirely written in Java so that they can be downloaded as part of an applet, or they can be implemented using native methods to bridge to existing database access libraries. SOLID JDBC Driver is written entirely in Java and speaks directly to SOLID Server using SOLID Server's native network protocol.

JavaSoft has made JDBC part of the core API for JDK 1.1.

Other JDBC related documentation by Solid:

Other SOLID Server documentation:

Other Java and JDBC related documentation provided by other parties:

USING SOLID JDBC DRIVER

Getting started

Getting started with SOLID JDBC Driver consists of following steps, each of which is briefly described below.

Installing the driver
Setting the development environment up
Registering the driver
Establishing a connection

Installing JDBC Driver

Installing JDBC Driver and verifying the installation is a relatively simple task. Before starting programming verify the installation by running and compiling the sample application. See the Readme document for more details.

Setting the development environment up

Make sure that your Java development environment supports JDBC properly. SOLID JDBC Driver expects support for JDBC version 1.20. JDBC interface is included in the java.sql package. To import this package a following line is needed in the application program.

import java.sql.*;

Registering JDBC Driver

The JDBC driver manager, which is written entirely in Java, handles loading and unloading drivers and interfacing connection requests with the appropriate driver. It was JavaSoft's intention to make the use of a specific JDBC driver as transparent as possible to the programmer and user.

The driver can be registered with the three alternative ways, which are shown below. The parameter required by Class.forName and Properties.put functions is the name of the driver, which is solid.jdbc.SolidDriver.

// registration using Class.forName service
Driver)Class.forName("solid.jdbc.SolidDriver")

// a workaround to a bug in some JDK1.1 implementations
Driver d = (Driver)Class.forName("solid.jdbc.SolidDriver").newInstance();

// Registration using system properties variable also
Properties p = System.getProperties();
p.put("jdbc.drivers", "solid.jdbc.SolidDriver");
System.setProperties(p);

See source code for example application sample1.

Connecting to the database

Once the driver is succesfully registered with the driver manager a connection is established by creating a Java Connection object with the following code. The parameter required by the DriverManager.getConnection functionis the JDBC connection string.

Connection conn = null;
try {
    conn = DriverManager.getConnection(sCon);
}
catch (Exception e) {
    System.out.println("Connect failed : " + e.getMessage());
    throw new Exception("Halted.");
}

The connect string structure is jdbc:solid://<machine name>:<port>/<user name>/<password>. The string "jdbc:solid://fb9:1314/dba/dba" attempts to connect a SOLID Server in machine fb9 listening tcp/ip protocol at port 1314.

The application can establish several Connection objects to database. Connections can be closed be the following code.

conn.close();

See source code for example application sample1.

RUNNING SQL STATEMENTS WITH JDBC

This chapter describes briefly how to do basic database operations with the SQL. The following operations are presented here

Executing statements through JDBC
- Simple statement
- Statement with parameters
Reading result sets
Transactions and autocommit mode
Handling database errors
Using DatabaseMetadata

For more detailed description on these subjects see JDBC and SOLID Server documentation.

Executing a simple statement

Following code expects that a Connection object conn is established before calling the code.

stmt= conn.createStatement();
stmt.execute("INSERT INTO JDB_TEST (I1,I2) VALUES (2,3)");

Note that the insert is not committed by the code unless the database is in autocommit mode.

See source code for example application sample1.

Statement with parameters

The code below creates a PreparedStatement object for a query, assigns values for its parameters and executes the query. Check the the available methods for setting values to different column types from JDBC Type Conversion Matrix . The code expects a Connection object conn to be established.

PreparedStatement pstmt;
int count, cnt;
int i;

sQuery = "INSERT INTO ALLTYPES (TI,SI,II,RR,FF,DP,DE,NU,CH,VC,DT,TM,TS) VALUES ";
sQuery = sQuery + "(?,?,?,?,?,?,?,?,?,?,?,?,?)";

pstmt= conn.prepareStatement(sQuery);

pstmt.setInt(1,101);
pstmt.setInt(2,102);
pstmt.setInt(3,103);
pstmt.setDouble(4,2104.56);
pstmt.setDouble(5,104.56);
pstmt.setDouble(6,3104.56);
pstmt.setDouble(7,204.56);
pstmt.setDouble(8,304.56);
pstmt.setString(9,"cccc");
pstmt.setString(10,"longer string");

java.sql.Time pTime = new java.sql.Time(11,11,11);
java.sql.Date pDate = new java.sql.Date(96,1,2);
java.sql.Timestamp pTimestamp = new java.sql.Timestamp(96,1,2,11,11,11,0);
pstmt.setDate(11,pDate);
pstmt.setTime(12,pTime);
pstmt.setTimestamp(13,pTimestamp);

pstmt.executeUpdate();

See source code for example application sample3.

Note that the insert is not committed by the code unless the database is in autocommit mode.

Reading result sets

The code below obtains a result set for the SQL

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM SYS_TABLES WHERE ID < 10000

and prints out column name and type information for each column in the result set using the ResultSetMetaData object. Then the code loops through the result set and prints the data in each column in each row by using getString method. Check the available methods for accessing data of different column types from JDBC Type Conversion Matrix. The code expects a Connectionobject conn to be established.

String sQuery;

ResultSetMetaData meta;
Statement stmt;
ResultSet result;
int count, cnt;
int i;

// the query to be executed
sQuery = "SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME," ;
sQuery = sQuery + "TABLE_TYPE FROM SYS_TABLES WHERE ID < 10000";

// we create statement for the query
stmt= conn.createStatement();
// execute it and obtain a result set
result = stmt.executeQuery(sQuery);

// to see what we got we obtain a ResultSetMetaData object
meta = result.getMetaData();
// check the number of columns
count = meta.getColumnCount();

// print some information about the columns
for (i=1; i <= count; i++)
{
   String sName = meta.getColumnName(i);
   int iType = meta.getColumnType(i);
   String sTypeName = meta.getColumnTypeName(i);

   System.out.println("Col:"+i+" "+sName+ "," + iType + "," + sTypeName);
}

// and finally, loop through the ResultSet and print the data out
int cnt = 1;
while(result.next())
{
   for (i=1; i <= count; i++)
   {
     System.out.println("Row:"+cnt+ " column:" +i+" : "+result.getString(i));
   }
   cnt++;
}

Note1: There is no JDBC method to step backwards in ResultSets. There a is next() but no prev(). The primary reason is because many databases do not support bi-directional cursors. Many database servers only support stepping through the result set in a single direction and therefore prev() has been left out of the standard. Many have argued, though, that a well-written object oriented program should not need to step backwards through a ResultSet, and that doing so may be either inefficient or result in unreliable data results.

Note2: It is possible to improve the performance of reading large result sets by instructiong SOLID Server to return several rows of the result set in one network message. This functionality is activated by editing configuration parameter RowsPerMessage in section [Srv]in SOLID Server configuration file solid.ini. The default value is 10. This is new functionality in JDBC Driver 2.3. In prior versions, the rows of the result set were always returned one by one.

See source code for example application sample1.

Transactions and autocommit mode

A SOLID Server database can be in either autocommit or non-autocommit mode. When not in autocommit mode each transaction needs to be explicitly committed before the modifications it made can be seen to other database connections. The autocommit state can be monitored by Connection.getAutoCommit() function. The state can be set by Connection.setAutoCommit(). If autocommit mode is off the transactions can be committed by two ways. SOLID Server's default setting for autocommit state is true.

using Connection.commit() function or
executing a statement for SQL 'COMMIT WORK'

Handling database errors

In some cases it is necessary for the application to recover from a database error. For example, a unique key constraint violation can be recovered by assigning the row a different key. The code below expects a Statementobject stmt to exist and String sQuery to contain SQL that may cause an error. A database native error code will be assigned to variable ec. See SOLID Server documentation for SOLID Server native error codes.

try {
   result = stmt.executeQuery(sQuery);
}
catch (SQLException e) {
   int ec = e.getErrorCode();
   String ss = e.getSQLState();
   String s2 = e.toString();
   System.out.println("Native error code:" + ec);
}

Using DatabaseMetadata

Class DatabaseMetadData contains information about the database behind the connection. Usually this information is necessary for application development tools not actual applications. If you are developing an application on JDBC interface for one kind of database engine this is seldom if ever necessary. If you are developing an application to run on several database engines the application can obtain necessary information about the database through DatabaseMetadData.

A DatabaseMetaData object can be obtained from the Connection object by the code below. The code also extracts database product name to string sNameand all the views in the database to ResultSet rTables. For more detailed information about the services provided by DatabaseMetaData see JDBC and SOLID Server documentation. As usually, the code expects that a Connection object conn is established before calling it.

DatabaseMetaData meta;

String sName;
ResultSet rTables;

String types[] = new String[1];
types[0] = "VIEW";

meta = conn.getMetaData();
sName = meta.getDatabaseProductName();
rTables = meta.getTables(null,"","",types);

SPECIAL NOTES ABOUT SOLID Server AND JDBC

JDBC does not really specify what SQL you can use, it simply passes the SQL on to the driver and lets the driver either pass it on directly to the database, or parse the SQL itself. Because of this of the SOLID JDBC Driver behavior is particular to SOLID Server database. In some functions the JDBC specifiaction leaves some details open. Check SOLID JDBC Driver Classes and Methods for the details particular to SOLID implementation of the methods.

Executing stored procedures

In SOLID Server database stored procedures can be called by executing statements 'CALL proc_name [parameter ...] ' just like any other SQL. Procedures are thus used in JDBC in the same way as any statement. Note, that SOLID stored procedures can return result sets. Calling procedures through JDBC CallableStatement class is not necessary. See source code for example application sample3 on calling SOLID Server procedures on JDBC.

Class CallableStatement

A JDBC CallableStatement class is intended to support calling database stored procedures. The class is not necessary when writing applications on SOLID Server only. Portability reasons, for instance, can make using CallableStatementa good decision. The example below illustrates running a simple SQL using this class.

CallableStatement csta;
int i1,i2;
String s1;
ResultSet res;

// creating a CallableStatement object
csta = conn.prepareCall("select * from keytest where i1 = ?");

// assigning a value for parameter
csta.setInt(1,1);

// obtaining a result set
res = csta.executeQuery();

while (res.next())
{
   i1 = csta.getInt(1);
   i2 = csta.getInt(2);
   s1 = csta.getString(3);
   System.out.println("Row contains " + i1 + "," + i2 + "," + s1);
}

Optimizing the Query Performance

!!! tekstiä Askolta!!!.