CODEWITHSUNDEEP
javasqlitesql-insertauto-increment
Anthea
Anthea

Reputation: 3809

ROWID INTEGER PRIMARY KEY AUTOINCREMENT - How to insert values?

I created an SQLite table in Java:

create table participants (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, col1,col2);

I tried to add rows :

insert into participants values ("bla","blub");

Error:

java.sql.SQLException: table participants has 3 columns but 2 values were supplied

I thought ROWID would be generated automatically. I tried another solution:

PreparedStatement prep = conn.prepareStatement("insert into participants values (?,?,?);");
Integer n = null;
prep.setInt(1,n);
prep.setString(2, "bla");
prep.setString(3, "blub");
prep.addBatch();
prep.executeBatch();

I received a null pointer exception at prep.setInt(1,n);. Do you see the fault?

Upvotes: 60

Views: 57609

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you create "person" table with "id" with AUTOINCREMENT and "name" as shown below:

CREATE TABLE person (    -- Here
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT
);

Then, set NULL to "id" of AUTOINCREMENT as shown below. *You can set NULL to "id" which has NOT NULL because "id" has INTEGER PRIMARY KEY:

INSERT INTO person VALUES (NULL, "John"), (NULL, "Tom");

Now, you can insert rows as shown below:

sqlite> SELECT * FROM person;
┌────┬──────┐
│ id │ name │
├────┼──────┤
│ 1  │ John │
│ 2  │ Tom  │
└────┴──────┘

Upvotes: 0

Padhu
Padhu

Reputation: 1580

Easiest way without using column names will be using null in the place of autoincreament is like this

insert into table values (null, col1, col2)

if you have already set the first column as autoincrement, it will work fine.

Upvotes: 34

jgauffin
jgauffin

Reputation: 101130

The reason to your error is that SQL Inserts expect you to provide the same number of values as there are columns in the table when a column specifier is not used.

i.e. when you write a SQL query like this:

INSERT INTO TableName VALUES(a1,a2, ...)

.. the values have to be in the exact same order as in the table definition (and also the same amount). The reason for this is to avoid ambiguity and reduce the numbers of errors.

In your case you have an auto increment column which you don't want to specify a value for. That is of course possible, but following the rules above you need to specify column names:

INSERT INTO TableName (columnName1, columnName2) VALUES(Value1, Value2);

Upvotes: 3

Anthea
Anthea

Reputation: 3809

found a working solution here: 

PreparedStatement prep = conn.prepareStatement("insert into participants values ($next_id,?,?);");
prep.setString(2, "bla");
prep.setString(3, "blub");

Upvotes: 9

seth
seth

Reputation: 1389

Have you tried indicating to which fields of the table the parameters you are passing are supposed to be related?

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

In your case maybe something like:

INSERT INTO participants(col1, col2) VALUES ("bla","blub");

Upvotes: 70

Related Questions