DBD-JDBC

 view release on metacpan or  search on metacpan

lib/DBD/JDBC.pod  view on Meta::CPAN

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";


=head2 Specifying JDBC connection properties

You can specify JDBC connection properties in the JDBC URL. You
can also specify connection properties as follows: 

   %properties = ('user' => 'user', 
                  'password' => 'password', 
                  'host.name' => 'dbhost', 
                  'host.port' => '7000');

   $dsn = "dbi:JDBC:hostname=host:9001;url=jdbc:opentext:db:";
   $dbh = DBI->connect($dsn, undef, undef, 
          { PrintError => 0, RaiseError => 1, jdbc_properties => \%properties })
          or die "Failed to connect: ($DBI::err) $DBI::errstr\n";

When specifying properties this way, use C<undef> for the user
and password parameters in the C<DBI-E<gt>connect> method. The
DBD::JDBC server will use the JDBC method
C<DriverManager.getConnection(String, Properties)> when the
username and password parameters are undefined. Otherwise, it
will use the  JDBC method
C<DriverManager.getConnection(String, String, String)> and ignore
any other connection properties. 


=head1 USAGE NOTES

=head2 Reading long data

As of DBD::JDBC 0.70, the default behavior of this driver is to
read long fields in their entirety. This behavior is controlled
by the driver attribute C<jdbc_longreadall>. When
C<jdbc_longreadall> is true, the DBI attributes LongReadLen and
LongTruncOk will be ignored and the entire contents of long
fields will be returned. To return to the default DBI behavior,
set C<jdbc_longreadall> to false. 


=head2 Calling JDBC methods

JDBC methods are exposed using the C<$h-E<gt>jdbc_func> method and Java
reflection. This feature is not intended to replace any existing
DBI methods, merely to provide access to JDBC-specific methods
with no DBI equivalent. Reflection, rather than explicit methods
corresponding to methods in the JDBC API, is used in order to
allow access to driver-specific methods not in the JDBC API.

The general syntax is

  $h->jdbc_func(parameter, ..., <jdbc_method_name>);

For example, 

  $ret = $dbh->jdbc_func("getAutoCommit");
  $ret = $sth->jdbc_func("mycursor", "Statement.setCursorName");

The driver-specific method C<jdbc_func> replaces the previous use
of the standard DBI method C<func> for calling JDBC
methods. Since C<jdbc_func> is a driver-specific method, the
C<jdbc_> prefix previously required on the method name argument
is no longer required. Use of the C<jdbc_> prefix on the method
name is still supported.

The following limitations apply: 

=over

=item *

Only public methods can be called.

=item *

In general, only methods whose parameters and return values are
primitive types (C<int>, C<boolean>, C<void>, etc.) or Strings can be
called. See below for more details.

=item *

DBD::JDBC doesn't know which methods are being called when
reflection is used. This means that interleaving DBI methods and
calls to JDBC methods using C<jdbc_func> may leave DBD::JDBC in an
inconsistent state. One example of this is C<$sth-E<gt>rows>: if you've
called C<ResultSet.next> directly, rather than using C<$sth-E<gt>fetch>,
the row count will not accurately reflect the rows in the result
set. 

=item *

Methods can only be called on the Connection, Statement,
ResultSet, and ResultSetMetaData objects.

=back

The JDBC method name is used as the jdbc_func method name. Parameters
are passed as strings by default. To pass parameters of other
types, pass the parameter as reference to an array in which the
first element is the parameter and the second is one of the DBI
SQL_XXX typecodes. For example,

    $h->jdbc_func("string parameter", [11 => SQL_INTEGER], 
             [1 => SQL_BIT], "method_name");

It is very important to use the correct typecodes for the actual
parameter types of the Java method in order to enable Java
reflection to locate the correct method. The method will be
looked up using the C<java.lang.class.getMethod(String, Class[])>
method, so if the parameter types don't match the actual method
parameters, the method won't be found.

