Select containing quote does not work

[philipk]

I have created a batch with a JdbcCursorItemReader and sql set as shown

<property name="sql" value="select * from V_EXPORT_MELT where SP_ACTOR_ID=1 and SEND_TYPE='N'"/>

The query gives no errors but returns no data, although the same query run from sqldeveloper does return data. The query works properly if the and SEND_TYPE='N' is removed.

Does anyone have any idea how to implement a query containing a quote? The SEND_TYPE field is a CHAR(1) and the query gives an error without the single quotes.

Philip

[dma_k]

I hope you connect to the same DB in Java and in SQLDeveloper (check count(*) for example). Your SQL looks to be valid and accepted by DB (although you can still use " to represent " in attributes), but it returns no records, right? What DB are you running? I can't imagine this happens to e.g. MySQL...

[philipk]

It is an Oracle database, and it could be a driver problem, I have just discovered that Oracle has multiple versions of the jdbc driver with the same file name, and it is necessary to look in the manifest file to see which Oracle version is implemented.

Logging the connection metadata as shown below I found that I was not using the latest driver. I shall try again with an updated driver.

2011-03-22 09:39:11,093 DEBUG [RechargeReadingQuerySetter] - <Database Name: Oracle>
2011-03-22 09:39:11,093 DEBUG [RechargeReadingQuerySetter] - <Database Version: Oracle Database 11g Release 11.1.0.0.0 - Production>
2011-03-22 09:39:11,093 DEBUG [RechargeReadingQuerySetter] - <Driver Name: Oracle JDBC driver>
2011-03-22 09:39:11,093 DEBUG [RechargeReadingQuerySetter] - <Driver Version: 10.2.0.2.0>

[philipk]

I replaced the ojdbc6.jar I was using with the ojdbc6.jar for Oracle11 and the query started working.

2011-03-22 14:02:30,671 DEBUG [RechargeReadingQuerySetter] - <Driver Version: 11.1.0.7.0-Production>

It is pretty annoying that the query stopped working with no signs of error, and could happen again if somebody else changes the driver on the server. Of course I can include the driver in the batch jar, but then if a new driver is required I will have to rerelease all the batch jars.