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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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        lt ----- this ps setString 2  germany       lt ---- and this ps executeUpdate

User · Answer

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

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

Examples related to java

Examples related to jdbc

Examples related to oracle11g