Cannot parse "TIMESTAMP" constant "aced000573720014..."

[Joel Byrnes]

Hi,

I'm getting a very strange parsing error for a TIMESTAMP value that should be completely normal, and in fact it works in the IDE, this only occurs when I run the script on the command line in Gradle.

My code is in Groovy. It is essentially running a query on an Oracle database, then inserting the results into a H2 database for reporting.

The whole thing is:

Caused by: org.h2.jdbc.JdbcSQLException: Cannot parse "TIMESTAMP" constant "aced0005737200146f7261636c652e73716c2e54494d455354414d50917795c29955641f020000787200106f7261636c652e73716c2e446174756d4078f514a362286f0200015b0004646174617400025b427870757200025b42acf317f8060854e002000078700000000b7872040b0e29101f9f8138"; SQL statement:
INSERT INTO EVENTS(EV_ID, OPERATION_REFERENCE,CREATION_DATE_DB,CREATION_DATE_APP,.....) -- (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28) [22007-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
        at org.h2.message.DbException.get(DbException.java:161)
        at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:87)
        at org.h2.value.Value.convertTo(Value.java:843)
        at org.h2.table.Column.convert(Column.java:145)
        at org.h2.command.dml.Insert.insertRows(Insert.java:144)
        at org.h2.command.dml.Insert.update(Insert.java:115)
        at org.h2.command.CommandContainer.update(CommandContainer.java:79)
        at org.h2.command.Command.executeUpdate(Command.java:253)
        at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:193)

I even added some debugging output to ensure the value being passed was sane:

CREATION_DATE_APP: 2014-04-11 13:40:15.528
CREATION_DATE_DB: 2014-04-11 13:40:15.530547
CREATION_DATE_DB timestampValue: 2014-04-11 13:40:15.530547
CREATION_DATE_APP timestampValue.time: 1397187615528
CREATION_DATE_DB timestampValue.time: 1397187615530
CREATION_DATE_DB fixed: 2014-04-11 13:40:15.530

and either the plain or "fixed" (parsed by SimpleDateFormat to remove nanoseconds) version gives the same error.

The problem essentially seems to be that the values are getting mangled at some point, perhaps being interpreted as a different encoding, having been passed a map of values. The crux of the insert statement is:

        def insertStmt = "INSERT INTO EVENTS(EV_ID, " +
                "OPERATION_REFERENCE,CREATION_DATE_DB,CREATION_DATE_APP,....) VALUES ( :EV_ID, " +
                ":OPERATION_REFERENCE,:CREATION_DATE_DB,:CREATION_DATE_APP,.....)"

The only TIMESTAMP values are the CREATION_DATE_* ones.


Has anyone seen anything like this, do you have any suggestions or things to try?

TIA,
Joel

[Noel Grandin]

On the preparedstatement, are you calling setTimestamp or something else? Because we sometimes get confused if you call setObject or some other method. 

If that doesn't help, if you can generate a self contained test-case I should be able to debug it on Monday. 

-- Noel. 

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Noel Grandin]

On the preparedstatement, are you calling setTimestamp or something else? Because we sometimes get confused if you call setObject or some other method. 

If that doesn't help, if you can generate a self contained test-case I should be able to debug it on Monday. 

-- Noel. 

On Friday, 11 April 2014, Joel Byrnes <fatal.except...@gmail.com> wrote:

[Joel Byrnes]

Hi, thanks for your response, I've had some time to do as you suggest and create a test case. In doing so, I've narrowed down the problem :)

I can give you my code by I'm not sure how useful it will be as you don't have my source database, but perhaps if you have an Oracle (11g) database you can create a similar table with test data. Obviously I've redacted the connection string.

Part of the problem may be that my gradle dependencies were pulling in an earlier ojdbc 10.2 version instead of the ojdbc6 11.2 version I declared. However, excluding the transitive dep didn't fix it.

Ultimately the problem seems to be that under the command-line gradle version, the timestamp field was not an instanceof TIMESTAMP, as seen in fixRow which was supposed to handle arbitrary wonky date fields just like this. The fix is to just explicitly convert the field to string.

I just love errors like this: Caused by: org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object '2014-03-19 20:10:34.920759' with class 'oracle.sql.TIMESTAMP' to class 'oracle.sql.TIMESTAMP'

So it's a oracle.sql.TIMESTAMP, but just... a different one. Somehow.

Hope this helps someone.

Cheers
Joel

import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import oracle.sql.TIMESTAMP
import java.text.SimpleDateFormat

class EventsCopierExample {

    static void main(String[] args) {
        new EventsCopierExample().run()
    }

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S")

