Corporate Info | News | Solutions | Products | Partners | Services | Events | Download | How To Buy

Using WebLogic jDriver for Microsoft SQL Server

Introduction

What's in this document

Implementing with WebLogic jDriver for Microsoft SQL Server

Connecting to a SQL Server DBMS

Connection example

Adding other connection options

Setting properties for WebLogic JDBC use

Overview of data manipulation

Making a simple SQL query

Inserting, updating, and deleting records

Creating and using stored procedures and functions

Disconnecting and closing objects

Codeset support

Using WebLogic jDriver for Microsoft SQL Server in applets

JDBC extensions and limitations

Support for JDBC Extended SQL

cursorName() method not supported

java.sql.TimeStamp limitations

Querying metadata

Changing autoCommit mode

Statement.executeWriteText() methods not supported

Sharing a Connection object in multi-threaded applications

Execute keyword with stored procedures

More Resources

Code examples

Introduction

WebLogic jDriver for Microsoft SQL Server is a new name for the product previously known as jdbcKona/MSSQLServer.

What's in this document

This document provides information on how to set up and use WebLogic jDriver for Microsoft SQL Server. WebLogic acquired this product, formerly known as FastForward, from Connect Software in April 1998.

WebLogic jDriver for Microsoft SQL Server is a Type 4, pure-Java, two-tier driver. It requires no client-side libraries since it connects to the database via a proprietary vendor protocol at the wire-format level. Unlike Type 2 JDBC drivers, Type 4 drivers make no "native" calls, so they can be used in Java applets.

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 four types of JDBC drivers, read the whitepaper Choosing a JDBC Driver.

Within the WebLogic environment, you can use a Type 2 or a Type 4 two-tier driver to connect the WebLogic Server to a database, and then use WebLogic JDBC, WebLogic's pure-Java Type 3 multitier JDBC driver, for client connections to the WebLogic Server.

The API reference for JDBC, of which this driver is a fully compliant implementation, is available online in several formats at JavaSoft.

Implementing with WebLogic jDriver for Microsoft SQL Server

Connecting to a SQL Server DBMS

Here is how to set up your application to connect to SQL Server using WebLogic jDriver for Microsoft SQL Server. In general, connecting happens in three steps:

1. Load the JDBC driver by calling Class.forName().newInstance() with the full class name of the WebLogic jDriver for Microsoft SQL Server JDBC driver class, and casting it to a java.sql.Driver object. This procedure properly loads and registers the JDBC driver. For example:

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

2. Create a java.util.Properties object describing the connection. This object contains name-value pairs containing information such as user name, 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");

3. Create a JDBC Connection object, which becomes an integral piece in your JDBC operations, by calling the Driver.connect() method, which 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);

In steps 1 and 3, you are describing the JDBC driver. In the first step, you use the full package name of the driver. Note that it is dot-delimited. In the third step, you identify the driver with its URL, which is colon-delimited. The URL must include at least weblogic:jdbc:mssqlserver4, and may include other information, including the server host name and the database name.

Connection example

This example 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 in this 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 MS SQL server, where they can be read in the sysprocesses table under the column names "program_name" and "hostname". The hostname value will be prepended with "WebLogic".

    Driver myDriver = (Driver)
      Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
    Connection conn  =
      myDriver.connect("jdbc:weblogic:mssqlserver4", props);

There are any number of variations on supplying information in the URL or in the Properties object. Whatever information you pass in the URL of the driver, you don't need to include in the Properties object. For more information, see U sing URLs to set properties for a JDBC connection.

Adding other 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 in this example:

    String myUrl =
    "jdbc:weblogic:mssqlserver4:db@myhost:myport?user=sa&password=";

You can use Driver.getPropertyInfo() to find out more about URL options at run-time.

Setting properties for WebLogic JDBC use

If you are using WebLogic JDBC in a multitier environment with a two-tier JDBC driver, you set connection properties in a slightly different way. See Using WebLogic JDBC

Overview of data manipulation

This section provides an overview of the basic steps for executing SQL data queries, making inserts and updates, using stored procedures, and using callable statements. These steps follow basic JDBC methodology and are intended as a brief introduction to data manipulation with JDBC. For additional information, you should consult your SQL Server documentation as well as Java-oriented texts discussing JDBC. You may also wish to consult Sun's JDBC tutorial http://jsp1.java.sun.com/docs/books/tutorial/jdbc/index.html.

Making a simple SQL query

