I don’t think I understand your question but I’ll try to answer it anyway.
First, let’s go over the code to open a database (or create one if it doesn’t exist). In this scenario, our database is named data.db and it’s located in the documents directory.
[lua]require( ‘sqlite3’ )
local pathForFile = system.pathForFile( ‘data.db’, system.DocumentsDirectory )
local db = sqlite3.open( pathForFile )[/lua]
Now the local variable db can be used to access our database. The following step is to create a table. I’m going to create a table named myTable with a primary key id and a text variable named myColumn.
[lua]local query = [[CREATE TABLE IF NOT EXISTS myTable (id INTEGER PRIMARY KEY, myColumn TEXT);]]
db:exec(query)[/lua]
Next, I’m going to create a lua table t and copy all existing records of myTable into t. The operand * is used to select all records. Since we haven’t added any records, myTable should be empty and so should be t.
[lua]local t = {}
query = [[SELECT * FROM myTable;]]
for row in db:nrows(query) do
table.insert( t, row )
end[/lua]
Now we’re going to use a very handy code snippet to determine whether t is empty or not. In this case, since we know t is empty it should return true.
[lua]function table.empty( self )
for _, _ in pairs( self ) do
return false
end
return true
end[/lua]
We’re going to use table.empty in a conditional statement. If it’s empty (which it is) then we’ll insert a record.
[lua]if table.empty( t ) then
query = [[INSERT INTO myTable VALUES (NULL, ‘myValue’);]]
db:exec(query)
end[/lua]
Notice that the first value is set to NULL. This is because the first value corresponds to the primary key, which automatically inserts a sequential integer starting from 1.
Now we have successfully created a database, created a table, checked to see if the table was empty, and added our first record into the table.
I hope this solves your problem and please try to ask more coherent questions in the future.
