The JDBC advanced data types were introduced in the JDBC 2.0 core API. The Microsoft SQL Server 2005 JDBC Driver uses the JDBC advanced data types to convert the SQL Server data types to a format that can be understood by the Java programming language.

The following table lists the default mappings between the advanced SQL Server, JDBC, and Java programming language data types.

SQL Server Types JDBC Types (java.sql.Types) Java Language Types

varbinary(max)

image

LONGVARBINARY

byte[] (default), Blob, CharacterStream, BinaryStream, String

text

ntext

varchar(max)

nvarchar(max)

LONGVARCHAR

String (default), Clob, CharacterStream, BinaryStream

xml

LONGVARCHAR

String (default), CharacterStream, Clob, byte[], BinaryStream, Blob

udt

VARBINARY (max size of 8000 bytes)

String (default), byte[], BinaryStream, Object

The following sections provide examples of how you can use the JDBC driver and the advanced data types.

BLOB and CLOB Data Types

The JDBC driver implements all the JDBC 3.0 methods of the java.sql.Blob and java.sql.Clob interfaces. By using these methods, you can retrieve and update the columns of type text, ntext, xml, and image.

Note: CLOB values can be used with SQL Server 2005 large-value data types. Specifically, CLOB types can be used with the varchar(max) and nvarchar(max) data types, and BLOB types can be used with varbinary(max) and xml data types.

Large Value Data Types

In earlier versions of SQL Server, working with large-value data types required special handling. Large-value data types are those that exceed the maximum row size of 8 KB. SQL Server 2005 introduces a max specifier for varchar, nvarchar, and varbinary data types to allow storage of values as large as 2^31 bytes. Table columns and Transact-SQL variables can specify varchar(max), nvarchar(max), or varbinary(max) data types.

The primary scenarios for working with large-value types involve retrieving them from a database, or adding them to a database. The following sections describe different approaches to accomplish these tasks.

Retrieving Large-Value Types from a Database

When you retrieve a non-binary large-value data type—such as the varchar(max) data type—from a database, one approach is to read that data as a character stream. In the following example, the executeQuery method of the SQLServerStatement class is used to retrieve data from the database and return it as a result set. Then the getCharacterStream method of the SQLServerResultSet class is used to read the large-value data from the result set.

ResultSet rs = stmt.executeQuery("SELECT TOP 1 * FROM Test1");
rs.next();
Reader reader = rs.getCharacterStream(2);
Note: This same approach can also be used for the text, ntext, and nvarchar(max) data types.

When you retrieve a binary large-value data type—such as the varbinary(max) data type—from a database, there are several approaches that you can take. The most efficient approach is to read the data as a binary stream, as in the following:

ResultSet rs = stmt.executeQuery("SELECT photo FROM mypics");
Rs.next();
InputStream is = rs.getBinaryStream(2);

You can also use the getBytes method to read the data as a byte array, as in the following:

ResultSet rs = stmt.executeQuery("SELECT photo FROM mypics");
Rs.next();
byte [] b = rs.getBytes(2);
Note: You can also read the data as a BLOB. However, this is less efficient than the two methods shown previously.

Adding Large-Value Types to a Database

Uploading large data with the JDBC driver works well for the memory-sized cases, and in the larger-than-memory cases, streaming is the primary option. However, the most efficient way to upload large data is through the stream interfaces.

Using a string or bytes is also an option, as in the following:

PreparedStatement pstmt = con.prepareStatement("INSERT INTO test1 (c1_id, c2_vcmax) VALUES (?, ?)");
pstmt.setInt(1, 1);
pstmt.setString(2, htmlStr);
pstmt.executeUpdate();
Note: This approach can also be used for values that are stored in text, ntext, and nvarchar(max) columns.

If you have an image library on the server and must upload entire binary image files to a varbinary(max) column, the most efficient method with the JDBC driver is to use streams directly, as in the following:

Statement stmt = con.createStatement();
PreparedStatement pstmt = con.prepareStatement("INSERT INTO test1 
VALUES( ?, ? )");
FileReader in = new FileReader(new File("CLOBFile20mb.txt");
long len = fileIn.length();
int id = 1;
pstmt.setInt(1,id);
pstmt.setCharacterStream(2, in, (int)len);
pstmt.executeUpdate();
in.close();
Note: Using either the CLOB or BLOB method is not an efficient way to upload large data.

Modifying Large-Value Types in a Database

In most cases, the recommended method for updating or modifying large values on the database is to pass parameters through the SQLServerPreparedStatement and SQLServerCallableStatement classes by using Transact-SQL commands like UPDATE, WRITE, and SUBSTRING.

If you have to replace the instance of a word in a large text file, such as an archived HTML file, you can use a Clob object, as in the following:

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM test1");
rs.next();
Clob clob = rs.getClob(2);
long pos = clob.position("dog", 1);
clob.setString(1, "cat", len, 3);
rs.updateClob(2, clob);
rs.updateRow();

Additionally, you could do all the work on the server and just pass parameters to a prepared UPDATE statement.

For more information about large-value types, see "Using Large-Value Types" in SQL Server Books Online.

XML Data Type

SQL Server 2005 provides an xml data type that lets you store XML documents and fragments in a SQL Server database. The xml data type is a built-in data type in SQL Server, and is in some ways similar to other built-in types, such as int and varchar. As with other built-in types, you can use the xml data type as a column type when you create a table; as a variable type, a parameter type, or a function-return type; or in Transact-SQL CAST and CONVERT functions.

In the JDBC driver, the xml data type can be mapped as a string, byte array, stream, CLOB, or BLOB object. String is the default representation. The implementation of the xml data type in the JDBC driver provides support for the following:

  • Access to the XML as a standard Java UTF-16 string for most common programming scenarios

  • Input of UTF-8 and other 8-bit encoded XML

  • Access to the XML as a byte array with a leading BOM when encoded in UTF-16 for interchange with other XML processors and disk files

SQL Server requires a leading BOM for UTF-16-encoded XML. The application must provide this when XML parameter values are supplied as byte arrays. SQL Server always outputs XML values as UTF-16 strings with no BOM or embedded encoding declaration. When XML values are retrieved as byte[], BinaryStream or Blob, a UTF-16 BOM is pre-pended to the value.

Note: The JDBC driver does not support reading stored procedure output parameters of the xml data type. However, it does support using the xml data type as an input parameter. If you have to use the xml data type as an output parameter, you must first convert the xml data type to a VARCHAR or LONGVARCHAR in the stored procedure before it can be read.

For more information about the xml data type, see "xml Data Type" in SQL Server Books Online.

User-Defined Data Type

The introduction of user-defined types (UDTs) in SQL Server 2005 extends the SQL type system by letting you store objects and custom data structures in a SQL Server database. UDTs can contain multiple data types and can have behaviors, differentiating them from the traditional alias data types that consist of a single SQL Server system data type. UDTs are defined by using any of the languages supported by the Microsoft .NET common language runtime (CLR) that produce verifiable code. This includes Microsoft Visual C# and Visual Basic .NET. The data is exposed as fields and properties of a .NET Framework-based class or structure, and behaviors are defined by methods of the class or structure.

In SQL Server 2005, a UDT can be used as the column definition of a table, as a variable in a Transact-SQL batch, or as an argument of a Transact-SQL function or stored procedure.

Note: The JDBC driver does not support getter or setter methods for the AsciiStream and CharacterStream methods on UDT columns. Additionally, like the xml data type, the JDBC driver does not support reading stored procedure output parameters of the udt data type. However, it does support using the udt data type as an input parameter. If you have to use the udt data type as an output parameter, you must first convert the udt data type to a varbinary(max) data type in the stored procedure before it can be read.

For more information about user-defined data types, see "Using and Modifying Instances of User-defined Types" in SQL Server Books Online.

See Also