Error Setting Value 1 On Prepared Statement String
View Site Leaders Advanced Search Forum Pentaho Users Pentaho Data Integration [Kettle] offending row - Error setting value java.lang.NullPointerException If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 3 of 3 Thread: offending row - Error setting value java.lang.NullPointerException Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 06-26-2009,04:33 PM #1 Irina View Profile View Forum Posts Private Message Junior Member Join Date Jun 2009 Posts 2 offending row - Error setting value java.lang.NullPointerException Hello everyone! I use Kettle - Spoon version 3.1.0. I have a job with 2 transformations, and one of them performs select on UDB tables based on parameters I am getting from the first one. I store parameters values in text file and then just do the read using Fixed file Input. Here is the piece of SQL where I pass parameters: WHERE MATRICULATION_TERM = ${TERM1} AND MATRICULATION_YEAR = ${YEAR1} Here is what I am getting in debugging mode: 2009/06/26 10:49:39 - Read the file terms.0 - Finished processing (I=1, O=0, R=0, W=1, U=1, E=0) 2009/06/26 10:49:39 - extract udb data.0 - Query parameters found = [1], [2010], [6], [2009], [9], [2009] 2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException: 2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, build 826 from 2009/01/23 16:38:46) : offending row : [TERM1 String(1)], [YEAR1 String(4)], [TERM2 String(1)], [YEAR2 String(4)], [TERM3 String(1)], [YEAR3 String(4)] 2009/06/26 10:49:39 - udbtest - ERROR (version 3.1.0, b
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up error setting java String[] http://forums.pentaho.com/showthread.php?70818-offending-row-Error-setting-value-java-lang-NullPointerException to postgres prepared statement up vote 3 down vote favorite I'm trying to insert an Array of Strings into Postgres. I get an invalid type error from Postgres. public static void main(String[] args) throws SQLException { String[] skus = { "0514", "0414", "0314", "0214", "0114", "1213", "1113", "1013", "0913", "0813", "0713", "0613" }; String sqlString = "Insert into dbo.Inventory_Metrics skus values(?)"; Connection conn = DriverManager.getConnection(getPostgresConnUrl()); http://stackoverflow.com/questions/25999014/error-setting-java-string-to-postgres-prepared-statement PreparedStatement ps = conn.prepareStatement(sqlString); //THIS NEXT LINE THROWS AN ERROR ps.setObject(1, skus, java.sql.Types.NVARCHAR, skus.length); int status = ps.executeUpdate(); ps.close(); System.out.print(status); } public static String getPostgresConnUrl() { String database = "mycode"; String userName = "xxxxxxxx"; String password = "xxxxxxxx"; return "jdbc:postgresql://192.168.0.50:5432/" + database + "?user=" + userName + "&password=" + password; } java postgresql jdbc share|improve this question edited Sep 25 '14 at 16:57 asked Sep 23 '14 at 15:28 Voltron 277 But you have a single question mark, and an array of values. You might try a string of comma-separated values instead. –Dave Newton Sep 23 '14 at 15:33 1 The JDBC API doesn't work this way. Please describe what you want to achieve. On top of that: PostgreSQL doesn't support the NVARCHAR type afaik –Mark Rotteveel Sep 23 '14 at 16:06 ps.setObject(1, skus, java.sql.Types.VARCHAR, skus.length); //Fixes error, post it as the answer. –Voltron Sep 23 '14 at 19:10 Near-identical repost at stackoverflow.com/q/26005620/398670 . (+1, thanks for including your code and the error message. In future, your PostgreSQL and PgJDBC version should also be included in all questions). –Craig Ringer Sep 24 '14 at 2:42 ad
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more http://stackoverflow.com/questions/26886387/preparedstatement-setting-values-in-in-clause-efficiency about Stack Overflow the company Business Learn more about hiring developers or posting ads https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/api/java.sql.PreparedStatement.html with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up preparedstatement setting values in IN clause efficiency up vote 4 down vote favorite error setting 1 The code should be database independent so I am using normal sql. My query can change at runtime say table1 changed to table2. But lets assume for table1 query I have to update 2000 records with slot of 50, means as soon as 50 records are processed I will commit. I have two approaches for setting values in IN clause of sql statement. I want to know which error setting value one of the below code is more efficient (priority is optimization and maintainability is secondary)? PreparedStatement preparedStatement = sqlObj.prepareStatement( "UPDATE table1 column1=? WHERE table_id IN (" + StringUtils.repeat("?,", paramArray.length-1)+ "?)"); preparedStatement.setInt(1, 0); for(int idx = 0; idx < paramArray.length; idx++) { preparedStatement.setInt(idx+2, paramArray[idx]); } preparedStatement.executeQuery(); Or PreparedStatement preparedStatement = sqlObj.prepareStatement( "UPDATE table1 column1=? WHERE table_id IN (?)"); for(int idx = 0; idx < paramArray.length; idx++) { preparedStatement.setInt(1, 0); preparedStatement.setInt(2, paramArray[idx]); preparedStatement.addBatch(); } preparedStatement.executeBatch(); Edit: Lets say param.length is 50 and this whole code exectutes 40 times i.e. processing 2000 records. So in first case it will append 50 ? and then set variable for them making one update query and in second case it will create batch of 50 update queries. java sql prepared-statement share|improve this question edited Nov 13 '14 at 4:53 asked Nov 12 '14 at 11:55 Naman Gala 3,7651728 If you're just calling the code once, or rarely, then the first one. Otherwise it depends on how often you make the same update, how many parameters you include each time, and the statement caching of your database. –khelwood Nov 12 '14 at 12:00 I have updated my question. –Naman Gala Nov 12 '14 at 13:12 add a comment| 3 An
can then be used to efficiently execute this statement multiple times. Note: The setXXX methods for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type Integer then setInt should be used. If arbitrary parameter type conversions are required then the setObject method should be used with a target SQL type. See Also: prepareStatement, ResultSet clearParameters() In general, parameter values remain in force for repeated use of a Statement. execute() Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled by executeQuery and executeUpdate. executeQuery() A prepared SQL query is executed and its ResultSet is returned. executeUpdate() Execute a SQL INSERT, UPDATE or DELETE statement. setAsciiStream(int, InputStream, int) When a very large ASCII value is input to a LONGVARCHAR parameter, it may be more practical to send it via a java.io.InputStream. setBigDecimal(int, BigDecimal) Set a parameter to a java.lang.BigDecimal value. setBinaryStream(int, InputStream, int) When a very large binary value is input to a LONGVARBINARY parameter, it may be more practical to send it via a java.io.InputStream. setBoolean(int, boolean) Set a parameter to a Java boolean value. setByte(int, byte) Set a parameter to a Java byte value. setBytes(int, byte[]) Set a parameter to a Java array of bytes. setDate(int, Date) Set a parameter to a java.sql.Date value. setDouble(int, double) Set a parameter to a Java double value. setFloat(int, float) Set a parameter to a Java float value. setInt(int, int) Set a parameter to a Java int value. setLong(int, long) Set a parameter to a Java long value. setNull(int, int) Set a parameter to SQL NULL. setObject(int, Object) Set the value of a parameter using an object; use the java.lang equivalent objects for integral values. setObject(int, Object, int) This method is like setObject above, but assumes a scale of zero. setObject(int, Object, int, int) Set the value of a parameter using an object; use the java.lang equivalent objects for integral values. setShort(int, short) Set a parameter to a Java short value. setString(int, String) Set a parameter to a Java String value. setTime(int, Time) Set a parameter to a java.sql.Time value. setTimestamp(int, Timestamp) Set a parameter to a java.sql.Timestamp value. setUnicodeStream(int, InputStream, int) When a very large UNICODE value i