Micorosoft SQL Server JDBC 3.0 Released!!!

Micorosoft SQL Server JDBC 3.0 Released!!!

  • Comments 21

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

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • 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"?

  • 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()

  • 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

    ----------------------------------------

  • 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);

     }

    }

  • 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.

  • 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

  • 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.

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

  • 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

  • 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.

  • Re: changing user contexts, Wes Clarks post

    Would execute as work for you?

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

  • 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.  

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

  • 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...

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

Page 1 of 2 (21 items) 12