db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6880) Update failing with java.sql.SQLDataException
Date Wed, 06 Apr 2016 13:57:25 GMT

    [ 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)

Mime
View raw message