SQL Server JDBC - Error when binding value to prepared statement with SQL Server 2005

Author

Error when binding value to prepared statement with SQL Server 2005

Arto Basmadjian

2005-11-28, 8:23 pm

I'm using the latest (beta 2) jdbc driver to connect to an Sql Express 2005
database.

I'm preparing the following sql statement:

SELECT md_database.database_name, md_database.desc_key,
md_database.mod_counter, '' data_model FROM md_database WHERE
md_database.database_name = ?
(note that its 2 single quotes just before data_model)

When I try to bind the value for database_name using:
ppdStmt.setString(1, "meta");

I get the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1 Valid
range is 1 to 0

The same code works fine using Sql Server 2000 (with its matching jdbc
driver(s) & connection string). If I use the new 2005 jdbc driver to
connect to the Sql Server 2000 database, I also get the error.

If I change my sql, and use any constant (other than an empty string), it
works fine. For example, doing the same bind on the following sql:
SELECT md_database.database_name, md_database.desc_key,
md_database.mod_counter, 'DontCare' data_model FROM md_database WHERE
md_database.database_name = ?
works fine.

Any ideas? driver bug?

Arto

Arto Basmadjian

2005-11-29, 11:23 am

Joe I had to adjust your program slightly to get it to work
(dd. getDatabaseMajorVers
ion() & dd. getDatabaseMinorVers
ion() are not
available to me, so I substituted...)

Anyhow... I still get the same error

From the results, looks like you are not using sqlServer 2005, nor the
latest(beta2) jdbc driver.

Results...
Database version is 9.0.1399
Driver version is 1.0.419.102
Col count is 2
Col 1 is 'bar'
Col 2 is 'schmeck'
'qwe' 'qweqwe'
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.tecsys.base.foo.main(foo.java:46)

Arto

