Using setNull(index,null) method on the preparedStatement produces java.sql.SQLException "[Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is not supported by this driver"

Technote (FAQ)
Problem
Using the WebSphere® Application Server data source with the Connect JDBC™ driver (DataDirect Connect JDBC, WebSphere embedded Connect JDBC or Microsoft® SQL Server 2000 Driver) to connect to SQL Server 2000, results in the following exception:

java.sql.SQLException "[Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is not supported by this driver"
Cause
This is not a problem with the WebSphere Application Server Connection Manager. The java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is not supported by this driver is returned from JDBC Driver (DataDirect Connect JDBC, WebSphere embedded Connect JDBC or Microsoft SQL Server 2000 Driver) when an application uses something that is not supported by the JDBC Driver.

The JDBC specification, Chapter 17 Customized Type Mapping, section 17.7 on "NULL Data" states:
    "An application uses the existing getObject and setObject mechanism to retrieve
    and store SQLData values. We note that when the second parameter, x, of method
    PreparedStatement.setObject has the value null, the driver executes the SQL
    statement as if the SQL literal NULL had appeared in its place.
    void setObject (int i, Object x) throws SQLException;
    When parameter x is null, there is no enforcement that the corresponding
    argument expression is of a Java type that could successfully be passed to that SQL
    statement if its value were not null. The Java programming language null carries
    no type information. For example, a null Java programming language variable of
    class AntiMatter could be passed as an argument to an SQL statement that
    requires a value of SQL type MATTER, and no error would result, even though the
    relevant type map object did not permit the translation of MATTER to AntiMatter."
Based on the JDBC specification, it is not recommended to use setObject(int, null).
Solution
The following 3 setObject methods are available for use with the PreparedStatement object:

  • setObject(int, Object)

  • setObject(int, Object, int)

  • setObject(int, Object, int, int)

Using the second method setObject(int, Object, int) where the third parameter is the targetJDBCType works fine. For example:
    setObject(1, null, java.sql.Types.VARCHAR)











Document Information

Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server, DB Connections/Connection Pooling
Operating system(s): AIX, HPUX, Linux, Multi-Platform, Solaris, Windows
Software version: 3.5, 4.0, 5.0, 5.1, 6.0
Software edition: Advanced, Base, Edition Independent, Network Deployment, Single Server, Standard
Reference #: 1195926
IBM Group: Software Group
Modified date: 2005-01-17