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();

Shouldn't it be INSERT INTO employee(emp_name, emp_address) ... and then set the parameters? — markus
– markus, Commented Aug 8, 2012 at 19:31

bvrakvs · Accepted Answer · 2017-09-14 07:17:55Z

35

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();

edited Sep 14, 2017 at 7:17

bvrakvs

14.7k4 gold badges55 silver badges96 bronze badges

answered Aug 8, 2012 at 19:27

Jim Garrison

87k20 gold badges162 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Orellana · Accepted Answer · 2012-08-08 19:28:52Z

5

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();

answered Aug 8, 2012 at 19:28

Frank Orellana

1,9381 gold badge22 silver badges29 bronze badges

Comments

bvrakvs · Accepted Answer · 2017-09-14 07:17:10Z

4

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();

edited Sep 14, 2017 at 7:17

bvrakvs

14.7k4 gold badges55 silver badges96 bronze badges

answered Aug 8, 2012 at 19:28

Razvan

10.2k6 gold badges41 silver badges51 bronze badges

Comments

Aaron · Accepted Answer · 2012-08-08 19:53:07Z

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.

Aaron Kurtzhals · Accepted Answer · 2012-08-08 19:29:39Z

0

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());

answered Aug 8, 2012 at 19:29

Aaron Kurtzhals

2,0343 gold badges17 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related