• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried that and the query works fine.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So what does com.myorg.prod.model.Select class do with the given properties?
 
Benarjee Gorikapudi
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That class is passed to a query builder which then constructs the actual query

 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, that query looks wrong, with all that garbage at the end of it.
 
Benarjee Gorikapudi
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Sheriff
Posts: 7125
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I'm not sure this is something you can do with a prepared statement.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Put the moon back where you found it! We need it for tides and poetry and stuff. Like this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic