java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Question

I made some Java 1.6-Oracle11g-JDBC (using OJDBC 6) code (below). I am getting an exception - java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Why is this happening and how do I fix it ?

My output is-

create CREATE TABLE employee(emp_name varchar(25),emp_address varchar(25)) insert INSERT INTO employee(jim,germany) values(?,?) Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1 

The code is-

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement;   public class Oracle {  public static void main(String[]args) {      try     {          Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "newman", "123456");         Statement stmt = con.createStatement();           String create = "CREATE TABLE employee(emp_name varchar(25),emp_address varchar(25))";         System.out.println("create " + create);//         stmt.execute(create);          //insert 1st row                     String inserting = "INSERT INTO employee(hans,germany) values(?,?)";         System.out.println("insert " + inserting);//         PreparedStatement ps = con.prepareStatement(inserting);          ps.executeUpdate();          //insert 2nd row                     inserting = "INSERT INTO employee(david,austria) values(?,?)";         System.out.println("insert " + inserting);//         ps = con.prepareStatement(inserting);          ps.executeUpdate();      }catch(SQLException ex){System.out.println("Exception: " + ex);}       }  } 

EDIT - To correct the code, we use-

//insert 1st row

        String inserting = "INSERT INTO                      employee(emp_name,emp_address) values(?,?)";         PreparedStatement ps = con.prepareStatement(inserting);         System.out.println("insert " + inserting);//         ps.setString(1, "hans");         ps.setString(2, "germany");         ps.executeUpdate(); 

//insert 2nd row

        inserting = "INSERT INTO                      employee(emp_name,emp_address) values(?,?)";         ps = con.prepareStatement(inserting);         System.out.println("insert " + inserting);//         ps.setString(1, "david");         ps.setString(2, "austria");          ps.executeUpdate(); 

Answer 1

This is not how SQL works:

INSERT INTO employee(hans,germany) values(?,?) 

The values (hans,germany) should use column names (emp_name, emp_address). The values are provided by your program by using the Statement.setString(pos,value) methods. It is complaining because you said there were two parameters (the question marks) but didn't provide values.

You should be creating a PreparedStatement and then setting parameter values as in:

String insert= "INSERT INTO employee(emp_name,emp_address) values(?,?)"; PreparedStatement stmt = con.prepareStatement(insert); stmt.setString(1,"hans"); stmt.setString(2,"germany"); stmt.execute(); 

Answer 2

You must use the column names and then set the values to insert (both ? marks):

//insert 1st row             String inserting = "INSERT INTO employee(emp_name ,emp_address) values(?,?)"; System.out.println("insert " + inserting);// PreparedStatement ps = con.prepareStatement(inserting);  ps.setString(1, "hans"); ps.setString(2, "germany"); ps.executeUpdate();