Re: Unexpected Exception when inserting data in SQL 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry



andrew.elmhorst@xxxxxxxxx wrote:

It's varchar. I have more information and a way to consistently
reproduce it. If a duplicate key insert is done the very first time I
call .execute() on the preparedstatement, the preparedstatement throws
the same SQLException every subsequent time it is used.

The jTDS and MSSQL 2000 JDBC driver both work fine in that scenario.

I traced the call it is making to the database: It uses sp_prepexec,
which is supposed to prepare and execute the statement. Apparently it's
not recovering properly if the statement does not execute the first
time because of a database error!


Yep, duped it:

C:\new_ms_driver>java foo
Driver version is 1.0.809.102
Got expected exception
com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__#foo_
_5D401C1C'. Cannot insert duplicate key in object '#foo________________________________________
________________________________________________________________________000000000C82'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source
)
at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.seekToOutParams(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepStmtHandle(Unknown So
urce)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source
)
at foo.main(foo.java:37)


from this code:

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement stmt = c.createStatement();
stmt.execute("create table #foo(bar varchar(50) primary key)");
stmt.execute("insert into #foo values ('joe')");

PreparedStatement p = c.prepareStatement("insert into #foo values(?)");

try {
p.setString(1, "joe");
p.executeUpdate();
} catch (Exception expected) {
System.out.println("Got expected exception");
}

p.setString(1, "andrew");
p.executeUpdate(); // fails here

.

Relevant Pages

  • Re: get SQL statement from PreparedStatement
    ... PreparedStatement to the database to execute? ... SQL into some driver or database specific format that allows for value ...
    (comp.lang.java.programmer)
  • Re: get SQL statement from PreparedStatement
    ... The idea behind the prepared statement is that the driver or database will precompile the SQL into some driver or database specific format that allows for value bindings. ... If what you really wanted to achieve was to known what parameters are being passed to the PreparedStatement there are JDBC spy tools that can help. ... you do not "execute the SQL in PreparedStatement." ...
    (comp.lang.java.programmer)
  • Re: get SQL statement from PreparedStatement
    ... PreparedStatement to the database to execute? ... SQL into some driver or database specific format that allows for value ...
    (comp.lang.java.programmer)
  • Re: get SQL statement from PreparedStatement
    ... The idea behind the prepared statement is that the driver or database will precompile the SQL into some driver or database specific format that allows for value bindings. ... If what you really wanted to achieve was to known what parameters are being passed to the PreparedStatement there are JDBC spy tools that can help. ... you do not "execute the SQL in PreparedStatement." ...
    (comp.lang.java.programmer)
  • Re: Unexpected Exception when inserting data in SQL 2000
    ... If a duplicate key insert is done the very first time I ... call .executeon the preparedstatement, ... which is supposed to prepare and execute the statement. ... time because of a database error! ...
    (microsoft.public.sqlserver.jdbcdriver)