Jaybird 2.1 JDBC Driver - Firebird
Jaybird 2.1 JDBC driverJava Programmer's Manual
The contents of this Documentation are subject to the Public Documentation License Version 1.0(the “License”); you may only use this Documentation if you comply with the terms of this License.A copy of the License is available at t.html.The Original Documentation is . The Initial Writer of the OriginalDocumentation is Roman Rokytskyy, Copyright (C) 2004-2008. All Rights Reserved. (InitialWriter contact: roman@rokytskyy.de).Contributor(s): .Portions created by are Copyright (C) [Insert year(s)]. All Rights Reserved.(Contributor contact(s): [Insert hyperlink/alias]).
Table Of ContentsTable of Contents1. Introduction.5Jaybird architecture.5Supported Servers.6Supported Specifications.7Distribution package.7Quality Assurance.8Useful resources.82. Obtaining a connection.9Obtaining connection java.sql.DriverManager.9Driver types.13Connection Pooling.16The javax.sql.ConnectionPoolDataSource implementation.16Using FBConnectionPoolDataSource with JNDI.18The javax.sql.DataSource implementation.20The javax.sql.XADataSource implementation.213. Handling exceptions.23Working with exceptions.23Warnings.26java.sql.SQLException in Jaybird.26SQL states.27Useful Firebird error codes.28
4. Executing statements.33The java.sql.Statement interface.33Statement behind the scenes.37The java.sql.PreparedStatement interface.38Prepared statement pooling.42The java.sql.CallableStatement interface.44Batch Updates.50Escaped Syntax.515. Working with result sets.53ResultSet properties.53ResultSet manipulation.556. Using transactions.59JDBC transactions.59Auto-commit mode.60Read-only Transactions.62Transaction Isolation Levels.63Savepoints.64Transaction Parameter Buffer.65Table Reservation.687. Working with Services.71ServiceManager.72Backup and restore.72User management.77Database maintenance.78Database statistics.838. Working with Events.85Database events.85Posting the events.86Subscribing for events.87Appendix A. Extended connection properties.89Appendix B. Data Type Conversion Table.91Appendix C. Connection Pool Properties.93Standard JDBC Properties.93Pool Properties.94Runtime Pool Properties.94Firebird-specific Properties.94Non-standard parameters.95Appendix D. Character Encodings.97Encodings Types.97Encodings in Java.98Available Encodings.100Appendix E. Supported JDBC Scalar Functions.103Numeric Functions.104String Functions.105String Functions (continued).106Time and Date Functions.107Time and Date Functions (continued).107System Functions.108Conversion Functions.108Java Programmer's Manual4
Chapter 1.1. IntroductionJaybird is a JCA/JDBC driver suite to connect to Firebird database server. WhenBorland released an open-source version of the InterBase RDBMS, it includedsources for a type 3 JDBC driver called InterClient. However due to someinherent limitations of the InterBase (and later Firebird) client library, it wasdecided that the type 3 driver was a dead end, and the Firebird team developed apure Java implementation of the wire protocol. This implementation became thebasis for Jaybird, a pure Java driver for Firebird relational database.This driver is based on both the new JCA standard for application serverconnections to enterprise information systems and the well known JDBCstandard. The JCA standard specifies an architecture in which an applicationserver can cooperate with a driver so that the application server managestransactions, security, and resource pooling, and the driver supplies only theconnection functionality.Jaybird architectureThe Jaybird driver consists of three layers, each of which is responsible for its partof the functionality. The component diagram depicting the Jaybird internalstructure contains two additional components: “pool” and “JMX manager”. The GDS layer represents a Java translation of the Firebird API. It isrepresented by two classes from org.firebirdsql.gds package: GDS interfaceand GDSFactory. GDS factory class is responsible for instantiating animplementation of the GDS interface depending of the type of driver used.
Implementation of the GDS interface determines the type of the driver that willbe used. The JCA layer represents the heart of the driver. Here all connection andtransaction management happens. Additionally this layer adapts the GDS APIand proxies the calls to the GDS implementation.id tionFactoryJCA«delegate»Type 4GDS«delegate»JMX ManagerType 2Illustration 1.1 Jaybird internal structure. The JDBC layer is an implementation of the JDBC specification. The Pool component represents implementation ofConnectionPoolDataSource, DataSource and XADataSource interfaces fromthe JDBC 2.0 Optional Package. The pool implementation usesManagedConnectionFactory to create physical connections to the database. The Manager component represents a JMX 1.0 compatible implementation thatuses The Services API to manage the database and the server itself. Currentlyonly calls to create and drop database are available, but in the future a full rangeof services will be made public: database backup/restore, user management,statistics gathering, etc.Supported ServersJaybird 2.1 supports all current Firebird servers, however no support for theoptimized wire protocol from the Firebird 2.1 is being implemented. Jaybirdversions 2.x in current distribution won't work with InterBase and Yaffil servers,however the compatibility might be restored in the future.Chapter 1. Introduction6
Supported SpecificationsJaybird supports the following specifications:SpecificationDetailsJDBC 3.0Driver passes the complete JDBC compatibility test suite,though some features are not implemented. It is notofficially JDBC compliant, because of the high certificationcosts.JDBC 2.0 OptionalPackage(formerly StandardExtension API)Jaybird provides an implementation of following interfacesfrom javax.sql.* package:JCA 1.0 The ConnectionPoolDataSource implementationprovides connection and prepared statement pooling. The DataSource implementation provides seamlessintegration with major web and application servers. The XADataSource implementation provides meansto use driver in distributed transactions.Jaybird provides an implementation lated interfaces. CCI interfaces are not supported.JTA 1.0.1The driver provides implementation of thejavax.transaction.xa.XAResource interface via the JCAframework and a javax.sql.XADataSourceimplementation.JAAS 1.0JMX 1.2Jaybird provides an MBean that allows creating anddropping databases via JMX agent.Distribution packageJaybird driver has compile-time and run-time dependencies to JCA 1.0, JTA 1.0.1,JAAS 1.0 and JDBC 2.0 Optional Package. Additionally, if Log4J classes arefound in the class path, it is possible to enable extensive logging inside the driver.Following file groups can be found in distribution package:File nameDescriptionjaybird-2.1.6.jarAn archive containing the JDBC driver, the JCAconnection manager, the Services API and eventmanagement classes.jaybird-full-2.1.6.jarSame as above but also the connection pooling classes.jaybird21.dllPrecompiled version of the JNI library for Type 2 andEmbedded Server drivers for 32-bit Windows platform.libjaybird21.soPrecompiled version of the JNI library for Type 2 andChapter 1. Introduction7
Embedded Server drivers for 32-bit Linux platforms.Quality AssuranceThe Jaybird team uses JUnit test cases to assure the quality of the released driver.Also during development unit tests are extensively used. It is not allowed tocommit a code to the CVS until it passes all existing unit tests. Also eachreproducible bug usually gets its own test case. This guarantees that a clean checkout from the CVS can be compiled and will not contain any previously discoveredand fixed bug. Currently there are more than 450 test cases covering most of thedriver code.Additionally, before the driver is released, is is required to pass JDBCcompatibility suite (CTS), which currently contains 1216 test cases. UnfortunatelyFirebird does not support all features used by the CTS, so some test cases from theoriginal CTS suite were excluded from run.Useful resourcesJDBCFor extensive JDBC documentation, see the “Documentation” section of Sun'swebsite http://java.sun.com/products/jdbc/.FirebirdGeneral information about the Firebird database is available from the Firebirdweb site (http://www.firebirdsql.org).Information about using SQL in Firebird, see the “Language Reference” and“Developer's Guide” documents, that are available for download from the “MainDownloads” section of the IBPhoenix web site.Jaybird SupportA new resource JaybirdWiki has become available. It can be found athttp://jaybirdwiki.firebirdsql.org. This is a place where the community sharesinformation about different aspects of Jaybird usage, configuration examples fordifferent applications/servers, tips and tricks, FAQ, etc.Chapter 1. Introduction8
Chapter 2.2. Obtaining a connectionJaybird is regular JDBC driver and supports two primary ways to obtainconnection: via java.sql.DriverManager and via javax.sql.DataSourceinterface.Obtaining connection java.sql.DriverManagerhistorically was the first connection factory in Java. Itis based on the concept of the JDBC URL, a string that uniquely identifies JDBCdriver to use and the database to which user wants to connect. Additionally thereis possibility to specify additional connection parameters, like user name andpassword.java.sql.DriverManagerJDBC URL consists of three parts that are presented on Illustration 2.1.jdbc: firebirdsql: localhost/3050:c:/database/example.fdbJDBC subprotocol,identifies driver to useJDBC protocolRDBMS specific part, identifies thedatabase to which driver must connect,in our case that is host / port : path to database Illustration 2.1: Structure of the JDBC URLFirst part, "jdbc:firebirdsql:" is always fixed and specifies the so called
protocol and subprotocol for the JDBC connection. In other words, the type of theconnection that the application wants to obtain, in our case it is a connection to aFirebird database. Example of obtaining the connection is shown on Illustration2.2.package hello;import java.sql.*;public class HelloServer {public static void main(String[] args) throws Exception nnection connection lhost/3050:c:/db/employee.fdb","SYSDBA", "masterkey");// do something here}}Illustration 2.2: Simple example shows how to obtain JDBC connection.The first line of this code is important – it tells JVM to load the Jaybird 2.1 JDBCdriver. According to JDBC specification, at this point driver registers itself injava.sql.DriverManager and tells it for which protocol it is responsible for.There are two ways to register JDBC driver: Possibility 1. The application loads the driver's class. The JDBC specificationrequires that during class initialization the driver performs the jdbc.FBDriver"); Possibility 2. The JDBC driver is listed in a jdbc.drivers system property.For example in your /.hotjava/properties file you can specify followingline:jdbc.drivers ely you can specify the value of this property during JVM startup:java-Djdbc.drivers org.firebirdsql.jdbc.FBDriver-classpath any.SomeJavaExampleThe second statement of the example tells the java.sql.DriverManager to opendatabase connection to the Firebird server running on the host where Java code isexecuted, and the path to the database is c:/database/employee.fdb.Database specification consists of the name of the server where the databaseserver resides, optionally you can specify a port to which the driver will connect(by default port 3050 is used). The server name can be specified either using its IPaddress (for example 192.168.0.5) or using its DNS name (for example fbserver.mycompany.com or just fb-server).Chapter 2. Obtaining a connection10
After the server name and port, path to the database is specified. The format inwhich the path is specified depends on the platform where the Firebird serverruns. On Windows it must include the drive letter and path, for example"c:/database/employee.gdb", which points to the employee database that canbe found in a root directory of drive C:. Java allows to use either "/" or "\\" aspath separator on the Windows platform. On Unix and Linux platform, you canuse only "/" as the path separator.If you are using Firebird 1.5 or higher, you can specify a database alias instead ofthe absolute database path. For more information about using aliases see thedocumentation of the Firebird server.Specifying extended propertiesWhat if we want to specify additional connection parameters, a client encoding,for example? JDBC specification provides another method that allows to specifyadditional connection properties (Illustration 2.3).package hello;import java.sql.*;import java.util.*;public class HelloServerWithEncoding {public static void main(String[] args) throws Exception operties props new Properties();props.setProperty("user", "SYSDBA");props.setProperty("password", "masterkey");props.setProperty("encoding", "UNICODE FSS");Connection connection lhost/3050:C:/employee.gdb",props);// do something here}}Illustration 2.3: Obtaining JDBC connection with additional connection properties.Additional properties, for example SQL role for the connection can be added tothe props map. The list of all available extended properties can be found inExtended connection properties.However, not in every place you can use the above described method. Jaybirdprovides a possibility to specify extended properties in the JDBC URL.Illustration 2.4 shows the specification for specifying extended JDBC propertiesChapter 2. Obtaining a connection11
in the URL.jdbc:firebirdsql:host[/port]: path to db ? properties properties :: property [& properties ] property :: name [ value ]Illustration 2.4: Extended JDBC URL format.In this case extended properties are passed together with the URL using theHTTP-like parameter passing scheme: first comes main part of the URL, then “?”,then name-value pairs separated with “&”. Code in Illustration 2.6 is equivalent tothe previous example.import Driver");Connection connection lhost/3050:C:/employee.gdb" "?encoding UNICODE FSS","SYSDBA","masterkey");Illustration 2.5: Example of specifying extended properties in JDBC URL.Obtaining a connection via javax.sql.DataSourceJDBC 2.0 specification introduced a new mechanism to obtain databaseconnection without requiring the application to know any specifics of theunderlying JDBC driver. The application is required to know a logical name underwhich application can find an instance of the javax.sql.DataSource interfaceusing Java Naming and Directory Interface (JNDI). This is a common way toobtain connections in web and application servers.In order to obtain a connection via DataSource object, you can use code showedon Illustration 2.6. This code assumes that you have correctly configured JNDIproperties. For more information about configuring JNDI please refer to theChapter 2. Obtaining a connection12
documentation provided with your web or application server.package hello;import java.sql.*;import javax.sql.*;import javax.naming.*;public class HelloServerJNDI {public static void main(String[] args) throws Exception {InitialContext ctx new InitialContext();DataSource ds (DataSource)ctx.lookup("jdbc/SomeDB");Connection connection ds.getConnection();try {// do something here.} finally {connection.close();}}}Illustration 2.6: Typical way to obtain JDBC connection via JNDI.Usually binding between the DataSource object and its JNDI name happens inthe configuration of your web or application server. However under somecircumstances (e.g. you are developing your own JNDI-enabled applicationserver/framework) you have to do this yourself. You can use the code snippet forthis purpose showed on Illustration 2.7.import javax.naming.*;import org.firebirdsql.pool.*;.FBWrappingDataSource ds new );ds.setPassword("masterkey");InitialContext ctx new InitialContext();ctx.bind("jdbc/SomeDB", ds);Illustration 2.7: Programmatic way to instantiate javax.sql.DataSource implementation.DataSource implementation supports all connection properties available to theDriverManager interface, but also it supports additional properties that controlconnection pooling. For more information on this topic please read the“Connection Pooling” chapter.Driver typesAs it was mentioned in the Chapter 3. Jaybird Architecture, Jaybird supportsmultiple implementations of the GDS interface. The original Jaybird distributioncontains two main categories of the GDS implementation: pure JavaChapter 2. Obtaining a connection13
implementation of the Firebird wire protocol and a JNI proxy that can use adynamically linked library with a compatible API.Below you find the list of existing types and their short configuration descriptionwith the corresponding JDBC URLs that should be used to obtain the connectionof desired type. The type of the JDBC driver for the javax.sql.DataSource isconfigured via corresponding property.PURE JAVA typeThe PURE JAVA driver type uses pure Java implementation of the Firebird wireprotocol. This type is recommended for connecting to a remote database serverusing TCP/IP sockets. No installation is required except adding the JDBC driverto the class path. This type of driver provides best performance when connectingto the remote server.In order to obtain connection using the PURE JAVA driver type you have to useJDBC URL that was shown on Illustration 2.2:jdbc:firebirdsql:host[/port]: path to database When using javax.sql.DataSource implementation, you can specify either"PURE JAVA" or "TYPE4" driver type, however this type is used by default.NATIVE and LOCAL typesThe NATIVE and LOCAL driver types use a JNI proxy to access the Firebirdclient library and requires installation of the Firebird client. The NATIVE drivertype is used to access the remote database server, the LOCAL type accesses thedatabase server running on the same host by means of IPC. Performance ofNATIVE driver is approximately 10% lower compared to the PURE JAVAdriver, but LOCAL type has up to 30% higher performance compared to thePURE JAVA driver when connecting the server on the same host. This is mostlydue to the fact that TCP/IP stack is not involved in this mode.In order to instantiate a connection using the NATIVE JDBC driver to connect toa remote server you have to use the following JDBC URL with new subprotocol:jdbc:firebirdsql:native:host[/port]: path to database When connecting to a local database server using the LOCAL driver, you shoulduse following:jdbc:firebirdsql:local: absolute path to database Additionally to the Firebird client library installation, driver requires a JNI proxyto be available to the JVM. The JNI proxy is a platform-dependent dynamicallylinked library that translates GDS calls into Firebird API calls.WindowsOn Windows, the JNI proxy is represented by a dynamically linked library (DLL)jaybird21.dll. You have to make this library available through PATHenvironment variable. Alternatively you can specify the directory containing thisDLL in java.library.path system property.For example, if you put library in the current directory you have to use thefollowing command to start Java:Chapter 2. Obtaining a connection14
java -Djava.library.path . com.mycompany.MyClassLinuxOn Linux JNI proxy is represented by a shared library libjaybird21.so. It mustbe available through the LD PATH environment variable. Usually shared librariesare stored in the /usr/lib/ directory; however you will need root permissions tocopy libjaybird21.so there. Alternatively you can specify directory containingthe proxy in java.library.path Java system property. See Windows exampleabove for more details.LimitationsFirebird client library is not thread-safe when connecting to a local databaseserver using IPC. Jaybird provides the necessary synchronization in Java codeusing a static object instance. However, this static object instance is local to theclassloader that has loaded the Jaybird classes.In order to guarantee correct synchronization , the Jaybird driver must be loadedby the top-most classloader. For example, when using the Type 2 JDBC driverwith a web or application server, you have to add the Jaybird classes to the mainclasspath (for example, to the lib/ directory of your web or application server),but not to the web or J2EE application, e.g. the WEB-INF/lib directory.EMBEDDED typeThe Embedded server JDBC driver is the Type 2 JDBC driver that rather thanusing the Firebird client library, loads Firebird embedded server library instead.This is the highest performance type of JDBC driver for accessing local databases,as the Java code accesses the database file directly.In order to obtain a connection via DriverManager you have to use followingURL:jdbc:firebirdsql:embedded: path to database jdbc:firebirdsql:embedded:host[/port]: path to database When host and, optionally, port is specified, embedded server acts as client library(i.e. you get the same Type 2 behavior as you would get with using “native”).Installation of the JNI proxy is same as described in the “NATIVE and LOCALtypes” chapter.LimitationsThe Firebird embedded server for Linux is not thread safe. Jaybird provides theneeded synchronization in Java code, similar to the one described for the Type 2JDBC driver. This implies the same restrictions on the classloader that will loadthe Jaybird classes.The Firebird embedded server for Windows opens databases in exclusive mode.This means that this particular database is accessible only to one Java virtualmachine. There is no exclusive mode on the POSIX platform. When the samedatabase file is accessed by multiple JVM instances, database will be corrupted!Chapter 2. Obtaining a connection15
Connection PoolingEach time a connection is opened via DriverManager, a new physical connectionto server is opened. It is closed when the connection is closed. In order to avoidthe overhead of creating connections, the DataSource implementation canmaintain a cache of open physical connections that can be reused between usersessions.The javax.sql.ConnectionPoolDataSource implementationFBConnectionPoolDataSource is an implementation of thejavax.sql.ConnectionPoolDataSource interface, which is used by anapplication to obtain PooledConnection objects. A PooledConnection instancerepresents a physical connection to a database and is a source of logicalconnection. Closing a logical connection returns the physical connection back intothe pool. Additionally, the logical connection provides caching of preparedstatements, which improves application performance even more than connectionpooling.Usually the connection pool is specified in web or application serverconfiguration. However, you can instantiate it also inside the application.Chapter 2. Obtaining a connection16
package hello;import java.sql.*;import javax.sql.*;import org.firebirdsql.pool.*;public class HelloConnectionPool {public static void main(String[] args) throws Exception {1org.firebirdsql.pool.FBConnectionPoolDataSource pool new l.setMaxStatements(10);pool.setMaxIdleTime(30 * 60 * masterkey");4// obtain a physical connection to the databasePooledConnection pooledCon pool.getPooledConnection();5// obtain a wrapped connectionConnection connection pooledCon.getConnection();try {// do something here.} finally {// release the connection back to poolconnection.close();6}}}Illustration 2.8.: Example of instantiating and using the e code on Illustration 2.8 we perform the following steps:1. Cr
Jaybird is a JCA/JDBC driver suite to connect to Firebird database server. When Borland released an open-source version of the InterBase RDBMS, it included sources for a type 3 JDBC driver called InterClient. However due to some inherent limitations o
Although JDBC was designed specifically to provide a Java interface to relational databases, you may find that you need to write Java code to access non-relational databases as well. JDBC Architecture Java application calls the JDBC library. JDBC loads a driver which talks to the database In general, to process any SQL statement with JDBC, you .
Changes in This Release for Oracle Database JDBC Developer's Guide Changes in Oracle Database 12c Release 2 (12.2) xxx Part I Overview 1 Introducing JDBC 1.1 Overview of Oracle JDBC Drivers 1-1 1.2 Choosing the Appropriate Driver 1-3 1.3 Feature Differences Between JDBC OCI and Thin Drivers 1-4 1.4 Environments and Support 1-4 1.4.1 Supported JDK and JDBC Versions 1-5 1.4.2 JNI and Java .
Note: InfoSphere CDC for Netezza databases only supports 64 bit operating systems. Supported Netezza JDBC drivers Netezza JDBC driver Netezza JDBC driver, version 6.0.3 or above. Supported databases Database Netezza database version 6.0.2 The JDBC driver must be compatible with all databases you want to replicate data to or from.
The JDBC API provides a programming interface for data access of relational databases from the Java programming language. WebSphere Application Server V7 supports the following JDBC APIs: JDBC 4.0 (New in V7) JDBC 3.0 JDBC 2.1 and Optional Package API (2.0) In the following sec
Introduction to J2EE APIs (Servlet, JSP, EJB, JMS, JavaMail, JSF, JNDI) Introduction to Containers Tomcat as a Web Container Introduction of JDBC JDBC Architecture Data types in JDBC Processing Queries Database Exception Handling Discuss types of drivers JDBC Introduction and Need for JDBC
JDBC / ODBC Driver The CDAP JDBC and ODBC drivers enable users to . (BI) applications with JDBC or ODBC support. The driver achieves this integration by translating Open Database Connectivity (JDBC/ODBC) calls from the application into SQL and passing the SQL queries to . Sensu, Cacti and Sp
JDBC on IBM i IBM offers three JDBC drivers for the access to IBM i: „IBM Toolbox for Java / JTOpen“, „IBM Native JDBC Driver“, „IBM JCC“ Third party distributors offer additional drivers, for example „HiT JDBC/400“ Toolbox and Native Driver are built and maintained by IBM Rochest
14 Mercedes Benz A250 2014 Wheel alignment Wheel alignment specifications. Contacted VACC, no information available, checked online for OEM websites, blocked from USA and Europe. 15 Suzuki Jimny 2008 Collision repair Body repair quarter panel replacement procedures. Contacted VACC, no information available, checked online for OEM websites, blocked from USA and Europe. 16 LDV G10 2016 Service .
