Simple update test fails
Jon Skeet
Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer
2000 Dri
ver for JDBC]Invalid parameter binding(s).
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.validateParameterIndex
(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.setObjectInternal
(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.setString
(Unknown Source)
at Test.main(Test.java:15)
Making it read-only instead of updatable makes it work fine. Obviously
in my real app, I need it to be updatable. Why should this fail?
(Fix <dbserver>, <user> and <password> to test this on your own
SQLServer machine.)
import java.sql.*;
public class Test
{
public static void main(String[] args)
throws Exception
{
String sql = "select Quantity, OrderID, ProductID from "+
"[Order Details] where OrderID=? and ProductID=?";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://<dbserver>:1433;"+
"DatabaseName=Northwind;SelectMethod=Cursor",
"<user>", "<password>");
PreparedStatement pst = conn.prepareStatement (sql,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
pst.setInt (1, 10643);
pst.setInt (2, 39);
ResultSet rs = pst.executeQuery();
while (rs.next())
{
System.out.println (rs.getInt (1));
System.out.println (rs.getInt (2));
System.out.println (rs.getInt (3));
}
rs.close();
pst.close();
conn.close();
}
}
--
Jon Skeet - <sk...@pobox.com>
http://www.pobox.com/~skeet/
If replying to the group, please do not mail me too
Jon Skeet
> Here's some code which fails quite bizarrely with:
<snip>
While I normally hate to do this, could I point out my previous post
again? It strikes me that failing to update in this simple case is quite
a major problem - and certainly one which is stopping me from making
progress.
Jon Skeet
> Here's some code which fails quite bizarrely with:
>
> Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer
> 2000 Dri
> ver for JDBC]Invalid parameter binding(s).
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.validateParameterIndex
> (Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.setObjectInternal
> (Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.setString
> (Unknown Source)
This last line of the exception wasn't generated by the code I posted -
sorry about that. The code I posted generates the same exception, but
with setInt instead of setString.
Jon Skeet
> Here's some code which fails quite bizarrely with:
<snip>
Update: I've now had another person independently verify this. The same
code with the driver from http://www.inetsoftware.de/ works fine.
Jon Skeet
> Jon Skeet <sk...@pobox.com> wrote:
> > Here's some code which fails quite bizarrely with:
>
> <snip>
>
> Update: I've now had another person independently verify this. The same
> code with the driver from http://www.inetsoftware.de/ works fine.
Further update: changing to a scrolling result set doesn't help. Logging
the warnings, however, shows something interesting:
[Microsoft][SQLServer 2000 Driver for JDBC]Updateable concurrency not
supported, downgraded to readonly concurrency.
I suspect that's the root of the problem, but for some reason it only
seems to happen on this table - other tables can be updated just fine...
Jon Skeet
> > Update: I've now had another person independently verify this. The same
> > code with the driver from http://www.inetsoftware.de/ works fine.
>
> Further update: changing to a scrolling result set doesn't help. Logging
> the warnings, however, shows something interesting:
>
> [Microsoft][SQLServer 2000 Driver for JDBC]Updateable concurrency not
> supported, downgraded to readonly concurrency.
>
> I suspect that's the root of the problem, but for some reason it only
> seems to happen on this table - other tables can be updated just fine...
Final update: that warning and the exception are also generated if you
try to use a non-existant table name with a space in it, using [] for
quoting.
Using
select .... from "Order Details"
instead of
select .... from [Order Details]
works.
Bizarre, eh?