Re: syntax error on escape sequences with bind variables

Lenny,
We spent some time looking at this issue. We were able to recreate your
problem using a smaller sql statement. We used
select count( * ) from datetable where fn YEAR(tran_date) >= fn YEAR({d
?})

According to the SQL Server books online, the YEAR function takes a
string argument. By using the date escape syntax above you are trying to
pass a date to the YEAR function. When we change the parameter in the YEAR
function to be a string using the syntax "select count( * ) from datetable
where fn YEAR(tran_date) >= fn YEAR(?)" and then binding the value with
setString, the query works.

Can you test this out for us to see if it addresses your issue? Is there
some particular functionality you are looking for by passing the date escape
syntax to the YEAR function?

Thanks.

Sue Purkis
DataDirect Technologies


<lpalozzi@xxxxxxxxx> wrote in message
news:1116264061.117026.71970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi,
>
> I'm getting the following syntax error while preparing my statement. I
> believe the problem is caused by the use of bind variables within JDBC
> escape sequences, eg. {fn YEAR( {d ?} )}.
>
> JDBC driver is:
>
> Microsoft SQL Server 2000 Driver for JDBC
> Service Pack 3
> Version 2.2.0040
> May 2004
>
>
> Note that the Oracle JDBC drivers parse and execute this just fine. Is
> this a bug? Is there a work around?
>
> See query and stack trace below.
>
> select count( * ) from ( select count( * ) as cnt from tablename where
> {fn YEAR (tran_date)} >= {fn YEAR( {d ? } )} and {fn YEAR (tran_date)}
> <= {fn YEAR( {d ? } )} and {fn MONTH(tran_date)} >= {fn MONTH( {d ?
> } )} and {fn MONTH(tran_date)} <= {fn MONTH( {d ? } )} and {fn
> DAYOFMONTH(tran_date)} >= {fn DAYOFMONTH( {d ? } )} and {fn
> DAYOFMONTH(tran_date)} <= {fn DAYOFMONTH( {d ? } )} ) temp
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC]Syntax error at token ?, line 0 offset 3.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.base.BaseEscapeTranslator.parseEscape(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseEscapeTranslator.translateEscape(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseSQLEscapeProcessor.visit(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseSQLTreeTraverser.visit(Unknown Source)
> at
> com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseSQLEscapeProcessor.processEscapes(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseSQLEscapeProcessor.visit(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseSQLTreeTraverser.visit(Unknown Source)
> at
> com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseSQLTreePreOrderTraverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseSQLEscapeProcessor.processEscapes(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseSQL.translateEscapes(Unknown Source)
> at com.microsoft.jdbc.base.BaseSQL.processSQL(Unknown Source)
> at com.microsoft.jdbc.base.BaseSQL.<init>(Unknown Source)
> at com.microsoft.jdbc.base.BaseStatement.preProcessSQL(Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.<init>(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseConnection.prepareStatement(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseConnection.prepareStatement(Unknown
> Source)
>
>
> Thanks,
>
> -Lenny
>


.

Relevant Pages

  • Re: recent drivel posted by Tony Rogerson on his blog
    ... Like I said, I want to search on terminology not on syntax - for instance, we call using the WITH syntax a 'common table expression' I've no idea what the equiv is in Oracle. ... the common SQL Standard term "derived table" ... Actually I tried and it returned 2,614,868 results back and not a useful link in sight for someone looking for the WITH SQL statement I was after? ... You guessed it, 42,185 links showing links on things to do with 'clause' ... ...
    (comp.databases.oracle.server)
  • Re: SQL Quotes Insanity !
    ... set Command1 = Server.CreateObject ... an INT in the SQL DB. ... syntax error so you can see the actual resulting sql statement. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Mailmerge, querystring and (un)logical SQL (Word 2003)
    ... the ` aren't usually smart quotes - theyare back quotes that ... are needed to quote names in the SQL statement. ... can surround the name by instead but it may depend on the dialect of SQL ... the syntax of the part should be: ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Can anyone see whats wrong with this ASP Page?
    ... utilizing SQL in this manner leaves your website ... "Brian Basquille" wrote in message ... > It's to do the Insert SQL statement in the btnSubmit_Clickmethod. ... And now it's saying the syntax is incorrect but> i've ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: XML vs SQL Server
    ... The built in factory assumes a common syntax among the ... the DBProvider Factory pattern is an interface-based ... Even if your SQL needs to be changed, ... change the value of the CommandText and everything else is fine. ...
    (microsoft.public.dotnet.languages.csharp)
Loading