Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which

  1. #1
    Join Date
    May 2014
    Posts
    19

    Default java.sql.SQLException: Parameter index out of range (1 > number of parameters, which

    Hi PDI,
    I want to read variable by using set variable and get variable step,but i am getting following error.


    2014/06/26 13:32:43 - Spoon - Starting job...
    2014/06/26 13:32:43 - read - Start of job execution
    2014/06/26 13:32:43 - read - Starting entry [Transformation]
    2014/06/26 13:32:43 - Transformation - Loading transformation from XML file [C:\Users\shivakumar.a\Downloads\reading variable\setvariable.ktr]
    2014/06/26 13:32:43 - Transformation - Dispatching started for transformation [setvariable]
    2014/06/26 13:32:43 - Data Grid.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
    2014/06/26 13:32:43 - Set Variables.0 - Setting environment variables...
    2014/06/26 13:32:43 - Set Variables.0 - Set variable ID to value [ 16138672]
    2014/06/26 13:32:43 - Set Variables.0 - Finished after 1 rows.
    2014/06/26 13:32:43 - Set Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/06/26 13:32:43 - read - Starting entry [Transformation 2]
    2014/06/26 13:32:43 - Transformation 2 - Loading transformation from XML file [C:\Users\shivakumar.a\Downloads\reading variable\readvariable.ktr]
    2014/06/26 13:32:43 - Transformation 2 - Dispatching started for transformation [readvariable]
    2014/06/26 13:32:43 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : offending row : [ID String(9)]
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error setting value #1 [ID String(9)] on prepared statement (String)
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - c - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ERROR in part: P Set values
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected error
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : An error occurred executing SQL in part [P Set values]:
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : select * from j_tgt
    where order_id= 16138672
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : offending row : [ID String(9)]
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error setting value #1 [ID String(9)] on prepared statement (String)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1918)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:225)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:133)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : offending row : [ID String(9)]
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error setting value #1 [ID String(9)] on prepared statement (String)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1134)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:875)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1855)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 4 more
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error setting value #1 [ID String(9)] on prepared statement (String)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:1107)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1130)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 6 more
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4616)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:982)
    2014/06/26 13:32:44 - Table input.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 7 more
    2014/06/26 13:32:44 - Table input.0 - Finished reading query, closing connection.
    2014/06/26 13:32:44 - Transformation 2 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2014/06/26 13:32:44 - Transformation 2 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2014/06/26 13:32:44 - Table input.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2014/06/26 13:32:44 - Transformation 2 - readvariable
    2014/06/26 13:32:44 - Transformation 2 - readvariable
    2014/06/26 13:32:44 - read - Finished job entry [Transformation 2] (result=[false])
    2014/06/26 13:32:44 - read - Finished job entry [Transformation] (result=[false])
    2014/06/26 13:32:44 - read - Job execution finished
    2014/06/26 13:32:44 - Spoon - Job has ended.

  2. #2
    Join Date
    Sep 2011
    Posts
    151

    Default

    Please find attached sample.
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2008
    Posts
    4,637

    Default

    Quote Originally Posted by Shivakumar View Post
    java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    The error message that you posted is very clear - but you do have to read it.
    You are supplying a parameter to a SQL statement that doesn't allow one.

    Specifically:
    Code:
    select * from j_tgt where order_id= 16138672
    You aren't allowed to pass parameters to that, because you aren't using any!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.