Error "column not allowed" executing query using JDBC

Question

 import java.util.Scanner;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.Statement;
      public class H {

              public static void main(String[] args) throws Exception {

                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                   Connection conn = DriverManager.getConnection("jdbc:odbc:dsn1","system","mandriva");
                   Statement stmt = conn.createStatement();
                   Scanner sc = new Scanner(System.in);

                String fName, lName, sql, input;
                 int id, age;

               do {

                  System.out.println("Enter ID");
                  id = sc.nextInt();
                  System.out.println("Enter First Name");
                  fName = sc.next();
                  System.out.println("Enter Last Name");
                  lName = sc.next();
                  System.out.println("Enter Age");
                  age = sc.nextInt();

                  sql = "insert into employee values(" + id + "," +  fName + "," +  lName + "," +  age + ")";
                 stmt.execute(sql);

              System.out.println("Do you want to insert one more(y/n)?");
              input = sc.next();
            } while("y".equals(input));

         System.out.println("done");
     }
 }

When I run this program, after entering the info, it shows the following error:

Exception in thread "main" java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00984: column not allowed here at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at SQL1.H.main(H.java:30)

Can anybody explain why this exception occurs?

the columns which are varchar, you need to wrap the values in ''. — Hirak, Jun 02 '14 at 11:52
Use a prepared statement: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet, Jun 02 '14 at 11:55

Ambrish · Answer 1 · 2014-06-02T12:03:07.573

Use Prepare stmt instead of passing inline parameters. Also provide column name in your insert statement:

// Assuming columns are: id, first_name, last_name, age
sql = "insert into employee(id, first_name, last_name, age) values(?, ?, ?, ?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, id);
pstmt.setString(2, fName);
pstmt.setString(3, lName);
pstmt.setInt(4, age);

pstmt.executeUpdate();

Martinus_ · Answer 2 · 2014-06-02T20:42:33.973

0

for insert (for all DML ) you have to use executeUpdate() method .

edited Jun 02 '14 at 20:42

answered Jun 02 '14 at 11:55

Martinus_

160
1
3
13

Insert is not DDL (Data Definition Language), it is DML (Data Manipulation Language) – Mark Rotteveel Jun 02 '14 at 15:47

score -3 · Answer 3 · answered Jun 02 '14 at 11:57

-3

you forgot the single quote mark for varchar columns. Use it like this

sql = "insert into employee values(" + id + ",'" +  fName + "','" +  lName + "'," +  age + ")";

answered Jun 02 '14 at 11:57

Manish Mudgal

1,166
1
9
24

Error "column not allowed" executing query using JDBC

3 Answers3