Query with column called "date" in SQL Anywhere 11

Question

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.

Answer 1

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

Answer 2

DATE is a reserved word in SQL. Either escape it using quotes (not sure which in SQL Anywhere), or better yet: Rename your column name. Date should not be a column name. Try Billing_Date or Entry_Date or something similar.

For future reference, here's a list of reserved keywords in SQL: https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words

Hope this helps with your problem!