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();
The first problem is that your query string is wrong:
I think this: "INSERT INTO employee(hans,germany) values(?,?)"
should be like this: "INSERT INTO employee(name,country) values(?,?)"
The other problem is that you have a parameterized PreparedStatement and you don't set the parameters before running it.
You should add these to your code:
String inserting = "INSERT INTO employee(name,country) values(?,?)";
System.out.println("insert " + inserting);//
PreparedStatement ps = con.prepareStatement(inserting);
ps.setString(1,"hans"); // <----- this
ps.setString(2,"germany");// <---- and this
ps.executeUpdate();
In your INSERT statements:
INSERT INTO employee(hans,germany) values(?,?)
You've got your values where your field names belong. Change it to be:
INSERT INTO employee(emp_name,emp_address) values(?,?)
If you were to run that statement from a SQL prompt, it would look like this:
INSERT INTO employee(emp_name,emp_address) values('hans','germany');
Note that you'd need to put single quotes around the string/varchar values.
Additionally, you are also not adding any parameters to your prepared statement. That is what's actually causing the error you're seeing. Try this:
PreparedStatement ps = con.prepareStatement(inserting);
ps.setString(1, "hans");
ps.setString(2, "germany");
ps.execute();
Also (according to Oracle), you can use "execute" for any SQL statement. Using "executeUpdate" would also be valid in this situation, which would return an integer to indicate the number of rows affected.
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();
See the link below for information about how to use PreparedStatement. I have also quoted from the link.
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named updateSales:
updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey());
Source: Stackoverflow.com