Home -> Usenet -> comp.databases.oracle -> JDBC FAQ

JDBC FAQ

From: Salman Khan <sakhan_at_us.oracle.com>
Date: 1998/11/25
Message-ID: <365C4752.9D719019_at_us.oracle.com> 





routines using the standard Oracle conversion routines. Then this data
is converted from the client character set to UCS-2 (the Java character
set).  Instead of writing different conversion programs for each client
character set (about 180 supported by Oracle) to UCS-2, the JDBC
developers chose to write one conversion routine in Java that will go
from a single client character set to Java Unicode (UCS-2).  This single



character set allows you to encode the largest number of languages
supported by Oracle.  For Oracle8 clients, this character set is UTF8
(for Oracle7 clients, this character set is AL24).



 In the case where client character set is set to ISO or ASCII, the JDBC



driver converts the data from the server character set to the respective



character set and then converts it to UCS-2. In all other cases, the
JDBC drivers use the OCI routines to first convert the data from the
server character set to UTF8 (or AL24 for Oracle7 clients) and then from



UTF8 (or AL24 for Oracle7 clients) to UCS-2.  The translation from UTF8
to UCS-2 happens in Java. In a nutshell, the client set character set
(set via NLS_LANG on the client) is ignored for Java applications
because the ultimate destination for all Java applications is UCS-2.
Other applications (non-Java) use the character set described in your
NLS_LANG environment variable.



 For applications or applets using the JDBC-thin drivers there may be no



client Oracle installations.  So the character set translation process
is slightly different compared to the JDBC/OCI drivers.  In case the
database character set is US7ASCII or WE8ISO8859P1, the data is
transferred to the client without any translation.  For all other
database character sets, the server first translates the data to UTF8
before transferring it to the client.  On the client, the JDBC driver
will converts the data to UCS-2.



 In both JDBC Thin and JDBC/OCI drivers, all these conversions are done
transparently.  There is nothing that the user has to do for these
conversions to happen.





II. Compatibility/Certification



    
  
  1.   Which Java VMs do Oracle's JDBC drivers work with?






 The JDBC/OCI drivers work with Java VMs that are based on JavaSoft's VM



code: Netscape, Borland, Symantec, Oracle's Appbuilder for Java.  The
JDBC/OCI driver does not work with Microsoft's VM because the native
method interface is different.



 The JDBC thin driver should work with all Java VMs.




 2. How do I find out the version of the JDBC driver?



 In future releases of the JDBC driver, a text file will be included
that will document the version of the JDBC driver.
 For now, you can invoke the getDriverVersion() of
theOracleDatabaseMetaData class.



 Here's sample code showing how to do it:



 import java.sql.*;


 import oracle.jdbc.driver.*;



 class JDBCVersion


 {


 public static void main (String args [])
 throws SQLException


 {


 // Load the Oracle JDBC driver




 DriverManager.registerDriver


 (new oracle.jdbc.driver.OracleDriver());
 Connection conn = DriverManager.getConnection
 ("jdbc:oracle:thin:_at_host:port:sid","scott","tiger");



 // Create Oracle DatabaseMetaData object


 DatabaseMetaData meta = conn.getMetaData ();



 // gets driver info:




 System.out.println("JDBC driver version is " +
meta.getDriverVersion());


 }


 }




 3. Do Oracle's JDBC drivers support JDK 1.2?



 No, currently Oracle’s JDBC/OCI drivers do not support JDK 1.2. When
JDK 1.2 goes production, we plan to support our OCI-based driver with
this new release.  The thin JDBC driver should work fine with it.




 4. Are Oracle's JDBC drivers JDBC 2.0 compliant?



 Oracle's JDBC drivers are currently JDBC 1.22 compliant. We are closely



working with JavaSoft on the JDBC 2.0 spec. When the specification is
finally available, we will implement features to become JDBC 2.0
compliant.




 5. Do Oracle’s JDBC drivers support SQL92 escape syntax?



 Oracle's JDBC drivers (thin and OCI) do support some embedded SQL92
syntax.  Syntactically, this is specified in between curly braces.  The
current support is basic - no outer join syntax, not all scalar
functions, etc..  You can turn off SQL92 syntax with
stmt.setEscapeProcessing(false) and use regular Oracle SQL syntax.



 The following is a few examples of the supported syntax:



    
  
  1.   Time and Date Literals
 Date:  {d 'yyyy-mm-dd'}






 // Connect to the database

 // You can put a database name after the _at_ sign in the connection


 // URL.




 Connection conn =


 DriverManager.getConnection ("jdbc:oracle:oci8:_at_", "scott", "tiger");



 // Create a Statement




 Statement stmt = conn.createStatement ();



 // Select the ENAME column from the EMP table where the HIREDATE is


 // Jan-23-1982




 ResultSet rset = stmt.executeQuery


 ("select ENAME from EMP where HIREDATE = {d '1982-01-23'}");



 // Iterate through the result and print the employee names


 while (rset.next ())


 System.out.println (rset.getString (1));



 Time:  {t 'hh:mm:ss'}



 ResultSet rset = stmt.executeQuery


 ("select ENAME from EMP where HIREDATE = {t '12:00:00'}");



 Timestamp:  {ts 'yyyy-mm-dd hh:mm:ss.f...'} where fractional seconds
