Micorosoft SQL Server JDBC 3.0 Released!!!



Dear SQL Server developers and users:


 


On behalf of Microsoft SQL Server JDBC team I am very excited to announce our latest JDBC driver Microsoft SQL Server JDBC 3.0 release.


 


This version of the JDBC driver provides support for features introduced in SQL Server 2008, which includes date and time data types, sparse columns, MERGE statements and large user-defined types (UDTs). The support for the new date and time data types includes new setter, getter, and updater methods for SQL Server time, date, datetime2 and datetimeoffset data types. Support for large UDTs includes handling CLR UDTs that are larger than 8000 bytes as binary data. Also, this release adds interfaces for unwrap and isWrapper in the Wrapper interface. In addition, this release enhances metadata support by adding sparse column metadata and new date and time metadata.


 


Thank you for providing great feedback on our CTP. We really appreciate your continued support on our driver. Feel free to download a copy and check it out!


 


Thank you,


Amina Saify – JDBC

Comments (21)

  1. Füge says:

    Environment

    OS: Vista HP SP2 x64

    SQLServer: 2008 R2 x64

    Java: 6u20 x64

    Driver: sqljdbc4-3.0.jar

    Running a Java app for retrieving some metadata, the driver still returns java.sql.Types.NVARCHAR for ‘date’, ‘time’ and ‘datetime2’ database types, rather than java.sql.Types.DATE/TIME/TIMESTAMP respectively, which is what I expected.

    What is the reason that the Java app (or the JDBC driver itself?) is treated as a "down-level client"?

  2. Mugunthan Mugundan - MSFT says:

    Do you have a concise repro you can share with us. The 2.0 driver returned nvarchar but the 3.0 should not. Are you sure that you are picking up the right jar? I would print the version of the jar in my application via getDriverVersion()

  3. Füge says:

    Yes, I am.

    Output (excerpt):

    —————————————-

    General information

    —————————————-

    Database product name   : Microsoft SQL Server

    Database product version: 10.50.1600

    Driver name             : Microsoft SQL Server JDBC Driver 3.0

    Driver version          : 3.0.1301.101

    —————————————-

    Supported data types

    —————————————-

    —————————————-

    TYPE_NAME: Database type name = date

    DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

    PRECISION: Maximum precision  = 10

    —————————————-

    TYPE_NAME: Database type name = time

    DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

    PRECISION: Maximum precision  = 16

    —————————————-

    TYPE_NAME: Database type name = datetime2

    DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

    PRECISION: Maximum precision  = 27

    —————————————-

    TYPE_NAME: Database type name = datetimeoffset

    DATA_TYPE: SQL/JDBC data type = -9 (corresponds to java.sql.Types.NVARCHAR)

    PRECISION: Maximum precision  = 34

    —————————————-

    —————————————-

    TYPE_NAME: Database type name = datetime

    DATA_TYPE: SQL/JDBC data type = 93 (corresponds to java.sql.Types.TIMESTAMP)

    PRECISION: Maximum precision  = 23

    —————————————-

    TYPE_NAME: Database type name = smalldatetime

    DATA_TYPE: SQL/JDBC data type = 93 (corresponds to java.sql.Types.TIMESTAMP)

    PRECISION: Maximum precision  = 16

    —————————————-

  4. Füge says:

    Source code:

    ————

    package test.database;

    import static java.sql.Types.*;

    import java.sql.Connection;

    import java.sql.DatabaseMetaData;

    import java.sql.DriverManager;

    import java.sql.ResultSet;

    import java.sql.SQLException;

    import java.util.HashMap;

    import java.util.Map;

    public final class SQLServerInfo

    {

     private static final Map<Integer,String>

       TYPES = new HashMap<Integer,String>(37);

     private static final String

       CONNECTION_URL = "jdbc:sqlserver://localhost:1433;databaseName=*;user=*;password=*",  // * to be set!

       NL = System.getProperty("line.separator"),

       SEPARATOR = "————————————————————" +

                   "————————————————————";

     static {

       TYPES.put(ARRAY,         "ARRAY");

       TYPES.put(BIGINT,        "BIGINT");

       TYPES.put(BINARY,        "BINARY");

       TYPES.put(BIT,           "BIT");

       TYPES.put(BLOB,          "BLOB");

       TYPES.put(BOOLEAN,       "BOOLEAN");

       TYPES.put(CHAR,          "CHAR");

       TYPES.put(CLOB,          "CLOB");

       TYPES.put(DATALINK,      "DATALINK");

       TYPES.put(DATE,          "DATE");

       TYPES.put(DECIMAL,       "DECIMAL");

       TYPES.put(DISTINCT,      "DISTINCT");

       TYPES.put(DOUBLE,        "DOUBLE");

       TYPES.put(FLOAT,         "FLOAT");

       TYPES.put(INTEGER,       "INTEGER");

       TYPES.put(JAVA_OBJECT,   "JAVA_OBJECT");

       TYPES.put(LONGNVARCHAR,  "LONGNVARCHAR");

       TYPES.put(LONGVARBINARY, "LONGVARBINARY");

       TYPES.put(LONGVARCHAR,   "LONGVARCHAR");

       TYPES.put(NCHAR,         "NCHAR");

       TYPES.put(NCLOB,         "NCLOB");

       TYPES.put(NULL,          "NULL");

       TYPES.put(NUMERIC,       "NUMERIC");

       TYPES.put(NVARCHAR,      "NVARCHAR");

       TYPES.put(OTHER,         "OTHER");

       TYPES.put(REAL,          "REAL");

       TYPES.put(REF,           "REF");

       TYPES.put(ROWID,         "ROWID");

       TYPES.put(SMALLINT,      "SMALLINT");

       TYPES.put(SQLXML,        "SQLXML");

       TYPES.put(STRUCT,        "STRUCT");

       TYPES.put(TIME,          "TIME");

       TYPES.put(TIMESTAMP,     "TIMESTAMP");

       TYPES.put(TINYINT,       "TINYINT");

       TYPES.put(VARBINARY,     "VARBINARY");

       TYPES.put(VARCHAR,       "VARCHAR");

     }

     public static void main(final String[] args)

     {

       Connection connection;

       DatabaseMetaData md;

       try {

         connection = DriverManager.getConnection(CONNECTION_URL);

         md = connection.getMetaData();

         /* General information */

         System.out.println(SEPARATOR);

         System.out.println("General information");

         System.out.println(SEPARATOR);

         System.out.println(

           "Database product name   : " + md.getDatabaseProductName() + NL +

           "Database product version: " + md.getDatabaseProductVersion() + NL +

           "Driver name             : " + md.getDriverName() + NL +

           "Driver version          : " + md.getDriverVersion()

           );

         /* Supported types */

         System.out.println(SEPARATOR);

         System.out.println("Supported data types");

         System.out.println(SEPARATOR);

         ResultSet rs = md.getTypeInfo();

         while (rs.next())

         {

           int type = rs.getInt("DATA_TYPE");

           System.out.println("TYPE_NAME: Database type name = " + rs.getString("TYPE_NAME"));

           System.out.print("DATA_TYPE: SQL/JDBC data type = " + type);

           System.out.println(" (corresponds to java.sql.Types." + TYPES.get(type) + ")");

           System.out.println("PRECISION: Maximum precision  = " + rs.getLong("PRECISION"));

           System.out.println(SEPARATOR);

         }

       }

       catch (SQLException e) {

         System.err.println(e.getMessage());

         System.exit(-1);

       }

       System.exit(0);

     }

    }

  5. Mugunthan Mugundan- MSFT says:

    Thank you for the detailed repro. It looks like this is a shortcoming in the current release. You can use

    sp_datatype_info_100 directly and use this information. In the meantime, we will consider fixing this in a future release. If you need this immediately fixed, you can contact Microsoft customer services in your country.

  6. shanky says:

    m using windows 7 and sql server 2005 express edition. m new to java.Try to connect my java proram(written in notepad) .plz tel me how to connect my java to sql server 2005  edition using this jdbc driver.plz help as i m in middle of my engg. project.thanks

  7. Wes Clark (wclark@guidewire.com) says:

    We’d like to be able to associate a user with a connection during the time we have it out of the pool.  Oracle lets you run "DBMS_SESSION.SET_IDENTIFIER" with the user name.  Is this possible with the 3.0 MS JDBC driver?  I tried playing around with the Connection.setClientInfo() and .getClientInfo() without luck.

  8. wesclark says:

    I wasn’t logged in when I left the last comment, so if you replied to this one, I’ll be notified.

  9. Wilfred says:

    another error found for mapping of varchar(max), nvarchar(max), varbinary(max) incorrectly to java.sql.Types "VARCHAR", "VARCHAR"(JRE5), "VARBINARY" respectively.

    table created through:

    CREATE TABLE ALL_DATA_TYPES_COLUMNS

    (

    VARBINARY_MAX_COL VARBINARY(MAX),

    CHAR_COL CHAR(30),

    VARCHAR_MAX_COL VARCHAR(MAX),

    DATETIME2_COL DATETIME2,

    NCHAR_COL NCHAR(30),

    NVARCHAR_MAX_COL NVARCHAR(MAX),

    NUMERIC_P_S_COL NUMERIC(10,3),

    NUMERIC_P_COL NUMERIC(10),

    FLOAT_COL FLOAT(53),

    NVARCHAR_COL NVARCHAR(30),

    VARCHAR_COL VARCHAR(30)

    );

    java source:

    package test;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.PreparedStatement;

    import java.sql.ResultSet;

    import java.sql.ResultSetMetaData;

    import java.util.Hashtable;

    public class DataTypeTestMSSQL {

    /**

    * @param args

    */

    public static void main(String[] args) throws Exception {

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    Connection loConnection = null;

    ResultSet loRS = null;

    PreparedStatement loStatement = null;

                   // putting all java.sql.Types constants into hashtable

    Hashtable <Integer, String> loSqlType = new Hashtable();

    loSqlType.put(java.sql.Types.ARRAY, "ARRAY");

    loSqlType.put(java.sql.Types.BIGINT, "BIGINT");

    loSqlType.put(java.sql.Types.BINARY, "BINARY");

    loSqlType.put(java.sql.Types.BIT, "BIT");

    loSqlType.put(java.sql.Types.BLOB, "BLOB");

    loSqlType.put(java.sql.Types.BOOLEAN, "BOOLEAN");

    loSqlType.put(java.sql.Types.CHAR, "CHAR");

    loSqlType.put(java.sql.Types.CLOB, "CLOB");

    loSqlType.put(java.sql.Types.DATALINK, "DATALINK");

    loSqlType.put(java.sql.Types.DATE, "DATE");

    loSqlType.put(java.sql.Types.DECIMAL, "DECIMAL");

    loSqlType.put(java.sql.Types.DISTINCT, "DISTINCT");

    loSqlType.put(java.sql.Types.DOUBLE, "DOUBLE");

    loSqlType.put(java.sql.Types.FLOAT, "FLOAT");

    loSqlType.put(java.sql.Types.INTEGER, "INTEGER");

    loSqlType.put(java.sql.Types.JAVA_OBJECT, "JAVA_OBJECT");

    loSqlType.put(java.sql.Types.LONGVARBINARY, "LONGVARBINARY");

    loSqlType.put(java.sql.Types.LONGVARCHAR, "LONGVARCHAR");

    loSqlType.put(java.sql.Types.NULL, "NULL");

    loSqlType.put(java.sql.Types.NUMERIC, "NUMERIC");

    loSqlType.put(java.sql.Types.OTHER, "OTHER");

    loSqlType.put(java.sql.Types.REAL, "REAL");

    loSqlType.put(java.sql.Types.REF, "REF");

    loSqlType.put(java.sql.Types.SMALLINT, "SMALLINT");

    loSqlType.put(java.sql.Types.STRUCT, "STRUCT");

    loSqlType.put(java.sql.Types.TIME, "TIME");

    loSqlType.put(java.sql.Types.TIMESTAMP, "TIMESTAMP");

    loSqlType.put(java.sql.Types.TINYINT, "TINYINT");

    loSqlType.put(java.sql.Types.VARBINARY, "VARBINARY");

    loSqlType.put(java.sql.Types.VARCHAR, "VARCHAR");

    try

    {

    loConnection = DriverManager.getConnection(

    "jdbc:sqlserver://*:*;databaseName=*", "*", "*"); // * to be set

    System.out.println("COLUMN_NAME," +

    "COLUMN_SIZE,DECIMIAL_DIGITS,TYPE_NAME,DATA_TYPE,java.sql.Types");

    loStatement = loConnection.prepareStatement("select * from ALL_DATA_TYPES_COLUMNS");

    loRS = loStatement.executeQuery();

    ResultSetMetaData loRSMD = loRS.getMetaData();

    for (int i = 1; i <= loRSMD.getColumnCount(); i++)

    {

    System.out.println(loRSMD.getColumnName(i) + ","

    + loRSMD.getColumnDisplaySize(i) + ","

    + loRSMD.getPrecision(i) + ","

    + loRSMD.getScale(i) + ","

    + loRSMD.getColumnTypeName(i) + ","

    + loRSMD.getColumnType(i) + ","

    + loSqlType.get(loRSMD.getColumnType(i))

    );

    }

    loStatement.close();

    }

    catch (Exception ex)

    {

    throw ex;

    }

    finally

    {

    try{

    if (loConnection != null) loConnection.close();

    }

    catch (Exception ex){throw ex;}

    }

    }

    the output:

    COLUMN_NAME,COLUMN_SIZE,DECIMIAL_DIGITS,TYPE_NAME,DATA_TYPE,java.sql.Types

    VARBINARY_MAX_COL,2147483647,2147483647,0,varbinary,-3,VARBINARY

    CHAR_COL,30,30,0,char,1,CHAR

    VARCHAR_MAX_COL,2147483647,2147483647,0,varchar,12,VARCHAR

    DATETIME2_COL,27,27,7,datetime2,93,TIMESTAMP

    NCHAR_COL,30,30,0,nchar,1,CHAR

    NVARCHAR_MAX_COL,1073741823,1073741823,0,nvarchar,12,VARCHAR

    NUMERIC_P_S_COL,12,10,3,numeric,2,NUMERIC

    NUMERIC_P_COL,12,10,0,numeric,2,NUMERIC

    FLOAT_COL,22,15,0,float,8,DOUBLE

    NVARCHAR_COL,30,30,0,nvarchar,12,VARCHAR

    VARCHAR_COL,30,30,0,varchar,12,VARCHAR

    As indicated on the documentation that comes with the Microsoft SQL Server JDBC Driver 3.0, data types varchar(max), nvarchar(max), varbinary(max) should map to java.sql.Types "LONGVARCHAR", "LONGVARCHAR"(JRE5), "LONGVARBINARY" respectively.

    Would this be fix any time soon? Or we need to contact Microsoft customer service in our country?

    Thanks,

    Wilfred

  10. Mugunthan Mugundan- MSFT says:

    It looks like the documentation is not correct here. From 3.0 the varchar(max) will be reported as varchar and similarly varchar(binary). If you want to know if the type is max type or standard type you have to also use precision to determine whether the type is max type or small type.

  11. Mugunthan Mugundan- MSFT says:

    Re: changing user contexts, Wes Clarks post

    Would execute as work for you?

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

  12. wesclark says:

    Answering question, "Would execute as work for you?," the answer is no.  We don’t want to change to the user’s credentials after we get the pooled connection.  The JavaDoc at http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setClientInfo(java.lang.String, java.lang.String) says exactly what we want to do.  

    I ran a test program using the 3.0 driver, and java.sql.DatabaseMetaData#getClientInfoProperties() returned a result set with now rows.  After calling connection.setClientInfo("ClientUser", "houdini"), a call to connection.getWarnings() returned a SQLWarning which said "This property is not supported: ClientUser."

    I also tried workstationID and applicationName, both of which were reported as not supported.  As I stated above, we are setting applicationName on the JDBC URL because it is the same for all the connections in the pool.  We want to change the clientUser property dynamically when we borrow a connection from the pool.  

  13. wesclark says:

    If we could modify applicationName cheaply and dynamically, we could slip in the user name.

  14. mike says:

    Hi, sorry for distrurbing.

    I can't find any help with problem:

    Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 193 …

    Removing dll lead to same error with different reason 126

    I tried all supplied dll versions… same result

    My config:

    server: win2k8 64bit + mssql2008 64bit  

    client: JDBC Driver 3.0 3.0.1301.101

    Please help if you can… just simple hint, where is the problem…

  15. Rama says:

    DatabaseMetaData.getUserName() method returns login name instead of username.  Is this as per the JDBC specification

  16. Hans says:

    Hi

    Nice release!! Any plans to support TVPs from JDBC ?

    BR

    Hans

  17. jmblock says:

    We are using MS-JDBC v3.0 against SQL 2008 but getting the following error from java/hibernate:

    2010-08-11 19:35:35,193 DEBUG [com.example.test.Test] – No Dialect mapping for JDBC type: -9

    org.hibernate.MappingException: No Dialect mapping for JDBC type: -9

    at org.hibernate.dialect.TypeNames.get(TypeNames.java:79)

    at org.hibernate.dialect.TypeNames.get(TypeNames.java:104)

    at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:393)

    at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:582)

    .

    .

    .

    The only work around we find is to provide a custom extension of the sqlDialect. Is this really needed ?

  18. max says:

    Any ideas whent TVP will be suported?

  19. Jay says:

    like other notorious MS software, the driver just issue "Specified driver could not be loaded due to system error  1114", what the heck is that?

  20. Brody says:

    How about a UDT example?  I can't seem to find any documentation on how to use it.

  21. Mike says:

    Standalone java TestCase:

    import java.sql.*;

    public class TestCase {

    public static void main(String args[]) {

    try {

    // Step 1: Load the JDBC driver.

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // Microsoft driver

    // Step 2: Establish the connection to the database.

    String url = "jdbc:ibm:sqlserver://localhost:1433;databaseName=MYDB;";

    Connection conn = DriverManager.getConnection(url,"user","password");  

    // MetaData info for debug

    DatabaseMetaData meta=conn.getMetaData();

    System.out.println("Driver Name="+meta.getDriverName());

    System.out.println("Driver Version="+meta.getDriverVersion());

    System.out.println("JDBC Major Version="+meta.getJDBCMajorVersion());

    System.out.println("getSchema()");

    ResultSet schema=meta.getSchemas();

    while(schema.next()){

    System.out.println(schema.getString(1));

    }

    System.out.println("got the schema -done");

    System.out.println("WORKED FINE");

    }// end try

    catch (SQLException sqe) {

    System.out.println(sqe.getMessage());

    System.out.println("SQLState="+sqe.getSQLState());

    System.out.println("SQL Error Code ="+sqe.getErrorCode());

    sqe.printStackTrace();

    System.exit(1);

    }// end catch

    catch (Exception e) {

    e.printStackTrace();

    System.exit(1);

    }// end catch

    System.exit(0);

    }

    }

    Produces this output:

    Output:

    Driver Name=Microsoft SQL Server JDBC Driver 3.0

    Driver Version=3.0.1301.101

    JDBC Major Version=4

    getSchema()

    Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the INTERSECT operation.

    SQLState=S0009

    SQL Error Code =468

    com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the INTERSECT operation.

    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)

    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(SQLServerStatement.java:619)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFromInternalQueries(SQLServerDatabaseMetaData.java:224)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getSchemasInternal(SQLServerDatabaseMetaData.java:1292)

    at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getSchemas(SQLServerDatabaseMetaData.java:1227)

    at TestCase.main(TestCase.java:22)

    This works fine with version 2.0 of the JDBC driver.

    The server collation is set to Latin1_General_CI_AS, whereas the database collation is set to SQL_Latin1_General_CP1_CS_AS

    Is this a deliberate limitation or a bug – an d is there any way around it without changing either the server or database collation?

Skip to main content