SQL - INSERT and catch the id auto-increment value

Question 1

What is the best way to get the auto-id value in the same SQL with a SELECT?

A forum said adding this "; has Return Scope_Identity()"
in the end of the SQL works in ASP.

Is there a corresponding way in PHP?

Question 2

It depends on your database server. Using MySQL, call mysql_insert_id() immediately after your insert query. Using PostgreSQL, first query "select nextval(seq)" on the sequence and include the key in your insert query.

Querying for "select max(id) + 1 from tbl" could fail if another request inserts a record simultaneously.

Question 3

You can do select max(id)+1 when you do the insertion.

For example:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT MAX(id) + 1 FROM log), 'rev_Id', 'some description')

Note that this will fail on an empty table since there won't be a record with id is 0 but you can either add a first dummy entry or change the sql statement to this:

INSERT INTO Log (id, rev_no, description) VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM Log), 'rev_Id', 'some description')

Question 4

No, I don't think this is possible.

You can create a UNIQUE INDEX which has essentially the same effect as a PRIMARY KEY:

CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

Besides, I fail to see the logic of your schema, that is -> if a column is autoincrement and you don't intend to mess with the values manually, it's going to be unique anyway, so it makes a good simple short primary key. Why the composite? You may have good reasons to make another index on the combination of columns, though.

Question 5

Through plain SQL:

 insert into language(name) values ('value');
 SELECT IDENTITY_VAL_LOCAL();

See the manual for details: http://db.apache.org/derby/docs/10.7/ref/rrefidentityvallocal.html

When doing this from a Java class (through JDBC) you can use getGeneratedKeys() after "requesting" them with the approriate executeUpdate() method.

Question 6

The primary key for SQLite tables is called _id. It is auto incrementing, and you should not be trying to insert values into it.

gamesdatabase = openOrCreateDatabase("GamesDatabase", MODE_PRIVATE, null);
gamesdatabase.execSQL("CREATE TABLE IF NOT EXISTS Games (_id INTEGER PRIMARY KEY, Name
VARACHAR, NPlayers INT(1), NRounds INT(2), WinScore INT(2));");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('TAWLA',2,0,0 );");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('DOMANA',4,0,0 );");


Cursor c = gamesdatabase.rawQuery("SELECT * FROM Games", null);
c.moveToFirst();
while (c.isAfterLast() == false) {
Log.d("BEZRA", String.valueOf(c.getInt(c.getColumnIndex("_id"))));
c.moveToNext();
}

SQL - INSERT and catch the id auto-increment value

Answers

php,sql,auto-increment

Browse Other Code Languages