I am trying to insert a record in a table but I keep getting error. First I tried:
local vari = "any name"
local today = os.date("%x")
local dbPref = sqlite3.open(path)
dbPref:exec[[
CREATE TABLE viewed (vDate DATE PRIMARY KEY, templeFile);
INSERT INTO viewed VALUES (today, vari)
]]
It didn’t saved. So, I move on to the stmt (as stated in the sqlite documentation):
local vari = "any name"
local today = os.date("%x")
local dbPref = sqlite3.open(path)
dbPref:exec[[
CREATE TABLE viewed (vDate DATE PRIMARY KEY, templeFile);
]]
stmt = dbPref:prepare[[INSERT INTO viewed VALUES (:vDate, :templeName)]]
stmt:bind{ vDate = today, templeName = "Hello World" }:exec()
Error stills. Am I missing something?
Thanks,
Alex [import]uid: 4883 topic_id: 1056 reply_id: 301056[/import]
Hi,
I tried to do exactly the same thing (making queries with variables, it could be useful lol)
Unfortunately, I have an error message on the bind function (something like “number expected, receiving table”).
Can someboy post a sample code with the working parameters please ?
And by the way, I have a veeery silly question but I’ll ask anyway : when you create a database in an app, when you exit and open it again, does this database still exist ? Is it possible to test if this db exists (to avoid creating it again and erase all the data)?
I couldn’t work in my app these past days so, no news in your first question. About the second one, what I am doing (don’t know if it is the best way, neither if it will work on the device - but it is working well in the simulator):
if you create a database (using the sample provided by Ansca) in memory, it will not exist next time;
in order to have the database created in the disk, you should a) create it via Firefox SQL Manager addon (easier than create by code - also you can insert data in an IDE/etc, OR b) create by code:
-- if your database does not exist, it will be prepared for existence
db = sqlite3.open("fileName.sqlite3", system.ResourceDirectory )
db:exec( "CREATE TABLE test (id, data)" )
db:exec[[INSERT INTO test VALUES (1, "Hello World")]]
In sum, to test if the database exists, before to create it or not, I am using the io.read function:
local path = system.pathForFile( "filename.sqlite3", system.DocumentsDirectory )
-- io.open opens a file at path. returns nil if no file found
local file = io.open( path, "r" )
if file then
-- closde io function and read all contents of file into a string
io.close( file )
db = sqlite3.open("fileName.sqlite3", system.ResourceDirectory )
db:exec( "SELECT \* FROM test" )
else
-- database does not exists
db = sqlite3.open("fileName.sqlite3", system.ResourceDirectory )
db:exec( "CREATE TABLE test (id, data)" )
db:exec[[INSERT INTO test VALUES (1, "Hello World")]]
end
[import]uid: 4883 topic_id: 1056 reply_id: 2648[/import]
I tried a few things, but no clue yet. The problem comes from the first argument of the bind function, that has to be the ID of the element you insert in the table (if I understood well…). I tried with numbers but I still have the error message… [import]uid: 4431 topic_id: 1056 reply_id: 2653[/import]
Same happens with me. I’ve tried several way, no success till now:
require "sqlite3"
local path = system.pathForFile( "myDb.sqlite", system.ResourceDirectory )
local db = sqlite3.open(path)
local today = os.date("%x")
local tName = "Alexandre Souza"
local q1 = 'INSERT INTO viewed VALUES ("'..today..',"'..tName..'")'
print(q1) --prints the string correctly
--First try
db:exec[[q1]] --DO NOT EXECUTE anything neither returns an error
--Second try
db:exec[[INSERT INTO viewed VALUES (?,?),"12/12/12","Alex"]] --DO NOT EXECUTE anything neither returns an error
--Third try
db:exec[[INSERT INTO viewed VALUES (?,?)],["12/12/12","Alex"]] --DO NOT EXECUTE anything neither returns an error
--Fourth try
insert\_stmt = db:prepare[[
INSERT INTO viewed VALUES (?, ?);
INSERT INTO viewed VALUES (?, ?)
]]
function insert(id1, data1, id2, data2)
insert\_stmt:bind(id1, data1, id2, data2)
insert\_stmt:exec()
end
insert( 1, "Hello World", 2, "Hello Lua" )
insert( 3, "Hello Sqlite3", 4, "Hello User" )
This returns the following error in the console
Runtime error
/Users/mac/Desktop/untitled folder/main.lua:34: attempt to call method ‘exec’ (a nil value)
stack traceback:
[C]: in function ‘exec’
/Users/mac/Desktop/untitled folder/main.lua:34: in function ‘insert’
/Users/mac/Desktop/untitled folder/main.lua:37: in main chunk
Runtime error: /Users/mac/Desktop/untitled folder/main.lua:34: attempt to call method ‘exec’ (a nil value)
stack traceback:
[C]: in function ‘exec’
/Users/mac/Desktop/untitled folder/main.lua:34: in function ‘insert’
/Users/mac/Desktop/untitled folder/main.lua:37: in main chunk
Any help from somebody else? [import]uid: 4883 topic_id: 1056 reply_id: 2697[/import]