Microsoft JDBC Driver Team Blog

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.

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.

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.

my environment is sql server 2008 enu, jboss 5.0, used sqljdbc4.jar.

i changed varchar to nvarchar that's ok!

sorry for disturbed.

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.

what about Table-valued parameters support in JDBC driver?

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

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 ?

I have found solution in following link

http://blogs.msdn.com/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx

Apologize for not checking the link before. Posting here in case some one else need a direction :)

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

ECS

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

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,

ECS

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? :-)

Thanks for the feedback David.

I guess we have two options, the Stored Procs need all to be updated with the SET NOCOUNT ON or set it system wide on the instance.

The latter will take less than 30 secs the other will take much longer but like you say if there are errors being thrown from a SP that contains multiple result sets then yes we do need to execute() and getMoreResults() to determine where the errors have been thrown which I am told has generated some strange results... more about that when I see them.

Thanks,

ECS

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, PRIVILEGE

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.

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?

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:

1)

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.

Hi dpblogs,

Thank you for the prompt response. Can you tell me what more information is required for a complete and concise repro?

Thanks.