|
|
|
Using jdbcKona/Sybase and jdbcKona/MSSQLServer
I. IntroductionWhat's in this document
Covered in this document are general guidelines for using the WebLogic
jdbcKona
If you are using WebLogic JDBC, WebLogic's multitier
Mapping of types between SQL Server and JDBCMappings from Java to JDBC and JDBC to Java are available in Section 8 of the JDBC specification at JavaSoft.
The following table illustrates mappings from JDBC types to Sybase DB-LIB types.
There are many similarities between Sybase SQL Server and Microsoft SQL Server. When the term SQL Server is used here, it refers to both. Notes specific to Sybase or Microsoft SQL Server are marked with the full name.
In general, connecting happens in two steps:
Note that each step describes the JDBC driver, but in a different format. The full package name is period-separated, and the URL is colon-separated. The URL must include at least weblogic:jdbc:sybase or weblogic:jdbc:mssqlserver, and may include other information, including server name and database name. There are variations on this basic pattern which are described here for both Sybase and MS SQL Server. For a full code example, check the beginning sections in the Overview and implementation guide. Method 1The simplest way to connect is with the URL of the JDBC driver. You can append information that describes the server name and the database name, as shown in this Sybase example: Class.forName("weblogic.jdbc.dblib.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:sybase:mach2:mydb",
"sa",
"");
where mach2 is the server name
and mydb is the name of the
database; and where sa is a
valid DBMS username.
Method 2You can also pass a java.util.Properties object with parameters for connection as an argument to the DriverManager.getConnection() method. In this example we illustrate how to connect to a Sybase database ("mydb") on the server host "mach2", with the DBMS username "sa": Properties props = new Properties();
props.put("user", "sa");
props.put("password", "");
props.put("server", "mach2");
props.put("db", "mydb");
Class.forName("weblogic.jdbc.dblib.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:sybase", props);
Here is another example that shows an additional, optional property that you may choose to set, for th application name for your Sybase or MS SQL Server JDBC connections. The DBMS associates these with the JDBC Connection. When set, this name will be put into sysprocesses under the column "program_name" and will show up in sp_who. The driver will automatically set the hostname. Properties props = new Properties();
props.put("user", "sa");
props.put("password", "");
props.put("server", "mach2");
props.put("db", "mydb");
props.put("appname", "MyApplication");
Class.forName("weblogic.jdbc.dblib.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:sybase", props);
Here is a similar example that shows how to connect to an Microsoft SQL Server database. Note that the only difference is the URL for the driver; for Microsoft SQLServer, it is "jdbc:weblogic:mssqlserver". Properties props = new Properties();
props.put("user", "sa");
props.put("password", "");
props.put("server", "mach2");
props.put("db", "mydb");
Class.forName("weblogic.jdbc.dblib.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:mssqlserver", props);
If you do not supply a server name ("mach2" in the example above), the system will look for an environment variable (DSQUERY in the case of Sybase, ORACLE_SID in the case of Oracle). You may also choose to add the server name to the URL, with this format: "jdbc:weblogic:sybase:mach2" You can also set all of the properties in a single URL, for use with products like PowerSoft's PowerJ. For details, see Using URLs with WebLogic products. Method 3If you prefer, you can load the JDBC driver from the command line with the command:
$ java -Djdbc.drivers=weblogic.jdbc.dblib.Driver classname where classname is the name of the application you want to run; and then use a Properties object to set parameters necessary for connecting to the DBMS. In this case, you will not need to call the Class.forName().newInstance() method, as shown here in this jdbcKona/MSSQLServer example: Properties props = new Properties();
props.put("user", "sa");
props.put("password", "");
props.put("server", "mach2");
props.put("db", "mydb");
Connection conn =
DriverManager.getConnection("jdbc:weblogic:mssqlserver", props);
Closing JDBC objectsIt is important to routinely and conscientiously close JDBC objects in your code in a finally {} code block. Closing objects releases resources on the remote DBMS and within your application. jdbcKona objects that you should close() after final use:
If you are using WebLogic JDBC in a multitier environment with a two-tier jdbcKona driver, you will set connection properties in a slightly different way. See the Developers Guide Using WebLogic JDBC for more details.
Properties props = new Properties();
props.put("user", "sa");
props.put("password", "");
props.put("server", "mach2");
props.put("db", "mydb");
props.put("weblogic.codeset", "Cp1254");
Class.forName("weblogic.jdbc.dblib.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:sybase", props);
Although the valid codeset names are JVM-dependent, these are described as part of the JavaSoft Internationalization specification. Note that codeset support depends upon the codesets available on your DBMS.
Executing queries: JDBC allows the Statement.executeUpdate() method to execute queries that return no results and the Statement.executeQuery() method to execute queries that return only a single ResultSet. Although this works with many SQL Server queries, a SQL Server query may return multiple ResultSets. Good SQL Server programming practice suggests that, when you are not sure about how the results will be returned, you should call the Statement.execute() method, and then call the following methods inside a loop until there are no more results:
The descriptions for these methods in the version 1.0 JavaSoft JDBC API specification or earlier were incomplete and ambiguous. There are new corrected descriptions in the JavaSoft JDBC specification, which WebLogic products implement, and which are reproduced here for clarity. /**
* getResultSet returns the current result as a ResultSet. It
* should only be called once per result.
*
* @return the current result as a ResultSet; null if the result
* is an update count or there are no more results
* @see #execute
*/
ResultSet getResultSet() throws SQLException;
/**
* getUpdateCount returns the current result as an update count;
* if the result is a ResultSet or there are no more results -1
* is returned. It should only be called once per result.
*
* The only way to tell for sure that the result is an update
* count is to first test to see if it is a ResultSet. If it is
* not a ResultSet it is either an update count or there are no
* more results.
*
* @return the current result as an update count; -1 if it is a
* ResultSet or there are no more results
* @see #execute
*/
int getUpdateCount() throws SQLException;
/**
* getMoreResults moves to a Statement's next result.
* It returns true if this result is a ResultSet.
* getMoreResults also implicitly closes any current
* ResultSet obtained with getResultSet.
*
* There are no more results when (!getMoreResults() &&
* (getUpdateCount() == -1)
*
* @return true if the next result is a ResultSet; false if it is
* an update count or there are no more results
* @see #execute
*/
boolean getMoreResults() throws SQLException;
Canceling queries: In DB-Library, you must read all of the results from your current query or call dbcancel or decanquery; otherwise, your connection can get out of sync and you will be unable to issue new queries. If you need to stop processing a query before all rows and ResultSets have been read, we require that you call either the ResultSet.close() or the Statement.close() method. Calling ResultSet.close() closes only your current result set; you can still retrieve other result sets if your query returns more than one.
Some application programs may require multiple Statements to execute simultaneously. This is needed if the application wishes to do its own JOIN outside of SQL, with a loop retrieving rows from an SQL select one row at a time and then performing another SQL statement for each iteration of the loop. The only way to execute two statements simultaneously is to open two JDBC Connections and create a JDBC Statement on each Connection. Each JDBC Connection object maintains a separate connection to the SQL Server, so there are no restrictions on executing different statements simultaneously over different Connection objects. Other applications may need to interleave two statements on the same connection, executing one and then the other. This is necessary to execute a JDBC PreparedStatement or CallableStatement repeatedly, or to avoid the small overhead of opening and closing Statements repeatedly. Rules for using multiple statements on the same connectionTo execute multiple statements on the same connection, the basic rule is this: After you execute a statement, you must either read all the results from that statement or cancel it or close it. If you try to perform any other executes on that Statement, or another Statement on that connection, you will get a DB-LIB error, which is trapped and reported as the following JDBC SQLException: You have tried to execute a Statement or call a DatabaseMetaData method without reading all of the results of a previous Statement execute or DatabaseMetaData method. A query may return multiple results and/or update counts. This can cause errors if Statements and their results are handled improperly. You should call Statement.close() after calling any of the execute methods, unless you exhaust all of the results of the executed query. Handling a Statement (and its subclasses CallableStatement and PreparedStatement) falls into three basic categories, based on the method used to execute the query. Each case has a set of precautions that will prevent problems.
For more advanced usage, follow these rules:
The JDBC extended SQL keyword CALL is used instead of the SQL Server keyword EXECUTE to call stored procedures from SQL. Stored procedure input parameters are mapped to JDBC IN parameters, using the java.sql.CallableStatement.setXXX() methods, such as setInt(), and JDBC PreparedStatement '?' syntax. CallableStatement is a subclass of PreparedStatement, so all of the PreparedStatement.setXXX() methods are inherited by CallableStatement. Stored procedure OUT parameters and return status are mapped to JDBC Out parameters, using the java.sql.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. Support for OpenServer named parameters WebLogic has an extension to JDBC that allows SQL Server users to apply names to parameters. The extension is the setParamName(int index, String name) method in weblogic.jdbc.dblib.CallableStatement. It is not necessary to use this extension to invoke an RPC unless the RPC is in an OpenServer application and the OpenServer application relies on named parameters. There are several guidelines for using this extension to JDBC:
Support for the SQL Server RPC facility In addition to stored procedures, WebLogic's implementation of JDBC supports the SQL Server RPC facility, where user-defined procedures on a Sybase OpenServer or Microsoft Open Data Service can be called as if they were local to the client. The JDBC extended SQL keyword CALLRPC, a WebLogic extension, is used to call OpenServer RPCs. CALLRPC can also be used instead of CALL to call SQL Server stored procedures, but CALLRPC is limited in the kinds of arguments it can handle. We recommend using CALL if you do not need to communicate with an Open Server or Open Data Service. Usage. CALL and CALLRPC use the same syntax and handle results and OUT parameters in exactly the same way. The code example at the end of this section works identically whether you use {? = CALL sp_getmessage(?, ?)} or {? = CALLRPC sp_getmessage(?, ?)}. Limitations for CALLRPC. CALLRPC does not work with parameters of type NUMERIC in some versions of Sybase System 10, but it does work with parameters of this type with Microsoft SQL Server 6.5 and 7.0. We will determine soon whether this works in Sybase System 11. The issue is a problem with the Sybase DB-LIB call "DBRPCPARAM()", which does not recognize the SYBNUMERIC type code. We will be working with Sybase to determine in which versions this problem has been fixed. Processing results from a stored procedure or OpenServer RPC. You must process all ResultSets returned by your stored procedure or RPC using the Statement.execute() and Statement.getResultSet() methods before any of the OUT parameters and return status are available. Here is an example that uses a Sybase system stored procedure named 'sp_getmessages', which takes a message number as an input parameter and returns the message text as an output parameter: #SQL Server Syntax (if you were programming in DB-LIBRARY)
DECLARE @retval int
DECLARE @msgno int
DECLARE @message varchar(100)
SELECT @msgno = 18000 # a typical SQL Server message number
EXECUTE @retval = sp_getmessage(@msgno, @message OUTPUT)
#Equivalent JDBC Syntax:
{ ? = CALL sp_getmessage(?, ?) }
#Actual JDBC code to get the results:
{
// This code assumes conn is an open JDBC Connection
CallableStatement stmt =
conn.prepareCall("{ ? = call sp_getmessage(?, ?) }" );
// Set up the 3 parameters to the callable statement:
// Parameter 1, which is output only, is the
// stored procedure return value
stmt.registerOutParameter(1, java.sql.Types.INTEGER);
// Parameter 2, which is input only, is the msgno
// argument to sp_getmessage
stmt.setInt(2, 18000); // msgno 18000
// Parameter 3, which is output only, is the
// message text returned for that msgno
stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
boolean hasResultSet = stmt.execute();
while (true) {
ResultSet rs = stmt.getResultSet();
int updateCount = stmt.getUpdateCount();
if (rs == null && updateCount == -1) // No more results
break;
// We have a ResultSet
if (rs != null) {
while (rs.next()) {
System.out.println("Get first col by id:" + rs.getString(1));
}
}
// We have an update count
else {
System.out.println("Update count = " + stmt.getUpdateCount());
}
stmt.getMoreResults();
}
int retstat = stmt.getInt(1);
String msg = stmt.getString(3);
System.out.println("sp_getmessage: status = " + retstat +
" msg = " + msg);
stmt.close();
}
Using WebLogic applications with Sybase OpenServer via RPCs WebLogic includes two files in the distribution that you can compile and use to demonstrate the communications between WebLogic and Sybase OpenServer applications via RPCs:
The rpcTest application invokes an RPC in the regWebProc OpenServer application. The RPC, rp_types() takes 3 input parameters -- a string, an int, and a float -- and returns raw data. When you run rpcTest successfully, the 3 parameters should be printed to stdout by regWebProc ("yesindeedy!", 123, and 128.5), and subsequently, 1 row of data (like select 1) should be printed to stdout by rpcTest as it receives a response from the OpenServer. If the test fails, the output file rpcTest.out will contain a Java stack trace.
SQL Server allows the execution of multiple SQL statements in the same "command batch." This is equivalent to typing multiple commands in ISQL before typing "go." You can use this facility to execute multiple statements in the same JDBC execute() method. Follow these rules:
Because these statements can return any combination of result sets and update counts, your result handling code must allow for this. (There is a similar issue in calling stored procedures, and the code for both cases looks almost identical.) Here is an example: Statement stmt = conn.createStatement();
String query = "select * from foo" + "\n"
+ "update bar set val =1 where id=1" + "\n"
+ "select * from bar";
boolean hasResultSet = stmt.execute(query);
while (true) {
ResultSet rs = stmt.getResultSet();
int updateCount = stmt.getUpdateCount();
// If there are no more results, break
if (rs == null && updateCount == -1)
break;
// Check to see if there is a ResultSet
if (rs != null) {
while (rs.next()) {
System.out.println("Get first col by id:" + rs.getString(1));
}
}
// Otherwise, there will be an update count
else {
System.out.println("Update count = " + stmt.getUpdateCount());
}
stmt.getMoreResults();
}
The default transaction mode for both JDBC and SQL Server is autocommit on. In this mode, each SQL statement is its own transaction, so there is never a need for a BEGIN TRANSACTION or an END TRANSACTION for a single statement. To execute a multistatement transaction, bracket the multiple statements with BEGIN TRANSACTION and END TRANSACTION. BEGIN TRANSACTION is not supported in all vendor versions of SQL, so it is impossible to write vendor-independent transaction logic in JDBC. This could be solved if JDBC had a Connection.begin() method. If you need this feature, please contact jdbc@wombat.eng.sun.com and ask that it be added to future revisions of the JDBC spec. JDBC also supports autocommit off mode, also known as ANSI-compliant mode. In this mode, the Connection always has an implicit transaction associated with it, and any call to the Connection.rollback() or Connection.commit() methods ends the current transaction and starts a new transaction. To toggle between modes, call Connection.setAutoCommit(boolean). This mode is implemented in Sybase SQL Server using the SET CHAINED ON command. There is no equivalent to autoCommit off in MS SQL Server or in Sybase SQL Server 4.9x that we have found. More on autocommit with Sybase. The setting for autocommit has significant implications for Sybase SQL Server, because autocommit affects how Sybase behaves in transactions, transaction counts, and transaction nesting. Sybase SQL Server behaves completely differently according to whether autocommit is set to on or off. WebLogic recommends that you use the default setting for autocommit (autocommit set to on) unless you have a full understanding of Sybase and of how changing autocommit will affect your application. If you do choose to set autocommit to off, Sybase recommends that you run stored procedures in the same mode as they were originally created. That is, if your stored proc was created with autocommit set to on, you should execute it with autocommit set to on, and if the stored proc was created with autocommit set to off, you should execute it with autocommit set to off. In the default mode (autocommit set to on), you can specify that a group of updates commit or rollback as a group in the following way: statement.executeUpdate("begin transaction");
// Do a series of updates
statement.executeUpdate("insert xyz values . . .");
statement.executeUpdate("update abc where . . .");
statement.executeUpdate("delete mnop where . . .");
// Decide whether to commit or rollback
// Commit or rollback
if (wantToCommit)
statement.executeUpdate("commit transaction");
else
statement.executeUpdate("rollback transaction");
You can also nest transactions, use named transactions, and establish savepoints with the same approach. Please check the SQL Server Reference Manual for more details. More on autocommit with MS SQL Server. MS SQL Server does not support setting autocommit to false, and WebLogic recommends always running with autocommit set to true. However, with WebLogic's jdbcKona/MSSQLServer, making a call to Connection.setAutoCommit(false) does supply functionality that simulates expected behavior, using the MS SQL Server set implicit_transactions command. If you have set autocommit to false, you must subsequently call Connection.commit() at some time before closing the Statement, if you want your executes to be saved. Ideally, commit() should be called after any series of calls to Statement.execute() that comprises a single logically consistent change to the data. Note that when you are within an uncommitted transaction, all database resources affected by calls to execute() are locked until commit() is called. For example, if you must debit one account and then credit another, those two actions should be done together and committed with a single call to commit(), but the commit should occur then, and not after any more logically related debit-credit pairs. Please note that if commit() is not called before the Statement is closed, all updates in the outstanding transaction on the Connection will be rolled back. The outstanding transaction includes all updates since the last commit(), rollback(), or since the Connection was made, whichever happened last. You should not mix transaction management calls between JDBC and SQL; for example, do not use Statement.execute("COMMIT TRANS") and Connection.commit() calls together. WebLogic strongly recommends that users of this added functionality rely exclusively on JDBC transaction management, by calling Connection.commit() and Connection.rollback() for transaction management.
When a SQLException is thrown as a result of a SQL Server error, set the SQLException error code to the SQL Server error number, so that an application program can determine the type of error.
Restrictions on Text and Image datatypesThe JDBC Longvarchar and Longvarbinary types are mapped to the SQL Server types Text and Image, the only SQL Server types that allow data longer than 255 bytes. With the restrictions described below, Text and data can be read and written through SQL queries or through a special API using textpointers. Image or Longvarbinary data must use the special API. If you are working with Longvarchar, you can use the same methods to select, insert, or update them in SQL queries as you use with any other datatypes. Here are the restrictions: There is a default 32K-per-field restriction on Text/Image data retrieved by an SQL select in the Sybase SQL Server, and a 4K restriction for MS SQL Server; a select will receive at most this much data per field. Larger values will be truncated silently by the DBMS. You can adjust the amount of Text/Image data returned by a select by calling the Statement.setMaxFieldSize() method with a different value (smaller or larger). Note that needlessly large settings could cause the JDBC drive to needlessly run out of memory. Also note that setMaxFieldSize() is an instance method, so it needs to be called for every Statement object that will retrieve Text/Image data.
If your Text/Image data fits within these restrictions, you can use
SQL queries as with any other datatype and ignore the following
section. Otherwise, if your Text data does not fit within these
restrictions, or if you are retrieving Image (non-Text) data, you will
use textpointers for retrieving, inserting, and updating, as explained
in "Handling larger Text/Image data."
Updates to Longvarchar or Longvarbinary columns using textpointers require methods that do not exist in the current JDBC 1.0 specification. This feature is valuable enough that we have supplied a public method for it that is not part of the JDBC API. If you need this feature, please contact jdbc@wombat.eng.sun.com and ask that it be added to future revisions of the JDBC spec. SQL Server does not handle columns of type Text/Image the same way it handles other datatypes. The differences, visible both in the SQL statements used to access Text/Image data and in the additional non-SQL programming necessary to access Text/Image, are based on the SQL Server concept known as the text pointer. A SQL Server table with a column of type Text or Image stores a 16-byte binary value (a text pointer) that describes where the Text/Image data is actually stored on the SQL Server logical device. Consequently, operations involving Text/Image data require two steps: you must
Retrieving Text/Image data. With the jdbcKona SQL Server drivers, you select Text/Image data in the same way that you get results of other types. The two-step process of retrieving the text pointer and using it to read the data is hidden. There are, however, two restrictions on working with the results:
Here is an example for retrieving Text/Image data. It assumes a table with the following schema: CREATE TABLE textTable (id INT NOT NULL, textdata TEXT NULL) Connection conn; //Open SQL Server connection
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("select textdata from textTable where id > 10");
while (rs.next()) {
InputStream x = rs.getAsciiStream(1);
if (rs.wasNull()) {
System.out.println("
Inserting or Updating Text/Image data. SQL Server does not allow you to insert Text/Image data as part of a SQL INSERT or UPDATE. These SQL operations with Text/Image data require a multistep operation with multiple SQL statements and a special DBLIB C routine called dbwritetext. We provide a special JDBC extension around dbwritetext: weblogic.jdbc.dblib.Statement.executeWriteText(). There are four method signatures for this method; you may use either a byte array or an InputStream as a source for the Text/Image type, and you may also elect to use a Sybase timestamp for versioning. Text/Image updates will usually require a second connection to the SQL Server DBMS; one connection selects the text pointer, and the other carries out the update referenced by the text pointer. If you are updating only a single text value in a single row, this may not be necessary; but if you are scanning through multiple rows and modifying Text/Image type values in each, you will need to use a second JDBC Connection. Here is a step-by-step example that outlines the sequence of operations for updating Text/Image types. Step 1. Open two connections to the SQL Server The example, which uses the table with the schema shown above, assumes that you have already opened two java.sql.Connection objects, conn and conn2. In this example, we also initialize several variables. Connection conn; Connection conn2; byte[] textptr; // Text pointer byte[] myData; // Text/image data to be sent int myDataOffset = 0; // Offset of Text/Image data int myDataLen = myData.length; // Length of Text/Image dataIf you are updating a Text/Image value that already has data in it, skip to Step 4. Step 2. Insert a row for the data Using the first Connection conn, insert the row and specify a null value in the Text/Image field. At this point there is no valid text pointer in the field. Statement stmt = conn.createStatement();
stmt.executeUpdate("insert textTable values(99, null)");
stmt.close();
Note that if you are updating a Text/Image value that already has
data in it, you should omit Step 2 and Step 3.
Step 3. Update the database Using the first Connection conn, update the text pointer to null again to force a valid text pointer value that points to empty data. After this, there will be a valid text pointer in the field. stmt = conn.createStatement();
stmt.executeUpdate("update textTable set textdata " +
"= null where id = 99");
stmt.close();
Note that if you are updating a Text/Image value that already has data in it, you should omit Step 2 and Step 3. Step 4. Select the text pointer to be updated as a BINARY value Using the first Connection conn, select the field to get the actual text pointer value as a BINARY value. stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("select textptr(textdata) " +
"from textTable where id = 99");
if (!rs.next())
throw new SQLException("Select from textTable failed");
textptr = rs.getBytes(1);
Step 5. Write the data Using the second Connection conn2, create a weblogic.jdbc.dblib.Statement object. Since you do not import this package or any of its classes -- you should import java.sql.* instead -- you must use the fully-qualified class name when you create the Statement object. Then use the retrieved text pointer value with one of the weblogic.jdbc.dblib.Statement.executeWriteText() methods (jdbcKona extensions to to JDBC) to actually write the Text/Image data. There are two executeWriteText() methods; one for writing an array of bytes (byte[]), the other for using an InputStream as the source of data. In the example, we use a byte array as the source of data. Note that you must explicitly cast the Statement. weblogic.jdbc.dblib.Statement stmt2 =
(weblogic.jdbc.dblib.Statement) conn2.createStatement();
stmt2.executeWriteText("textTable",// Table name for update
"textdata", // Text/Image column name for update
textptr, // Text pointer value
true, // True if write operation should be logged
mydata, // Text/Image data to be written
myoffset, // Offset into mydata of 1st byte
myDataLen, // Length of data to be written
Note that logging a write of Text/Image data is an expensive operation, so you may want to set logging to false. In that case, you must configure the SQL Server DBMS to allow nonlogged text updates. Here is an example that sets the unlogged updates option: 1> use master 2> go 1> sp_dboption mydb, "sel", "true" 2> go Database option 'select into/bulkcopy' turned ON for database 'mydb'. Run the CHECKPOINT command in the database that was changed. (return status = 0) 1> use mydb 2> go 1> checkpoint 2> go Note also that Step 5 can be done in SQL using a SQL Server extension to standard SQL called WRITETEXT, but that can only be done with data embedded in the statement, and with a maximum of 120 Kbytes or less. Here is how Step 5 would be written using SQL WRITETEXT: WRITETEXT textTable.textdata @textptr with log "My data" Here is another example of using the executeWriteText() method that incorporates the use of a SQL Server timestamp to compare the version to be updated and the version stored in the column. WebLogic provides an extension to JDBC, weblogic.jdbc.dblib.Statement.getTextTimestamp() to get access to the timestamp data. This code example shows how getTextTimestamp() is used. Note that the Statement must be cast as a weblogic.jdbc.dblib.Statement to take advantage of WebLogic's extensions to JDBC. The following code snippet shows the usage: // Select the data using a Statement "stmt"
ResultSet rs =
stmt.executeQuery("select doc from db..texttable where doc_no = 99");
if (!rs.next())
throw new SQLException("Select from texttable failed");
weblogic.jdbc.dblib.Statement wstmt =
(weblogic.jdbc.dblib.Statement) stmt;
byte[] timestamp = wstmt.getTextTimestamp();
while(rs.next());
FileInputStream file = new FileInputStream(mytextfile);
stmt.executeWriteText("db..texttable",
"doc",
textptr,
true,
file,
(int)mytextfile.length(),
timestamp);
The full example for this illustration is included in the distribution
in both the examples/jdbc/sybase and examples/jdbc/mssqlserver directories as simpleTextWithTimestamp.
With release version 2.1, all of DatabaseMetaData is implemented in jdbcKona/Sybase and jdbcKona/MSSQLServer. There are some variations that are specific to SQL Server that are detailed here:
WebLogic release 2.3 added new DEFERABILITY column to the ResultSet for the following methods, to conform to JDBC 1.1:
The JDBC Connection.setTransactionIsolation(level) method allows the transaction isolation level to be set for all subsequent transactions. Note that if you are already in a transaction, a call to Connection.setTransactionIsolation() will fail. For Microsoft SQL Server, all transaction isolation levels except TRANSACTION_NONE are supported. The mapping between JDBC constants (found in the Connection class) and Sybase isolation levels is as follows:
Some of the JDBC API is not currently implemented, including: Support for the SQL Server "compute" functionality. SQL Server supports "compute" queries that interleave regular result rows and vector aggregate (computed) rows. An example is: select db_name(id), id, size from sysusages order by id compute sum(size) by id Although this feature could be supported in jdbcKona, there is no way to retrieve the results of such a query within the parameters of the JDBC API, and no easy way to extend the JDBC model. If you need this feature, please contact jdbc@wombat.eng.sun.com and ask that it be added to future revisions of the JDBC spec. Support for Sybase SQL Server cursors. Cursors are not currently supported.
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|
