Android Question SQLite ID INTEGER PRIMARY KEY AUTOINCREMENT Insert

M

Mahares

Expert
Licensed User
Longtime User
This is somewhat triggered by another thread very recently created by a member where thanks to too many answers became a little muddled. So, I created this one fresh for an independent table not joined to another table:

Although a NULL or a number (not used previously) can be inserted in ID column, I like to go further with this:
  1. Do you agree that if a number (not used previously as ID and became available because of a row deletion) can be accepted by the INSERT query. Example: 845 and the last ID is: 874
  2. Do you agree that if a number (not used previously as ID) and enclosed in double quotes can be accepted by the INSERT query. Example: “901”.
  3. Do you agree that if you are inserting new records and you are listing some or all the column names in the INSERT query, you can leave out the ID and it will automatically be filled with the incremented number.
I am really interested only in any detailed answers if you only disagree with any of the above statements.
 
Sort by date Sort by votes

Erel

B4X founder
Staff member
Licensed User
Longtime User
Worth reading:
B4X: 
#AdditionalJar: sqlite-jdbc-3.7.2
Sub Process_Globals
  
End Sub

Sub AppStart (Args() As String)
    Log("Hello world!!!")
    Dim sql As SQL
    File.Delete(File.DirApp, "1.db")
    sql.InitializeSQLite(File.DirApp, "1.db", True)
    sql.ExecNonQuery("CREATE TABLE table1 (col1 INTEGER PRIMARY KEY  AUTOINCREMENT , col2 TEXT)")
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col1, col2) VALUES (?, ?)", Array(100, "test"))
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '101
    sql.ExecNonQuery("DELETE FROM table1 WHERE col1 = 2")
    sql.ExecNonQuery2("INSERT INTO table1 (col1, col2) VALUES (?, ?)", Array(2, "test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '2
    sql.ExecNonQuery2("INSERT INTO table1 (col2) VALUES (?)", Array("test"))
    Log(sql.ExecQuerySingleResult("SELECT last_insert_rowid()")) '102
End Sub

1. Yes.
2. Not tried it. Will probably work. Don't see any reason to do it.
3. Yes.
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Erel said:
2. Not tried it. Will probably work. Don't see any reason to do it.
Click to expand...
Nice concise example Erel. I have a B4A SQLite test database with few columns and the ID I tried them all and confirm that #2 works too, but I agree there is no reason to enclose it in double quotes. You were well prepared.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…