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

    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
    +1 -1Share on Facebook

    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
      http://appcodingeasy.com/Gideros-Mobile
      +1 -1Share on Facebook
    • zakkwyldezakkwylde Member
      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.
      +1 -1Share on Facebook
    • ar2rsawseenar2rsawseen Maintainer
      Hmm, it should have worked.

      What error does it give?
      http://appcodingeasy.com/Gideros-Mobile
      +1 -1Share on Facebook
    • zakkwyldezakkwylde Member
      main.lua:9: attempt to index local 'statement' (a nil value)
      stack traceback:
      main.lua:9: in main chunk
      +1 -1Share on Facebook
    • zakkwyldezakkwylde Member
      anyone?
      +1 -1Share on Facebook
    • ar2rsawseenar2rsawseen Maintainer
      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
      http://appcodingeasy.com/Gideros-Mobile
      +1 -1Share on Facebook
    • zakkwyldezakkwylde Member
      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?
      +1 -1Share on Facebook
    • 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()
      http://en.wikipedia.org/wiki/Cytorus
      +1 -1Share on Facebook
    • BBurgess66BBurgess66 Member
      none of this is working for me either .. is it not working only on the Gideros Player?
      +1 -1Share on Facebook
    • BBurgess66BBurgess66 Member
      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.
      +1 -1Share on Facebook
    • ar2rsawseenar2rsawseen Maintainer
      For Android, did you copy the database in the Documents folder as described here:
      http://docs.giderosmobile.com/reference/plugin/sqlite3#sqlite3
      http://appcodingeasy.com/Gideros-Mobile
      +1 -1Share on Facebook
    • BBurgess66BBurgess66 Member
      yes .. everything as mentioned here. But is fixed, it was two different syntax problems in the code ;-)
      +1 -1Share on Facebook
    Sign In or Register to comment.