Quantcast

SQLDataException when updating rows (Vert.x)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQLDataException when updating rows (Vert.x)

simonz
Hello all,

I am not completely sure if this problem is to do with Vert.x and its JDBC wrappers or Derby 10.12.1.1. 

My table is defined like this:

        create table pipeline_command (
               id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) primary key,
               pipeline_id integer references pipeline(id),
               pguid varchar(40),
               command varchar(512),
               arguments varchar(512),
               status varchar(15)
        )    

I try to perform a simple update of a table using Vert.x:

    update pipeline_command set status = 'WAIT_RESULT' where id = ?

Derby throws an error while trying to interrogate the result set for updated rows:

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

What's happening seems to be an inconsistent expectation about what is in the result set.

In the code below from UpdateResultSet.java Derby interrogates the columns of the table and correctly decides the first column is auto increment:

    for(col=1;col<=maxColumns;col++)
    {
        ColumnDescriptor cd = td.getColumnDescriptor(col);
        if(cd.isAutoincrement())
        {
            break;
        }
    }

However it then tries to use that column value on the result set. which has only 3 columns - they are the original value, the modified value and the indices of the updated columns.

    if(col <= maxColumns)
    {
       DataValueDescriptor dvd = row.cloneColumn(col);
       identityVal = dvd.getLong();
    }



It addresses the wrong column (the modified column is type VARCHAR, so it gets back a VARCHAR column and tries to call getLong() on it), and throws the above exception.

I'm not sure if this is because Vert.x is calling something wrong, or if I'm defining the table wrong or something else.

I would very much appreciate any help!

Cheers / thanks!

Simon
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLDataException when updating rows (Vert.x)

Bryan Pendleton-3
> I am not completely sure if this problem is to do with Vert.x and its JDBC wrappers or Derby 10.12.1.1.

Me either.

> Derby throws an error while trying to interrogate the result set for updated rows:
>

> Caused by: ERROR 22018: Invalid character string format for type long.

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

Well, that code in collectAffectedRows is new with Derby 10.12 (DERBY-6742),
so you certainly might have found a new bug in Derby.

Is it possible for you to run your program against Derby 10.11?
If so, does the problem disappear (that would be a strong clue)?

> What's happening seems to be an inconsistent expectation about what is in the result set.

> It addresses the wrong column

Your analysis is very interesting, and I think you have explained the
behavior quite plausibly.

Is there any chance that you can construct a standalone test case
that demonstrates this behavior?

Ideally, a Minimal Complete Verifiable Example (http://stackoverflow.com/help/mcve)
would be wonderful, but anything you can do to help simplify the reproduction
of the problem will go a long ways to those developers trying to
track down and fix it.

Possibly, running your test case with statement logging and query plans
enabled (http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) will
give you some clues about how to construct a simpler reproduction case.

At the very least, please log a bug against Derby in the issue tracker
(http://db.apache.org/derby/DerbyBugGuidelines.html) with as much
information as you can provide.

thanks,

bryan

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLDataException when updating rows (Vert.x)

simonz
Thanks Bryan,

I'll try and get a complete test case worked up. 

One thing I'll note is that I don't get this error when I execute the update via other means. For example, if I do it directly via SquirrelSQL, it's fine. That's why I wonder if Vert.x is doing something wrong. However Vert.x is using a generic JDBC wrapper that is used with heaps of other drivers just fine, so it seems odd if it is at fault, that it's not causing problems elsewhere.

Cheers,

Simon


On Mon, Mar 28, 2016 at 3:36 AM, Bryan Pendleton <[hidden email]> wrote:
I am not completely sure if this problem is to do with Vert.x and its JDBC wrappers or Derby 10.12.1.1.

Me either.

Derby throws an error while trying to interrogate the result set for updated rows:


Caused by: ERROR 22018: Invalid character string format for type long.

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)

Well, that code in collectAffectedRows is new with Derby 10.12 (DERBY-6742),
so you certainly might have found a new bug in Derby.

Is it possible for you to run your program against Derby 10.11?
If so, does the problem disappear (that would be a strong clue)?

What's happening seems to be an inconsistent expectation about what is in the result set.

It addresses the wrong column

Your analysis is very interesting, and I think you have explained the
behavior quite plausibly.

Is there any chance that you can construct a standalone test case
that demonstrates this behavior?

Ideally, a Minimal Complete Verifiable Example (http://stackoverflow.com/help/mcve)
would be wonderful, but anything you can do to help simplify the reproduction
of the problem will go a long ways to those developers trying to
track down and fix it.

Possibly, running your test case with statement logging and query plans
enabled (http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) will
give you some clues about how to construct a simpler reproduction case.

At the very least, please log a bug against Derby in the issue tracker
(http://db.apache.org/derby/DerbyBugGuidelines.html) with as much
information as you can provide.

thanks,

bryan


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLDataException when updating rows (Vert.x)

Bryan Pendleton-3
> One thing I'll note is that I /don't/ get this error when I execute the update via other means.

Perhaps, those other tools (Squirrel, etc.) are not using the special
form of Statement.executeUpdate() which requests that the auto-generated
keys should be made available, while the Vert.x framework is.

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)

bryan


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLDataException when updating rows (Vert.x)

simonz
Ok - here's my attempt at making a fairly minimal reproducible example.


Cheers,

Simon 

On Tue, Mar 29, 2016 at 11:24 AM, Bryan Pendleton <[hidden email]> wrote:
One thing I'll note is that I /don't/ get this error when I execute the update via other means.

Perhaps, those other tools (Squirrel, etc.) are not using the special
form of Statement.executeUpdate() which requests that the auto-generated
keys should be made available, while the Vert.x framework is.

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)

bryan



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLDataException when updating rows (Vert.x)

simonz
Hello all,

Just one more note, I made an issue for this problem:


Cheers,

Simon


On Tue, Mar 29, 2016 at 9:44 PM, Simon <[hidden email]> wrote:
Ok - here's my attempt at making a fairly minimal reproducible example.


Cheers,

Simon 

On Tue, Mar 29, 2016 at 11:24 AM, Bryan Pendleton <[hidden email]> wrote:
One thing I'll note is that I /don't/ get this error when I execute the update via other means.

Perhaps, those other tools (Squirrel, etc.) are not using the special
form of Statement.executeUpdate() which requests that the auto-generated
keys should be made available, while the Vert.x framework is.

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20int)

bryan




Loading...