syntax error on escape sequences with bind variables

[lpal...@gmail.com]

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

[Sue Purkis]

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

<lpal...@gmail.com> wrote in message
news:1116264061....@o13g2000cwo.googlegroups.com...

[Alin Sinpalean]

Sue Purkis wrote:
> 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

I'll give you an even simpler query that generates the error message:
"SELECT {d ?}". The error is not caused by the YEAR function but rather
by the parameterized JDBC escape; the MS driver doesn't support that.

And it's pretty much useless anyway: why would you want the driver to
convert a parameter to a date? In most cases the DBMS will do that for
you and if it's not an implicit conversion then do the conversion
yourself.

Alin,
The jTDS Project.

[lpal...@gmail.com]

Thanks Sue. I've since moved on and I can't test it atm.

>From what I see online the docs say that YEAR() takes an expression of
datetime or smalldatetime. It's not limited to strings.

I had gotten around the problem by creating and using a JDBC timeStamp
and doing away with the escape sequences altogether.

In any case it looks like the driver doesn't handle parameterized JDBC
escape sequences.

Thanks for looking into this.

-Lenny

[Sue Purkis]

Lenny,

It is true that this driver does not support parameterized JDBC escape
sequences such as

update emp set hire_date = '{d ? } where first_name = 'Sue' ";

But the driver does support parameterized JDBC escape sequences for stored
procedures -- such as

{[?=]call procedure-name[([parameter][,[parameter]]...)]}
where: procedure-name specifies the name of a stored procedure and parameter
specifies a stored procedure parameter.

Sue Purkis
DataDirect Technologies

<lpal...@gmail.com> wrote in message
news:1117661005....@o13g2000cwo.googlegroups.com...