Quick Links: Download Gideros Studio | Gideros Documentation | Gideros community chat | DONATE
SQlite Binding parameters — Gideros Forum

SQlite Binding parameters

zakkwyldezakkwylde Member
edited August 2012 in General questions
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.

Thanks

Comments

  • ar2rsawseenar2rsawseen Maintainer
    edited August 2012
    from http://lua.sqlite.org wiki
    http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki

    Simple example:
    --you open a database
    local myDB=sqlite3.open('MyDatabase.sqlite3')  -- open
     
    --then you write prepared statement
    local 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 result
    for row in statement:rows() do
         print(row.id, row.data)
    end
     
    --free statement
    statement:finalize()
     
    myDB:close()  -- close
    Here are some more examples:

    http://www.mroth.net/lua-sqlite3/documentation.html
    http://www.nessie.de/mroth/lua-sqlite3/index.html
  • Thanks. examples I have found. But I could not get the above to work either.
    it always fails on

    statement:bind("somevalue").

    Small working example would be nice if someone has the time.
  • Hmm, it should have worked.

    What error does it give?
  • main.lua:9: attempt to index local 'statement' (a nil value)
    stack traceback:
    main.lua:9: in main chunk
  • 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
    See this thread for more info:
    http://www.giderosmobile.com/forum/discussion/1402/where039s-the-sqlite-database-stored-and-how-to-make-it-persistent#Item_1
  • Hi,
    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?
  • atilimatilim Maintainer
    edited August 2012
    Hi,

    Here is a complete example. Hope this helps:
    require "lsqlite3"
     
    -- open the database
    local db = sqlite3.open("|D|db.sqlite3")
     
    -- check if 'numbers' table exists
    local exists = false
    db:exec("SELECT name FROM sqlite_master WHERE type='table' AND name='numbers'", function() exists = true end)
     
    -- if not, create it and put some values
    if not exists then
    	local 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 statement
    local 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 result
    for num,str in stmt:urows() do
    	print(num, str)
    end
     
    -- close the database
    db:close()
  • none of this is working for me either .. is it not working only on the Gideros Player?
  • However I got the same to work (in both iOS and Android) with a Titanium generated app. So the SQLite file is valid, as well as the SQL code.
  • For Android, did you copy the database in the Documents folder as described here:
    http://docs.giderosmobile.com/reference/plugin/sqlite3#sqlite3
  • yes .. everything as mentioned here. But is fixed, it was two different syntax problems in the code ;-)
Sign In or Register to comment.