Introducing the PSQL JDBC Driver
 
Introducing the PSQL JDBC Driver
An Overview of PSQL support for JDBC Development
The following topics introduce you to the PSQL JDBC interface:
PSQL JDBC Support
JDBC Requirements
JDBC Features
PSQL JDBC Data Types
PSQL JDBC Driver Limitations
Unsupported APIs
Driver Limitations
For instructions and details on this PSQL feature, see Programming with the PSQL JDBC 2 Driver and JDBC API Reference.
PSQL JDBC Support
JDBC is a standard API that Java programmers can use to develop database and Internet applications using Java. It consists of interfaces to develop SQL based database applications in the Java programming language. The JDBC interfaces are included as part of the Java Developer Kit.
JDBC is the counterpart of ODBC in Java and is heavily influenced by ODBC and relational databases.
Detailed information on the JDBC API is available at Oracle’s website.
JDBC Requirements
The PSQL JDBC driver works in conjunction with PSQL. You can use the Server, Vx Server, or Workgroup engines.
JDBC Features
The following is a summary of features of the PSQL JDBC driver:
100% Java certified
JDBC 4 compliant, type 4 driver that is also compatible with applications that use JDBC 2 drivers
Supports thread safe operation
Supports transactions isolation levels supported by the PSQL engine, for example READ_COMMITTED, serializable
Performs result set caching to reduce network access
Supports binary data through the longvarbinary data type (2 GB limit)
Supports long char data through the longvarchar and nlongvarchar data types (2 GB limit)
Supports stored procedures with parameters
Encrypts connection strings to provide security
Support for code page filtering when reading from the database by specified the code page using a connection string parameter
Support for result set cursors CONCUR_UPDATABLE, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE
Supports the DataSource interface to register PSQL databases in JNDI, shielding your applications from specific driver features for PSQL
Supports the ParameterMetaData interface
PSQL JDBC Data Types
The numerical identifiers used to specify PSQL JDBC data types in some cases differ from the JDBC standard identifiers. The following table gives the full list.
Data Type
Identifier
BFLOAT4
7
BFLOAT8
8
BIGIDENTITY
-5
BIGINT
-5
BINARY
-2
BIT
-7
CHAR
1
CURRENCY
3
DATE
9
DATETIME
11
DECIMAL
3
DOUBLE
8
IDENTITY
4
INTEGER
4
LONGVARBINARY
-4
LONGVARCHAR
-1
NCHAR
-8
NLONGVARCHAR
-10
NUMERIC
2
NVARCHAR
-9
REAL
7
SMALLIDENTITY
5
SMALLINT
5
TIME
10
TIMESTAMP
11
TINYINT
-6
UBIGINT
-5
UINTEGER
4
USMALLINT
5
UTINYINT
-6
VARCHAR
12
PSQL JDBC Driver Limitations
Unsupported APIs
The JDBC driver does not support the following JDBC interfaces:
Array
Blob
Clob
NClob
Ref
RowId
SQLXML
Struct
SQLData
SQLInput
SQLOutput
URL
These are not supported due to the fact the PSQL engine does not currently support the underlying SQL 3 data types.
Driver Limitations
You cannot use long data in “out” parameters
The smallest actual fetch size is two rows
You cannot have an updateable result set with a join
You cannot have an updateable result set with a “group by”
The JDBC driver will not store data in UnicodeBig or UnicodeLittle formats
The only Holdability is HOLD_CURSORS_OVER_COMMIT
Pooled statements are not supported
Named parameters are not supported