Announcing Microsoft SQL Server JDBC Driver 2.0
We are excited to announce the newest release of the Microsoft SQL Server JDBC Driver 2.0!
This version of the JDBC driver supports features introduced in the JDBC 4.0 API, including:
· national character set data types: NCHAR, NVARCHAR, LONGNVARCHAR, NCLOB
· SQLXML data type
· Wrapper interface to access SQL Server JDBC Driver specific methods
· client info properties
· new database metadata methods
· LOB creator methods
The 2.0 driver also adds:
· default adaptive response buffering behavior
· support for SQL Server 2008 collations
· enhanced tracing, including public method entry and exit traces
· performance improvements and bug fixes
Please feel free to download a copy and see for yourself!
Comments
Anonymous
April 10, 2009
I'm so disappointed by the result of performance optimization for ParameterUtils.scanSQLForChar() and it's caller. The optimization should be more deeper. I think if your team open source, I can give a patch for the performance. I posted on forum:http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/ace7badf-90d7-4fd7-aebb-6f09af62077f/ contact me :lunxian@hotmail.com, if neccessarily.Anonymous
April 18, 2009
Dear Sir Can I use JDBC 2.0 to connect to MS-SQL SQL-Server 2009? I believe it will be released soon. Thanks in advance and Sorry for any inconvenience.Anonymous
April 20, 2009
The comment has been removedAnonymous
April 22, 2009
can you give me some advice? I create a table with varchar column,if input all English words, it can be displayed well, but if contain Simplified Chinese characters there is nothing to display. <% ... out.print(rs.getString("name")); ... %> no exception or something prompte.Anonymous
April 22, 2009
my environment is sql server 2008 enu, jboss 5.0, used sqljdbc4.jar.Anonymous
April 22, 2009
i changed varchar to nvarchar that's ok! sorry for disturbed.Anonymous
April 24, 2009
Here's a problem i noticed with this new driver. When you get a binary stream InputStream s = resultSet.getBinaryStream("filedata"); and then try to to return this stream after closing the connection you get an error when attempting to read from this stream. This wasn't the behavior in the previous driver. In the previous driver you could have methods like this //NOTE this is pseudo code public InputStream getBlobStream() { connection = pool.getConnection() statement = onnection.createStatement(); resultset = stmt.executeQuery("select blob from blob"); InputStream s = resultSet.getBinaryStream("filedata"); connection.close(); return s; } In the new driver, closing the connection or releasing it back to the pool closes the steam on the result set. This is really poor behavior IMO since you can no longer pass a stream around without worrying about the underlying DB connection.Anonymous
April 30, 2009
Hi Max, The JDBC specification for Connection.close() says that the method "releases this Connection object's database and JDBC resources immediately". That means releasing resources associated with all of the Statements, ResultSets, InputStreams, etc. that were created in the context of that Connection. Your repro relies on the driver not behaving per the JDBC specification in that respect in this particular situation. My first recommendation would be to fix the repro not to depend on the incorrect driver behavior as we may correct the behavior at any point to align with the JDBC spec. That said, the likely cause of the difference in behavior is the change to default to adaptive response buffering in the 2.0 driver. So you may be able to restore the previous behavior by setting the connection property responseBuffering=full. But in doing so, the repro still relies on a driver bug. Regards, --David Olix [SQL Server]Anonymous
May 13, 2009
what about Table-valued parameters support in JDBC driver?Anonymous
May 13, 2009
Hello Palesz, This release of the JDBC driver was targeted towards the JDBC 4.0 specification. However, we are in the planning stage of our next driver and would love to hear feedback from you. Do you mind heading here and letting us know what features you'd like to see in the next version of our driver? http://blogs.msdn.com/jdbcteam/archive/2008/10/14/sql-server-2008-feature-support-survey.aspx If possible, please include your intended usage scenario. We appreciate it. Thanks, --Tres London [SQL Server]Anonymous
May 23, 2009
(I apologize if this shows up twice...my first time responding on MSDN blogs, and after hitting the Submit button once, I didn't see my response appear) I'm using the 2.0 driver and am having some issues with XA Transactions not properly aborting. I wonder if you could possibly shed any light on the matter, or provide any advice for tracking the issue down. Here's a link to a posting to the Glassfish forum describing some of the details: http://forums.java.net/jive/thread.jspa?threadID=61749&tstart=0 In short, I'm using glassfish to get an XADataSource to connect to a SQL Server 2005 database, and most things seem to work properly. I can begin transactions, commit them, and even explicitly do a rollback(). The problem I am having occurs when the clien app (NOT the app server) closes unexpectedly. The XA transaction times out, SQL Server still seems to continue to hang on to resources (locks) and there seems to be a "hanging" UOW with connection id "-2" hanging around. If I use a different DB server (Derby) the XA Abort seems to work properly. Any thoughts? Any way to tell if SQL Server actually receives word that it ought to abort the transaction? At least one other person seems to have had a similar problem, and provided a concise description here: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-jdbc/1043/xa-connection-issues Any help is appreciated. Thanks in advance.Anonymous
May 24, 2009
i have changed my my driver to JDBC Driver 2.0 and i am facing a problem. Some of the stored procedure which were working in my web application are now not working now. I am using sqljdbc.jar. I am getting "com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set" I can see that from query analyzer it is returning values. I have tried different combination of executeQuery and all are returing the same. Am i missing something ?Anonymous
May 25, 2009
The comment has been removedAnonymous
June 15, 2009
Since jdbc 1.2 driver it appears some uniqueness constraint violations no longer cause an exception when the insert is inside a stored proc. There is one application that I have (running Java 1.6), that uses JDBC 1.1 and raises a SQLException but if I take the same application and swap out the JDBC driver 1.1 with either a 1.2 or 2.0, it no longer generates a Java SQLException. I have written a small prototype application to try and determine what might be going on but now all three (driver versions) raise the exception as exepected! I notice in the 1.2 driver release blog of this msdn blogs, Georg also has the same issue but nobody replied. Any ideas on what might be the cause of this? Thanks ECSAnonymous
June 15, 2009
The comment has been removedAnonymous
June 15, 2009
dpblogs, thanks for the reply. There are 5 selects (grabbing external info) and two inserts. Whilst doing Negative Testing it was noticed that the Duplicate key (2627) didn't throw an exception but a divide by zero did! It has a TRY/CATCH, where the CATCH RAISES an error which does not get thrown.
begin catch if xact_state() <> 0 rollback transaction RAISERROR ('Error raised in TRY block.', -- Message text. 17, -- Severity. 1 -- State. ); return error_number() end catch
Any ideas yet? Thanks
Anonymous
June 15, 2009
After numerious hours of debugging I have found that if the Stored Proc does not contain SET NOCOUNT ON; then it appears not to throw an exception with Drivers 1.2 and 2.0. Drivers 1.1 throws the error. Thanks, ECSAnonymous
June 15, 2009
And now(!) I find the link I was looking for! http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=358804 Even using the update() it still fails to throw the SQLException. So why the change? :-)Anonymous
June 16, 2009
The comment has been removedAnonymous
June 21, 2009
The comment has been removedAnonymous
June 25, 2009
I'm using SchemaCrawler to get metadata from SQL Server, and was surprised that the table privileges returned didn't match those I find when I examine the database using Microsoft SQL Server Management Studio. It seem as if the grants on tables were being returned as column privs rather than table privs. I suspect the problem is that getTablePrivileges invokes sp_table_privileges which doesn't IMHO match the expected semantics. I expected that getTablePrivileges would work as if it were based on this query (against a single db, not all dbs): select * from ( select null as TABLE_CAT, SCHEMA_NAME(o.schema_id) as TABLE_SCHEM, o.name as TABLE_NAME, USER_NAME(p.grantor_principal_id) as GRANTOR, USER_NAME(p.grantee_principal_id) as GRANTEE, p.permission_name as PRIVILEGE, CASE WHEN p.state = 'W' THEN 'YES' ELSE 'NO' END as IS_GRANTABLE from sys.database_permissions p inner join sys.objects o on p.class = 1 -- Is an object or column and p.minor_id = 0 -- Is an object, not a column and p.state in ('G', 'W') -- GRANT or GRANT WITH OPTIONS and p.major_id = o.object_id and o.type in ('U', 'V') -- Object is a table or view ) x order by TABLE_SCHEM, TABLE_NAME, PRIVILEGEAnonymous
July 06, 2009
Thanks for your comments jsynge. We're investigating the issue. --Tres London [SQL Server]Anonymous
July 08, 2009
jsynge, We've checked our implementation and it appears to be according to the spec. Could you provide an example? --Tres LondonAnonymous
August 11, 2009
There is still a bug with the driver and the getPareameterMetaData call when the query in question contains an aliased table that has a column that needs to be set to a parameter... query... select p.id, p.name from person p where p.age = ? the java code... PreparedStatement ps = conn.prepareStatement(sql); ps.getParamterMetaData(); // THIS THROWS AN EXCEPTION This bug was also in the older version of the driver (1.2 i think). Any plan on fixing this? It is really causing our team headaches. We use Apache Commons DbUtils QueryRunner as part of our data access framework and it calls that method and fails. The only workaround is to not alias any tables and spell out the table names completely. very annoying.Anonymous
August 14, 2009
There is a bug in the Driver 2.0 when closing a prepared statement while underlying conncetion is already closed. To reproduce use the Example from http://msdn.microsoft.com/en-us/library/aa342335(SQL.90).aspx but modify it to try{ pstmt.setInt(1,1); pstmt.setString(2,xid.toString()); pstmt.executeUpdate(); }finally{ pstmt.close(); } Now Run the Program and pause at "try{". Cancel Database Connection by shutting down or blocking the port with a firewall. pstmt.isClosed() = false but pstmt.getConnection().isClosed()= true when pstmt.close(); is executed the CPU goes up to 100% and FINEST Logging shows a infintiy loop of FEINER: ENTRY 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.SQLServerConnection:2 close FEINER: RETURN 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.TDSCommand close AM FEINSTEN: TDSCommand@25474e45 (SQLServerPreparedStatement:4 executeXXX): close ignoring error processing response: Connection reset 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.TDSChannel read FEIN: TDSChannel (ConnectionID:2) read failed:Connection reset 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.SQLServerException logException FEIN: *** SQLException:ConnectionID:2 com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset Connection reset 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.SQLServerException logException FEIN: com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368)com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1355)com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1532)com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3274)com.microsoft.sqlserver.jdbc.TDSReader.nextPacket(IOBuffer.java:3227)com.microsoft.sqlserver.jdbc.TDSReader.ensurePayload(IOBuffer.java:3203)com.microsoft.sqlserver.jdbc.TDSReader.peekTokenType(IOBuffer.java:3420)com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:50)com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:39)com.microsoft.sqlserver.jdbc.SQLServerStatement.processExecuteResults(SQLServerStatement.java:1064)com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.processResponse(SQLServerPreparedStatement.java:345)com.microsoft.sqlserver.jdbc.TDSCommand.close(IOBuffer.java:4111)com.microsoft.sqlserver.jdbc.SQLServerStatement.discardLastExecutionResults(SQLServerStatement.java:99)com.microsoft.sqlserver.jdbc.SQLServerStatement.closeInternal(SQLServerStatement.java:592)com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.closeInternal(SQLServerPreparedStatement.java:170)com.microsoft.sqlserver.jdbc.SQLServerStatement.close(SQLServerStatement.java:604)testXA.main(testXA.java:73) 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.SQLServerPooledConnection notifyEvent FEINER: SQLServerXAConnection:1 Exception:com.microsoft.sqlserver.jdbc.SQLServerException: Connection resetConnectionID:2 14.08.2009 14:00:07 com.microsoft.sqlserver.jdbc.SQLServerConnection:2 close How is official way for a bug report?Anonymous
August 25, 2009
Thanks Johannes. Johannes and the JDBC product team are communicating regarding this issue through: https://connect.microsoft.com/default.aspx -- Tres London [SQL Server]Anonymous
August 27, 2009
Rob Fellows, You are correct - there is still a bug. We are aware of the bug and will consider fixing it in a future release. -- Tres London [SQL Server]Anonymous
September 24, 2009
Hi, I am running MS SQL Server 2000 on Windows Server 2003 SP2. And I want to connect to this SQL Server from a Windows XP host using the new MS SQL Server JDBC driver 2.0. But I keep getting the following 2 exceptions:
com.microsoft.sqlserver.jdbc.SQLServerException: The TDS protocol stream is not valid. at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368) at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1355) at com.microsoft.sqlserver.jdbc.SQLServerConnection.throwInvalidTDS(SQLServerConnection.java:1344) at com.microsoft.sqlserver.jdbc.TDSReader.throwInvalidTDS(IOBuffer.java:3190) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onRetValue(SQLServerResultSet.java:4624) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:66) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4696) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1683) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:956) at com.a.ag.sdk.ba.db.sdkbda.eq(sdkbda.java:842) at com.a.ag.ss.tfp.istp(tfp.java:167) at com.a.ag.ss.tfp.istp(tfp.java:313) at com.a.ag.u.ff.istp(ff.java:467) at com.a.ag.u.ff.pf(ff.java:359) at com.a.ag.u.ff.pfaf(ff.java:316) at com.a.ag.u.ff.run(ff.java:265) at java.lang.Thread.run(Thread.java:619) 2) com.microsoft.ss.jdbc.SQLServerException: The connection is closed. at com.microsoft.ss.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170) at com.microsoft.ss.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:304) at com.microsoft.ss.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1863) at com.microsoft.ss.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1566) at com.bitmechanic.sql.PooledConnection.prepareStatement(PooledConnection.java:341) at c.a.ag.sdk.ba.db.sdkbda.eq(sdkbda.java:794) at c.a.ag.ss.tfp.istp(tfp.java:167) at c.a.ag.ss.tfp.istp(tfp.java:313) at c.a.ag.u.ff.istp(ff.java:467) at c.a.ag.u.ff.pf(ff.java:359) at c.a.ag.u.ff.pfaf(ff.java:316) at c.a.ag.u.ff.run(ff.java:265) at java.lang.Thread.run(Thread.java:619) Note that I have NO ISSUES in using the JDBC driver 1.2 for this same purpose. The DB URL that I used in both the tests is the same. Can someone point out the issue(s?) here? Thanks.
Anonymous
September 24, 2009
Hi rk, I'm sorry you're running into this problem with our JDBC driver. Given the call stack you provided (thank you), we will need to investigate this further. Unfortunately, the JDBC driver team blog is not well-suited to such an investigation. If you don't yet have a complete and concise repro, we will need to gather databases, queries, code, and driver trace logs to investigate further. In that case, I encourage you to contact Microsoft Customer Support (http://support.microsoft.com) to open a support case. Our support engineers will gather the necessary information, investigate the issue, and, if the issue turns out to be a driver bug, arrange for you to get a fix. If you have a complete and concise repro though, you may post it directly here or, preferably, at the Microsoft Connect site for SQL Server at https://connect.microsoft.com/SQLServer/Feedback Regards, --David Olix [SQL Server]Anonymous
September 25, 2009
Hi dpblogs, Thank you for the prompt response. Can you tell me what more information is required for a complete and concise repro? Thanks.Anonymous
September 28, 2009
Hi rk, A repro consists of a Java code sample that demonstrates the problem when compiled and run standalone. If that is something that you cannot (easily) provide then working with a Microsoft Customer Support engineer to scope the problem and investigate it thoroughly is probably a better approach to getting the problem solved. Regards, --David Olix [SQL Server]Anonymous
January 19, 2010
Currently we few of our applications are using SQL JDBC Driver 1.2 and we are planning to move to JDBC 2.0 (we are also moving from SQL 2000 to SQL 2008). In the past we had issues with XA transaction and that was fixed by hotfix KB 950520. We have the following questions/concerns before we make this move. I will appreciate any response from your team.
- Can we install multiple versions of JDBC drivers side by side on the same client machine (for example we want to have JDBC 1.2 and 2.0 drivers since we may not be able to move all our exiting applications to use JDBC 2.0)
- We assume that if any application is using the 2.0 JDBC driver AND that application uses XA, that we must use the 2.0 version of the XA DLL. That you can’t use the 2.0 jar file with the 1.2 DLL. Is that correct?
- If we install the 2.0 XA DLL, can some applications continue to use the 1.2 JDBC driver? In other words, is the 2.0 XA DLL backwards compatibly with older 1.2 jar file?
- Is the 2.0 JDBC driver and related XA DLL an improvement from a stability point of view when compared to our 1.2 driver (XA transaction in 1.2 was fixed in hotfix KB 950520)? In other words, are we going to see XA issues with the 2.0 driver?
Anonymous
January 28, 2010
We use the sqljdbc4.jar from sqljdbc_2.0.1803.100_enu.tar.gz It works fine in our application. However the Ant "sql" task fails upon connection. We/I use this often; it's very simple/vanilla. Thus, odd that it fails. Here's the error: "The server version is not supported. The target server must be SQL Server 2000 or later." It works for 2000, and 2005, but fails in 2008. Odd. We're running sql server 2008 SP1 Enterprise Here's a stack trace excerpt: com.microsoft.sqlserver.jdbc.SQLServerException: The server version is not supported. The target server must be SQL Server 2000 or later. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source) at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at org.apache.tools.ant.taskdefs.JDBCTask.getConnection(JDBCTask.java:31 fyi, I have cross-posted this on stack overflow. any insights? thanksAnonymous
January 28, 2010
It looks like the ant path is not picking up the right jar. The error from the callstack looks like coming not from a 2.0 driver but something like 1.1. Make sure that your ant class path has the 2.0 jar.Anonymous
January 28, 2010
Mohammed, please see inline for answers
- Can we install multiple versions of JDBC drivers side by side on the same client machine (for example we want to have JDBC 1.2 and 2.0 drivers since we may not be able to move all our exiting applications to use JDBC 2.0) Mugunthan answer: Yes, as far as you set your class paths correctly for your applications to pick up the right versioned jars.
- We assume that if any application is using the 2.0 JDBC driver AND that application uses XA, that we must use the 2.0 version of the XA DLL. That you can’t use the 2.0 jar file with the 1.2 DLL. Is that correct? Mugunthan answer: Yes. 2.0 driver has to have the 2.0 dll not with 1.2 dll.
- If we install the 2.0 XA DLL, can some applications continue to use the 1.2 JDBC driver? In other words, is the 2.0 XA DLL backwards compatibly with older 1.2 jar file? Mugunthan answer: Yes, 2.0 XA dll will work with 1.2 jar.
- Is the 2.0 JDBC driver and related XA DLL an improvement from a stability point of view when compared to our 1.2 driver (XA transaction in 1.2 was fixed in hotfix KB 950520)? In other words, are we going to see XA issues with the 2.0 driver? Mugunthan answer: There are no outstanding issues in the 2.0 driver that we think needs to be fixed at this time.
Anonymous
February 17, 2010
I am using SQL Server 2008 and am using the JDBC 2.0 driver to connect to the database. I use the Apache Tomcat server integrated into the Eclipse IDE.I am trying to connect using a dynamic web application in the Eclipse IDE and it gives me an error saying com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication When I use the same code for connecting to a desktop application, it works fine. I think there is some problem with placing my 'sqljdbc_auth.dll'. Could you guide me as to where I would have to place my .dll for the application to run in Eclipse with the Tomcat server?Anonymous
March 26, 2010
Where did you place your integrated auth dll. Have you followed the instructions in this link on the integrated authentication section http://msdn2.microsoft.com/en-us/library/ms378428.aspx Usually placing in the windows system directory works for all applications. I am not sure if Apache Tomcat requirements are different.Anonymous
April 04, 2010
The comment has been removedAnonymous
April 04, 2010
I forgot to say, that the problem occurs on 2.0 and 3.0 CTP