Pentaho Kettle Spoon Error During CSV to mySQL error

Question

I am trying to update and/or insert records that need to be updated in a mySQL database. I am trying to use spoon by pentaho. I am using the update transformation.

I keep getting an error during import that I just cannot figure out what is wrong.

I've tried formatting and reformatting and just about anything else I can think of.

Here is the error I get when I try to run the process.

2016/04/20 20:53:24 - General - Logging plugin type found with ID: CheckpointLogTable
2016/04/20 20:53:25 - cfgbuilder - Warning: The configuration parameter [org] is not supported by the default configuration builder for scheme: sftp
2016/04/20 20:54:24 - General - Starting agile-bi
2016/04/20 20:54:25 - class org.pentaho.agilebi.platform.JettyServer - WebServer.Log.CreateListener localhost:10000
2016/04/20 21:01:27 - Spoon - Transformation opened.
2016/04/20 21:01:27 - Spoon - Launching transformation [MLSFileUpdate]...
2016/04/20 21:01:27 - Spoon - Started the transformation execution.
2016/04/20 21:01:28 - MLSFileUpdate - Dispatching started for transformation [MLSFileUpdate]
2016/04/20 21:01:28 - Pull Latest MLS data.0 - Opening file: file:///C:/Users/Administrator/Downloads/MLS Data/Full Export/fullExport1.txt
2016/04/20 21:01:28 - Pull Latest MLS data.0 - Finished processing (I=792, O=0, R=0, W=791, U=1, E=0)
2016/04/20 21:01:29 - Insert / Update.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected error
2016/04/20 21:01:29 - Insert / Update.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleStepException: 
2016/04/20 21:01:29 - Insert / Update.0 - Error in step, asking everyone to stop because of:
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - offending row : [Folio_Number String(17)], [#Beds Integer(15)], [#FBaths Integer(15)], [#HBaths Integer(15)], [#HBaths_1 Integer(15)], [Address String(25)], [Approx._Sqft_Total_Area Integer(15)], [Approximate_Lot_Size String(10)], [Area String(15)], [City___Original String(35)], [City_Name String(35)], [Closing_Date String(19)], [Compass_Point String(2)], [Compass_Point_1 String(9)], [Construction_Type String(67)], [County String(17)], [Dade_Assessed_$/SOH_Value Integer(15)], [Dade_Market_$/Assessed_$ Integer(15)], [Days_on_Market Integer(15)], [Design String(8)], [Design_Description String(37)], [Development_Name String(20)], [Elementary_School String(13)], [Geographic_Area String(51)], [I# Integer(15)], [IDX String(3)], [Last_Transaction_Date String(19)], [List_Price Integer(15)], [Listing_Type String(23)], [Lot_Description String(64)], [Map_Coordinates Integer(15)], [Map_Coordinates_1 String], [ML# String(9)], [Model_Name String(18)], [Municipal_Code Integer(15)], [Occupancy_Information String(18)], [Parcel_Number Integer(15)], [Property_SqFt Integer(15)], [Property_Type String(13)], [Remarks String(510)], [Sale_Price Integer(15)], [Sale_Terms String(12)], [Section Integer(15)], [Serial_Number String], [Special_Information String(71)], [SqFt_L.A._of_Guest_House Integer(15)], [SqFt_Liv_Area Integer(15)], [State String(7)], [Status String(16)], [Street_Name String(20)], [Street_Number Integer(15)], [Street_Suffix String], [Subdivision_Information String(61)], [Subdivision_Name String(25)], [Subdivision_Number Integer(15)], [Tax_Amount Integer(15)], [Tax_Information String(78)], [Tax_Year Integer(15)], [Township/Range Integer(15)], [Unit_Number String], [Type_of_Property String(6)], [Type_of_Contingencies String], [Waterfront_Property_(Y/N) String(3)], [Water_Access String(60)], [Year_Built Integer(15)], [Year_Built_Description String(16)], [Zip_Code Integer(15)], [Zoning_Information String(8)], [Agent_Email_Address String(35)], [Agent_License_# String], [Agent_Phone String(12)], [Agent's_Office_Extension Integer(15)], [Exterior_Features String(102)], [Listing_Agent's_Name String(23)], [Zip_Code_(Last_4_Digits) Integer(15)]
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - Error setting value #1 [String(17)] on prepared statement
2016/04/20 21:01:29 - Insert / Update.0 - Parameter index out of range (1 > number of parameters, which is 0).
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:313)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2016/04/20 21:01:29 - Insert / Update.0 -   at java.lang.Thread.run(Unknown Source)
2016/04/20 21:01:29 - Insert / Update.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2016/04/20 21:01:29 - Insert / Update.0 - offending row : [Folio_Number String(17)], [#Beds Integer(15)], [#FBaths Integer(15)], [#HBaths Integer(15)], [#HBaths_1 Integer(15)], [Address String(25)], [Approx._Sqft_Total_Area Integer(15)], [Approximate_Lot_Size String(10)], [Area String(15)], [City___Original String(35)], [City_Name String(35)], [Closing_Date String(19)], [Compass_Point String(2)], [Compass_Point_1 String(9)], [Construction_Type String(67)], [County String(17)], [Dade_Assessed_$/SOH_Value Integer(15)], [Dade_Market_$/Assessed_$ Integer(15)], [Days_on_Market Integer(15)], [Design String(8)], [Design_Description String(37)], [Development_Name String(20)], [Elementary_School String(13)], [Geographic_Area String(51)], [I# Integer(15)], [IDX String(3)], [Last_Transaction_Date String(19)], [List_Price Integer(15)], [Listing_Type String(23)], [Lot_Description String(64)], [Map_Coordinates Integer(15)], [Map_Coordinates_1 String], [ML# String(9)], [Model_Name String(18)], [Municipal_Code Integer(15)], [Occupancy_Information String(18)], [Parcel_Number Integer(15)], [Property_SqFt Integer(15)], [Property_Type String(13)], [Remarks String(510)], [Sale_Price Integer(15)], [Sale_Terms String(12)], [Section Integer(15)], [Serial_Number String], [Special_Information String(71)], [SqFt_L.A._of_Guest_House Integer(15)], [SqFt_Liv_Area Integer(15)], [State String(7)], [Status String(16)], [Street_Name String(20)], [Street_Number Integer(15)], [Street_Suffix String], [Subdivision_Information String(61)], [Subdivision_Name String(25)], [Subdivision_Number Integer(15)], [Tax_Amount Integer(15)], [Tax_Information String(78)], [Tax_Year Integer(15)], [Township/Range Integer(15)], [Unit_Number String], [Type_of_Property String(6)], [Type_of_Contingencies String], [Waterfront_Property_(Y/N) String(3)], [Water_Access String(60)], [Year_Built Integer(15)], [Year_Built_Description String(16)], [Zip_Code Integer(15)], [Zoning_Information String(8)], [Agent_Email_Address String(35)], [Agent_License_# String], [Agent_Phone String(12)], [Agent's_Office_Extension Integer(15)], [Exterior_Features String(102)], [Listing_Agent's_Name String(23)], [Zip_Code_(Last_4_Digits) Integer(15)]
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - Error setting value #1 [String(17)] on prepared statement
2016/04/20 21:01:29 - Insert / Update.0 - Parameter index out of range (1 > number of parameters, which is 0).
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.core.database.Database.setValues(Database.java:1030)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:83)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:299)
2016/04/20 21:01:29 - Insert / Update.0 -   ... 2 more
2016/04/20 21:01:29 - Insert / Update.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2016/04/20 21:01:29 - Insert / Update.0 - Error setting value #1 [String(17)] on prepared statement
2016/04/20 21:01:29 - Insert / Update.0 - Parameter index out of range (1 > number of parameters, which is 0).
2016/04/20 21:01:29 - Insert / Update.0 - 
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:4867)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.core.database.Database.setValue(Database.java:1012)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.core.database.Database.setValues(Database.java:1028)
2016/04/20 21:01:29 - Insert / Update.0 -   ... 4 more
2016/04/20 21:01:29 - Insert / Update.0 - Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3319)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3304)
2016/04/20 21:01:29 - Insert / Update.0 -   at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4016)
2016/04/20 21:01:29 - Insert / Update.0 -   at org.pentaho.di.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:4759)
2016/04/20 21:01:29 - Insert / Update.0 -   ... 6 more
2016/04/20 21:01:29 - Insert / Update.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
2016/04/20 21:01:29 - MLSFileUpdate - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!
2016/04/20 21:01:29 - Spoon - The transformation has finished!!
2016/04/20 21:01:29 - MLSFileUpdate - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!
2016/04/20 21:01:29 - MLSFileUpdate - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!
2016/04/20 21:01:29 - MLSFileUpdate - Transformation detected one or more steps with errors.
2016/04/20 21:01:29 - MLSFileUpdate - Transformation is killing the other steps!

