our company is using Sybase with jTDS driver and we ran into the following problem while migrating to Sybase 15.
Our application started crashing on inserts containing BigDecimals, NUMERIC(20, 2) on Sybase (changing NUMERIC to MONEY did not help).
I am not sure weather this is a jTDS bug, or should this be addressed to the Sybase team. I am providing the logs, code to reproduce the issue and some observations I have made.
On the Java side we are getting I/O Errors after the statement is executed (nothing gets inserted):
java.sql.SQLException: I/O Error: DB server closed connection.
at net.sourceforge.jtds.jdbc.TdsCore.sybasePrepare(TdsCore.java:1281)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:704)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:557)
The Sybase log shows a SEGFAULT in the connection's process:
Sybase version:
Adaptive Server Enterprise/15.0.3/EBF 18902 ESD#4 ONE-OFF/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit/FBO/Fri Apr 1 17:59:58 2011
jTDS version:
1.2.6 (the same problem occurs with earlier versions as well)
The code:
packagefoo.bar.test;importstaticorg.junit.Assert.assertFalse;importstaticorg.junit.Assert.fail;importjava.math.BigDecimal;importjava.math.RoundingMode;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importorg.junit.After;importorg.junit.Before;importorg.junit.Test;publicclassTestJdtsBigDecimalSybase15{privatestaticfinalStringDB_URL="jdbc:jtds:sybase://***";privatestaticfinalStringDB_USER="***";privatestaticfinalStringDB_PASSWORD="***";privatestaticfinalStringCREATE_SQL="CREATE TABLE testBigDecimalJtds ( "+"idField INT IDENTITY, bigDecimalField NUMERIC(20 , 2) NOT NULL, "+"intField INT NULL )";privatestaticfinalStringDROP_SQL="DROP TABLE testBigDecimalJtds";privatestaticfinalStringINSERT_SQL="INSERT INTO testBigDecimalJtds (bigDecimalField, intField) VALUES (?, ?)";privatestaticfinalStringIDENTITY_SQL="SELECT @@IDENTITY";@BeforepublicvoidsetUp()throwsException{DriverManager.registerDriver(newnet.sourceforge.jtds.jdbc.Driver());Connectionconnection=getConnection();PreparedStatementstmt=connection.prepareStatement(CREATE_SQL);stmt.execute();}@AfterpublicvoidtearDown()throwsException{Connectionconnection=getConnection();PreparedStatementstmt=connection.prepareStatement(DROP_SQL);stmt.execute();}privateConnectiongetConnection()throwsSQLException{Connectionconnection=DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);connection.setAutoCommit(false);returnconnection;}@Testpublicvoidtest(){BigDecimalfrom;BigDecimalto;BigDecimalaugend;try{from=newBigDecimal(10.00d).setScale(2,RoundingMode.HALF_UP);to=newBigDecimal(11.00d).setScale(2,RoundingMode.HALF_UP);augend=newBigDecimal(0.01d).setScale(2,RoundingMode.HALF_UP);for(BigDecimali=from;i.compareTo(to)<=0;i=i.add(augend)){doInsert(getConnection(),i);}from=newBigDecimal(9999999999999.00d).setScale(2,RoundingMode.HALF_UP);to=newBigDecimal(10000000000000.00d).setScale(2,RoundingMode.HALF_UP);augend=newBigDecimal(0.01d).setScale(2,RoundingMode.HALF_UP);for(BigDecimali=from;i.compareTo(to)<=0;i=i.add(augend)){doInsert(getConnection(),i);}}catch(SQLExceptionex){ex.printStackTrace();fail(ex.getMessage());}}privatevoiddoInsert(Connectionconnection,BigDecimalbigDecimalValue)throwsSQLException{try{PreparedStatementstmt=connection.prepareStatement(INSERT_SQL,PreparedStatement.RETURN_GENERATED_KEYS);stmt.setBigDecimal(1,bigDecimalValue);stmt.setInt(2,0);stmt.executeUpdate();assertFalse("nothing was inserted",0==stmt.getUpdateCount());stmt=connection.prepareStatement(IDENTITY_SQL);stmt.execute();ResultSetrs=stmt.getResultSet();if(!rs.next()){fail("no identity column returned");}assertFalse("Identity == 0",0==rs.getInt(1));rs.close();connection.commit();}catch(SQLExceptionex){ex.printStackTrace();connection.rollback();throwex;}finally{connection.close();}}}
Observations:
works on Sybase ASE 12;
works without PreparedStatement.RETURN_GENERATED_KEYS, data gets inserted;
inserting just the BigDecimal and leaving the last argument default to NULL, works;
doing "INSERT INTO testBigDecimalJtds (intField, bigDecimalField) VALUES (?, ?)" and
stmt.setInt(1, 0);
stmt.setBigDecimal(2, bigDecimalValue);
also works in this test case, however leaving BigDecimal as the last parameter still causes Sybase to segfault with production code (larger tables, multiple NUMERIC fields);
stmt.setObject(1, bigDecimalValue); also causes the same segfault;
In this case where only two decimal places precision is required, using
stmt.setDouble(1, bigDecimalValue.setScale(2, RoundingMode.HALF_UP).doubleValue());
seems to do the trick as a temporary workaround, however I do not like it as a permanent solution.
Please share your insights about this problem.
Thanks,
Vaidotas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
at a first glance, I do not see how jTDS could directly be responsible for that SEGFAULT. The exception at the Java side is just normal, given the server side process crashed.
Unfortunately I don't have access to an ASE 15 at the moment, so I am not able to verify the problem. But maybe you could get some help from Sybase support? Btw., did you try the jConnect driver? Does it cause crashes as well?
Cheers,
momo
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
the jConnect7 driver does not crash and passes the given test case. It returns the generated identity column ResultSet value through stmt.getGeneratedKeys(). However jConnect is not compatible with our persistence framework.
I will try the Sybase support as well.
Vaidotas
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you for clarification. Maybe I get access to an ASA 15 somehow so I can have a closer look. Please keep me updated meanwhile if you get any response from Sybase.
Cheers,
momo
Vaidotas schrieb am 17.08.12 15:10:
Hi momo,
the jConnect7 driver does not crash and passes the given test case. It returns the generated identity column ResultSet value through stmt.getGeneratedKeys(). However jConnect is not compatible with our persistence framework.
I will try the Sybase support as well.
Vaidotas
Hello,
our company is using Sybase with jTDS driver and we ran into the following problem while migrating to Sybase 15.
Our application started crashing on inserts containing BigDecimals, NUMERIC(20, 2) on Sybase (changing NUMERIC to MONEY did not help).
I am not sure weather this is a jTDS bug, or should this be addressed to the Sybase team. I am providing the logs, code to reproduce the issue and some observations I have made.
On the Java side we are getting I/O Errors after the statement is executed (nothing gets inserted):
The Sybase log shows a SEGFAULT in the connection's process:
Sybase version:
Adaptive Server Enterprise/15.0.3/EBF 18902 ESD#4 ONE-OFF/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit/FBO/Fri Apr 1 17:59:58 2011
jTDS version:
1.2.6 (the same problem occurs with earlier versions as well)
The code:
Observations:
stmt.setInt(1, 0);
stmt.setBigDecimal(2, bigDecimalValue);
also works in this test case, however leaving BigDecimal as the last parameter still causes Sybase to segfault with production code (larger tables, multiple NUMERIC fields);
In this case where only two decimal places precision is required, using
stmt.setDouble(1, bigDecimalValue.setScale(2, RoundingMode.HALF_UP).doubleValue());
seems to do the trick as a temporary workaround, however I do not like it as a permanent solution.
Please share your insights about this problem.
Thanks,
Vaidotas
Hi Vaidotas,
at a first glance, I do not see how jTDS could directly be responsible for that SEGFAULT. The exception at the Java side is just normal, given the server side process crashed.
Unfortunately I don't have access to an ASE 15 at the moment, so I am not able to verify the problem. But maybe you could get some help from Sybase support? Btw., did you try the jConnect driver? Does it cause crashes as well?
Cheers,
momo
Hi momo,
the jConnect7 driver does not crash and passes the given test case. It returns the generated identity column ResultSet value through stmt.getGeneratedKeys(). However jConnect is not compatible with our persistence framework.
I will try the Sybase support as well.
Vaidotas
Thank you for clarification. Maybe I get access to an ASA 15 somehow so I can have a closer look. Please keep me updated meanwhile if you get any response from Sybase.
Cheers,
momo
Vaidotas schrieb am 17.08.12 15:10:
Hi momo,
the jConnect7 driver does not crash and passes the given test case. It returns the generated identity column ResultSet value through stmt.getGeneratedKeys(). However jConnect is not compatible with our persistence framework.
I will try the Sybase support as well.
Vaidotas
Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/jtds/discussion/104389/
To unsubscribe from further messages, please visit https://sourceforge.net/auth/prefs/