NAME

DBD::JDBC - JDBC proxy driver for the DBI module

SYNOPSIS

  use DBI;

  $dbh = DBI->connect("dbi:JDBC:hostname=$hostname;port=$port;url=$url",
                      $user, $password);

  # See the DBI module documentation.

REQUIRES

Perl 5.004 or higher
DBI 1.11 or higher
Convert::BER 1.31
Java Virtual Machine compatible with JDK 1.1
A JDBC driver

This module was developed and tested under Perl 5.004_04 (Solaris) and Sun's JDK 1.1.7b (Windows NT).

DESCRIPTION

DBD::JDBC is a Perl module which works in conjunction with a server written in Java to provide a DBI front end to a JDBC driver. The Perl module and Java server may be installed on different machines, as long as socket connections are allowed. The Java server portion is multi-threaded and supports multiple simultaneous connections.

This driver currently supports JDBC drivers which implement the JDBC 1.22 interface. JDBC 2.0-compatible drivers are expected to work, but no JDBC 2.0 functionality is exposed via DBD::JDBC.

The expected use for this module is as a DBI interface to databases with JDBC drivers but no DBI drivers. The implementation of this module was originally done for a non-SQL database in order to take advantage of the existing SQL parser in the database's JDBC driver.

The Java classes provided with this module also allow a Java application or servlet to create a JDBC connection and then execute a Perl script which can use that pre-existing JDBC connection via DBI. This particular functionality was implemented in order to allow Perl customizations to a Java servlet-based application. See the example in the example/ directory.

CONNECTING

Before using DBD::JDBC, you must start the DBD::JDBC server.

Starting the server

The DBD::JDBC server is a Java application intended to be run from the command line. It may be installed, along with whatever JDBC driver you wish to use, on any host capable of accessing the database you wish to use via JDBC. Perl applications using DBD::JDBC will open a socket connection to this server. You will need to know the hostname and port where this server is running.

To start the server,

Place the dbd_jdbc.jar file and your database's JDBC driver on the machine where you wish to run the server.
Add dbd_jdbc.jar and your database's JDBC driver to your classpath. Follow any other instructions which came with your JDBC driver. For example, a type 2 JDBC driver may require that the database's native libraries be added to your path or library path.
Start the server, providing at least the required system properties on the command line:

jdbc.drivers (required)

This should be set to the complete class name of your JDBC driver. If you want to use more than one JDBC driver, use a colon-separated list of driver names. See the standard Javadoc documentation for java.sql.DriverManager for an example.

dbd.port (required)

This is the port to which this server will listen. Your Perl client applications will need to know this in order to connect.

dbd.trace

If set, this value indicates the tracing level for the server. Tracing output will be written to stderr. Legal values are silent (the default), brief, verbose, tedious, and abusive.

Example

java -Djdbc.drivers=foo.bar.Driver -Ddbd.port=12345 com.vizdom.dbd.jdbc.Server

Connecting to the server

A dsn for DBD::JDBC has the following form:

dbi:JDBC:hostname=$host;port=$port;url=$url;jdbc_character_set=$charset

where

$host is the hostname on which the DBD::JDBC server is running (optionally followed by :$port; if so, the port name/value pair is optional).
$port is the port on which the DBD::JDBC server is running.
$url is a complete JDBC url for your JDBC driver. You might want to test this URL in a Java application (in the same environment in which you intend to run the DBD::JDBC server) before attempting to connect from Perl. Your JDBC url may need to include your database host and port information; these values are distinct from those needed in the DBD::JDBC dsn, which are for the DBD::JDBC server, not the database.