This is the mySQL code for the table that it is going into:

 `Folio Number` varchar(50) DEFAULT NULL,
  `#Beds` int(11) DEFAULT NULL,
  `#FBaths` int(11) DEFAULT NULL,
  `#HBaths` int(11) DEFAULT NULL,
  `#HBaths1` int(11) DEFAULT NULL,
  Address varchar(50) DEFAULT NULL,
  `Approx. Sqft Total Area` int(11) DEFAULT NULL,
  `Approximate Lot Size` varchar(50) DEFAULT NULL,
  Area varchar(50) DEFAULT NULL,
  `City - Original` varchar(50) DEFAULT NULL,
  `City Name` varchar(100) DEFAULT NULL,
  `Closing Date` datetime DEFAULT NULL,
  `Compass Point` varchar(50) DEFAULT NULL,
  `Compass Point1` varchar(50) DEFAULT NULL,
  `Construction Type` varchar(150) DEFAULT NULL,
  County varchar(50) DEFAULT NULL,
  `Dade Assessed $/SOH Value` int(11) DEFAULT NULL,
  `Dade Market $/Assessed $` int(11) DEFAULT NULL,
  `Days on Market` int(11) DEFAULT NULL,
  Design varchar(50) DEFAULT NULL,
  `Design Description` varchar(50) DEFAULT NULL,
  `Development Name` varchar(50) DEFAULT NULL,
  `Elementary School` varchar(50) DEFAULT NULL,
  `Geographic Area` varchar(100) DEFAULT NULL,
  `I#` int(11) DEFAULT NULL,
  IDX varchar(50) DEFAULT NULL,
  `Last Transaction Date` datetime DEFAULT NULL,
  `List Price` int(11) DEFAULT NULL,
  `Listing Type` varchar(50) DEFAULT NULL,
  `Lot Description` varchar(100) DEFAULT NULL,
  `Map Coordinates` varchar(50) DEFAULT NULL,
  `Map Coordinates1` varchar(255) DEFAULT NULL,
  `ML#` varchar(50) DEFAULT NULL,
  `Model Name` varchar(50) DEFAULT NULL,
  `Municipal Code` int(11) DEFAULT NULL,
  `Occupancy Information` varchar(50) DEFAULT NULL,
  `Parcel Number` int(11) DEFAULT NULL,
  `Property SqFt` int(11) DEFAULT NULL,
  `Property Type` varchar(50) DEFAULT NULL,
  Remarks text DEFAULT NULL,
  `Sale Price` int(11) DEFAULT NULL,
  `Sale Terms` varchar(50) DEFAULT NULL,
  Section int(11) DEFAULT NULL,
  `Serial Number` varchar(255) DEFAULT NULL,
  `Special Information` varchar(100) DEFAULT NULL,
  `SqFt L.A. of Guest House` int(11) DEFAULT NULL,
  `SqFt Liv Area` int(11) DEFAULT NULL,
  State varchar(50) DEFAULT NULL,
  Status varchar(50) DEFAULT NULL,
  `Street Name` varchar(50) DEFAULT NULL,
  `Street Number` int(11) DEFAULT NULL,
  `Street Suffix` varchar(255) DEFAULT NULL,
  `Subdivision Information` varchar(100) DEFAULT NULL,
  `Subdivision Name` varchar(50) DEFAULT NULL,
  `Subdivision Number` int(11) DEFAULT NULL,
  `Tax Amount` int(11) DEFAULT NULL,
  `Tax Information` varchar(150) DEFAULT NULL,
  `Tax Year` int(11) DEFAULT NULL,
  `Township/Range` int(11) DEFAULT NULL,
  `Unit Number` varchar(50) DEFAULT NULL,
  `Type of Property` varchar(50) DEFAULT NULL,
  `Type of Contingencies` varchar(255) DEFAULT NULL,
  `Waterfront Property (Y/N)` varchar(50) DEFAULT NULL,
  `Water Access` varchar(100) DEFAULT NULL,
  `Year Built` int(11) DEFAULT NULL,
  `Year Built Description` varchar(50) DEFAULT NULL,
  `Zip Code` varchar(50) DEFAULT NULL,
  `Zoning Information` varchar(50) DEFAULT NULL,
  `Agent Email Address` varchar(100) DEFAULT NULL,
  `Agent License #` varchar(255) DEFAULT NULL,
  `Agent Phone` varchar(50) DEFAULT NULL,
  `Agent's Office Extension` int(11) DEFAULT NULL,
  `Exterior Features` varchar(150) DEFAULT NULL,
  `Listing Agent's Name` varchar(50) DEFAULT NULL,
  `Zip Code (Last 4 Digits)` int(11) DEFAULT NULL

score 0 · Answer 1 · answered Apr 21 '16 at 01:20

0

You're having the error: " Parameter index out of range "

Check your output table and your mapping. Your input file might have more fields than your table.

answered Apr 21 '16 at 01:20

Adrian

442
4
15

Or check your csv file, Some of rows have less fields then usual. If csv file have some key field then add filter to check that this field value exists in csv file using filter step. – simar Apr 21 '16 at 12:07

score 0 · Answer 2 · answered Apr 21 '16 at 06:28

The actual error is

Parameter index out of range (1 > number of parameters, which is 0)

It seems that you have not specified key lookup fields (the upper table) correctly - whether did not specified them at all or did not specified the right-hand part of condition.

Check for example screenshot at http://edpflager.com/wp-content/uploads/2014/02/insert-updatewindow.jpg - one must specify all three columns in the "The key(s) to lookup the value(s)" table: Table field, Comparator and Stream field.

Pentaho Kettle Spoon Error During CSV to mySQL error

2 Answers2