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