If the JDBC url contains the characters ';' or '=', those characters must be URL-encoded. For example,
```
$url =~ s/([=;])/uc sprintf("%%%02x",ord($1))/eg;
```
The driver will unescape the url before sending it to the JDBC driver. [See the driver code if you really want to know why.]
$charset is the character set used by your DBI application (i.e. the character set in use on whichever machine is running Perl, not the machine running the DBD::JDBC server, unless they're the same). This should be specified in the form of a valid Java character encoding name. If no character set is given, the driver defaults to ISO8859_1. See http://java.sun.com/products/jdk/1.1/docs/guide/intl/encoding.doc.html for a list of supported encodings. The character set name must be encoded in ASCII so that the server can appropriately decode it.

Any strings sent to the DBD::JDBC server as parameter values will be converted from this encoding to Unicode (Java's native encoding). Use bind_param and a type hint to avoid character set conversion of binary data. Data being returned as strings (everything other than binary columns) will be converted to this encoding.

The character set in use will be available as the database handle attribute 'jdbc_character_set'. Changing this attribute will have no effect on character conversion; the character set is established at connection time.

Example

$dsn = "dbi:JDBC:hostname=myhost;port=12345;url=jdbc:odbc:mydatasource";
$dsn = "dbi:JDBC:hostname=myhost:12345;url=jdbc:oracle:thin:\@mydbhost:1521:test;jdbc_character_set=ASCII";

USAGE NOTES

Character sets

You can find out what character set Java thinks your platform uses by examining the value of the system property file.encoding.

System.out.println("This system uses: " + System.getProperty("file.encoding"));

Local experimentation (in the US) indicates that Windows NT uses "Cp1252" (Windows Latin-1) and Unix variants (AIX, Solaris) use "ISO8859_1".

jdbc_error

When a JDBC exception is thrown in the server, the exception and any exceptions chained to the original are returned and placed in the jdbc_error attribute of the most-recently-used handle. This attribute will contain an array of hashrefs with keys err, errstr, and state. The first error's values will also be available via $h->err, $h->errstr, and $h->state.

foreach $err (@{$sth->{jdbc_error}}) {
    print "Error: ($err->{err}/$err->{state}) $err->{errstr}\n";
}

DBI->JDBC method mappings

What follows is a guide to the JDBC methods being called when a DBI method or property is accessed. See the source code for com.vizdom.dbd.jdbc.Connection for details.

DBI->connect

DriverManager.getConnection(url, username, password)
Connection.setAutoCommit   [if AutoCommit was specified]

$dbh->prepare

Connection.prepareStatement(statement)

$dbh->commit

Connection.commit()

$dbh->rollback

Connection.rollback()

$dbh->disconnect

PreparedStatement.close()   [for each open statement]
Connection.close()

$dbh->ping

Connection.isClosed()

$dbh->{AutoCommit}

Connection.getAutoCommit()
Connection.setAutoCommit()

$sth->execute

PreparedStatement.setXXX(value)   [if there are any parameters]
PreparedStatement.execute()
PreparedStatement.getResultSet()
PreparedStatement.getUpdateCount()

$sth->fetch

ResultSet.next()
ResultSet.getXXX

$sth->{CursorName}

ResultSet.getCursorName()

$sth->{NAME}

ResultSetMetaData.getColumnName()

$sth->{TYPE}

ResultSetMetaData.getColumnType()

$sth->{PRECISION}

ResultSetMetaData.getPrecision()

$sth->{SCALE}

ResultSetMetaData.getScale()

$sth->{NULLABLE}

ResultSetMetaData.isNullable()

Statement parameters and column values

Parameter values are sent to the DBD::JDBC server as a sequence of bytes. Numeric parameters are converted to strings first (Perl handles this automatically).

When the DBD::JDBC server receives the parameter values, the bytes are converted to a Java String (using the character encoding specified at connection time by the jdbc_character_set value) and the PreparedStatement.setString() method is used to set the parameter value.

If a type hint (one of the SQL_XXX types you can import from the DBI module) is supplied along with a parameter in $sth->bind_param(), the type code will be mapped to the corresponding JDBC type code and passed along to the DBD::JDBC server. The JDBC type will be used to determine which PreparedStatement.setXXX method to call. The mapping from type hint to setXXX method is taken from Table 21.2, p. 394, in JDBC Data Access with Java.

BINARY, VARBINARY, LONGVARBINARY: setBytes
TINYINT, SMALLINT: setShort
INTEGER: setInt
BIGINT: setLong
REAL: setFloat
FLOAT, DOUBLE: setDouble
DECIMAL, NUMERIC: setBigDecimal
BIT: setBoolean
CHAR, VARCHAR, LONGVARCHAR, DATE, TIME, TIMESTAMP: setString

Type hints are required for binary data in order to avoid having binary parameter values passed through the default character conversion process. In other cases, they are generally optional and may in fact reduce performance by causing unneccessary data conversions (for example, if your database's JDBC driver passes all data to the database as strings, the JDBC driver will have to convert numbers back to strings anyway).

A call to $sth->fetch() will cause the DBD:JDBC server to use the column type information from ResultSetMetaData to determine how to retrieve column data.

Column type         Method used        
BINARY, VARBINARY   getBytes
LONGVARBINARY       getBinaryStream
LONGVARCHAR         getUnicodeStream
all others          getString

Once retrieved, the data is returned to Perl as a sequence of bytes. String-to-numeric conversions are again left to Perl.

NUM_OF_PARAMS implementation

The statement attribute NUM_OF_PARAMS is set when $dbh->prepare is called. Since JDBC doesn't expose this information about PreparedStatements, DBD::JDBC uses a simple '?' counting method which may fail to provide an accurate count if the parameter marker is not '?' or the syntax does not conform to standard SQL (and possibly even if it does, if I've interpreted the SQL grammar poorly). Depending on this value to be accurate is not recommended, although you may find that it is sufficient for your use.

DIAGNOSTICS

All errors generated by DBD::JDBC have IJDBC as the sql state.

Errors generated by the Perl driver

Unsupported AutoCommit value (no error number): If you attempt to set AutoCommit to anything other than 0 or 1, the driver will die with this error.

Error code 100: An error occurred while sending a request to the server.
Error code 101: An error occurred while receiving a response from the server.
Error code 102: There was a problem decoding a server response packet.
Error code 103: The dsn supplied to connect is missing one or more required values.
Error code 104: A connection to the server could not be established. The server may not be running, or the host or port information may be incorrect.
Error code 105: An $sth->execute call caused the server to return an invalid response. This is an internal error.

Errors generated by the Java server

Error code 1: The client requested an operation on a statement object which does not exist on the serer.
Error code 2: fetch was called on a statement which has no data. For example, this error might result if fetch is called before a statement is executed.
Error code 3: The server was asked to return the value of an unknown attribute.
Error code 4: This error code indicates that the client attempted to do something which requires a cursor (a ResultSet) on the server, but no cursor is present.
Error code 5: No metadata is currently available. This error will result if a request is made for a statement attribute at a time when no ResultSet is associated with the statement.
Error code 6: This error code indicates that the client sent a message to the server which the server does not understand.
Error code 7: The server was unable to respond to the client's request. This error would likely be sent as the result of another, undetected, error on the server.
Error code 8: This error code is used when the server wishes to send a random error string to the client. For example, arbitrary Java exceptions may be sent with this error code.
Error code 9: An error occurred during fetch. The error text will describe the actual error.
Error code 10: This error code indicates that the client's requested character encoding is not supported.
Error code 11: An error occurred while setting a statement parameter.
Error code 12: A long field was truncated during fetch.

TO DO

See the ToDo file included with the distribution. Highlights include

Make the complete JDBC interface available from DBI.
DBI metadata methods, cancel, row cache.
Better handling of long fields via some sort of streaming interface.
JDBC 2.0 support.

AUTHOR

Gennis Emerson <gemerson@vizdom.com>

COPYRIGHT

This program is free software; you can redistribute it and/or modify it under the same terms as the Perl Kit, namely, under the terms of either:

    a) the GNU General Public License as published 
    by the Free Software Foundation; either version 1 
    of the License, or (at your option) any later 
    version, or

    b) the "Artistic License" that comes with the 
    Perl Kit.

This program is distributed in the hope that it will be seful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See either the GNU General Public License or the Artistic License for more details.

To install DBD::JDBC, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBD::JDBC

CPAN shell

perl -MCPAN -e shell
install DBD::JDBC

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)