Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-24
4
Medium Priority
?
2,894 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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

572 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