.nextval JDBC insert problem

Question

I try to insert into table with sequence .nextval as primary key, the sql in Java is

sql = "INSERT INTO USER 
         (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) 
       VALUES 
         (?,?,?,?,?)";
   ps = conn.prepareStatement(sql);
   ps.setString(1, "User.nextval");
   ps.setString(2, accountNumber);
   ps.setString(3, firstName);
   ps.setString(4, lastName);
   ps.setString(5, email);

However, the error is ORA-01722: invalid number

All the other fields are correct, I think it is the problem of sequence, is this correct?

Is the name of your sequence User? It needs to be SequenceName.NextVal Also, just use the prepared statement for everything BUT the sequencename.NextVal — Stefan H, Dec 21 '10 at 04:48
I don't do Oracle, but in PostgreSQL they (the sequences which are created as `SERIAL PRIMARY KEY` type) are auto-incremented on every `INSERT`. You would usually like to omit it from the `INSERT` statement so that the DB will handle it itself. So, in your case, you'd likely omit `USER_PK` column from your SQL. MySQL, DB2 and SQLServer also has similar constructs. — BalusC, Dec 21 '10 at 04:50
@BalusC: SQL Server 2011 will support sequences - now just need to get MySQL to do it :/. Oracle doesn't have anything similar to PostgreSQL's `serial` that I'm aware of. — OMG Ponies, Dec 21 '10 at 04:54

score 16 · Accepted Answer · edited Dec 23 '16 at 07:46

16

The problem is that the first column is a numeric data type, but your prepared statement is submitting a string/VARCHAR data type. The statement is run as-is, there's no opportunity for Oracle to convert your use of nextval to get the sequence value.

Here's an alternative via Java's PreparedStatement syntax:

sql = "INSERT INTO USER 
        (USER_PK, ACCOUNTNUMBER, FIRSTNAME, LASTNAME, EMAIL ) 
       VALUES 
        (user.nextval, ?, ?, ?, ?)";
ps = conn.prepareStatement(sql);
ps.setString(1, accountNumber);
ps.setString(2, firstName);
ps.setString(3, lastName);
ps.setString(4, email);

This assumes that user is an existing sequence -- change to suit.

edited Dec 23 '16 at 07:46

Andrew Tobilko

48,120
14
91
142

answered Dec 21 '10 at 04:47

OMG Ponies

325,700
82
523
502

3

Though personally, this would be an Oracle function if it were up to me. Then the sequence reference would be in the function, just supply the same parameters for insertion. – OMG Ponies Dec 21 '10 at 04:53
Worked like a charm THANKS! :D – Skizo-ozᴉʞS ツ Apr 25 '17 at 09:09

.nextval JDBC insert problem

1 Answers1

Linked