(.f...) portion can  be omitted.



 ResultSet rset = stmt.executeQuery


 ("select ENAME from EMP where HIREDATE = {ts '1982-01-23 12:00:00'}");



 b) Scalar Functions


 Not all scalar functions are supported.  To find out which functions
are supported, use the DatabaseMetadata methods: getNumericFunctions(),
getStringFunctions(), getTimeDateFunctions() and getSystemFunctions().



 Oracle's JDBC drivers do not support the 'fn' keyword.  You will get
the error "Non supported SQL92 token at position xx: fn" when trying to
run your Java application using the 'fn' keyword.  The workaround is to
use the Oracle SQL syntax.



 Instead of:


 Statement stmt = conn.createStatement ();
 stmt.executeUpdate("update EMP set ENAME = {fn CONCAT('My', 'Name')}");





 Use Oracle SQL syntax:


 stmt.executeUpdate("update EMP set ENAME = CONCAT('My', 'Name')");



 c) LIKE escape characters


 Since the characters "%" and "_" have special meaning in SQL LIKE
clauses ("%" - to match zero or more characters; "_" - exactly one
character), to interpret them literally, they can be preceeded with a
special escape character in strings, e.g. "&".  The syntax for
specifying the escape character is:  {escape '&'}



 For example,



 Statement stmt = conn.createStatement ();



 // Select the EMPNO column from the EMP table where the ENAME


 // starts with '_'




 ResultSet rset = stmt.executeQuery("select EMPNO from EMP where ENAME
like '&_%' {escape '&'}");



 // Iterate through the result and print the employee numbers


 while (rset.next ())


 System.out.println (rset.getString (1));



 Note:  if you want to use '\' as escape character, '\' will need to be
specified twice (i.e. '\\'):


 ResultSet rset = stmt.executeQuery("select EMPNO from EMP where ENAME
like '\\_%' {escape '\\'}");



 d) Outer Joins


 Currently, Oracle's JDBC drivers do not support outer join syntax: {oj
outer-join}.  The workaround is to use Oracle Join syntax:



 Instead of:


 Statement stmt = conn.createStatement ();
 ResultSet rset = stmt.executeQuery


 ("select ENAME, DNAME


 from {oj DEPT left outer join EMP on DEPT.DEPTNO = EMP.DEPTNO}
 order by ENAME");



 Use Oracle SQL syntax:


 ResultSet rset = stmt.executeQuery


 ("select ENAME, DNAME


 from EMP a, DEPT b where a.DEPTNO = b.DEPTNO(+)
 order by ENAME");



III. Encryption/Security



    
  
  1.   Do Oracle's JDBC drivers support encryption?






In both thin and the OCI-based drivers, the password is always
encrypted.  Currently, no data is encrypted in either of the JDBC
drivers.  For the JDBC/OCI driver, data can be encrypted depending on
the SQL*NET ANO (Advanced Networking Option) option chosen.  You would
also need a JDBC driver built specially for use with ANO. (Current JDBC
drivers do not support ANO.  This is targeted in future releases)




2. Can I connect to an Oracle database from an applet?



When connecting to a database, applets need to be able to open a socket
connection to the machine where the database is running.  However, there



are certain security restrictions with applets.  Applets, in general,
cannot open arbitrary network connections except to the host machine it
was downloaded from (originating host).  Hence, your JDBC applet is
restricted to connecting to a database running on the originating host
machine.



When opening a database connection to the host machine, make sure you
name the host machine in exactly the same way as was specified in the
HTTP address for the HTML page.



For example, if you load the HTML page using:



http://myserver.us.oracle.com/mystuff/test.html



Then, in order to connect to a database on this machine, in your connect



string you need to refer to it as 'myserver.us.oracle.com'.  For
example:



Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:_at_myserver.us.oracle.com:1521:ORCL","scott", "tiger");



However, if from the applet, you need to connect to a database that is
running on a machine different from the one from where you downloaded
the applet, you have 2 alternatives:



    
  
  1.   Use Connection Manager on the host machine from which the applet was
downloaded:






 If you use Connection Manager on the host machine, then your JDBC
applet can connect to Connection Manager, which in turn connects to a
database on a different machine.



 The connection information used in the getConnection() method of your
JDBC applet would use the name-value pair syntax of SQL*NET: (Note: In
case you are using the Thin JDBC drivers, you can not use the
hostname:port:sid form of the connect string.  You will have to use the
name-value pair syntax to specify your connect string)



 (NOTE: Indentation added to make it readable)


 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)
 (HOST = CmanHost.us.oracle.com)
 (PORT =1610))
 (ADDRESS = (PROTOCOL = TCP)
 (HOST = DBHost.us.oracle.com)
 (PORT = 1521))




 )


 (CONNECT_DATA = (SID=ORCL))



 (SOURCE_ROUTE=YES)



 )



 The first address has the address of the host machine from which the
applet was downloaded (and where Connection Manager is running).  The
second address has the address of the target database running on a
different machine.  Note you need to put (SOURCE_ROUTE=YES).



 In your JDBC applet, you would use it as:



 DriverManager.getConnection("jdbc:oracle:thin:scott/tiger_at_(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=CmanHost.us.oracle.com)(PORT=1610))(ADDRESS=(PROTOCOL=TCP)(HOST=DBHost.us.oracle.com)(PORT=1521)))(CONNECT_DATA=



(SID=ORCL))(SOURCE_ROUTE=YES))";


 In this particular example, the CMAN.ORA configuration file used by
Connection Manager would have this information:



 CMAN =



  (ADDRESS_LIST =



   (ADDRESS =


    (PROTOCOL=TCP)
    (HOST=CmanHost.us.oracle.com)
    (PORT=1610)




   )


  )



 CMAN_PROFILE =



  (PARAMETER_LIST=



   ...


   (AUTHENTICATION_LEVEL=0)



   ...


  )



 etc.



 Note that the AUTHENTICATION_LEVEL should be set to zero (the default)
so that requests that are not using ANO's Secure Network Services will
not be rejected.  (Connections via the JDBC thin driver do not use any
ANO option).




b) The other option is to use Signed Applets



If your browser supports JDK 1.1.x, (e.g. Netscape 4.0), then you can
use signed applets.  Signed applets can request socket connections
privileges to machines other than the one from where they were
downloaded.  You need to carry out the following steps to set this up:



b.1)  Sign the applet



There are some steps you need to follow to sign an applet.  Refer to
JavaSoft’s "Signed Applet Example"


(http://java.sun.com/security/signExample/index.html) for more
information.



b.2)  Include applet code that asks for appropriate permission before
opening a socket.



If you are using Netscape, then your code would include something like
this:



netscape.security.PrivilegeManager.enablePrivilege("UniversalConnect");
Connection conn = DriverManager.getConnection(...);



For more information, refer to Netscape's "Introduction to Capabilities
Classes"


(http://developer.netscape.com/docs/manuals/signedobj/capabilities/contents.htm).



This is how the modified sample JdbcApplet.java (bundled with the JDBC
driver) would look:



// import Netscape's security classes


import netscape.security.PrivilegeManager;



// Import the JDBC classes


import java.sql.*;



// Import the java classes used in applets
import java.awt.*;



import java.io.*;


import java.util.*;



public class JdbcApplet extends java.applet.Applet
{


// The driver to load


static final String driver_class =


"oracle.jdbc.driver.OracleDriver";



// The connect string


static final String connect_string =


"jdbc:oracle:thin:scott/tiger_at_dbhost.us.oracle.com:1521:ORCL";



// The query we will execute


static final String query =


"select 'Hello JDBC: ' || sysdate from dual";



// The button to push for executing the query
Button execute_button;



// The place where to dump the query result
TextArea output;



// The connection to the database


Connection conn;



// Create the User Interface


public void init ()


{


this.setLayout (new BorderLayout ());


Panel p = new Panel ();


p.setLayout (new FlowLayout (FlowLayout.LEFT));
execute_button = new Button ("Hello JDBC");
p.add (execute_button);


this.add ("North", p);


output = new TextArea (10, 60);


this.add ("Center", output);


}



// Do the work


public boolean action (Event ev, Object arg)
{


if (ev.target == execute_button)


{


try


{


// Clear the output area


output.setText (null);



// See if we need to open the connection to the
// database


if (conn == null)


{


// Load the JDBC driver


output.appendText ("Loading JDBC driver " +
driver_class + "\n");


Class.forName (driver_class);



// get the necessary privileges to open a
//network connection


try


{


output.appendText("Requesting privilege to
open network connection...\n");


PrivilegeManager.enablePrivilege("UniversalConnect");
output.appendText("\tSuccess!");


}


catch (netscape.security.ForbiddenTargetException e)
{


output.appendText("\tFailed! Permission to open network connection
denied by user.\n");


}


catch (Exception e)


{


output.appendText("\tFailed! Unknown exception while enabling
privilege.\n");


output.appendText(e.toString());


}



// Connect to the databse


output.appendText ("Connecting to " + connect_string + "\n");
conn = DriverManager.getConnection (connect_string);
output.appendText ("Connected\n");


}



// Create a statement


Statement stmt = conn.createStatement ();



// Execute the query


output.appendText ("Executing query " + query + "\n");
ResultSet rset = stmt.executeQuery (query);



// Dump the result


while (rset.next ())


output.appendText (rset.getString (1) + "\n");



// We're done


output.appendText ("done.\n");


}


catch (Exception e)


{


// Oops


output.appendText (e.getMessage () + "\n");
}


return true;


}


else


return false;


}


}





I. Performance



    
  
  1.   Are there any benchmark results available for JDBC drivers at this
time?






There are no performance benchmark results available for the JDBC
Drivers at this time. Work is in progress (stay tuned).



2. Are there any recommendations/suggestions to improve/tune my JDBC
application?



Here are some suggestions that you can use to improve the performance of



your JDBC applications:



i) Set AutoCommit off - By default, the JDBC driver commits all INSERTs
and UPDATEs as soon as a statement is executed.  You can turn AutoCommit



off then later use the explicit COMMIT statement to commit the
transaction.  Use the setAutoCommit entry point of the Connection class
to turn AutoCommit off:



   Connection.setAutoCommit(false);



ii) Batching for multiple inserts - Standard JDBC makes a round trip to
the database to execute a prepared statement whenever the statement's
executeUpdate method is executed.  Oracle’s JDBC can accumulate many
execution requests for the statement before passing the requests to the
database for execution thereby reducing roundtrips and network traffic.



The following example illustrates the use of this feature. It assumes
you have imported the classes oracle.jdbc.driver.*



Connection conn = DriverManager.getConnection
("jdbc:oracle:oci8:_at_<hoststring>","scott","tiger");



PreparedStatement ps =


conn.prepareStatement ("insert into dept values (?, ?, ?)");



//Change batch size for this statement to 3
((OraclePreparedStatement)ps).setExecuteBatch (3);


ps.setInt (1, 23);
ps.setString (2, "Sales");
ps.setString (3, "USA");
ps.executeUpdate (); //JDBC queues this for later execution

ps.setInt (1, 24);
ps.setString (2, "Blue Sky");




ps.setString (3, "Montana");


ps.executeUpdate (); //JDBC queues this for later execution


ps.setInt (1, 25);
ps.setString (2, "Applications");
ps.setString (3, "India");
ps.executeUpdate (); //The queue size equals the batch value of 3


//JDBC sends the requests to the database


ps.setInt (1, 26);
ps.setString (2, "HR");
ps.setString (3, "Mongolia");
ps.executeUpdate (); //JDBC queues this for later execution





((OraclePreparedStatement)ps).sendBatch ();
//JDBC sends the queued request


ps.close();



iii) Pre-fetching rows - By pre-fetching rows, Oracle JDBC drivers use
client-side buffers to replace expensive round trips by inexpensive
local pointer manipulation for most rows returned by a query.  The
default row pre-fetch value is currently set to 10.



The following example illustrates the use of this feature. It assumes
you have imported the classes "oracle.jdbc.driver.*":



Connection conn = DriverManager.getConnection
("jdbc:oracle:oci8:_at_<hoststring>","scott","tiger");



//Set the default row prefetch setting for this connection
((OracleConnection)conn).setDefaultRowPrefetch (7);



/* The following statement gets the default row prefetch value for
the connection, that is, 7.*/


Statement stmt = conn.createStatement ();



/* Subsequent statements look the same, regardless of the row
prefetch value. Only execution time changes. */
ResultSet rset = stmt.executeQuery ("select ename from emp");
System.out.println ( rset.next () );



while( rset.next () )


System.out.println ( rset.getString (1) );



//Override the default row prefetch setting for this statement
((OracleStatement)stmt).setRowPrefetch (2);



ResultSet rset = stmt.executeQuery ("select ename from emp")
System.out.println ( rset.next () );



while( rset.next () )


System.out.println ( rset.getString (1) );



stmt.close ();



iv) Put close statements in "finally" blocks to avoid memory leaks when
an exception is thrown:



CallableStatement cstmt = conn.prepareCall(...);
[...]


try


{


cstmt.execute();


}


catch (SQLException e) {}


finally


{


cstmt.close();


}



Work is in progress to improve the performance of JDBC drivers in the
following two areas:



i) Invoking PL/SQL blocks


ii) Calling DatabaseMetaData methods








--


Received on Wed Nov 25 1998 - 00:00:00 CET












Original text of this message