|
|
|
|
|
|
The jdbcKona/MSSQLServer4 is a Type 4, pure-Java, two-tier driver. It requires no client-side libraries because 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.
The API reference for JDBC, of which this driver is a fully compliant implementation, is available online in several formats at the Sun Microsystems, Inc. Web site.
To connect to an SQL Server database in a WLE Java server application, perform the following steps.
Note:
See the section Obtaining Connections from a WLE Connection Pool for more information about an alternative way of connecting to the DBMS.
Connecting to an SQL Server with the jdbcKona/MSSQLServer4 Driver
An efficient way to load the JDBC driver is to invoke the Class.forName().newInstance() method, specifying the name of the driver class, as in the following example:
Class.forName("weblogic.jdbc20.mssqlserver4.Driver").newInstance();
After loading the JDBC driver, request a JDBC connection by invoking the DriverManager.getConnection method. You invoke this method with a connection URL, which, again, specifies the JDBC driver and other connection information.
There are several ways to specify connection information in the DriverManager.getConnection method. The following sections describe three methods.
The simplest method is to use a connection URL that includes the database name, host name and port number of the database server, and two additional arguments to specify the database user name and password, as in the following example:
Class.forName("weblogic.jdbc20.mssqlserver4.Driver").newInstance();
Connection conn =
DriverManager.getConnection(
"jdbc:weblogic:mssqlserver4:database@host:port",
"sa", // database user name
""); // password for database user
In this example, host is the name or IP number of the computer running SQL Server, and port is the port number the SQL Server is listening on.
You can set connection information in a Properties object and pass this information to the DriverManager.getConnection method. The following example specifies the server , user , and password in a Properties object:
Properties props = new Properties();
props.put("server", "pubs@myhost:1433");
props.put("user", "sa");
props.put("password", "");
Class.forName("weblogic.jdbc20.mssqlserver4.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:mssqlserver4", props);
You can add connection options to the end of the connection URL, instead of creating a Properties object. Separate the URL from the connection options with a question mark (?), and separate options with ampersands (&), as in the following example:
Class.forName("weblogic.jdbc20.mssqlserver4.Driver).newInstance();
DriverManager.getConnection(
"jdbc:weblogic:mssqlserver4:database@myhost:myport?user=
sa&password=");
You can use the Driver.getPropertyInfo method to find out more about URL options at run time.
The jdbcKona/MSSQLServer4 driver recognizes SQL Server 7 automatically. You must set the sql7 property in the connection URL or in a Properties object to true to connect to SQL Server 7. For example, the connection URL for an SQL Server 7 connection would be similar to the following:
"jdbc:weblogic:mssqlserver4:pubs@myhost:myport?sql7=true"
The jdbcKona/MSSQLServer4 driver has been used successfully in the Java SDK 1.2 for Sun and Windows NT development environment.
This section describes the following JDBC extensions and limitations:
The Sun Microsystems, Inc. JDBC specification includes a feature called SQL Extensions, or SQL Escape Syntax. The jdbcKona/MSSQLServer4 driver supports Extended SQL. For information about this feature, see Using the jdbcKona Drivers.
The cursorName
method is not supported, because its definition does not apply to the Microsoft SQL Server.
The java.sql.TimeStamp
class in the Java 2 software is limited to dates after 1970. Earlier dates raise an exception. However, if you retrieve dates using the getString
method, the jdbcKona/MSSQLServer4 driver uses its own date class to overcome the limitation.
You can only query metadata for the current database. The metadata methods call the corresponding SQL Server stored procedures, which operate only on the current database. For example, if the current database is master, only the metadata relative to master is available on the connection.
Invoke the Connection.setAutoCommit
method with a true
or false
argument to enable or disable chained transaction mode. When autoCommit
is true
, the jdbcKona/MSSQLServer4 driver begins a transaction whenever the previous transaction is committed or rolled back. You must explicitly end your transactions with a commit
or a rollback
. If there is an uncommitted transaction when you invoke the setAutoCommit
method, the driver rolls back the transaction before changing the mode. Be sure to commit any changes before you invoke this method.
The jdbcKona Type 2 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.executeWriteText()
, requires the DB-Library native libraries, and thus is not supported by the jdbcKona/MSSQLServer4 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.
The jdbcKona/MSSQLServer4 driver allows you to write multithreaded applications in which multiple threads can share a single Connection
object. Each thread can have an active Statement
object. However, if you invoke the Statement.cancel
method on one thread, SQL Server may cancel a Statement
on a different thread. The Statement
object that is cancelled depends on timing issues in the SQL Server. To avoid this unexpected behavior, we recommend that you get a separate Connection
object for each thread.
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, the jdbcKona/MSSQLServer4 driver 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.
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 Multithreaded Applications
EXECUTE Keyword with Stored Procedures
|
|
|
Copyright © 1999 BEA Systems, Inc. All rights reserved.
|