"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:ewoHhDH9FHA.2716@TK2MSFTNGP11.phx.gbl...
>
>
> Arto Basmadjian wrote:
>
>
> Hi Arto.
> I can't reproduce the problem. Here's a program to try. I get the output:
>
> C:\new_ms_driver>java foo
> Driver version is 1.0.107.104
> Database Major version is 8
> Database Minor version is 0
> Col count is 2
> Col 1 is 'bar'
> Col 2 is 'schmeck'
> 'qwe' 'qweqwe'
> Col count is 2
> Col 1 is 'bar'
> Col 2 is 'schmeck'
> 'qwe' ''
>
> import java.sql.*;
> import java.util.*;
>
> public class foo
> {
> public static void main(String args[])
> throws Exception
> {
> Connection c = null;
> try
> {
> Properties props = new Properties();
> Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
>
> props.put("user", "joe");
> props.put("password", "joe");
>
> 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 table joetable"); } catch (Exception
> ignore){}
>
> s.executeUpdate("create table joetable (bar varchar(30) not
> null)");
> s.executeUpdate("insert into joetable values('qwe')");
>
> PreparedStatement ps = c.prepareStatement("select bar, 'qweqwe'
> schmeck from joetable where bar = ?");
> ps.setString(1, "qwe");
> ResultSet r = ps.executeQuery();
> ResultSetMetaData rm = r.getMetaData();
>
> System.out.println("Col count is " + rm.getColumnCount() );
> System.out.println("Col 1 is '" + rm.getColumnName(1) + "'");
> System.out.println("Col 2 is '" + rm.getColumnName(2) + "'");
> while (r.next()) System.out.println("'" + r.getString(1) + "' '"
> + r.getString(2) + "'" );
>
> PreparedStatement ps2 = c.prepareStatement("select bar, ''
> schmeck from joetable where bar = ?");
> ps2.setString(1, "qwe");
> ResultSet r2 = ps2.executeQuery();
> ResultSetMetaData rm2 = r2.getMetaData();
> System.out.println("Col count is " + rm2.getColumnCount() );
> System.out.println("Col 1 is '" + rm2.getColumnName(1) + "'");
> System.out.println("Col 2 is '" + rm2.getColumnName(2) + "'");
> while (r2.next()) System.out.println( "'" + r2.getString(1) + "'
> '" + r2.getString(2) + "'");
> }
> catch(Exception exception1)
> {
> exception1.printStackTrace();
> }
> finally
> {
> if (c != null) try {c.close();} catch (Exception ignore){}
> }
> }
> }
>

Kamil Sykora [MSFT]

2005-12-06, 11:23 am

Hello Arto, Joe,

I have reproduced the problem and filed a bug. Thank you for reporting the
issue.

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.
--------------------
| Date: Tue, 29 Nov 2005 08:39:31 -0800
| From: Joe Weinstein <joeNOSPAM@bea.com>
| Subject: Re: Error when binding value to prepared statement with SQL
Server
| 2005
|
|
|
| Arto Basmadjian wrote:
|
| > Joe I had to adjust your program slightly to get it to work
| > (dd. getDatabaseMajorVers
ion() & dd. getDatabaseMinorVers
ion() are not
| > available to me, so I substituted...)
| >
| > Anyhow... I still get the same error
| >
| > From the results, looks like you are not using sqlServer 2005, nor the
| > latest(beta2) jdbc driver.
|
| Ok, that indicates the bug is in the later
| driver version. MS will take it from here
| I'm sure.
|
| Joe Weinstein at BEA Systems
|
| >
| > Results...
| > Database version is 9.0.1399
| > Driver version is 1.0.419.102
| > Col count is 2
| > Col 1 is 'bar'
| > Col 2 is 'schmeck'
| > 'qwe' 'qweqwe'
| > 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.tecsys.base.foo.main(foo.java:46)
| >
| >
| > Arto
| >
| > "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
| > news:ewoHhDH9FHA.2716@TK2MSFTNGP11.phx.gbl...
| >
| >>
| >>Arto Basmadjian wrote:
| >>
| >>
| >>>I'm using the latest (beta 2) jdbc driver to connect to an Sql Express
| >>>2005 database.
| >>>
| >>>I'm preparing the following sql statement:
| >>>
| >>>SELECT md_database.database_name, md_database.desc_key,
| >>>md_database.mod_counter, '' data_model FROM md_database WHERE
| >>>md_database.database_name = ?
| >>>(note that its 2 single quotes just before data_model)
| >>>
| >>>When I try to bind the value for database_name using:
| >>>ppdStmt.setString(1, "meta");
| >>>
| >>>I get the following error:
| >>>com.microsoft.sqlserver.jdbc.SQLServerException: Index out of range1
| >>>Valid range is 1 to 0
| >>>
| >>>The same code works fine using Sql Server 2000 (with its matching jdbc
| >>>driver(s) & connection string). If I use the new 2005 jdbc driver to
| >>>connect to the Sql Server 2000 database, I also get the error.
| >>>
| >>>If I change my sql, and use any constant (other than an empty string),
it
| >>>works fine. For example, doing the same bind on the following sql:
| >>>SELECT md_database.database_name, md_database.desc_key,
| >>>md_database.mod_counter, 'DontCare' data_model FROM md_database WHERE
| >>>md_database.database_name = ?
| >>>works fine.
| >>>
| >>>Any ideas? driver bug?
| >>>
| >>>Arto
| >>
| >>Hi Arto.
| >>I can't reproduce the problem. Here's a program to try. I get the
output:
| >>
| >>C:\new_ms_driver>java foo
| >>Driver version is 1.0.107.104
| >>Database Major version is 8
| >>Database Minor version is 0
| >>Col count is 2
| >>Col 1 is 'bar'
| >>Col 2 is 'schmeck'
| >>'qwe' 'qweqwe'
| >>Col count is 2
| >>Col 1 is 'bar'
| >>Col 2 is 'schmeck'
| >>'qwe' ''
| >>
| >>import java.sql.*;
| >>import java.util.*;
| >>
| >>public class foo
| >>{
| >> public static void main(String args[])
| >> throws Exception
| >> {
| >> Connection c = null;
| >> try
| >> {
| >> Properties props = new Properties();
| >> Driver d = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
| >>
| >> props.put("user", "joe");
| >> props.put("password", "joe");
| >>
| >> 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 table joetable"); } catch
(Exception
| >>ignore){}
| >>
| >> s.executeUpdate("create table joetable (bar varchar(30) not
| >>null)");
| >> s.executeUpdate("insert into joetable values('qwe')");
| >>
| >> PreparedStatement ps = c.prepareStatement("select bar,
'qweqwe'
| >>schmeck from joetable where bar = ?");
| >> ps.setString(1, "qwe");
| >> ResultSet r = ps.executeQuery();
| >> ResultSetMetaData rm = r.getMetaData();
| >>
| >> System.out.println("Col count is " + rm.getColumnCount() );
| >> System.out.println("Col 1 is '" + rm.getColumnName(1) + "'");
| >> System.out.println("Col 2 is '" + rm.getColumnName(2) + "'");
| >> while (r.next()) System.out.println("'" + r.getString(1) + "'
'"
| >>+ r.getString(2) + "'" );
| >>
| >> PreparedStatement ps2 = c.prepareStatement("select bar, ''
| >>schmeck from joetable where bar = ?");
| >> ps2.setString(1, "qwe");
| >> ResultSet r2 = ps2.executeQuery();
| >> ResultSetMetaData rm2 = r2.getMetaData();
| >> System.out.println("Col count is " + rm2.getColumnCount() );
| >> System.out.println("Col 1 is '" + rm2.getColumnName(1) + "'");
| >> System.out.println("Col 2 is '" + rm2.getColumnName(2) + "'");
| >> while (r2.next()) System.out.println( "'" + r2.getString(1) +
"'
| >>'" + r2.getString(2) + "'");
| >> }
| >> catch(Exception exception1)
| >> {
| >> exception1.printStackTrace();
| >> }
| >> finally
| >> {
| >> if (c != null) try {c.close();} catch (Exception ignore){}
| >> }
| >> }
| >>}
| >>
| >
| >
| >
|
|

Sponsored Links