camel-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Claus Ibsen (JIRA)" <j...@apache.org>
Subject [jira] [Assigned] (CAMEL-7653) camel-sql endpoint oracle failure
Date Mon, 04 Aug 2014 06:53:12 GMT

     [ https://issues.apache.org/jira/browse/CAMEL-7653?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Claus Ibsen reassigned CAMEL-7653:
----------------------------------

    Assignee: Claus Ibsen

> camel-sql endpoint oracle failure
> ---------------------------------
>
>                 Key: CAMEL-7653
>                 URL: https://issues.apache.org/jira/browse/CAMEL-7653
>             Project: Camel
>          Issue Type: Bug
>          Components: camel-sql
>    Affects Versions: 2.12.0
>            Reporter: Jo Geraerts
>            Assignee: Claus Ibsen
>             Fix For: 2.12.5, 2.13.3, 2.14.0
>
>
> I use the sql endpoint against oracle, however the {{DefaultSqlPrepareStatementStrategy}}
uses {{PreparedStatement.setObject}} to set all the named parameters. With most jdbc drives
this works fine except for oracle. Oracle throws an ORA-17004 exception stating an invalid
column type. 
> My route looks like this
> {code}
> from("{{ktn.stkbal.in")
>                 .routeId("KTN.StkBal")
>                 .onCompletion().onCompleteOnly()
>                     .to("seda:generatestockbalancereport")
>                 .end()
>                 .setHeader("ts",constant(new Timestamp(new Date().getTime())))
>                 .split().tokenizeXML("product","stockPicture").streaming()
>                     .setHeader("ean",xpath("/st:product/@ean").resultType(String.class).namespaces(KTNConstants.STOCK_NAMESPACES))
>                     .setHeader("available", xpath("/st:product/st:available").resultType(Integer.class).namespaces(KTNConstants.STOCK_NAMESPACES))
>                     .setHeader("blocked",xpath("/st:product/st:blocked").resultType(Integer.class).namespaces(KTNConstants.STOCK_NAMESPACES))
>                     .to("sql:insert into stockbalance(ean,available,blocked,picturets)
values (:#ean,:#available,:#blocked,:#ts)?dataSourceRef=hybrisDataSource");
> {code}
> And from our dear oracle i get
> {code}
> 23:34:20,821 | TRACE | 2/STKBAL/inbound | faultSqlPrepareStatementStrategy | 286 - org.apache.camel.camel-sql
- 2.12.0.redhat-610379 | Prepared query: insert into stockbalance(ean,available,blocked,picturets)
values (?,?,?,?)
> 23:34:20,824 | TRACE | 2/STKBAL/inbound | faultSqlPrepareStatementStrategy | 286 - org.apache.camel.camel-sql
- 2.12.0.redhat-610379 | Setting parameter #1 with value: 10000000000001
> 23:34:20,825 | TRACE | 2/STKBAL/inbound | faultSqlPrepareStatementStrategy | 286 - org.apache.camel.camel-sql
- 2.12.0.redhat-610379 | Setting parameter #2 with value: 2
> 23:34:20,826 | TRACE | 2/STKBAL/inbound | faultSqlPrepareStatementStrategy | 286 - org.apache.camel.camel-sql
- 2.12.0.redhat-610379 | Setting parameter #3 with value: 5
> 23:34:20,828 | TRACE | 2/STKBAL/inbound | faultSqlPrepareStatementStrategy | 286 - org.apache.camel.camel-sql
- 2.12.0.redhat-610379 | Setting parameter #4 with value: Wed Jul 30 23:33:18 CEST 2014
> 23:34:20,831 | ERROR | 2/STKBAL/inbound | DefaultErrorHandler              | 142 - org.apache.camel.camel-core
- 2.12.0.redhat-610379 | Failed delivery for (MessageId: ID-tst-esb-inno1-41429-1406755976005-6-7
on ExchangeId: ID-tst-esb-inno1-41429-1406755976005-6-9). Exha
> usted after delivery attempt: 1 caught: org.springframework.jdbc.UncategorizedSQLException:
PreparedStatementCallback; uncategorized SQLException for SQL [insert into stockbalance(ean,available,blocked,picturets)
values (?,?,?,?)]; SQL state [99999]; error code [17004];
> Invalid column type; nested exception is java.sql.SQLException: Invalid column type
> Message History
> ---------------------------------------------------------------------------------------------------------------------------------------
> RouteId              ProcessorId          Processor                                 
                                      Elapsed (ms)
> [KTN.StkBal        ] [KTN.StkBal        ] [sftp://TSTKATOEN2341@<edited>/STKBAL/inbound?antExcl]
[        21]
> [KTN.StkBal        ] [setHeader69       ] [setHeader[ean]                           
                                    ] [         2]
> [KTN.StkBal        ] [setHeader70       ] [setHeader[available]                     
                                    ] [         1]
> [KTN.StkBal        ] [setHeader71       ] [setHeader[blocked]                       
                                    ] [         1]
> [KTN.StkBal        ] [to107             ] [sql:insert into stockbalance(ean,available,blocked,picturets)
values (:#ean,:#] [         9]
> Stacktrace
> ---------------------------------------------------------------------------------------------------------------------------------------
> org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized
SQLException for SQL [insert into stockbalance(ean,available,blocked,picturets) values (?,?,?,?)];
SQL state [99999]; error code [17004]; Invalid column type; nested exception is
>  java.sql.SQLException: Invalid column type
>         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)[285:org.apache.servicemix.bundles.spring-jdbc:3.2.8.RELEASE_1]
>         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[285:org.apache.servicemix.bundles.spring-jdbc:3.2.8.RELEASE_1]
>         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)[285:org.apache.servicemix.bundles.spring-jdbc:3.2.8.RELEASE_1]
> {code}
> As workaround i extended the {{DefaultSqlPrepareStatementStrategy}} and override {{populateStatement}}.
It leverages springs {{ArgumentPreparedStatementSetter}} to do the right thing.  
> {code}
>     @Override
>     public void populateStatement(PreparedStatement ps, Iterator<?> iterator, int
expectedParams) throws SQLException {
>         final Object[] args = new Object[expectedParams];
>         int i = 0 ;
>         while (iterator != null && iterator.hasNext()) {
>             args[i]=iterator.next();
>             i++;
>         }
>         final ArgumentPreparedStatementSetter setter = new ArgumentPreparedStatementSetter(args);
>         setter.setValues(ps);
>     }
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message