SQL Server JDBC - Bug in JDBC SQL extensions parser of the Beta SQLServer driver

Author

Bug in JDBC SQL extensions parser of the Beta SQLServer driver

Joe Weinstein

2005-08-25, 1:23 pm

Hi all.

I just tried this code with the new beta driver and it fails
at the execute() call!

c = d.connect("jdbc:sqlserver://joe:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
System.out.println("Database Major version is " + dd. getDatabaseMajorVers
ion() );
System.out.println("Database Minor version is " + dd. getDatabaseMinorVers
ion() );

Statement s = c.createStatement();
try { s.executeUpdate("drop procedure joeproc"); } catch (Exception ignore){}
try { s.executeUpdate("drop table joetable"); } catch (Exception ignore){}

s.executeUpdate("create table joetable (bar varchar(30) not null)");
s.executeUpdate("create procedure joeproc as "
+ " begin "
+ " insert into joetable values('1') "
+ " insert into joetable values(NULL) "
+ " insert into joetable values('2') "
+ " end ");

PreparedStatement ps = c.prepareStatement("{ call joeproc() }");
boolean getResultSet = ps.execute();

I get:

Driver version is 1.0.107.104
Database Major version is 8
Database Minor version is 0
com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '{'.
at com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDatabaseErro
r(Unknown Source)
at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement. getNextResult(Unknow
n Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.execute(Unknown Source)

The problem is in the driver's JDBC SQL extension parser. If I remove the whitespace
in the SQL to be c.prepareStatement("{call joeproc()}"), it works. This parser should
not be sensitive to white space...

Joe Weinstein at BEA Systems

Angel Saenz-Badillos[MS]

2005-08-25, 8:23 pm

I agree that the the parser should not be sensitive to white space. I have
entered a bug to track this issue, thank you!

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:%23xndgbZqFHA.2604@TK2MSFTNGP14.phx.gbl...
> Hi all.
>
> I just tried this code with the new beta driver and it fails
> at the execute() call!
>
> c = d.connect("jdbc:sqlserver://joe:1433", props );
>
> DatabaseMetaData dd = c.getMetaData();
> System.out.println("Driver version is " +
> dd.getDriverVersion() );
> System.out.println("Database Major version is " +
> dd. getDatabaseMajorVers
ion() );
> System.out.println("Database Minor version is " +
> dd. getDatabaseMinorVers
ion() );
>
> Statement s = c.createStatement();
> try { s.executeUpdate("drop procedure joeproc"); } catch
> (Exception ignore){}
> try { s.executeUpdate("drop table joetable"); } catch (Exception
> ignore){}
>
> s.executeUpdate("create table joetable (bar varchar(30) not
> null)");
> s.executeUpdate("create procedure joeproc as "
> + " begin "
> + " insert into joetable values('1') "
> + " insert into joetable values(NULL) "
> + " insert into joetable values('2') "
> + " end ");
>
> PreparedStatement ps = c.prepareStatement("{ call joeproc() }");
> boolean getResultSet = ps.execute();
>
> I get:
>
> Driver version is 1.0.107.104
> Database Major version is 8
> Database Minor version is 0
> com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax
> near '{'.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDatabaseErro
r(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement. getNextResult(Unknow
n
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecute(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.execute(Unknown
> Source)
>
> The problem is in the driver's JDBC SQL extension parser. If I remove the
> whitespace
> in the SQL to be c.prepareStatement("{call joeproc()}"), it works. This
> parser should
> not be sensitive to white space...
>
> Joe Weinstein at BEA Systems
>

Angel Saenz-Badillos[MS]

2005-10-01, 3:23 am

I wanted to follow up on this issue. We have not been able to fix this
satisfactorily yet.

The problem:
We are not sure what the behavior of calling a stored procedure with a
PreparedStatement should be. We verified that using a callableStatement
works fine.
There is the potential for many related parsing issues and we need to do
more than fix an isolated case. We are trying to determine what the right
behavior should be here in all cases.

Unfortunatelly what this means is that we will not be fixing this problem
for beta 2.

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
news:O7X2fcaqFHA.528@TK2MSFTNGP09.phx.gbl...
>I agree that the the parser should not be sensitive to white space. I have
>entered a bug to track this issue, thank you!
>
>
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:%23xndgbZqFHA.2604@TK2MSFTNGP14.phx.gbl...
>
>

Joe Weinstein

2005-10-01, 11:23 am

Angel Saenz-Badillos[MS] wrote:
> I wanted to follow up on this issue. We have not been able to fix this
> satisfactorily yet.
>
> The problem:
> We are not sure what the behavior of calling a stored procedure with a
> PreparedStatement should be. We verified that using a callableStatement
> works fine.

Well, thanks for the update. A CallableStatement *is* a PreparedStatement.
It is just a PreparedStatement with extra methods to extract output
parameters. Ie: by spec it is a subclass of PreparedStatement, and by
spec, there is nothing overriding the functionality of a PreparedStatement
in the CallableStatement spec. In a case of no output parameters, there
should be no need for a CallableStatement, and the spec has no provision
for a difference in SQL extension parsing between a PreparedStatement and
CallableStatement.

> There is the potential for many related parsing issues and we need to do
> more than fix an isolated case. We are trying to determine what the right
> behavior should be here in all cases.

Right. The driver should have one parser only, and I understand that
assumptions about white-space would be a generic problem needing a
structural change to the parser, not a spot fix.

> Unfortunatelly what this means is that we will not be fixing this problem
> for beta 2.

Understood.
thanks,
Joe
[color=darkred]
>"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
>
>

Sponsored Links