SQL types are mapped to Java types by mapping the DBI constants
to values from java.sql.Types, then mapping the C<java.sql.Types>
values to Java types.

    DBI constant           java.sql.Types constant    Java type

lib/DBD/JDBC.pod  view on Meta::CPAN

For C<SQL_DATE>, C<SQL_TIME>, and C<SQL_TIMESTAMP> parameters,
the default JDBC string representations for these types must be
used. 

    SQL_DATE: yyyy-mm-dd
    SQL_TIME: hh:mm:ss
    SQL_TIMESTAMP: yyyy-mm-dd hh:mm:ss.f

(The C<.f> portion of the timestamp format is in nanoseconds and
is optional.)


Possible return values from C<$h-E<gt>jdbc_func> are C<undef> if the
Java method returned C<null> or had a C<void> return type, 1 or 0
if the Java method had a C<boolean> return type, or a scalar for
any other return type (the C<Object> returned by
C<java.lang.reflect.Method.invoke> will be converted to a String
by calling its C<toString> method).

You are not limited to calling methods defined by the JDBC
API. Any public method defined by your JDBC driver on the
available objects, with parameters and return type as described
above, may be called.

C<jdbc_func> is made available using DBI's C<install_method>
method. This means that errors are handled in the standard DBI
manner, not the way they're handled for C<$h-E<gt>func>.


B<Connection methods>

To call JDBC methods on the JDBC Connection object, use the
C<jdbc_func> method on the C<$dbh> handle. 

Examples

  
    $ret = $dbh->jdbc_func("jdbc_getAutoCommit");
    $dbh->jdbc_func([1 => SQL_BIT], "jdbc_setAutoCommit");
    $ret = $dbh->jdbc_func("select * from client", "jdbc_nativeSQL");
    $dbh->jdbc_func([4 => SQL_INTEGER], "jdbc_setTransactionIsolation");
     

B<Statement, ResultSet, and ResultSetMetaData methods>

To call JDBC methods on the JDBC Statement, ResultSet, and
ResultSetMetaData objects, use the C<jdbc_func> method on the C<$sth>
handle and prefix the method name with one of the listed
interface names. You may use either Statement or
PreparedStatement to indicate the current PreparedStatement
object, since DBD::JDBC uses PreparedStatements internally.

ResultSet and ResultSetMetaData methods are not available until
after C<$sth-E<gt>execute> has been called.

Examples


    $ret = $sth->jdbc_func("jdbc_Statement.getMaxFieldSize");
    $sth1->jdbc_func("mycursor", "jdbc_Statement.setCursorName");
    $sth1->jdbc_func([22 => SQL_INTEGER], "jdbc_Statement.setMaxFieldSize");

    $ret = $sth1->jdbc_func("jdbc_ResultSet.next");
    $ret = $sth1->jdbc_func("cname", "jdbc_ResultSet.getString");
    $ret = $sth2->jdbc_func("eno", [5003 => SQL_INTEGER], "jdbc_ResultSet.updateInt");

    $ret = $sth1->jdbc_func([1 => SQL_INTEGER], "jdbc_ResultSetMetaData.getSchemaName");


B<Notes>

If for some reason you reach the end of a ResultSet using
C<$sth-E<gt>jdbc_func("ResultSet.next")> rather than one of the standard DBI
methods (C<fetch>, etc.), the DBI statement handle will continue
to think that it's active. You must call C<$sth-E<gt>finish> explicitly
in this case.

Be aware of which JDBC methods are called by the standard DBI
methods. For example, C<$sth-E<gt>fetch> calls C<next> and reads all the
columns in the current row. With some JDBC drivers, you will not
be able to call $sth->fetch followed by C<$sth-E<gt>jdbc_func("column_name",
"ResultSet.getString")> because all the data for the row has already been
read. 


If you are using a JDBC driver with scrollable result sets,
please note that support for such is provided purely through
C<jdbc_func>, not through any explicit DBD::JDBC support. This means
that a loop over the set, such as

    while ($row = $sth->fetch()) { 
        # do something
    }

will cause DBD::JDBC to mark the statement handle as inactive at
the end of the loop (C<$sth-E<gt>{Active}> will be false). You can still
use C<jdbc_func> to operate on the underlying ResultSet, but you can't
continue to use any DBI method which requires that the statement
handle be active. The following sequence seems to work, though
perhaps it shouldn't: 

    while ($row = $sth->fetch()) { 
        # do something
    }
    $sth->jdbc_func("ResultSet.beforeFirst");
    while ($row = $sth->fetch()) { 
        # do something else
    }

Some sort of explicit support for scrollable result sets will
probably be implemented at a later date.



=head2 Closing cursors

When a statement handle goes out of scope, Perl will call its
DESTROY method. This method will cause C<Statement.close> to be
called on the associated Java C<Statement> object in the
DBD::JDBC server. For many applications, this is
sufficient. However, if you find that statement handles are not
being destroyed quickly enough, or you are maintaining a
collection of statements for repeated use, you may choose to
close the ResultSet associated with the Statement explicitly
using C<jdbc_func>. Closing the ResultSet will not prevent you from
executing the statement again, but it will release any database
resources held by the ResultSet.

Typical usage: 

    $sth = $dbh->prepare("select id from sched");
    $sth->execute();
    while ($row = $sth->fetch()) { 
        # do something
    }
    # At this point, the statement handle is no longer active, but
    # the ResultSet still exists on the server.
    $sth->jdbc_func("ResultSet.close");


DBD::JDBC does not close ResultSet objects when C<$sth-E<gt>finish> is
called (whether it is called implicitly when the end of the
result set is reached or explicitly in your program) in order to
support scrollable result sets. With a scrollable result set,
reaching the end of the data does not mean that the ResultSet
is unusable, so calling C<close> would be unfortunate. 



=head2 Character sets

You can find out what character set Java thinks your platform
uses by examining the value of the system property
C<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". 



=head2 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 C<jdbc_error> attribute of the most-recently-used
handle. This attribute will contain an array of hashrefs with
keys C<err>, C<errstr>, and C<state>. The first error's values
will also be available via C<$h-E<gt>err>, C<$h-E<gt>errstr>, and
C<$h-E<gt>state>.

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

lib/DBD/JDBC.pod  view on Meta::CPAN

=item 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.

=back

=over 4

=item Error code 100

An error occurred while sending a request to the server.

=item Error code 101

An error occurred while receiving a response from the server.

=item Error code 102

There was a problem decoding a server response packet.

=item Error code 103

The dsn supplied to C<connect> is missing one or more required values.

=item 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. 

=item Error code 105

An C<$sth-E<gt>execute> call caused the server to return an invalid
response. This is an internal error.

=back

=head2 Errors generated by the Java server

=over 4

=item Error code 1

The client requested an operation on a statement object which does not
exist on the serer. 

=item Error code 2

C<fetch> was called on a statement which has no data. For
example, this error might result if C<fetch> is called before a
statement is executed.

=item Error code 3

The server was asked to return the value of an unknown attribute.

=item 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. 

=item 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. 

=item Error code 6

This error code indicates that the client sent a message to the
server which the server does not understand.

=item 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.

=item 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. 

=item Error code 9

An error occurred during C<fetch>. The error text will describe
the actual error.

=item Error code 10

This error code indicates that the client's requested character
encoding is not supported. 

=item Error code 11

An error occurred while setting a statement parameter.

=item Error code 12

A long field was truncated during C<fetch>.

=item Error code 13

A reflection request was made, but there's no object on which to
call the indicated method. For example, trying to call
C<ResultSet.next> before calling C<$sth-E<gt>execute> will cause this
error to be reported, since no ResultSet exists.

=item Error code 14

An unknown class name was passed to C<$sth-E<gt>jdbc_func>.

=item Error code 15

A Java exception related to reflection was thrown. This may
include, for example, C<NoSuchMethodException> if the requested
method can't be located.

=back