The most fundamental task in database access is to retrieve data. With WebLogic jDriver for Microsoft SQL Server, this is a three-step process:

1. Create a Statement to send a SQL query to the DBMS

2. Execute the Statement

3. 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());
       for (int i = 1; i

Inserting, updating, and deleting records

We illustrate three common database tasks in this step: inserting, updating, and deleting records from a database table. We use a JDBC PreparedStatement for these operations; we create the PreparedStatement, then execute it and close it.

A PreparedStatement (subclassed from JDBC Statement) allows you to execute the same SQL over and over again with different values. PreparedStatements use the JDBC "?" syntax.

      String inssql =
         "insert into emp(empid, name, dept) values (?, ?, ?)";
      PreparedStatement pstmt = conn.prepareStatement(inssql);

      for (int i = 0; i

We also use a PreparedStatement to update records. In this 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);

      for (int i = 0; i

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

      for (int i = 0; i

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. First, 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, like 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 both IN and OUT, which requires both a setXXX() and a registerOutParameter() call to be done on the same parameter number.

In this example, we use a JDBC Statement to create a stored procedure, then we 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);
      for (int i = 0; i

In the next example, 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);
      for (int i = 0; i

This next example uses a stored procedure named sp_getmessages (the code for this stored procedure is not included with this example) named sp_getmessages, which takes a message number as an input parameter and returns the message text in a ResultSet as an output parameter. Note that 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.

First, we set up the three parameters to the CallableStatement:

1. Parameter 1 (output only) is the stored procedure return value

2. Parameter 2 (input only) is the msgno argument to sp_getmessage

3. Parameter 3 (output only) is the message text return for the message number

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

We execute the stored procedure and check the return value to see if 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();
      }

Once we have finished processing the ResultSet, the OUT parameters and return status are available.

      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

There are occasions on which you will want to call the commit() method to commit changes you have made to the database before you close the 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, and any call to the rollback() or commit() methods will end the current transaction and start a new one. Calling commit() before close() ensures that all of the transactions are completed before closing the Connection.

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 try {} block, and you should catch exceptions and deal with them appropriately. The final two lines of this example include a call to commit and then a call to 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. You can find the List of supported encodings at the JavaSoft website.

For example, to use the cp932 codeset, create a Properties object and set the weblogic.codeset property before calling Driver.connect(), as in this example:

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

Using WebLogic jDriver for Microsoft SQL Server in applets

Since WebLogic jDriver for Microsoft SQL Server is 100% Java, you can use it in Java applets. The examples directory includes Java applets to help you set up WebLogic jDriver for Microsoft SQL Server for use in applets. See the examples documentation (in the examples directory of your WebLogic jDriver for Microsoft SQL Server installation) for information about setting up and running the examples. If you're planning to use WebLogic jDriver for Microsoft SQL Server in an applet in a two-tier configuration, you will need to install the driver on your web server host. In addition, the web server and the SQL Server must be located on the same machine. Because an applet must communicate only with the host from which its classes were loaded, the SQL Server and the web server must be on the same IP host.

One way to overcome applet security restrictions is to use WebLogic jDriver for Microsoft SQL Server in a three-tier configuration with a server such as the WebLogic Server. In this configuration, applets connect to WebLogic Server via a WebLogic type-3 driver and WebLogic connects to SQL Server via WebLogic jDriver for Microsoft SQL Server. For more information on using WebLogic with applets, read Using WebLogic for applet programming.

The SocketApplet applet (see examples.jdbc.mssqlserver4.SocketApplet.html in the examples directory of your WebLogic jDriver for Microsoft SQL Server installation) tests whether the driver can connect to the SQL Server socket. Once you have run SocketApplet successfully, you can use the ConnectionApplet applet to verify that you can log into the SQL Server.

To run SocketApplet:

1. Make sure that c:\weblogic\mssqlserver4\classes is in your CLASSPATH. (Or c:\weblogic\mssqlserver4v70\classes, if you are using WebLogic jDriver for Microsoft SQL Server version 7.0.)

2. At a command prompt, change to the weblogic\mssqlserver4\examples\jdbc\mssqlserver4 directory (or weblogic\mssqlserver4v70\examples\jdbc\mssqlserver4, depending on which version of the driver you are using).

3. Edit the SocketApplet.java file. Make the following changes (you can search for the string "FIXME" to find the locations):

   - Replace "myhost" with the name of the computer running SQL Server.

   - Replace "1433" with the TCP/IP port number the SQL Server is listening on. The default port number is 1433, but you should check for the actual port your SQL Server is listening on.

     Save your changes and compile the applet:

           javac SocketApplet.java

     If you see compiler errors, make sure that the WebLogic jDriver for Microsoft SQL Server distribution is installed on the web server's computer and that c:\weblogic\mssqlserver4\classes (or c:\weblogic\mssqlserver4v70\classes, if you are using WebLogic jDriver for Microsoft SQL Server 7.0) is in the CLASSPATH when you start the web server. Copy SocketApplet.html and SocketApplet.java into a directory served by your web server.

     Load SocketApplet.html in your browser.

SocketApplet displays an error message if it was unable to connect to the SQL Server socket or a success message if it connected successfully.

Applet troubleshooting hints:

- The web server host name in the URL that you type in the browser must match the host name in the JDBC connection URL. For example, if you are running the browser, SQL Server and web server all on the same computer, you could refer to the computer by its name, IP number, or "localhost". Although these names all resolve to the same computer, some browsers require the names used to match.

- Be sure to load SocketApplet.html via a web server. Loading the page with a file:/// URL will not work.

- If you are unable to connect with SocketApplet, make sure that you test the SQL Server connectivity with dbping.

- If you have more questions about applet security issues, read Troubleshooting Applet Security problems

To run ConnectionApplet:

1. Edit ConnectionApplet.java:

   - Replace "myhost" in the connection URL with the name of the computer running SQL Server

   - Add your own database user name and password

     Save your changes.

     Compile ConnectionApplet.java:

           javac ConnectionApplet.java

     Make sure that c:\weblogic\mssqlserver4\classes (or c:\weblogic\mssqlserver4v70\classes, if you are using WebLogic jDriver for Microsoft SQL Server 7.0) is in the CLASSPATH before you start the web server.

     Copy ConnectionApplet.html and ConnectionApplet.class into a directory served by your web server.

     Load ConnectionApplet.html with Appletviewer:

           Appletviewer ConnectionApplet.html

     The applet displays a message telling you that either the connection succeeded or that there was an exception. The exception message will help you diagnose problems.

The WebLogic jDriver for Microsoft SQL Server distribution includes several additional examples. Refer to the examples directory of your WebLogic jDriver for Microsoft SQL Server installation for information about these examples.

JDBC extensions and limitations

Support for JDBC Extended SQL

The JavaSoft JDBC specification includes SQL Extensions, also called SQL Escape Syntax. WebLogic jDriver for Microsoft SQL Server supports and 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 DBMSs as follows:

      select {fn dayname(date_column)} from table_with_dates

Here's an example that 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. Here is Extended SQL used as part of a CallableStatement:

       CallableStatement cstmt =
        conn.prepareCall("{ ? = call func_squareInt(?)}");

This example shows that you can nest extended SQL expressions:

       select {fn dayname({fn now()})}

You can retrieve lists of supported Extended SQL functions from a DatabaseMetaData object. This example shows how to list all the functions a JDBC driver supports:

       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();

Refer to chapter 11 of the JDBC 1.2 specification at JavaSoft for a description of Extended SQL.

cursorName() method not supported

The cursorName() method is not supported.

java.sql.TimeStamp limitations

The java.sql.TimeStamp class in the JavaSoft 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.

Querying metadata

You can only query metadata for the current database. 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.

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, and thus is not supported by the WebLogic jDriver for Microsoft SQL Server JDBC driver.

To read and write text and image data with streams, you can use the prepareStatement.setAsciiStream(), prepareStatement.setBinaryStream(), ResultSet.getAsciiStream(), and ResultSet.getBinaryStream() JDBC methods.

Sharing a Connection object in multi-threaded applications

WebLogic jDriver for Microsoft SQL Server allows you to write multithreaded applications where multiple threads can 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. The actual Statement that is cancelled depends on timing issues in the SQL Server. To avoid this unexpected behavior, 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.

More Resources

- WebLogic JDBC Options

  Contains information on other WebLogic JDBC drivers, additional documentation, support resources, and more.

- Using connection pools with server-side Java (in Using WebLogic HTTP Servlets)

- Creating a startup connection pool

- Sun's JDBC tutorial

Code examples

We also ship a number of code examples in the distribution that will help you get started. Code examples are located in the weblogic/mssqlserver4/examples/ directory in the distribution. If you are using WebLogic jDriver for Microsoft SQL Server version 7.0, examples are located at weblogic/mssqlserver4v70/examples/

Copyright © 2000 BEA Systems, Inc. All rights reserved. Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher.