Hi, could someone provide an example of how to select data from an sqlite database using binding. I would like to get an image from a database, but I cannot figure out how to do this. I googled a lot but it not clear.
--you open a databaselocal myDB=sqlite3.open('MyDatabase.sqlite3')-- open--then you write prepared statementlocal statement = myDB:prepare("SELECT * from table WHERE fieldname = ?")--then you bind value to prepared statements
statement:bind("somevalue")--execute your prepared query
statement:exec()--iterate through resultfor row in statement:rows()doprint(row.id, row.data)end--free statement
statement:finalize()
myDB:close()-- close
Weekend is probably a bad time to get your questions answered.
I haven't used sqlite in Gideros due to fact, that its IOS only. But someone who can test IOS and have used SQLlite will probably answer your question.
Till then it seems that prepare method does not return statement. Thus these leads to conclusion if SQL statement that you want to prepare valid? Does the table exist and is there a specified field in that table?
Oh, and you should store your DB in documents directory, as
local myDB=sqlite3.open('|D|MyDatabase.sqlite3')-- open
require"lsqlite3"-- open the databaselocal db = sqlite3.open("|D|db.sqlite3")-- check if 'numbers' table existslocal exists =false
db:exec("SELECT name FROM sqlite_master WHERE type='table' AND name='numbers'", function() exists =trueend)-- if not, create it and put some valuesifnot exists thenlocal sql =[[
CREATE TABLE numbers(num,str);
INSERT INTO numbers VALUES(1,'ABC');
INSERT INTO numbers VALUES(2,'DEF');
INSERT INTO numbers VALUES(3,'UVW');
INSERT INTO numbers VALUES(4,'XYZ');]]
db:exec(sql)end-- create a prepared statementlocal stmt = db:prepare("SELECT * FROM numbers WHERE num=? AND str=?")
stmt:bind(1, 1)-- bind 1st value as 1
stmt:bind(2, "ABC")-- bind 2nd value as "ABC"-- execute and print the resultfor num,str in stmt:urows()doprint(num, str)end-- close the database
db:close()
Comments
http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki
Simple example:
http://www.mroth.net/lua-sqlite3/documentation.html
http://www.nessie.de/mroth/lua-sqlite3/index.html
it always fails on
statement:bind("somevalue").
Small working example would be nice if someone has the time.
What error does it give?
stack traceback:
main.lua:9: in main chunk
I haven't used sqlite in Gideros due to fact, that its IOS only. But someone who can test IOS and have used SQLlite will probably answer your question.
Till then it seems that prepare method does not return statement.
Thus these leads to conclusion if SQL statement that you want to prepare valid? Does the table exist and is there a specified field in that table?
Oh, and you should store your DB in documents directory, as
http://www.giderosmobile.com/forum/discussion/1402/where039s-the-sqlite-database-stored-and-how-to-make-it-persistent#Item_1
Thanks for your help but this is not working. I can query the table and get data with a static string like
for r in myDB:nrows("select tile_id from map WHERE zoom_level = 0 AND tile_row = 0")
but I cannot concatenate string dynamically to change the query.
Or using the prepare statement I cannot bind. It could be me. has anyone actually used the plugin????
atilim what testing has been done with the sqlite plugin? are you able to help with a small demo with binding parameters?
Here is a complete example. Hope this helps:
http://docs.giderosmobile.com/reference/plugin/sqlite3#sqlite3