-2

I want to insert a row in a table in Oracle SQL Developer. I have a set of Strings, String name, String address, and String contact.

It won't work if I use this code:

Connection conn = null;
Statement stmt = null;
String URL = "jdbc:oracle:thin:mariel@//localhost:1521/XEXDB";
String USER = "mariel";
String PASS = "1234";
String name = "a", address = "a", contact="a";

try {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  try {
    conn = DriverManager.getConnection(URL, USER, PASS);
    stmt = conn.createStatement();

    String sql = "INSERT INTO CUSTOMER" +
                 "VALUES(CustNumSeq.NEXTVAL, name, address, contact)";
    stmt.executeUpdate(sql);
  } 
  catch (SQLException ex) {
               Logger.getLogger(FinishTransaction.class.getName()).log(Level.SEVERE, null, ex);
  }
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
}
finally{
   try{
     if(stmt!=null)
        stmt.close();
   }
   catch(SQLException se2){
   }
   try{
      if(conn!=null)
      conn.close();
   }
   catch(SQLException se){
      se.printStackTrace();
   }
}
Mariel
  • 169
  • 2
  • 5
  • 18
  • I would guess that it fails because you have no whitespace between `CUSTOMER` and `VALUES` – JonK Dec 24 '15 at 14:46
  • 2
    You need to know how to pass parameters to your SQL. You'll want to read: [Using Prepared Statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to learn how to do that. But it should be mentioned that you are not being clear at all as to how your code doesn't work, what error you are getting, etc... – sstan Dec 24 '15 at 14:47
  • Where do the values for `name`, `address` and `contact` come from? You've declared the variables but you never do anything with them – JonK Dec 24 '15 at 14:51

1 Answers1

1

modify your code as below

String sql = "INSERT INTO CUSTOMER " +
                 "VALUES(CustNumSeq.NEXTVAL, name, address, contact)"

you must keep a space between table name(CUSTOMER) and VALUES

As per modified post

 String sql = "INSERT INTO CUSTOMER " +
                     "VALUES('"+ name+"','"+ address+"','" +contact+"')"

Note: You should always prefer to use PreparedStatement than Statement as PreparedStatement is having much advantages than Statement.Please see the link for more info

Community
  • 1
  • 1
SpringLearner
  • 13,738
  • 20
  • 78
  • 116
  • This is just the tip of the iceberg. OP has bigger problems. For instance, how do you suppose the SQL will interpret `name, address, contact`? – sstan Dec 24 '15 at 14:50
  • thats next problem which I think OP has not mentioned. OP just said *I want to add a row in a table in sql developer.* and because the SQL is wrong so no rows will be inserted. Now atleast *CustNumSeq.NEXTVAL, name, address, contact* will be inserted – SpringLearner Dec 24 '15 at 14:53
  • No, it won't. The SQL is still invalid. – sstan Dec 24 '15 at 14:54
  • i initialized name, address, and contact as "a". – Mariel Dec 24 '15 at 14:56
  • 1
    I already did: In its current form, the SQL cannot interpret `name, address, contact`. Those are not referring to the Java variable names. They are just literals in SQL that Oracle will have no clue about. the proper solution is to bind the variables in using prepared statements. Suggesting concatenating strings is also not ideal, as it's easy to miss a single quote here or there, the SQL can break if one of the values has a single quote, and it's vulnerable to SQL injection. – sstan Dec 24 '15 at 14:59
  • @Mariel try as per the modified answer – SpringLearner Dec 24 '15 at 15:01
  • 2
    Suggesting that the OP dump potentially unsanitised strings directly into a SQL statement is asking for trouble... – JonK Dec 24 '15 at 15:02
  • @JonK its ok for beginners. I want to give only the mistake where the OP is making instead of changing the all way – SpringLearner Dec 24 '15 at 15:07
  • 1
    I disagree with that - we should be teaching beginners the best practice to start with, especially when it's not even that complicated to do – JonK Dec 24 '15 at 15:08
  • @JonK we should not change the OP's idea.And why you are sticking with SQL part why dont you suggest other part which OP is not doing in an efficient way – SpringLearner Dec 24 '15 at 15:12
  • Try inserting *John O'Neill* as the name, and see if that still works. Completely agree with @JonK. Not to mention that in some databases, including Oracle, failing to use parameter binding has a direct impact on performance because of excessive query parsing, and it can bloat the database server memory. I'm not saying that a beginner needs to learn all of those details at once, but the point is that if they follow best practices, they won't need to worry, and they'll be fine. – sstan Dec 24 '15 at 15:12
  • There's quite a big difference between "could be written in a cleaner way" and "has a gaping security vulnerability in it". But yes, it'd be nice to see try-with-resources here, but that's a coding style issue, and we don't know what version of Java the OP is using. I still routinely have to maintain Java 1.6 codebases for instance. – JonK Dec 24 '15 at 15:14
  • @sstan That would be another problem for OP we have to answer as per the current scenario and as per the current inputs. – SpringLearner Dec 24 '15 at 15:21
  • Spring, all you have to do is make mention of the concerns raised in your answer, not overhaul the whole thing bud – Drew Dec 30 '15 at 06:23
  • @Drew I am not modifying because I think its different from what OP is facing – SpringLearner Dec 30 '15 at 06:29
  • It is our self-imposed responsibility to point out things. I am talking about a sentence or two at the bottom, not code changes. Instead, all this bickering under your question. It's noise that doesn't need to be there. Moreover, half of us keep stumbling into this question because it is in the Close Vote review queue and will be for a week maybe – Drew Dec 30 '15 at 06:30
  • @Drew I have modified – SpringLearner Dec 30 '15 at 06:40