SQL Server JDBC - Error when comparing against empty string using JDBC 2005 Beta 2

Author

Error when comparing against empty string using JDBC 2005 Beta 2

jgarza

2005-12-02, 8:23 pm

I am having a problem with JDBC 2005 Beta when comparing against an empty
string.
tate : S1093
Message: Index out of range1 Valid range is 1 to 0
Error : 0
com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1 Valid
range is 1 to 0
at
com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDriverError(
Unknown
Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.setParam(Unknown Source)
at
com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.setString(Unknown
Source)
at com.acornsys.mmjavaapi.MsJdbc2005Bug.main(MsJdbc2005Bug.java:46)
====

Here is a code sample to reproduce the bug

/*
* MS JDBC Driver Beta 2 has problem when performing
* something like col = ''
*
* CREATE TABLE dbo.mytable (
* table_id INT NOT NULL,
* table_name VARCHAR(128) NOT NULL
* )
*/
package com.sample.bug;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MsJdbc2005Bug
{
public static void main(String args[])
{
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch(java.lang. ClassNotFoundExcepti
on e) {
System.out.println("Unable to load Driver class");
return;
}

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rSet = null;

try {
String dbUrl="jdbc:sqlserver:// deathstar;databaseNa
me=JORGE_BUG";
String user = "sa";
String password = "acorn";

String query = ("SELECT COUNT(*) FROM mytable"
+ " WHERE table_name <> ''"
+ " AND table_name <> ? "
+ " AND table_id = ?");

conn = java.sql.DriverManager. getConnection(dbUrl,
user,
password);
pstmt = conn. prepareStatement(que
ry);

//IT will FAIL here....
pstmt.setString(1, "xxxx");
pstmt.setInt(2, 0);

rSet = pstmt.executeQuery();
while (rSet.next()) {
System.out.println(rSet.getInt(1));
}
} catch (SQLException e) {

// Loop through the SQL Exceptions
SQLException e2 = e;

while(e2 != null) {
System.out.println("State : " + e2.getSQLState()) ;
System.out.println("Message: " + e2.getMessage()) ;
System.out.println("Error : " + e2.getErrorCode()) ;

e2 = e2.getNextException() ;
}
e.printStackTrace();
} catch(Exception e) {
System.out.println(e) ;
e.printStackTrace();
} finally {
try {
if (rSet != null)
rSet.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (Exception e) {}
}
}
}

jgarza

2005-12-02, 8:23 pm

By the way if you change the empty string '' with something like 'xxx', JDBC
would not end up with the problem. That is why I am indicating that this is a
problem when comparing an empty string. It is like JDBC decided to truncate
my query at the empty string location.

"jgarza" wrote:

> I am having a problem with JDBC 2005 Beta when comparing against an empty
> string.
> tate : S1093
> Message: Index out of range1 Valid range is 1 to 0
> Error : 0
> com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1 Valid
> range is 1 to 0
> at
> com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDriverError(
Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.setParam(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.setString(Unknown
> Source)
> at com.acornsys.mmjavaapi.MsJdbc2005Bug.main(MsJdbc2005Bug.java:46)
> ====
>
> Here is a code sample to reproduce the bug
>
> /*
> * MS JDBC Driver Beta 2 has problem when performing
> * something like col = ''
> *
> * CREATE TABLE dbo.mytable (
> * table_id INT NOT NULL,
> * table_name VARCHAR(128) NOT NULL
> * )
> */
> package com.sample.bug;
>
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
>
> public class MsJdbc2005Bug
> {
> public static void main(String args[])
> {
> try {
> Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
> } catch(java.lang. ClassNotFoundExcepti
on e) {
> System.out.println("Unable to load Driver class");
> return;
> }
>
> Connection conn = null;
> PreparedStatement pstmt = null;
> ResultSet rSet = null;
>
> try {
> String dbUrl="jdbc:sqlserver:// deathstar;databaseNa
me=JORGE_BUG";
> String user = "sa";
> String password = "acorn";
>
> String query = ("SELECT COUNT(*) FROM mytable"
> + " WHERE table_name <> ''"
> + " AND table_name <> ? "
> + " AND table_id = ?");
>
> conn = java.sql.DriverManager. getConnection(dbUrl,
user,
> password);
> pstmt = conn. prepareStatement(que
ry);
>
> //IT will FAIL here....
> pstmt.setString(1, "xxxx");
> pstmt.setInt(2, 0);
>
> rSet = pstmt.executeQuery();
> while (rSet.next()) {
> System.out.println(rSet.getInt(1));
> }
> } catch (SQLException e) {
>
> // Loop through the SQL Exceptions
> SQLException e2 = e;
>
> while(e2 != null) {
> System.out.println("State : " + e2.getSQLState()) ;
> System.out.println("Message: " + e2.getMessage()) ;
> System.out.println("Error : " + e2.getErrorCode()) ;
>
> e2 = e2.getNextException() ;
> }
> e.printStackTrace();
> } catch(Exception e) {
> System.out.println(e) ;
> e.printStackTrace();
> } finally {
> try {
> if (rSet != null)
> rSet.close();
> if (pstmt != null)
> pstmt.close();
> if (conn != null)
> conn.close();
> } catch (Exception e) {}
> }
> }
> }
>
>
>
>
>

Arto Basmadjian

2005-12-04, 3:23 am

jgazara;

Looks very similar to the problem I encountered (posted Nov. 28, 2005). In
my case I was selecting an empty constant. If I changed the empty constant
to 'xxx', it worked just fine.

Arto

"jgarza" <jgarza@discussions.microsoft.com> wrote in message
news:F6355927-16E7-4094-9D81- BEE72C1C51F8@microso
ft.com...
>I am having a problem with JDBC 2005 Beta when comparing against an empty
> string.
> tate : S1093
> Message: Index out of range1 Valid range is 1 to 0
> Error : 0
> com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1
> Valid
> range is 1 to 0
> at
> com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDriverError(
Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.setParam(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.setString(Unknown
> Source)
> at com.acornsys.mmjavaapi.MsJdbc2005Bug.main(MsJdbc2005Bug.java:46)
> ====
>
> Here is a code sample to reproduce the bug
>
> /*
> * MS JDBC Driver Beta 2 has problem when performing
> * something like col = ''
> *
> * CREATE TABLE dbo.mytable (
> * table_id INT NOT NULL,
> * table_name VARCHAR(128) NOT NULL
> * )
> */
> package com.sample.bug;
>
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
>
> public class MsJdbc2005Bug
> {
> public static void main(String args[])
> {
> try {
> Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
> } catch(java.lang. ClassNotFoundExcepti
on e) {
> System.out.println("Unable to load Driver class");
> return;
> }
>
> Connection conn = null;
> PreparedStatement pstmt = null;
> ResultSet rSet = null;
>
> try {
> String
> dbUrl="jdbc:sqlserver:// deathstar;databaseNa
me=JORGE_BUG";
> String user = "sa";
> String password = "acorn";
>
> String query = ("SELECT COUNT(*) FROM mytable"
> + " WHERE table_name <> ''"
> + " AND table_name <> ? "
> + " AND table_id = ?");
>
> conn = java.sql.DriverManager. getConnection(dbUrl,
user,
> password);
> pstmt = conn. prepareStatement(que
ry);
>
> //IT will FAIL here....
> pstmt.setString(1, "xxxx");
> pstmt.setInt(2, 0);
>
> rSet = pstmt.executeQuery();
> while (rSet.next()) {
> System.out.println(rSet.getInt(1));
> }
> } catch (SQLException e) {
>
> // Loop through the SQL Exceptions
> SQLException e2 = e;
>
> while(e2 != null) {
> System.out.println("State : " + e2.getSQLState()) ;
> System.out.println("Message: " + e2.getMessage()) ;
> System.out.println("Error : " + e2.getErrorCode()) ;
>
> e2 = e2.getNextException() ;
> }
> e.printStackTrace();
> } catch(Exception e) {
> System.out.println(e) ;
> e.printStackTrace();
> } finally {
> try {
> if (rSet != null)
> rSet.close();
> if (pstmt != null)
> pstmt.close();
> if (conn != null)
> conn.close();
> } catch (Exception e) {}
> }
> }
> }
>
>
>
>
>

Kamil Sykora [MSFT]

2005-12-06, 8:23 pm

Hello,

This does indeed look like the same issue that was posted on 11/28. I filed
a bug on that issue and I added this case to the bug as well.

Thanks,
Kamil

Kamil Sykora [MSFT]
Microsoft Developer Support - Webdata

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.
--------------------
| Thread-Topic: Error when comparing against empty string using JDBC 2005
Beta 2
| From: =?Utf-8?B?amdhcnph?= <jgarza@discussions.microsoft.com>
| Subject: RE: Error when comparing against empty string using JDBC 2005
Beta 2
| Date: Fri, 2 Dec 2005 13:21:02 -0800
|
|
| By the way if you change the empty string '' with something like 'xxx',
JDBC
| would not end up with the problem. That is why I am indicating that this
is a
| problem when comparing an empty string. It is like JDBC decided to
truncate
| my query at the empty string location.
|
| "jgarza" wrote:
|
| > I am having a problem with JDBC 2005 Beta when comparing against an
empty
| > string.
| > tate : S1093
| > Message: Index out of range1 Valid range is 1 to 0
| > Error : 0
| > com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1
Valid
| > range is 1 to 0
| > at
| >
com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDriverError(
Unknown
| > Source)
| > at com.microsoft.sqlserver.jdbc.SQLServerStatement.setParam(Unknown
Source)
| > at
| >
com.microsoft.sqlserver.jdbc. SQLServerPreparedSta
tement.setString(Unknown
| > Source)
| > at com.acornsys.mmjavaapi.MsJdbc2005Bug.main(MsJdbc2005Bug.java:46)
| > ====
| >
| > Here is a code sample to reproduce the bug
| >
| > /*
| > * MS JDBC Driver Beta 2 has problem when performing
| > * something like col = ''
| > *
| > * CREATE TABLE dbo.mytable (
| > * table_id INT NOT NULL,
| > * table_name VARCHAR(128) NOT NULL
| > * )
| > */
| > package com.sample.bug;
| >
| > import java.sql.Connection;
| > import java.sql.PreparedStatement;
| > import java.sql.ResultSet;
| > import java.sql.SQLException;
| >
| > public class MsJdbc2005Bug
| > {
| > public static void main(String args[])
| > {
| > try {
| >
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
| > } catch(java.lang. ClassNotFoundExcepti
on e) {
| > System.out.println("Unable to load Driver class");
| > return;
| > }
| >
| > Connection conn = null;
| > PreparedStatement pstmt = null;
| > ResultSet rSet = null;
| >
| > try {
| > String
dbUrl="jdbc:sqlserver:// deathstar;databaseNa
me=JORGE_BUG";
| > String user = "sa";
| > String password = "acorn";
| >
| > String query = ("SELECT COUNT(*) FROM mytable"
| > + " WHERE table_name <> ''"
| > + " AND table_name <> ? "
| > + " AND table_id = ?");
| >
| > conn = java.sql.DriverManager. getConnection(dbUrl,
user,
| > password);
| > pstmt = conn. prepareStatement(que
ry);
| >
| > //IT will FAIL here....
| > pstmt.setString(1, "xxxx");
| > pstmt.setInt(2, 0);
| >
| > rSet = pstmt.executeQuery();
| > while (rSet.next()) {
| > System.out.println(rSet.getInt(1));
| > }
| > } catch (SQLException e) {
| >
| > // Loop through the SQL Exceptions
| > SQLException e2 = e;
| >
| > while(e2 != null) {
| > System.out.println("State : " + e2.getSQLState()) ;
| > System.out.println("Message: " + e2.getMessage()) ;
| > System.out.println("Error : " + e2.getErrorCode()) ;
| >
| > e2 = e2.getNextException() ;
| > }
| > e.printStackTrace();
| > } catch(Exception e) {
| > System.out.println(e) ;
| > e.printStackTrace();
| > } finally {
| > try {
| > if (rSet != null)
| > rSet.close();
| > if (pstmt != null)
| > pstmt.close();
| > if (conn != null)
| > conn.close();
| > } catch (Exception e) {}
| > }
| > }
| > }
| >
| >
| >
| >
| >
|

Sponsored Links