Skip to content

Instantly share code, notes, and snippets.

@mariuz
Created June 23, 2011 19:54
Show Gist options
  • Save mariuz/1043473 to your computer and use it in GitHub Desktop.
Save mariuz/1043473 to your computer and use it in GitHub Desktop.
Firebird driver example using jaybird-full jar
// Original version of this file was part of InterClient 2.01 examples
//
// Copyright InterBase Software Corporation, 1998.
// Written by com.inprise.interbase.interclient.r&d.PaulOstler :-)
//
// Code was modified by Roman Rokytskyy to show that Firebird JCA-JDBC driver
// does not introduce additional complexity in normal driver usage scenario.
//
// A small application to demonstrate basic, but not necessarily simple, JDBC features.
//
// Note: you will need to hardwire the path to your copy of employee.gdb
// as well as supply a user/password in the code below at the
// beginning of method main().
public class DriverExample
{
// Make a connection to an employee.gdb on your local machine,
// and demonstrate basic JDBC features.
// Notice that main() uses its own local variables rather than
// static class variables, so it need not be synchronized.
public static void main (String args[]) throws Exception
{
// Modify the following hardwired settings for your environment.
// Note: localhost is a TCP/IP keyword which resolves to your local machine's IP address.
// If localhost is not recognized, try using your local machine's name or
// the loopback IP address 127.0.0.1 in place of localhost.
// String databaseURL = "jdbc:firebirdsql:localhost/3050:c:/database/employee.gdb";
//String databaseURL = "jdbc:firebirdsql:native:localhost/3050:c:/database/employee.gdb";
//String databaseURL = "jdbc:firebirdsql:local:c:/database/employee.gdb";
//String databaseURL = "jdbc:firebirdsql:embedded:c:/database/employee.fdb?lc_ctype=WIN1251";
String databaseURL = "jdbc:firebirdsql:localhost/3050:/var/lib/firebird/2.5/data/employee.fdb?sql_dialect=3";
String user = "sysdba";
String password = "masterkey";
String driverName = "org.firebirdsql.jdbc.FBDriver";
// As an exercise to the reader, add some code which extracts databaseURL,
// user, and password from the program args[] to main().
// As a further exercise, allow the driver name to be passed as well,
// and modify the code below to use driverName rather than the hardwired
// string "org.firebirdsql.jdbc.FBDriver" so that this code becomes
// driver independent. However, the code will still rely on the
// predefined table structure of employee.gdb.
// Here are the JDBC objects we're going to work with.
// We're defining them outside the scope of the try block because
// they need to be visible in a finally clause which will be used
// to close everything when we are done.
// The finally clause will be executed even if an exception occurs.
java.sql.Driver d = null;
java.sql.Connection c = null;
java.sql.Statement s = null;
java.sql.ResultSet rs = null;
// Any return from this try block will first execute the finally clause
// towards the bottom of this file.
try {
// Let's try to register the Firebird JCA-JDBC driver with the driver manager
// using one of various registration alternatives...
int registrationAlternative = 1;
switch (registrationAlternative) {
case 1:
// This is the standard alternative and simply loads the driver class.
// Class.forName() instructs the java class loader to load
// and initialize a class. As part of the class initialization
// any static clauses associated with the class are executed.
// Every driver class is required by the jdbc specification to automatically
// create an instance of itself and register that instance with the driver
// manager when the driver class is loaded by the java class loader
// (this is done via a static clause associated with the driver class).
//
// Notice that the driver name could have been supplied dynamically,
// so that an application is not hardwired to any particular driver
// as would be the case if a driver constructor were used, eg.
// new org.firebirdsql.jdbc.FBDriver().
try {
Class.forName ("org.firebirdsql.jdbc.FBDriver");
}
catch (java.lang.ClassNotFoundException e) {
// A call to Class.forName() forces us to consider this exception :-)...
System.out.println ("Firebird JCA-JDBC driver not found in class path");
System.out.println (e.getMessage ());
return;
}
break;
case 2:
// There is a bug in some JDK 1.1 implementations, eg. with Microsoft
// Internet Explorer, such that the implicit driver instance created during
// class initialization does not get registered when the driver is loaded
// with Class.forName().
// See the FAQ at http://java.sun.com/jdbc for more info on this problem.
// Notice that in the following workaround for this bug, that if the bug
// is not present, then two instances of the driver will be registered
// with the driver manager, the implicit instance created by the driver
// class's static clause and the one created explicitly with newInstance().
// This alternative should not be used except to workaround a JDK 1.1
// implementation bug.
try {
java.sql.DriverManager.registerDriver (
(java.sql.Driver) Class.forName ("org.firebirdsql.jdbc.FBDriver").newInstance ()
);
}
catch (java.lang.ClassNotFoundException e) {
// A call to Class.forName() forces us to consider this exception :-)...
System.out.println ("Driver not found in class path");
System.out.println (e.getMessage ());
return;
}
catch (java.lang.IllegalAccessException e) {
// A call to newInstance() forces us to consider this exception :-)...
System.out.println ("Unable to access driver constructor, this shouldn't happen!");
System.out.println (e.getMessage ());
return;
}
catch (java.lang.InstantiationException e) {
// A call to newInstance() forces us to consider this exception :-)...
// Attempt to instantiate an interface or abstract class.
System.out.println ("Unable to create an instance of driver class, this shouldn't happen!");
System.out.println (e.getMessage ());
return;
}
catch (java.sql.SQLException e) {
// A call to registerDriver() forces us to consider this exception :-)...
System.out.println ("Driver manager failed to register driver");
showSQLException (e);
return;
}
break;
case 3:
// Add the Firebird JCA-JDBC driver name to your system's jdbc.drivers property list.
// The driver manager will load drivers from this system property list.
// System.getProperties() may not be allowed for applets in some browsers.
// For applets, use one of the Class.forName() alternatives above.
java.util.Properties sysProps = System.getProperties ();
StringBuffer drivers = new StringBuffer ("org.firebirdsql.jdbc.FBDriver");
String oldDrivers = sysProps.getProperty ("jdbc.drivers");
if (oldDrivers != null)
drivers.append (":" + oldDrivers);
sysProps.put ("jdbc.drivers", drivers.toString ());
System.setProperties (sysProps);
break;
case 4:
// Advanced: This is a non-standard alternative, and is tied to
// a particular driver implementation, but is very flexible.
//
// It may be possible to configure a driver explicitly, either thru
// the use of non-standard driver constructors, or non-standard
// driver "set" methods which somehow tailor the driver to behave
// differently from the default driver instance.
// Under this alternative, a driver instance is created explicitly
// using a driver specific constructor. The driver may then be
// tailored differently from the default driver instance which is
// created automatically when the driver class is loaded by the java class loader.
// For example, perhaps a driver instance could be created which
// is to behave like some older version of the driver.
//
// d = new org.firebirdsql.jdbc.FBDriver ();
// DriverManager.registerDriver (d);
// c = DriverManager.getConnection (...);
//
// Since two drivers, with differing behavior, are now registered with
// the driver manager, they presumably must recognize different jdbc
// subprotocols. For example, the tailored driver may only recognize
// "jdbc:interbase:old_version://...", whereas the default driver instance
// would recognize the standard "jdbc:interbase://...".
// There are currently no methods, such as the hypothetical setVersion(),
// for tailoring an Firebird JCA-JDBC driver so this 4th alternative is academic
// and not necessary for Firebird JCA-JDBC driver.
//
// It is also possible to create a tailored driver instance which
// is *not* registered with the driver manager as follows
//
// d = new org.firebirdsql.jdbc.FBDriver ();
// c = d.connect (...);
//
// this is the most usual case as this does not require differing
// jdbc subprotocols since the connection is obtained thru the driver
// directly rather than thru the driver manager.
d = new org.firebirdsql.jdbc.FBDriver ();
}
// At this point the driver should be registered with the driver manager.
// Try to find the registered driver that recognizes interbase URLs...
try {
// We pass the entire database URL, but we could just pass "jdbc:interbase:"
d = java.sql.DriverManager.getDriver (databaseURL);
System.out.println ("Firebird JCA-JDBC driver version " +
d.getMajorVersion () +
"." +
d.getMinorVersion () +
" registered with driver manager.");
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to find Firebird JCA-JDBC driver among the registered drivers.");
showSQLException (e);
return;
}
// Advanced info: Class.forName() loads the java class for the driver.
// All JDBC drivers are required to have a static clause that automatically
// creates an instance of themselves and registers that instance
// with the driver manager. So there is no need to call
// DriverManager.registerDriver() explicitly unless the driver allows
// for tailored driver instances to be created (each instance recognizing
// a different jdbc sub-protocol).
// Now that Firebird JCA-JDBC driver is registered with the driver manager,
// try to get a connection to an employee.gdb database on this local machine
// using one of two alternatives for obtaining connections...
int connectionAlternative = 1;
switch (connectionAlternative) {
case 1:
// This alternative is driver independent;
// the driver manager will find the right driver for you based on the jdbc subprotocol.
// In the past, this alternative did not work with applets in some browsers because of a
// bug in the driver manager. I believe this has been fixed in the jdk 1.1 implementations.
try {
c = java.sql.DriverManager.getConnection (databaseURL, user, password);
System.out.println ("Connection established.");
}
catch (java.sql.SQLException e) {
e.printStackTrace();
System.out.println ("Unable to establish a connection through the driver manager.");
showSQLException (e);
return;
}
break;
case 2:
// If you're working with a particular driver d, which may or may not be registered,
// you can get a connection directly from it, bypassing the driver manager...
try {
java.util.Properties connectionProperties = new java.util.Properties ();
connectionProperties.put ("user", user);
connectionProperties.put ("password", password);
connectionProperties.put ("lc_ctype", "WIN1251");
c = d.connect (databaseURL, connectionProperties);
System.out.println ("Connection established.");
}
catch (java.sql.SQLException e) {
e.printStackTrace();
System.out.println ("Unable to establish a connection through the driver.");
showSQLException (e);
return;
}
break;
}
// Let's disable the default autocommit so we can undo our changes later...
try {
c.setAutoCommit (false);
System.out.println ("Auto-commit is disabled.");
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to disable autocommit.");
showSQLException (e);
return;
}
// Now that we have a connection, let's try to get some meta data...
try {
java.sql.DatabaseMetaData dbMetaData = c.getMetaData ();
// Ok, let's query a driver/database capability
if (dbMetaData.supportsTransactions ())
System.out.println ("Transactions are supported.");
else
System.out.println ("Transactions are not supported.");
// What are the views defined on this database?
java.sql.ResultSet tables = dbMetaData.getTables (null, null, "%", new String[] {"VIEW"});
while (tables.next ()) {
System.out.println (tables.getString ("TABLE_NAME") + " is a view.");
}
tables.close ();
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to extract database meta data.");
showSQLException (e);
// What the heck, who needs meta data anyway ;-(, let's continue on...
}
// Let's try to submit some static SQL on the connection.
// Note: This SQL should throw an exception on employee.gdb because
// of an integrity constraint violation.
try {
s = c.createStatement ();
s.executeQuery("select cast('????' as varchar(30) character set win1251) from rdb$database order by 1 collate pxw_cyrl");
s.executeUpdate ("update employee set salary = salary + 10000");
}
catch (java.sql.SQLException e) {
e.printStackTrace();
System.out.println ("Unable to increase everyone's salary.");
showSQLException (e);
// We expected this to fail, so don't return, let's keep going...
}
// Let's submit some static SQL which produces a result set.
// Notice that the statement s is reused with a new SQL string.
try {
rs = s.executeQuery ("select full_name from employee where salary < 50000");
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to submit a static SQL query.");
showSQLException (e);
// We can't go much further without a result set, return...
return;
}
// The query above could just as easily have been dynamic SQL,
// eg. if the SQL had been entered as user input.
// As a dynamic query, we'd need to query the result set meta data
// for information about the result set's columns.
try {
java.sql.ResultSetMetaData rsMetaData = rs.getMetaData ();
System.out.println ("The query executed has " +
rsMetaData.getColumnCount () +
" result columns.");
System.out.println ("Here are the columns: ");
for (int i = 1; i <= rsMetaData.getColumnCount (); i++) {
System.out.println (rsMetaData.getColumnName (i) +
" of type " +
rsMetaData.getColumnTypeName (i));
}
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to extract result set meta data.");
showSQLException (e);
// What the heck, who needs meta data anyway ;-(, let's continue on...
}
// Ok, lets step thru the results of the query...
try {
System.out.println ("Here are the employee's whose salary < $50,000");
while (rs.next ()) {
System.out.println (rs.getString ("full_name"));
}
}
catch (java.sql.SQLException e) {
System.out.println ("Unable to step thru results of query");
showSQLException (e);
return;
}
// As an exercise to the reader, rewrite this code so that required
// table structures are created dynamically using executeUpdate() on DDL.
// In this way the code will be able to run against any database file rather
// than just a previously setup employee.gdb.
// Just to get you started, you'll want to define a method something like
// the following...
//
// private static void createTableStructures (java.sql.Connection c) throws java.sql.SQLException
// {
// // Some drivers don't force commit on DDL, Firebird JCA-JDBC driver does,
// // see DatabaseMetaData.dataDefinitionCausesTransactionCommit().
// // This is not necessary for Firebird JCA-JDBC driver, but may be for other drivers...
// c.setAutoCommit (true);
//
// java.sql.Statement s = c.createStatement();
//
// // Drop table EMPLOYEE if it already exists, if not that's ok too.
// try { s.executeUpdate ("drop table EMPLOYEE"); } catch (java.sql.SQLException e) {}
//
// // Ok, now that we're sure the table isn't already there, create it...
// s.executeUpdate ("create table EMPLOYEE (...)");
//
// // Ok, now populate the EMPLOYEE table...
// s.executeUpdate ("insert into EMPLOYEE values (...)");
//
// s.close();
// c.setAutoCommit (false);
// }
//
}
// This finally clause will be executed even if "return" was called in case of any exceptions above.
finally {
System.out.println ("Closing database resources and rolling back any changes we made to the database.");
// Now that we're all finished, let's release database resources.
try { if (rs!=null) rs.close (); } catch (java.sql.SQLException e) { showSQLException (e); }
try { if (s!=null) s.close (); } catch (java.sql.SQLException e) { showSQLException (e); }
// Before we close the connection, let's rollback any changes we may have made.
try { if (c!=null) c.rollback (); } catch (java.sql.SQLException e) { showSQLException (e); }
try { if (c!=null) c.close (); } catch (java.sql.SQLException e) { showSQLException (e); }
}
}
// Display an SQLException which has occured in this application.
private static void showSQLException (java.sql.SQLException e)
{
// Notice that a SQLException is actually a chain of SQLExceptions,
// let's not forget to print all of them...
java.sql.SQLException next = e;
while (next != null) {
System.out.println (next.getMessage ());
System.out.println ("Error Code: " + next.getErrorCode ());
System.out.println ("SQL State: " + next.getSQLState ());
next = next.getNextException ();
}
}
}
@lazarch
Copy link

lazarch commented Feb 15, 2019

Excellent code. Thank you mariuz. I search answer to my problem with firebird 2 day, your example gives me it at minute.

@bitsnaps
Copy link

bitsnaps commented Feb 11, 2021

Doesn't work for me on Mac (only embedded tried).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment