Using WebLogic jDriver for Microsoft SQL Server

Using WebLogic jDriver for Microsoft SQL Server

This chapter explains how to set up and use WebLogic jDriver for Microsoft SQL Server, as described in the following sections:
What Is the WebLogic jDriver for Microsoft SQL Server?
Connecting to an SQL Server DBMS
Manipulating Data with JDBC
Codeset Support
JDBC Extensions
JDBC Limitations
References

What Is the WebLogic jDriver for Microsoft SQL Server?

WebLogic jDriver for Microsoft SQL Server is a Type 4, pure-Java, two-tier JDBC driver that you can use to create the physical database connections in a connection pool. It requires no client-side libraries because it connects to the database via a proprietary vendor protocol at the wire-format level.
A Type 4 JDBC driver is similar to a Type 2 driver in many other ways. Type 2 and Type 4 drivers are two-tier drivers - each client requires an in-memory copy of the driver to support its connection to the database. For more information on the types of JDBC drivers, see Introduction to WebLogic JDBC.
Within the WebLogic Server environment, you can use either a Type 2 or a Type 4 two-tier driver to connect the WebLogic Server to a database, and then one of WebLogic's multitier drivers, the RMI, JTS, or Pool driver. These are pure-Java Type 3 multitier JDBC drivers, for client connections to the WebLogic Server.
The API reference for JDBC, for which this driver is compliant, is available online at JavaSoft.

Connecting to an SQL Server DBMS

The following sections describe how to use WebLogic jDriver for Microsoft SQL Server to connect WebLogic Server to a database.

Using A Language Other Than English for a Connection

For Microsoft SQL Server databases that use a language other than English, specify the language property to reflect the language you use. For example, to specify the French language, add the following property when making a connection:
props.put("language","francais")
Failure to specify this property may result in exceptions, such as a "Primary Key Constraint Violation."

Connecting to a Database Using WebLogic Server in a Two-Tier Configuration

Complete the following procedure to set up your application to get a database connection through WebLogic Server using WebLogic jDriver for Microsoft SQL Server.
In steps 1 and 3, you describe the JDBC driver. In the first step, you use the full package name of the driver, which is dot-delimited. In the third step, you identify the driver with its URL, which is colon-delimited. The URL must include the following string: weblogic:jdbc:mssqlserver4. It may also include other information, such as the server host name and the database name.
Load and register the JDBC driver by doing the following:

Call Class.forName().newInstance() with the full class name of the WebLogic jDriver for Microsoft SQL Server JDBC driver class.
Cast it to a java.sql.Driver object.

For example:
Driver myDriver = (java.sql.Driver)Class.forName



 ("weblogic.jdbc.mssqlserver4.Driver").newInstance();
Create a java.util.Properties object describing the connection. This object contains name-value pairs containing information such as username, password, database name, server name, and port number. For example:
Properties props = new Properties();



props.put("user",         "scott");



props.put("password",     "secret");



props.put("db",           "myDB");



props.put("server",       "myHost");



props.put("port",         "8659");
Create a JDBC Connection object, which becomes an integral piece in your JDBC operations, by calling the Driver.connect() method. This method takes, as its parameters, the URL of the driver and the java.util.Properties object you created in step 2. For example:
Connection conn =  


myDriver.connect("jdbc:weblogic:mssqlserver4", props);
Connection Example

The following sample code shows how to use a Properties object to connect to a database named myDB on a server named myHost:
Properties props = new Properties();



props.put("user",         "scott");



props.put("password",     "secret");



props.put("db",           "myDB");



props.put("server",       "myHost");



props.put("port",         "8659");







Driver myDriver = (Driver)


Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();



Connection conn =  


myDriver.connect("jdbc:weblogic:mssqlserver4", props);
You can combine the db, server, and port properties into one server property, as shown in the following example:
Properties props = new Properties();



props.put("user",         "scott");



props.put("password",     "secret");



props.put("server",       "myDB@myHost:8659");



//  props.put("appname",    "MyApplication");



//  props.put("hostname",   "MyHostName");
The last two properties, appname and hostname, are optional and are passed to the Microsoft SQL server, where they can be read in the sysprocesses table under the column names program_name and hostname. The hostname value is prepended with WebLogic.
Driver myDriver = (java.sql.Driver)


Class.forName


  ("weblogic.jdbc.mssqlserver4.Driver").newInstance();


Connection conn  =  


   myDriver.connect("jdbc:weblogic:mssqlserver4", props);
Various methods can be used to supply information in the URL or in the Properties object. The information you pass in the URL of the driver does not need to be included in the Properties object.

Adding Connection Options

You can also add connection options to the end of the connection URL. Separate the URL from the connection options with a question mark, and separate options with ampersands, as shown in the following example:
  String myUrl = 


"jdbc:weblogic:mssqlserver4:db@myhost:myport?


   user=sa&password=";
To find out more about URL options at run time, use Driver.getPropertyInfo().

Connecting Using WebLogic Server in a Multi-Tier Configuration

To make a connection from your application to an SQL Server DBMS in a WebLogic Server multi-tier configuration, complete the following procedure:

To access the WebLogic RMI driver using JNDI, obtain a Context from the JNDI tree by looking up the JNDI name of your DataSource object. For example, to access a DataSource with JNDI name "myDataSource" that is defined in Administration Console:
  try {


  Context ctx = new InitialContext();


  javax.sql.DataSource ds 


    = (javax.sql.DataSource) ctx.lookup ("myDataSource");


} catch (NamingException ex) {
    // lookup failed
  }
To obtain the JDBC connection from the DataSource object:
   try {


  java.sql.Connection conn = ds.getConnection();


 } catch (SQLException ex) {


   // obtain connection failed


 }
For more information, see Configuring and Using DataSources.

Manipulating Data with JDBC

This section is a brief introduction to data manipulation with JDBC, and provides basic procedures for implementing the following basic tasks in an application:
Making Simple SQL Queries
Inserting, Updating, and Deleting Records
Creating and Using Stored Procedures and Functions
Disconnecting and Closing Objects

For more information, see your Microsoft SQL Server documentation and Java-oriented texts about JDBC. Note that the WebLogic jDriver for Microsoft SQL Server cannot handle question marks ("?") in table names or column names. To avoid errors, do not use question marks in the table names and column names in your database.

Making Simple SQL Queries

The most fundamental task in database access is to retrieve data. With WebLogic jDriver for Microsoft SQL Server, you can retrieve data by completing the following three-step procedure:

Create a Statement to send an SQL query to the DBMS.
Execute the Statement.
Retrieve the results into a ResultSet. In this example, we execute a simple query on the Employee table (alias emp) and display data from three of the columns. We also access and display metadata about the table from which the data was retrieved. Note that we close the Statement at the end.
Statement stmt = conn.createStatement();



stmt.execute("select * from emp");



ResultSet rs = stmt.getResultSet();







while (rs.next()) {


System.out.println(rs.getString("empid") + " - " +


                   rs.getString("name")  + " - " +


                   rs.getString("dept"));


}







ResultSetMetaData md = rs.getMetaData();







System.out.println("Number of columns: " + 


    md.getColumnCount());



stmt.close();
Inserting, Updating, and Deleting Records

In this section we show how to perform three common database tasks: inserting, updating, and deleting records from a database table. We use a JDBC PreparedStatement for these operations: first we create the PreparedStatement; then we execute and close it.
A PreparedStatement (subclassed from JDBC Statement) allows you to execute the same SQL repeatedly with different values.
In the following sample code, we create a PreparedStatement. Note the use of the ? syntax:
  String inssql = 


   "insert into emp(empid, name, dept) values (?, ?, ?)";


PreparedStatement pstmt = conn.prepareStatement(inssql);


pstmt.setString(1, "12345");


pstmt.setString(2, "gumby");


pstmt.setString(3, "Cartoons");
Next, we use a PreparedStatement to update records. In the following example, we add the value of the counter i to the current value of the dept field:
  String updsql = 


   "update emp set dept = dept + ? where empid = ?";


PreparedStatement pstmt2 = conn.prepareStatement(updsql);
  pstmt2.setString(1, "Cartoons");


pstmt2.setString(2, "12345);







Finally, we use a PreparedStatement to delete the records that we added and then updated:
  String delsql = "delete from emp where empid = ?";


PreparedStatement pstmt3 = conn.prepareStatement(delsql);


pstmt3.setString(1, "12345");
Creating and Using Stored Procedures and Functions

You can use WebLogic jDriver for Microsoft SQL Server to create, use, and drop stored procedures and functions.
In the following sample code, we execute a series of Statements to drop a set of stored procedures and functions from the database:
  Statement stmt = conn.createStatement();


try {stmt.execute("drop procedure proc_squareInt");}


catch (SQLException e) {;}


try {stmt.execute("drop procedure func_squareInt");}


catch (SQLException e) {;}


try {stmt.execute("drop procedure proc_getresults");}


catch (SQLException e) {;}


stmt.close();
We use a JDBC Statement to create a stored procedure or function, and then we use a JDBC CallableStatement (subclassed from Statement) with the JDBC ? syntax to set IN and OUT parameters.
Stored procedure input parameters are mapped to JDBC IN parameters, using the CallableStatement.setXXX() methods, such as setInt(), and the JDBC PreparedStatement ? syntax. Stored procedure output parameters are mapped to JDBC OUT parameters, using the CallableStatement.registerOutParameter() methods and JDBC PreparedStatement ? syntax. A parameter may be set to both IN and OUT. If it is, calls to both setXXX() and registerOutParameter() on the same parameter number must be made.
In the following example, we use a JDBC Statement to create a stored procedure and then execute the stored procedure with a CallableStatement. We use the registerOutParameter() method to set an output parameter for the squared value.
  Statement stmt1 = conn.createStatement();


stmt1.execute


  ("CREATE OR REPLACE PROCEDURE proc_squareInt " +


  "(field1 IN OUT INTEGER, field2 OUT INTEGER) IS " + 


  "BEGIN field2 := field1 * field1; field1 := " +


  "field1 * field1; END proc_squareInt;");


stmt1.close();










String sql = "{call proc_squareInt(?, ?)}";


CallableStatement cstmt1 = conn.prepareCall(sql);






// Register out parameters


cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
Next, we use similar code to create and execute a stored function that squares an integer:
  Statement stmt2 = conn.createStatement();


stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt " +


              "(field1 IN INTEGER) RETURN INTEGER IS " +


              "BEGIN return field1 * field1; " +



END func_squareInt;");


stmt2.close();










sql = "{ ? = call func_squareInt(?)}";


CallableStatement cstmt2 = conn.prepareCall(sql);






cstmt2.registerOutParameter(1, Types.INTEGER);



 
In the following example we use a stored procedure named sp_getmessages. (The code for this stored procedure is not included with this example.) sp_getmessages takes a message number as an input parameter and returns the message text, as an output parameter, in a ResultSet. You must process all ResultSets returned by a stored procedure using the Statement.execute() and Statement.getResult() methods before OUT parameters and return status are available.
String sql = "{ ? = call sp_getmessage(?, ?)}";


  CallableStatement stmt = conn.prepareCall(sql);






  stmt.registerOutParameter(1, java.sql.Types.INTEGER);


  stmt.setInt(2, 18000);           // msgno 18000


  stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
First, we set up the three parameters to the CallableStatement:

Parameter 1 (output only) is the stored procedure return value.
Parameter 2 (input only) is the msgno argument to sp_getmessage.
Parameter 3 (output only) is the message text return for the message number.

Next, we execute the stored procedure and check the return value to determine whether the ResultSet is empty. If it is not, we use a loop to retrieve and display its contents.
  boolean hasResultSet = stmt.execute();


while (true)


{


  ResultSet rs = stmt.getResultSet();


  int updateCount = stmt.getUpdateCount();


  if (rs == null && updateCount == -1) // no more results


    break;


  if (rs != null) {


    // Process the ResultSet until it is empty


    while (rs.next()) {


      System.out.println


      ("Get first col by id:" + rs.getString(1));


    }


  } else {


    // we have an update count


    System.out.println("Update count = " + 


     stmt.getUpdateCount());


  }


  stmt.getMoreResults();


}
After we finish processing the ResultSet, the OUT parameters and return status are available, as shown in the following example:
  int retstat = stmt.getInt(1);


String msg = stmt.getString(3);






System.out.println("sp_getmessage: status = " +


                   retstat + " msg = " + msg);


stmt.close();
Disconnecting and Closing Objects

You may want to call the commit() method to commit changes you have made to the database before closing a connection.
When autocommit is set to true (the default JDBC transaction mode) each SQL statement is its own transaction. After we created the Connection for these examples, however, we set autocommit to false. In this mode, the Connection always has an implicit transaction associated with it; any call to the rollback() or commit() method ends the current transaction and start a new one. Calling commit() before close() ensures that all transactions are completed before the Connection is closed.
Just as you close Statements, PreparedStatements, and CallableStatements when you have finished working with them, you should always call the close() method on the connection as final cleanup in your application, in a finally {} block. You should catch exceptions and deal with them appropriately. The final two lines of this example contain calls to commit and close the connection:
  conn.commit();


conn.close();
Codeset Support

As a Java application, WebLogic jDriver for Microsoft SQL Server handles character strings as Unicode strings. To exchange character strings with a database that may operate with a different codeset, the driver attempts to detect the codeset of the database and convert Unicode strings using a character set supported by the JDK. If there is no direct mapping between the codeset of your database and the character sets provided with the JDK, you can set the weblogic.codeset connection property to the most appropriate Java character set. For example, to use the cp932 codeset, create a Properties object and set the weblogic.codeset property before calling Driver.connect(), as shown in the following sample code:
java.util.Properties props = new java.util.Properties();



props.put("weblogic.codeset", "cp932");



props.put("user", "sa");



props.put("password", "");







String connectUrl = "jdbc:weblogic:mssqlserver4:myhost:1433";







Driver myDriver = (Driver)Class.forName


 ("weblogic.jdbc.mssqlserver4.Driver").newInstance();







Connection conn = myDriver.connect(connectUrl, props);
JDBC Extensions

This section describes the following extensions to JDBC:
Support for JDBC Extended SQL
Querying Metadata
Sharing a Connection Object in Multithreaded Applications
Execute Keyword with Stored Procedures

Support for JDBC Extended SQL

The JDBC specification includes SQL Extensions, also called SQL Escape Syntax. WebLogic jDriver for Microsoft SQL Server supports Extended SQL. Extended SQL provides access to common SQL extensions in a way that is portable between DBMSs.
For example, the function to extract the day name from a date is not defined by the SQL standards. For Oracle, the SQL is:
  select to_char(date_column, 'DAY') from table_with_dates
Using Extended SQL, you can retrieve the day name for both Oracle and SQL Server DBMSs as follows:
  select {fn dayname(date_column)} from table_with_dates
The following example code demonstrates several features of Extended SQL:
  String query =


"-- This SQL includes comments and " +


    "JDBC extended SQL syntax.\n" +


"select into date_table values( \n" +


"      {fn now()},        -- current time \n" +


"      {d '1997-05-24'},  -- a date       \n" +


"      {t '10:30:29' },   -- a time       \n" +


"      {ts '1997-05-24 10:30:29.123'},  -- a timestamp\n" +


"     '{string data with { or } will not be altered}'\n" +


"-- Also note that you can safely include" +


   " { and } in comments or\n" +


"-- string data.";


Statement stmt = conn.createStatement();


stmt.executeUpdate(query);
Extended SQL is delimited with curly braces ({}) to differentiate it from common SQL. Comments are preceded by two hyphens, and are ended by a newline (\n). The entire Extended SQL sequence, including comments, SQL, and Extended SQL, is placed within double quotes and passed to the execute() method of a Statement object.
The following code sample shows how Extended SQL can be used as part of a CallableStatement:
   CallableStatement cstmt = 


  conn.prepareCall("{ ? = call func_squareInt(?)}");
The following example shows how you can nest extended SQL expressions:
   select {fn dayname({fn now()})}
You can retrieve lists of supported Extended SQL functions from a DatabaseMetaData object. The following example shows how to list the functions supported by a JDBC driver:
   DatabaseMetaData md = conn.getMetaData();


System.out.println("Numeric functions:     " +


    md.getNumericFunctions());


System.out.println("\nString functions:    " + 


    md.getStringFunctions());


System.out.println("\nTime/date functions: " + 


    md.getTimeDateFunctions());


System.out.println("\nSystem functions:    " + 


    md.getSystemFunctions());


conn.close();
For a description of Extended SQL, see Chapter 11 of the JDBC 1.2 specification.

Querying Metadata

You can query metadata for the current database only. The metadata methods call the corresponding SQL Server stored procedures, which only operate on the current database. For example, if the current database is master, only the metadata relative to master is available on the connection.

Sharing a Connection Object in Multithreaded Applications

WebLogic jDriver for Microsoft SQL Server allows you to write multithreaded applications in which multiple threads share a single Connection option. Each thread can have an active Statement object. However, if you call Statement.cancel() on one thread, SQL Server may cancel a Statement on a different thread. Which Statement is cancelled depends on timing issues in the SQL Server. To avoid unexpected cancellations, we recommend that you get a separate Connection for each thread.

Execute Keyword with Stored Procedures

A Transact-SQL feature allows you to omit the EXECUTE keyword on a stored procedure when the stored procedure is the first command in the batch. However, when a stored procedure has parameters, WebLogic jDriver for Microsoft SQL Server adds variable declarations (specific to the JDBC implementation) before the procedure call. Because of this, it is good practice to use the EXECUTE keyword for stored procedures. Note that the JDBC extended SQL stored procedure syntax, which does not include the EXECUTE keyword, is not affected by this issue.

JDBC Limitations

This section describes the following limitations to JDBC:
cursorName( ) Method Not Supported
java.sql.TimeStamp Limitations
Changing autoCommit Mode
Statement.executeWriteText( ) Methods Not Supported

cursorName( ) Method Not Supported

The cursorName() method is not supported.

java.sql.TimeStamp Limitations

The java.sql.TimeStamp class in the JDK is limited to dates after 1970. Earlier dates raise an exception. However, if you retrieve dates using getString(), WebLogic jDriver for Microsoft SQL Server uses its own date class to overcome the limitation.

Changing autoCommit Mode

Call Connection.setAutoCommit() with a true or false argument to enable or disable chained transaction mode. When autoCommit is false, the WebLogic jDriver for Microsoft SQL Server driver begins a transaction whenever the previous transaction is committed or rolled back. You must explicitly end your transactions with a commit or rollback. If there is an uncommitted transaction when you call setAutoCommit(), the driver rolls back the transaction before changing the mode, so be sure to commit any changes before you call this method.

Statement.executeWriteText( ) Methods Not Supported

The WebLogic Type 2 JDBC drivers support an extension that allows you to write text and image data into a row as part of an SQL INSERT or UPDATE statement without using a text pointer. This extension, Statement.exexecuteWriteText() requires the DB-Library native libraries. Because the WebLogic jDriver for Microsoft SQL Server is a Type 4 JDBC driver, it does not use the DB-Library native libraries to communicate with the database, and therefore, cannot support the Statement.exexecuteWriteText() extension.
Use the following methods to read and write text and image data with streams:
prepareStatement.setAsciiStream()
prepareStatement.setBinaryStream()
ResultSet.getAsciiStream()
ResultSet.getBinaryStream()

References

This section provides references to documents and code examples that may help you learn about using BEA WebLogic jDriver for Microsoft SQL Server.

Related Documentation

Introduction to JDBC.
Contains information about other WebLogic JDBC drivers, additional documentation, support resources, and more.
Programming Tasks in Programming WebLogic HTTP Servlets.
Contains information about using connection pools with server-side Java.
Configuring JDBC.
Describes administrative tasks for configuring JDBC connectivity, such as creating connection pools, datasources, and multipools.
Sun's JDBC tutorial
.