SQLite bind (or insert into with parameters)

Solar2D General Questions/Discussion
#1

Hello all,

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]

#2

What error are you getting? [import]uid: 54 topic_id: 1056 reply_id: 2590[/import]

#3

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)?

Thanks in advance !

[import]uid: 4431 topic_id: 1056 reply_id: 2641[/import]

#4

Tooonyo,

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]

#5

Thanks a lot for the tip, it will help a lot !
If I find something about our first problem I’ll update the post

Bye.
Tonyo [import]uid: 4431 topic_id: 1056 reply_id: 2650[/import]

#6

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]

#7

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]

#8

Hmm…

db is null, not finding the database most likely.

is the databse myDb.sqlite in the systems resources directory?

can you fwd that to me?

carlos [import]uid: 24 topic_id: 1056 reply_id: 2698[/import]

#9

Carlos, db is not null and I can save using:

db:exec[[INSERT INTO viewed VALUES ("11/11/11","Alex")]]

tell me where to send it and I will forward you the code and the db.
Thanks,
Alex [import]uid: 4883 topic_id: 1056 reply_id: 2701[/import]

#10

send it info @ anscamobile.com

c [import]uid: 24 topic_id: 1056 reply_id: 2702[/import]

#11

I tried a few new things, but no clue. Do you have a solution ? Thanks in advance [import]uid: 4431 topic_id: 1056 reply_id: 2752[/import]

#12

Try this

[lua]require “sqlite3”
local path = system.pathForFile( “templesPref.sqlite”, system.ResourceDirectory )
local db = sqlite3.open(“templesPref.sqlite”)
local today = os.date("%x")
local tName = “Alexandre Souza”
local q1 = “INSERT INTO viewed VALUES (’”…today…"’,’"…tName…"’)"
print(q1)
print (db);
db:exec(“CREATE TABLE viewed (id, name)” );
local b = db:exec( q1);
print (b);
db:close();[/lua] [import]uid: 24 topic_id: 1056 reply_id: 2788[/import]