Apache camel getting Oracle database generated ID

Question

I am using a camel jdbc component to insert a record into an Oracle table. the insert uses a sequence to populate a primary key ID column.

INSERT INTO my_table (id, data) VALUES (my_seq.nextval, 'some data')

The relevant part of the route looks like below:

 from("some end point here")
     .process(preInsertProcessor)
     .to("jdbc:myDataSource")
     .process(new Processor() {
         public void process(Exchange exchange) throws Exception {
             LOGGER.info("Extracting database generated id");
             // This list is null
             List<Integer> ids = exchange.getIn().getHeader(
                   JdbcConstants.JDBC_GENERATED_KEYS_DATA, List.class);
       });

Inside the preInsertProcessir I set the message body to be my insert statement and also set some two header values to instruct camel I want the generated ID back:

    message.setBody("INSERT INTO my_table (id, data) VALUES (my_seq.nextval, ?:data)");
    message.setHeader("data", "some data");
    message.setHeader(JDBC_RETRIEVE_GENERATED_KEYS, true);
    message.setHeader(JDBC_GENERATED_COLUMNS, new String[]{"ID"});

End if I look at the logs I can see:

[DEBUG] org.apache.camel.component.bean.MethodInfo - Setting bean invocation result on the OUT message: [Message: INSERT INTO my_table(id, data)VALUES (my_seq.nextval, :?data]
[DEBUG] org.apache.camel.spring.spi.TransactionErrorHandler - Transaction begin (0x1de4bee0) redelivered(false) for (MessageId: ID-MELW1TYGC2S-62650-1438583607644-0-8 on ExchangeId: ID-MELW1TYGC2S-62650-1438583607644-0-9))
[INFO ] au.com.nab.cls.router.non.repudiation.GeneratedIdExtractor - Extracting database generated id
[DEBUG] org.apache.camel.processor.MulticastProcessor - Done sequential processing 1 exchanges
[DEBUG] org.apache.camel.spring.spi.TransactionErrorHandler - Transaction commit (0x1de4bee0) redelivered(false) for (MessageId: ID:414d5120445041594855423120202020027844552045b302 on ExchangeId: ID-MELW1TYGC2S-62650-1438583607644-0-7))

If I get it well from the look of the logs the insert would be executed and my final processor should be able to get the generated ID. In reality what happens is that no record gets inserted and no ID is present in the header of the message. Without the final processor everything works fine.

Obviously I am doing something wrong here but I cannot figure out what. I am aware I could use a message en-richer to get the ID before the insert but I would prefer to avoid an extra database trip.

Thank you in advance for your inputs.

UPDATE I put a break point in org.apache.camel.component.jdbc.JdbcProducer and found out the reason for not having the INSERT executed and consequently not getting the ID back.

// JdbcProducer code; creating a prepared statement part
if (shouldRetrieveGeneratedKeys) {
...
    } else if (expectedGeneratedColumns instanceof String[]) {
        // Execution gets herestatement
        ps = conn.prepareStatement(preparedQuery, (String[]) expectedGeneratedColumns);
...
}
// Expected count returned here is 2
int expectedCount = ps.getParameterMetaData().getParameterCount();
if (expectedCount > 0) {
    ...
    // And here I get the crash:
    // java.sql.SQLException: Number of parameters mismatch. Expected: 2, was:1
    getEndpoint().getPrepareStatementStrategy().populateStatement(ps, it, expectedCount);
 }

This is where my research stopped as digging too much in the various three parties code is not really easy. I suspect one of the following two options are the cause:

I am still not doing it the right way
A camel bug which does not work as expected when header contains both named parameters and retrieve generated keys settings

Please advise about any fix or work around.

Thanks again

You can use tracer to see what headers you have: camel.apache.org/tracer or do some java debug and set a breakpoint in that processor code to inspect the exchange. — Claus Ibsen
Thanks Claus. Setting a break point and inspecting the exchange was the very first thing I did. To me it was a surprise that the exchange.getIn() came up with the INSERT statement and the exchange.getOut() returned basically and empty message. It was just like the break point was set before sending the message to jdbc:myDataSource rather than after. But on the other hand the log entries contradicted my feelings so I was no longer sure. — Julian
Did anyone find a solution for this. I'm getting the same error — SashikaXP
Unfortunately not so far. My plans are to come back to this issue at some point and try to do more digging but my first impression is that this may be a camel bug — Julian

PascalSchumacher PascalSchumacher · Accepted Answer · 2017-09-21T08:26:41

I also ran into this. My workaround:

Use camel-sql instead of camel-jdbc. Add parametersCount option to the endpoint URL (in adddition to setHeader(SqlConstants.SQL_RETRIEVE_GENERATED_KEYS, constant(true)) and setHeader(SqlConstants.SQL_GENERATED_COLUMNS, constant(new String[] {"ID_COLUMN_NAME"})).

Update: Works with 11.2.0.4 jdbc driver (does not work with 12.2.0.1 jdbc driver).

Apache camel getting Oracle database generated ID

1 Answers