Are there any good or basic tutorials for creating a sqlite database and tables. Along with inserting data into fields to create records from an app. And then retreiving the data to display in the app???
I am sorry for asking but the documentation for Corona is so weak that is it very hard to learn how to do things.
Therefore, I ask, is the syntax for Lua the same for the Corona SDK? Can I find my answers at lua.org or elsewhere?
I just signed up and I am getting very frustrated at the limited documentation. I have some ideas but am having a hard time implementing them without any useful documentation.
I am still unsure of how to set it up. LuaForge just gives some reference material and says I have to install something but no explaination of what to install and where I need to install it.
As a newbie to Corona and Lua this is of little help to me. It is a start and I guess I just need to do a lot more research and testing to understand what it all means.
This is too bad, Corona looked so promising to create apps with it’s easier to read syntax but it looks like it is going to take me a long time just to figure out what the syntax is suppose to be with the limited reference material.
Signed,
Dan the frustrated [import]uid: 4637 topic_id: 1594 reply_id: 4618[/import]
That’s a fair point, I’ll make a note to expand the sqlite docs.
That said I’m not sure we would ever get beyond the basics as far as documenting what sqlite can actually do. It’s a fully ACID compliant SQL DB (SQL-92 spec IIRC), and has many volumes written on using it’s language.
Can you give me some examples of what you’re trying to get passed that isn’t covered in the api guide? I’ll try to write something up for you. [import]uid: 3 topic_id: 1594 reply_id: 4619[/import]
Here’s an updated version of our sqlite sample code the integrates some of the things you’re trying to do.
require "sqlite3"
local db = sqlite3.open\_memory()
local tablesetup = [[CREATE TABLE test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
local testvalue = {}
testvalue[1] = 'Hello'
testvalue[2] = 'World'
testvalue[3] = 'Lua'
local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[[');]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[[');]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[[');]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
print( "version " .. sqlite3.version() )
for row in db:nrows("SELECT \* FROM test") do
local text = row.content.." "..row.content2
local t = display.newText(text, 20, 30 \* row.id, null, 16)
t:setTextColor(255,0,255)
end
Here’s a version that saves the db to a file, and is a little nicer to the db (only trying to create the table if it isn’t there)
This will add three rows every time you relaunch. data.db will exist in ~/
[code]
–Include sqlite
require “sqlite3”
–Open data.db. If the file doesn’t exist it will be created
local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )
–Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == “applicationExit” ) then
db:close()
end
end
–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
–Add rows with a auto index in ‘id’. You don’t need to specify a set of values because we’re populating all of them
local testvalue = {}
testvalue[1] = ‘Hello’
testvalue[2] = ‘World’
testvalue[3] = ‘Lua’
local tablefill =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[2]…[[’);]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[2]…[[’,’]]…testvalue[1]…[[’);]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[3]…[[’);]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
–print the sqlite version to the terminal
print( "version " … sqlite3.version() )
–print all the table contents
for row in db:nrows(“SELECT * FROM test”) do
local text = row.content…" "…row.content2
local t = display.newText(text, 20, 30 * row.id, null, 16)
t:setTextColor(255,0,255)
end
–setup the system listener to catch applicationExit
Runtime:addEventListener( “system”, onSystemEvent )
Thank you so much seanh. That’s all I needed just a lil kick start. And I was pleased to see it worked. The luaforge site made it sound like I had to install something for it to work. [import]uid: 4637 topic_id: 1594 reply_id: 4703[/import]
Well, I guess I jumped the gun. It works in the simulator but does not work on my droid. Are databases supported by the droid? [import]uid: 4637 topic_id: 1594 reply_id: 4705[/import]
You need to open the database like this in order for it to work on the device, by specifying a valid path to the database:
[lua]local path = system.pathForFile(“yourdbname”, system.DocumentsDirectory)
db = sqlite3.open(path);[/lua] [import]uid: 6678 topic_id: 1594 reply_id: 4706[/import]
this as a workaround works but there must me a better way : ?
local content = 0
for row in db:nrows(“SELECT * FROM test”) do
content = 1
end
print(content) --content variable 0 or 1
thanks in advance . koji
[import]uid: 7165 topic_id: 1594 reply_id: 5950[/import]
I am having a bit of a hard time with all the documentation online. I am totally new to SQLlite or SQL. I don’t know any syntax. Can someone point me in the right direction? The SQL lite site is not nearly as good as the mysql site in explaining how to use things as they lack examples. Which makes it fairly confusing by looking just at the diagrams.
My current problem is updating. If you can take a look at the code below. I am trying to get a grasp on how to update a row. I have a couple of update examples but neither seem to work.
[code]
–Add rows with a auto index in ‘id’. You don’t need to specify a set of values because we’re populating all of them
local testvalue = {}
testvalue[1] = ‘Hello’
testvalue[2] = ‘World’
testvalue[3] = ‘Lua’
testvalue[4] = ‘Ciao’
local tablefill =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[2]…[[’);]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[2]…[[’,’]]…testvalue[1]…[[’);]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[3]…[[’);]]
local header = [[UPDATE test (id, subject, startFocusTime) SET (1, ‘]]…testvalue[3]…[[’,’]]…testvalue[3]…[[’);]]
local headeru = [[UPDATE test SET id = ‘1’, subject = 'ciao;]]
–db:exec( tablefill )
–db:exec( tablefill2 )
–db:exec( tablefill3 )
db:exec( headeru )
–print the sqlite version to the terminal
print( "version " … sqlite3.version() )
–print all the table contents
for row in db:nrows(“SELECT * FROM test”) do
local text = row.subject…" "…row.startFocusTime
print(text)
local t = display.newText(text, 20, 30 * row.id, null, 16)
t:setTextColor(255,0,255)
end
[/code] [import]uid: 8192 topic_id: 1594 reply_id: 27429[/import]
@DFox
You say that you have to open the database with the path to have it work on the device, this makes sense. my question is where does that code go? do i need to put it in every .lua file that wants to access that database? or can i just state it once in my .main?