Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 80B5119714 for ; Wed, 6 Apr 2016 13:57:26 +0000 (UTC) Received: (qmail 35797 invoked by uid 500); 6 Apr 2016 13:57:26 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 35769 invoked by uid 500); 6 Apr 2016 13:57:26 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 35644 invoked by uid 99); 6 Apr 2016 13:57:25 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 06 Apr 2016 13:57:25 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 9D3B62C1F69 for ; Wed, 6 Apr 2016 13:57:25 +0000 (UTC) Date: Wed, 6 Apr 2016 13:57:25 +0000 (UTC) From: "Bryan Pendleton (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Comment Edited] (DERBY-6880) Update failing with java.sql.SQLDataException MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6880?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15228281#comment-15228281 ] Bryan Pendleton edited comment on DERBY-6880 at 4/6/16 1:56 PM: ---------------------------------------------------------------- That's a really interesting perspective, thank you for suggesting it! The JavaDoc in question, I think, is for Statement.getGeneratedKeys: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() It says: {quote} Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned. Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys. {quote} Which isn't a lot of detail. But, to me, it clearly uses the phrases {quote} auto-generated keys created as a result {quote} and {quote} generate any keys {quote} To me, that specifically means that the update must cause a *NEW* value for a column which is "GENERATED ... AS IDENTITY" to be generated by the statement, and the reproduction's statement: {quote} update pipeline_command set status = 'WAIT RESULT' where id = ? {quote} only updates the "status" column, and does not cause any new values to be generated for the "id" column. However, while doing a bit of web searching, I came across this page from the DB2 documentation set: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0057054.html which suggests a thoroughly different reading of how Statement.getGeneratedKeys should behave, stating that: {quote} The following code names the EMPNO column as an automatically generated key, updates the thirty rows in the EMP_BONUS table, and retrieves the values of EMPNO for the updated rows. {quote} The statement in the DB2 example, like the statement in this issue's reproduction, does not modify nor create new values for any automatically generated keys, but the DB2 example suggests that Statement.getGeneratedKeys is intended to return the generated key values of *other* columns in the rows which were updated, even if those columns where not actually created or updated by the statement. It's a bit of a puzzle, actually. But, your comment does suggest some new test cases that I might right (thank you!) I'll try to find some time to dig into that some more. In the meantime, if anyone knows of any more information about how Statement.getGeneratedKeys is intended to behave, that would be welcomed. was (Author: bryanpendleton): That's a really interesting perspective, thank you for suggesting it! The JavaDoc in question, I think, is for Statement.getGeneratedKeys: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys() It says: Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned. Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys. Which isn't a lot of detail. But, to me, it clearly uses the phrases auto-generated keys created as a result and generate any keys To me, that specifically means that the update must cause a *NEW* value for a column which is "GENERATED ... AS IDENTITY" to be generated by the statement, and the reproduction's statement: update pipeline_command set status = 'WAIT RESULT' where id = ? only updates the "status" column, and does not cause any new values to be generated for the "id" column. However, while doing a bit of web searching, I came across this page from the DB2 documentation set: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0057054.html which suggests a thoroughly different reading of how Statement.getGeneratedKeys should behave, stating that: The following code names the EMPNO column as an automatically generated key, updates the thirty rows in the EMP_BONUS table, and retrieves the values of EMPNO for the updated rows. The statement in the DB2 example, like the statement in this issue's reproduction, does not modify nor create new values for any automatically generated keys, but the DB2 example suggests that Statement.getGeneratedKeys is intended to return the generated key values of *other* columns in the rows which were updated, even if those columns where not actually created or updated by the statement. It's a bit of a puzzle, actually. But, your comment does suggest some new test cases that I might right (thank you!) I'll try to find some time to dig into that some more. In the meantime, if anyone knows of any more information about how Statement.getGeneratedKeys is intended to behave, that would be welcomed. > Update failing with java.sql.SQLDataException > ---------------------------------------------- > > Key: DERBY-6880 > URL: https://issues.apache.org/jira/browse/DERBY-6880 > Project: Derby > Issue Type: Bug > Affects Versions: 10.12.1.1 > Reporter: Simon Zee > Attachments: repro.diff, standalone.java, undo6742.diff > > > When updating a single column in a table using executeUpdate() via Vert.x I am receiving the following exception: > java.sql.SQLDataException: Invalid character string format for type long. > at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:84) > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:233) > at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424) > at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353) > at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405) > at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88) > at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1432) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1709) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeLargeUpdate(EmbedPreparedStatement.java:320) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:309) > at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410) > at io.vertx.ext.jdbc.impl.actions.JDBCUpdate.execute(JDBCUpdate.java:50) > at io.vertx.ext.jdbc.impl.actions.JDBCUpdate.execute(JDBCUpdate.java:34) > at io.vertx.ext.jdbc.impl.actions.AbstractJDBCAction.handle(AbstractJDBCAction.java:48) > at io.vertx.ext.jdbc.impl.actions.AbstractJDBCAction.handle(AbstractJDBCAction.java:33) > at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$15(ContextImpl.java:296) > at io.vertx.core.impl.OrderedExecutorFactory$OrderedExecutor.lambda$new$261(OrderedExecutorFactory.java:91) > at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > at java.lang.Thread.run(Thread.java:745) > Caused by: ERROR 22018: Invalid character string format for type long. > at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290) > at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:285) > at org.apache.derby.iapi.types.SQLChar.getLong(SQLChar.java:447) > at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:534) > at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:272) > at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:473) > at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:352) > at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1340) > ... 13 more > Further details and discussion can be found here: > https://mail-archives.apache.org/mod_mbox/db-derby-user/201603.mbox/%3CCAHbUnCXkHMKE1u9R3D-z9Njp8goAV7%2B0vPOmgafH8DCqG8mSpQ%40mail.gmail.com%3E > Notably, I have tried executing the same update via other means (for example, manually via SquirrelSQL, and via the ORMLite framework) and the update succeeds. This may be due to the exact JDBC APIs they are using (for example, SquirrelSQL is not using a prepared statement, and ORMLite updates all the columns when it updates a table rather than just some of them). > I have created a complete but minimal example that illustrates the problem in the following GitHub project: > https://github.com/ssadedin/DerbyDebug > My derby / system information is as follows: > $ java -cp 'lib/*' org.apache.derby.tools.sysinfo > ------------------ Java Information ------------------ > Java Version: 1.8.0_72 > Java Vendor: Oracle Corporation > Java home: /Library/Java/JavaVirtualMachines/jdk1.8.0_72.jdk/Contents/Home/jre > Java classpath: lib/derby.jar:lib/derbyclient.jar > OS name: Mac OS X > OS architecture: x86_64 > OS version: 10.11.1 > Java user name: simon > Java user home: /Users/simon > Java user dir: /Users/simon/Documents/workspace/BrokenDerby > java.specification.name: Java Platform API Specification > java.specification.version: 1.8 > java.runtime.version: 1.8.0_72-b15 > --------- Derby Information -------- > [/Users/simon/Documents/workspace/BrokenDerby/lib/derby.jar] 10.12.1.1 - (Unversioned directory) > [/Users/simon/Documents/workspace/BrokenDerby/lib/derbyclient.jar] 10.12.1.1 - (Unversioned directory) > ------------------------------------------------------ > ----------------- Locale Information ----------------- > ------------------------------------------------------ > ------------------------------------------------------ -- This message was sent by Atlassian JIRA (v6.3.4#6332)