?
Solved

JDBC --> Prepared Statement -->  Error: Invalid parameter number

Posted on 2003-03-24
4
Medium Priority
?
2,852 Views
Last Modified: 2012-08-14
Im writing a program that takes a whole dump load of data from a raw file and sticks it into a database table.
Im using a prepared statement that has 22 question marks in it... all of them strings.  The error is occuring when i try to do executeUpdate() on my statement.  Im sorry i dont have any more points..  but i would appreciate any help anyone could give me...  Thanks.
-RighteousRaven

the error i get is:
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0612E  Invalid parameter number. SQLSTATE=S1093
     at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throwParamIndexError(SQLExceptionGenerator.java:483)
     at COM.ibm.db2.jdbc.net.DB2PreparedStatement.setString(DB2PreparedStatement.java:1220)
     at COM.ibm.db2.jdbc.net.DB2PreparedStatement.setString(DB2PreparedStatement.java:1203)
     at Parser.addUpdateToBatch(Parser.java:72)
     at Parser.<init>(Parser.java:117)
     at Parser.main(Parser.java:144)


my simplified code:

import java.io.*;
import java.sql.*;

public class Parser {
     private final int NUM_OF_FIELDS = 22;    

     private final String TEMPLATE_TABLE = "Template";
     private final String SCHEMA = "myData";
     private final String TABLE_LIST_TABLE = "table_list";

     private final String connectionUrl = "jdbc:db2:DTTDATA";
     private final String username = "username";
     private final String password = "pass";
     
     private String[] fields = new String[NUM_OF_FIELDS];
     
     static {
          try {
               Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");
          } catch (Exception e) {}    
     }
     
     private String getCreateTableSQL(String table) {
          String sql = new String("");
          sql = "create table " + SCHEMA + "." + table + " like " + SCHEMA + "." + TEMPLATE_TABLE;
          return sql;
     }
     
     private String getInsertSQL(String tableName) {
          String sql = new String("Insert into " + SCHEMA + "." + tableName + " Values ( '?'");
          for (int i = 1; i < NUM_OF_FIELDS; i++) {
               sql += ", '?'";
          }
          sql += ")";
          return sql;
     }
     
     private void addUpdateToBatch(PreparedStatement s) {
          try {
               for (int i = 0; i < NUM_OF_FIELDS; i++) {
                    s.setString(i+1, fields[i]);    
               }
                        s.executeUpdate();
               //s.addBatch();
          } catch (SQLException e) {
               e.printStackTrace();
          }
     }
     
     private Parser( File outputFile, String tableName) {
         
          System.out.println((new Date()).toString());
          try {
                Connection dbConnection= DriverManager.getConnection(connectionUrl, username, password);
                dbConnection.prepareStatement(getInsertSQL(tableName));
               BufferedReader br = new BufferedReader (new FileReader (outputFile));
               String defectData = null;
               while ( (defectData = br.readLine()) != null) {
                    defectData = defectData.replace('\'', '`').replace('"', '`');                    
                    /* parse data from defectData into fields array!*/
               }
               //statement.executeBatch();              
               statement.close();
               dbConnection.close();
          } catch (IOException e) {
               e.printStackTrace();
          } catch (SQLException e) {
               e.printStackTrace();
          }
     }
}
0
Comment
Question by:RighteousRaven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Expert Comment

by:allahabad
ID: 8199619
Can you post your main  method also . It looks like you need to pass less than 22 params in prepare statement(Invalid parameter number).
0
 
LVL 2

Accepted Solution

by:
amit_chauhan earned 270 total points
ID: 8199738
Hi,
Dont put ' (single quote) before and after '?' in the method getInsertSQL. The method will be like this :

    private String getInsertSQL(String tableName) {
         StringBuffer sql = new StringBuffer("Insert into " + SCHEMA + "." + tableName + " Values ( ?");
         for (int i = 1; i < NUM_OF_FIELDS; i++) {
              sql.append (", ?");
         }
         sql.append (")");
         return sql.toString ();
    }

See if it works.
Couldnt find anything else thats wrong.

Thanks
Amit
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8200841
>>  sql += ", '?'";

You don't need to give single quotes around the question mark symbol.

>> Invalid parameter number

This error generally comes only when the number of arguments to PreparedStameent is violated. Please verify if you have 22 or 21 columns or not. (Are all of them Strings?).

Mayank.
0
 

Author Comment

by:RighteousRaven
ID: 8202612
Thanks everyone!

i think i got it working....  and the problem was the single quotes around the placeholders...

thanks for your help all!

-RighteousRaven
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question