SQLite inserting ID

[JanriKallas]

When I insert new row(?) to table, I want each row has their own ID

Should i do it like this?

  
ID = 0 
  
function() 
    if (dbExec( connection, "INSERT INTO abc (id,x,y,z) VALUES (?,?,?,?)",ID,x,y,z))) then  
       ID = ID+1 
    end 
end 
  

or how?

[JanriKallas]

I found this

  
CREATE SEQUENCE SEQ_USER START WITH 1 INCREMENT BY 1; 
We will specify that we want to use the sequence and the NEXTVAL function in the INSERT INTO statement as follows: 
  
INSERT INTO USER_TABLE VALUES (SEQ_USER.NEXTVAL, 'Washington', 'George');  
  

so i tried

  
dbExec("CREATE SEQUENCE SEQ_USER START WITH 1 INCREMENT BY 1") 
dbExec(connection, "INSERT INTO USER_TABLE VALUES (?,?,?,?)",SEQ_USER .NEXTVAL,x,y,z) 
  

but it gets only errors

attempt to index global "SEQ_USER" (a nil value)

[JanriKallas]

Problem solved..

  
dbExec(CREATE TABLE USER_TABLE (id INT NOT NULL AUTO_INCREMENT, x INT, y INT, z INT, PRIMARY KEY (id)) 
  

so everytime you insert it automatically puts an ID

[Walid]

Try to use sth like this :

local db = dbConnect("sqlite", "databaseName.db") -- Put your database here. 
  
function getNewID() 
    local result = dbPoll(dbQuery(db, "SELECT ID FROM YourTable ORDER BY ID ASC"), -1) 
        newID = false 
            for i, id in pairs (result) do 
                if id["ID"] ~= i then -- ID column 
                newID = i 
            break 
        end 
    end 
    if newID then  
        return newID  
    else return #result + 1  
  end 
end 

[JanriKallas]

I tried but it gets all the time ID 1 when inserting new row??

[JanriKallas]

oh my bad, case sensitive

Thanks a lot really!

[Walid]

np.