setLong() in preparedStatement.executeBatch()

[amresh]

Hi,

I am trying to use prepared statements for inserting multiple rows in a batch. I get exceptions if I try to use setLong to insert value in a numeric(23,0) column.

I am using:

Jboss 3.2.2 (using cached prepared statements)
jConnect 5.5
Sybase 11.9.2.5

Code snippet:

public static void main(String[] args) {
PreparedStatementTest preparedStatementTest = new PreparedStatementTest(new Long(args[0]).longValue());
}

public PreparedStatementTest(long seed) {
connection = DataSourceFactory.getDataSource().getConnection();
preparedStatement = connection.prepareStatement("INSERT INTO DEV2_DebtGateway..TEST_PREPARED_STATEMENT (ID, NAME, DESCRIPTION) VALUES (?,?,?)");
for (int i = 0; i < 3; i++) {
getTestSQL(preparedStatement, seed + i);
}
preparedStatement.executeBatch();
System.out.println("Finished inserts");
}

private static PreparedStatement getTestSQL(PreparedStatement preparedStatement,
long id) throws SQLException {
preparedStatement.setDouble(1, id);
preparedStatement.setString(2, "Joe");
preparedStatement.setString(3, "HELLO");
preparedStatement.addBatch();
return preparedStatement;
}


com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: The parameter of
type 38 did not have a valid value.

at com.sybase.jdbc2.jdbc.ErrorMessage.raiseBatchUpdateException(ErrorMessage.java:698)
at com.sybase.jdbc2.jdbc.SybStatement.batchLoop(SybStatement.java:1330)
at com.sybase.jdbc2.jdbc.SybStatement.sendBatch(SybStatement.java:1139)
at com.sybase.jdbc2.jdbc.SybStatement.executeBatch(SybStatement.java:1106)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeBatch(SybPreparedStatement.java:737)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:396)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:701)
at com.xxx.yyyy.persistence.PreparedStatementTest.(PreparedStatementTest.java:34)



1. Outside the application server the same code works without any problem.With the same driver etc.

2. Interestingly if I use setDouble instead of setLong I get a different exception (below), again only in the app server.

com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Attempt to
insert duplicate key row in object 'TEST_PREPARED_STATEMENT' with unique index 'TEST_PREP_14692482891'
at com.sybase.jdbc2.jdbc.ErrorMessage.raiseBatchUpdateException(ErrorMessage.java:698)
at com.sybase.jdbc2.jdbc.SybStatement.batchLoop(SybStatement.java:1330)
at com.sybase.jdbc2.jdbc.SybStatement.sendBatch(SybStatement.java:1139)
at com.sybase.jdbc2.jdbc.SybStatement.executeBatch(SybStatement.java:1106)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeBatch(SybPreparedStatement.java:737)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:396)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:701)
at com.xxx.yyyy.persistence.PreparedStatementTest.(PreparedStatementTest.java:32)

Any help with regards to this would be much appreciated.


Thanks in advance

Amresh