|
|
|
Using jdbcKona/MSSQLServer4
I. IntroductionWhat's in this documentThis document provides information on how to set up and use WebLogic's jdbcKona/MSSQLServer4 JDBC driver. WebLogic acquired this product, formerly known as FastForward, from Connect Software in April 1998.
jdbcKona/MSSQLServer4 is a Type 4, pure-Java, two-tier 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 If you are using jdbcKona/MSSQLServer4 with WebLogic JDBC, you should also refer to the Developers Guide Using WebLogic JDBC for more information.
This JDBC driver is only supported with versions
of the JDK This JDBC driver works with Microsoft SQL Server versions 6.5 and 7.0. It is shipped in two versions -- one that supports both versions 6.5 and 7.0, but does not support any version 7.0 specific features -- and one that only supports version 7.0 and the new SQL Server 7.0 nchar, nvarchar, and ntext datatypes. Both are available from the same download page. The API reference for JDBC, of which this driver is a fully compliant implementation, is available online in several formats at JavaSoft.
II. Using jdbcKona/MSSQLServer4Connecting to SQL Server with jdbcKona/MSSQLServer4To connect to a SQL Server database in a Java program, you:
An efficient way to load the JDBC driver is to call Class.forName().newInstance() with the name of the driver class, as in this example: Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
After loading the JDBC driver, request a JDBC connection by calling the DriverManager.getConnection() method. You call 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. This document describes three methods. For a full code example, check the beginning sections in the Implementation guide in the overview, Using the jdbcKona family of JDBC drivers. Method 1The 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: Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
Connection conn =
DriverManager.getConnection(
"jdbc:weblogic:mssqlserver4:database@host:port",
"sa", // database user name
""); // password for database user
where 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.
Method 2You can set connection information in a Properties object and pass this 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", "");
// 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"
Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:mssqlserver4", props);
Method 3You 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 this example: Class.forName("weblogic.jdbc.mssqlserver4.Driver).newInstance();
DriverManager.getConnection(
"jdbc:weblogic:mssqlserver4:database@myhost:myport?user=sa&password=");
You can use Driver.getPropertyInfo()
to find out more about URL options at run-time.
Codeset supportAs a Java application, jdbcKona/MSSQLServer4 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 DriverManager.getConnection(), 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";
Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
Connection conn =
DriverManager.getConnection(connectUrl, props);
Using jdbcKona/MSSQLServer4 in appletsSince jdbcKona/MSSQLServer4 is 100% Java, you can use it in Java applets. The examples/jdbc/mssqlserver4 directory includes Java applets to help you set up jdbcKona/MSSQLServer4 for use in applets. See the examples documentation for information about setting up and running the examples. If you are planning to use jdbcKona/MSSQLServer4 in an applet in a two-tier configuration, you will need to install the driver on your webserver host. In addition, the webserver 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 webserver must be on the same IP host.
One way to overcome applet security restrictions is to use jdbcKona/MSSQLServer4 in a three-tier configuration with a server such as the WebLogic Server. In this configuration, applets connect to WebLogic and WebLogic connects to SQL Server via jdbcKona/MSSQLServer. For more information on using WebLogic with applets, read Using WebLogic for applet programming. The SocketApplet applet 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:
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:
To run ConnectionApplet:
The jdbcKona/MSSQLServer4 distribution includes several additional examples. Refer to code examples for information about these examples.
Using jdbcKona/MSSQLServer4 in Java development environmentsjdbcKona/MSSQLServer4 has been used successfully in the following development environments: Java SDK 1.1.x for Sun and Windows NT, Symantec Cafe, Symantec Visual Cafe for Windows and Macintosh, Microsoft J++, Borland JBuilder, and Metrowerks Codewarrior.III. JDBC extensions and limitationsSupport for JDBC Extended SQLJavaSoft's JDBC specification includes a feature called SQL Extensions, or SQL Escape Syntax. jdbcKona/MSSQLServer4 supports Extended SQL. For information about this feature, see Using the jdbcKona Family of JDBC drivers.cursorName() method not supportedThe cursorName() method is not supported, since its definition does not apply to Microsoft SQL Server. java.sql.TimeStamp limitationsThe 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(), jdbcKona/MSSQLServer4 uses its own date class to overcome the limitation.Querying metadataYou 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 modeCall Connection.setAutoCommit() 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 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 supportedThe WebLogic Type 2 jdbcKona 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-Libary 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. Sharing a Connection object in multi-threaded applications
jdbcKona/MSSQLServer4 allows you to write multithreaded Execute keyword with stored proceduresA 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, jdbcKona/MSSQLServer4 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.
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|
