SQL Server JDBC - syntax error on escape sequences with bind variables

Author

syntax error on escape sequences with bind variables

lpalozzi@gmail.com

2005-05-16, 1:23 pm

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(Unkn
own
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(Unkn
own
Source)
at com.microsoft.jdbc.base. BaseSQLEscapeProcess
or.visit(Unknown
Source)
at com.microsoft.jdbc.base. BaseSQLTreeTraverser
.visit(Unknown Source)
at
com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
Source)
at
com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
Source)
at
com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
Source)
at
com.microsoft.jdbc.base. BaseSQLEscapeProcess
or. processEscapes(Unkno
wn
Source)
at com.microsoft.jdbc.base. BaseSQLEscapeProcess
or.visit(Unknown
Source)
at com.microsoft.jdbc.base. BaseSQLTreeTraverser
.visit(Unknown Source)
at
com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
Source)
at
com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
Source)
at
com.microsoft.jdbc.base. BaseSQLEscapeProcess
or. processEscapes(Unkno
wn
Source)
at com.microsoft.jdbc.base.BaseSQL. translateEscapes(Unk
nown 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(Unknow
n Source)
at com.microsoft.jdbc.base. BasePreparedStatemen
t.<init>(Unknown
Source)
at com.microsoft.jdbc.base.BaseConnection. prepareStatement(Unk
nown
Source)
at com.microsoft.jdbc.base.BaseConnection. prepareStatement(Unk
nown
Source)

Thanks,

-Lenny

Sue Purkis

2005-05-31, 11:23 am

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@gmail.com> wrote in message
news:1116264061.117026.71970@o13g2000cwo.googlegroups.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(Unkn
own
> 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(Unkn
own
> Source)
> at com.microsoft.jdbc.base. BaseSQLEscapeProcess
or.visit(Unknown
> Source)
> at com.microsoft.jdbc.base. BaseSQLTreeTraverser
.visit(Unknown Source)
> at
> com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base. BaseSQLEscapeProcess
or. processEscapes(Unkno
wn
> Source)
> at com.microsoft.jdbc.base. BaseSQLEscapeProcess
or.visit(Unknown
> Source)
> at com.microsoft.jdbc.base. BaseSQLTreeTraverser
.visit(Unknown Source)
> at
> com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base. BaseSQLTreePreOrderT
raverser.traverse(Unknown
> Source)
> at
> com.microsoft.jdbc.base. BaseSQLEscapeProcess
or. processEscapes(Unkno
wn
> Source)
> at com.microsoft.jdbc.base.BaseSQL. translateEscapes(Unk
nown 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(Unknow
n Source)
> at com.microsoft.jdbc.base. BasePreparedStatemen
t.<init>(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseConnection. prepareStatement(Unk
nown
> Source)
> at com.microsoft.jdbc.base.BaseConnection. prepareStatement(Unk
nown
> Source)
>
>
> Thanks,
>
> -Lenny
>

Alin Sinpalean

2005-06-01, 7:23 am

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.

lpalozzi@gmail.com

2005-06-01, 8:23 pm

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

2005-06-17, 1:23 pm

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

<lpalozzi@gmail.com> wrote in message
news:1117661005.041495.23240@o13g2000cwo.googlegroups.com...
> Thanks Sue. I've since moved on and I can't test it atm.
>
> 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
>

Sponsored Links