• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'

 
Benarjee Gorikapudi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi I am able to run the below query in SQL Server 2012 without any issues

select pid
from mydb.dbo.analysis 
where subject_key = ? and cast(my_date as date) = cast(? as date) 
order by my_date desc

But when this is executed through Java code, I am seeing below exception.

java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'

Here is how my query is defined:
<entry key="scanid">
    <bean class="com.myorg.prod.model.Select">
        <property name="expression" value="pid" />
        <property name="from" value="mydb.dbo.analysis " />
        <property name="where" value="subject_key = ? and cast(my_date as date) = cast(? as date)  order by my_date desc " />
        <property name="params" value="subject_key,acq_date" />
    </bean>
</entry>

I see this problem when I am using CAST or CONVERT functions to convert and compare the dates.

Any help would be much appreciated!
 
Tim Moores
Bartender
Posts: 3137
50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, who knows what the Java code that somehow transforms this XML into a DB call does?

Try writing a short test code that uses a PreparedStatement to execute the query, and see what happens.
 
Benarjee Gorikapudi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried that and the query works fine.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what does com.myorg.prod.model.Select class do with the given properties?
 
Benarjee Gorikapudi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That class is passed to a query builder which then constructs the actual query

 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And what does the query look like?
I don't see any logging in there to show what has been produced.
 
Benarjee Gorikapudi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am printing the query in the calling method.
Here is what the query and the exception i see
java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
Query:
at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:542)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:399)
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that query looks wrong, with all that garbage at the end of it.
 
Benarjee Gorikapudi
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Parameters: [1JT10212T00181984, 2009-01-02]

This is actually the input params  to the query. Not part of the query
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK.
It helps if the value posted actually matches the String being used.

We are reliant on the information you provide.

Have you tried a simple JDBC test harness that creates a PreparedStatement with that SQL string and executes it with those values?
 
Knute Snortum
Bartender
Pie
Posts: 2908
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I'm not sure this is something you can do with a prepared statement.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was wondering that one myself.

The whole casting thing implies the data (on both sides) is the wrong datatype.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic