Re: Error when binding value to prepared statement with SQL Server 2005

Joe I had to adjust your program slightly to get it to work
(dd.getDatabaseMajorVersion() & dd.getDatabaseMinorVersion() 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.SQLServerPreparedStatement.setString(Unknown
Source)
at com.tecsys.base.foo.main(foo.java:46)


Arto

"Joe Weinstein" <joeNOSPAM@xxxxxxx> wrote in message
news:ewoHhDH9FHA.2716@xxxxxxxxxxxxxxxxxxxxxxx
>
>
> 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.getDatabaseMajorVersion() );
> System.out.println("Database Minor version is " +
> dd.getDatabaseMinorVersion() );
>
> 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){}
> }
> }
> }
>


.

Relevant Pages

  • make any database have the ability of logging within 10 seconds
    ... With JDBMonitor,you can log the database execution in many ways,for ... JDBMonitor is licensed under the terms of the GNU Lesser General Public ... Almost every large database application includes its own SQL execution ... Make the application load the JDBMonitor JDBC Driver. ...
    (comp.lang.java.programmer)
  • make any database have the ability of logging within 10 seconds
    ... With JDBMonitor,you can log the database execution in many ways,for ... JDBMonitor is licensed under the terms of the GNU Lesser General Public ... Almost every large database application includes its own SQL execution ... Make the application load the JDBMonitor JDBC Driver. ...
    (comp.lang.java.help)
  • Which JDBC driver for MS SQL Server?
    ... which JDBC driver for MS SQL do you use? ... Microsoft SQL Server 2005 JDBC Driver ... - it doesn't work for me at all, cannot connect to database ...
    (comp.lang.java.databases)
  • Re: Which JDBC driver for MS SQL Server?
    ... which JDBC driver for MS SQL do you use? ... - it doesn't work for me at all, cannot connect to database ... jTDS JDBC Driver ...
    (comp.lang.java.databases)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)