Search code examples
javasqlanywherejconnect

Query with column called "date" in SQL Anywhere 11

I have a table on a SQL Anywhere 11 DB where one of the columns is called "date". I am using jconnect JDBC drivers to connect to the DB from my Java code. I have quoted the "date" column with double quotes as shown below.

SELECT inv."date" FROM DBA.acc_invoicereturn inv

I have 2 database files, one is a copy of a running SQL Anywhere 11 instance. The other is from a SQL Anywhere 5 DB which I unloaded and reloaded(using dbunload) into a SA 11 DB. When I run this query on the first instance, it runs fine. But, the one that I upgraded, I get the below error.

com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -131: Syntax error near 'date' on line 4 
    at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
    at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
    at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeQuery(Unknown Source)

These DBs are part of billing systems deployed in some stores. I thought it was something to do with the db upgrade that I did, but, I encountered the same error when connecting to some production instances as well.

I think this has something to do with the quoted_string configuration setting(which I am not touching at all), but I am not sure why it works in the first case and not the second.

Any help with this is greatly appreciated.

Regards, Parikshit.

Solution

  • date can be used as a column name by escaping it within [ ]