    def run() {
        println "Opening database in memory"
        def dbUrl = "jdbc:h2:mem:inserttest"
        def db = Sql.newInstance(dbUrl, "org.h2.Driver")

        println "Creating table 'events'"

        def createStmt = "CREATE TABLE EVENTS( " +
                "    EV_ID NUMBER(10) PRIMARY KEY, " +
                "    OPERATION_REFERENCE VARCHAR2(37), " +
                "    CREATION_DATE_DB TIMESTAMP(6), " +
                "    CLIENT_APPLICATION_NAME VARCHAR2(40) ) "
        db.execute(createStmt)

        def sourceDb = Sql.newInstance("jdbc:oracle:thin:@FOOBAR:1526:FOOBAR", "FOOBAR", "FOOBAR", "oracle.jdbc.OracleDriver")

        println "connected to source DB, querying"

        def rows = sourceDb.rows("select EV_ID, OPERATION_REFERENCE, CREATION_DATE_DB, CLIENT_APPLICATION_NAME " +
                " from vedct_event where rownum < 10")

        rows.each {
            def t = it.CREATION_DATE_DB //as TIMESTAMP
            println "${t.class} ${t} string: ${t.toString()} date: ${t.toDate()} timestamp: ${t.timestampValue()}"
            it.CREATION_DATE_DB = it.CREATION_DATE_DB.toString()
        }

//        rows = rows.collect { fixRow(it) }
//        rows.each { println it.CREATION_DATE_DB }

        db.withTransaction {
            def insertStmt = "INSERT INTO EVENTS(EV_ID, OPERATION_REFERENCE,CREATION_DATE_DB,CLIENT_APPLICATION_NAME) " +
                    " VALUES ( :EV_ID, :OPERATION_REFERENCE, :CREATION_DATE_DB, :CLIENT_APPLICATION_NAME) "

            db.withBatch(rows.size(), insertStmt) { ps ->
                  rows.each {
                      ps.addBatch(it)
                  }
            }
        }

        println "events: " + db.firstRow("select count(*) count from events").count
        db.eachRow("select * from events") { println it }
    }

    Map<?, ?> fixRow(GroovyRowResult row) {
        // we want "2013-09-05 05:12:46.855856"
        def map = row.collectEntries { k, v ->
            if (v instanceof TIMESTAMP) [k, sdf.format(new Date(v.timestampValue().time))]
            else [k, v]
        }
        map

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Joel Byrnes]

Of course, if you have a better way to copy rows from one database to another, being that they have virtually the same definition, I'm all ears :)

[Thomas Mueller]

Hi,

What you could do is (all in H2):

    create linked table ora_events(

        'oracle.jdbc.OracleDriver', 

        'jdbc:oracle:thin:@FOOBAR:1526:FOOBAR',

        'FOOBAR', 'FOOBAR',

        '(select EV_ID, OPERATION_REFERENCE, CREATION_DATE_DB, ' ||

        'CLIENT_APPLICATION_NAME ' || 

        'from vedct_event where rownum < 10)');

    create table events(...) as select * from ora_events;

    drop table ora_events;

Please note I didn't test it, there might be bugs. See also http://h2database.com/html/grammar.html#create_linked_table

Regards,

Thomas

On Thu, Apr 17, 2014 at 5:01 AM, Joel Byrnes <fatal.except...@gmail.com> wrote:

Of course, if you have a better way to copy rows from one database to another, being that they have virtually the same definition, I'm all ears :)

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Joel Byrnes]

Wow, the database can create a transparent connection to another database?? That's awesome! If you created more than one linked table, would it open a separate DB connection for each? And are the connections persistent or opened on read?

If the linked table is a view, can you read new inserts to that table, or is it essentially a materialised view/copy in the local DB? 

The oracle events table is a view of events, and what I'm really doing here is polling it for new events every so often by event ID, and then processing them in the local DB, deleting entries that are part of completed transactions and leaving others. Is there some way I could make this simpler/more transparent/automatic?

Thanks

Joel

[Thomas Mueller]

Hi,

The documentation for linked tables is here: http://h2database.com/html/advanced.html#linked_tables

> If you created more than one linked table, would it open a separate DB connection for each?

Both is supported, see the documentation.

> And are the connections persistent or opened on read?

Persisted.

> If the linked table is a view, can you read new inserts to that table, or is it essentially a materialised view/copy in the local DB?

It is dynamic.

> The oracle events table is a view of events, and what I'm really doing here is polling it for new events every so often by event ID, and then processing them in the local DB, deleting entries that are part of completed transactions and leaving others. Is there some way I could make this simpler/more transparent/automatic?

Well, it depends on what you need to do with the data. I don't know what I would do.

Regards,

Thomas