Where does your database exist? I didn’t see it in system.DocumentsDirectory. I don’t see where you’re creating/populating it.
Rob
You are right, when I check the proj sandbox and it´s nothing there? but it fires up the db that lies in the root dir: ‘Birds.sqlite’ in line 250 (where I iterate over the sql statement), in playGame if I´m not mistaken
but I´m not shure
If you like to se how I like it to be you can first run the code in sim and see (you see all entries), then you can put i.e. this statement in line 245 right before count = 0:
[LUA]“SELECT * FROM birds WHERE COLOR1 = ‘Sort’”[/LUA]
and save. what you now see in the simulator is a narrow selection just like I want right?
I made a video that describes better my situation:
https://www.dropbox.com/s/z16sowhc2gtmpfq/BirdAppComposer.mp4?dl=0
This shows how I want it to work in the update process 
There is an issue with the SQL going to the db:nrows() call that’s not returning anything from the query. Still investigating.
I will like to thank you, Rob for all help you have done for me
Everything is working nicely now. Here´s the trick from your code that helped me out
[LUA]
function functionSqlView()
local sql = nil
tableView:deleteAllRows()
local counter = 1
local res = {}
– NOW WE BUILD THE SQL QUERY IN A FORLOOP FROM THE mySqlView TABLE, INSERTED FROM THE SUBBUTTONS VALUES
sql = "SELECT * FROM birds WHERE "
for k, v in ipairs(mySqlView) do
sql = sql … v … " "
if counter < #mySqlView then
sql = sql … " AND "
end
counter = counter + 1
end
dbTable = {} – START WITH A FRESH TABLE
count = 0 – AND FRESH COUNTER
– PREPARE THE ROWS SELLECTION BASED ON THE DATABASE ROWS THROUGH THE SQL QUERY
for row in db:nrows( sql ) do
count = count +1
dbTable[count]={}
dbTable[count].NAME = row.NAME
dbTable[count].LATIN = row.LATIN
dbTable[count].INFO = row.INFO
end
– STYLING FOR THE ROWS ON SCREEN
for i = 1, #dbTable do
local isCategory = false
local rowHeight = 95
local rowColor = {
default = { colorConverter(114, 72, 30, 0) },
}
local lineColor = { colorConverter(20, 20, 0, 0) }
– Insert the row into the tableView
tableView:insertRow
{
rowHeight = rowHeight,
rowColor = rowColor,
lineColor = lineColor,
}
end
return true
end
[/LUA]
Again, thanx a lot Rob
Quite welcome!
I fixed it with a function that builds and update the sql query:
[lua]
function sqlViewBuilder(argA, argB)
sql = "SELECT * FROM birds WHERE " … tostring(argA) … " = " … “’” … tostring(argB) … “’”
return sql
end
for i = 1, #dbTable do
local isCategory = false
local rowHeight = 70
local rowColor = {
default = { colorConverter(114, 72, 30, 0) },
}
local lineColor = { colorConverter(20, 20, 0, 0) }
tableView:insertRow
{
– isCategory = isCategory,
rowHeight = rowHeight,
rowColor = rowColor,
lineColor = lineColor,
}
end
return sql
[/LUA]
If I do a call just to check:
[LUA]
sqlViewBuilder(‘PATTERN’, ‘Spotted’)
[/LUA]
It´s crucial to clear the tableView before drawing a new one, so I do that before i call the sqlViewBuilder i.e. in another function
[LUA]
tableView:deleteAllRows()
[/LUA]
I get this result in the terminal:
SELECT * FROM birds WHERE PATTERN = ‘Spotted’
Now it filters my scene view nice and tidy
Next thing I will test is to make a table that I feed in all the arguments in separate strings and the do an unPack()
I will have to make a function that will deal with AND, OR for the selections to be nice and complex …but that´s another story
I will post that too for those who´s interested
…Corona coding is fun!
Here´s the table constructor way I mentioned earlier in the post.
Scenario: You want to construct a complex sql query based on input from buttons or other display objects that feeds the string into an array instead of just returning the string. The little knowledge I got in SQL is that when building a fairly complex query, I have to use a *start sequence that is like “SELECT * FROM myDB WHERE”. After that I can use AND / OR operators to narrow the search more and more like:
“SELECT * FROM myDB WHERE Shoes = ‘Red’ AND Hair = ‘Green’ OR Hair = ‘Blue’”
here is how I did it (suppose we already have inserted stuff from i.e. some button that return strings back):
[LUA]
local mySqlView =
{
“Shoes = ‘Red’”,
“Hair = ‘Green’”,
“Nails = ‘Red’”
}
[/LUA]
and then we have a function that iterates over mySqlView table and puts the *start sequence first, then it puts AND between all the children of the table -(I didn´t include OR operator because I just need AND in my game ;D).
It is obvious that we use these operators between the entries in the array except the last child because an OR or an AND is always followed by something, right? This function puts the operator between every child until it reaches the end and on the last it skips the operators.
Then I use table.concat() to concatenate all the entries in the table. I have a variable called sep(separator) I use at the start and the end just because SQL requires it.
[LUA]
local function functionSqlView()
local counter = 1
local res = {}
local sep = ‘"’
table.insert(res, "SELECT * FROM myDB WHERE ")
for _,v in ipairs(mySqlView) do
if counter < #mySqlView then
table.insert( res, (v … " AND "))
else
table.insert( res, v )
end
counter = counter + 1
end
print( sep … table.concat( res ) … sep )
end
[/LUA]
Then, when I call the function [Lua] functionSqlView()[/LUA]
I get this in return in the terminal:
“SELECT * FROM myDB WHERE Shoes = ‘Red’ AND Hair = ‘Green’ AND Nails = ‘Red’”
If we look at my first example over I can just replace the last command, print, in the function functionSqlView with: [LUA] return sql = sep … table.concat( res ) … sep [/LUA]
(this is not tried yet, but I guess it´s a valid tail call
Hope this can be of any help and as I said earlier, I´m not very good at sql so any comments and suggestions here are more than welcome!
Have fun
Well, I have to say that it didn´t work. The problem is that I can’t seem to update the view with right information, it just change the first entries in the tableView and it won’t filter out those I dont want to have in the list.
I’m not sure I really understand the question at hand. Are you needing help with SQL as a language?
Rob
Not really I think my problem is how to get the tableView to update with my new sql query (In this game I like to first list every entry in the db and then I like to filter out and narrow the search so to say)
I use composer and everything but the updatefunction works nice! Heres a stripped version of the playGame file:
[LUA]
– THE PLAYGAME FILE:
– LOCALS FWD
local composer = require “composer”
local scene = composer.newScene()
local path = system.pathForFile( “Birds.sqlite”, system.ResourceDirectory ) – IM NOT WRITING TO THE DB FILE
local imagePath = “Images/Birds/”
local bgImage = imagePath … “bgTheme.jpg”
local text, themeText, bg, t, count, bgItem, bg, colorConverter, tableView, leftRib, woodPanel, butNext, butBack
local leftIndent = 50
local dbTable = {}
local rowBg = {}
local rowName = {}
local rowLatin = {}
local playButton = {}
local onRowRender = {}
local onRowTouch = {}
local funtionSqlView = {}
local db = sqlite3.open( path )
local _W, _H = display.contentWidth, display.contentHeight
local group = {}
local containerGroup = display.newGroup( )
local dashboardGroup = display.newGroup( )
–GLOBALS FWD
mySqlView = {} – I CHOOSE TO HAVE THIS GLOBAL FOR NOW
_G.specPool = {}
_G.catText = “Push one of the buttons”
updateSql = {}
– FUNCTIONS
function functionSqlView() – THIS IS THE SQL STATEMENT BUILDER (YOU CAN SEE IN THE TERMINAL)… LAME NAME, I KNOW HAHA
local counter = 1
local res = {}
local sep = ‘"’ – FOR LAZYNESS
table.insert(res, "SELECT * FROM birds WHERE ")
for _,v in ipairs(mySqlView) do
if counter < #mySqlView then
table.insert( res, (v … " AND ")) – I WILL DEAL WITH ‘OR’ LATER ;D
else
table.insert( res, v )
end
counter = counter + 1
end
sql = sep … table.concat( res ) … sep – CONCATENATES THE WHOLE STATEMEMT STRING
updateSql(sql)
– HERE IS WHERE I START TO GET LOST, I HAVE THE STATEMENT AND I WANT TO UPDATE AND REMOVE
– THE ROWS THAT DOESN´T MEET THEESE CRITERIAS… FOLLOW DOWN TO UPDATESQL FUNCTION
end
local function onSystemEvent( event )
if ( event.type == “applicationExit” ) then
db:close()
end
end
local function colorConverter(r, g, b, a) – JUST SO I CAN USE RGB VALUES DIRECT FROM PHOTOSHOP OR ILLUSTRATOR
local colorR, colorG, colorB, alphA
colorR = r/256; colorG = g/256; colorB = b/256
alphA = a or nil
return colorR, colorG, colorB, alphA
end
function updateCatText() – FOR UPDATING THE GLOBAL CATEGORY TEXT OVER THE BUTTONS
if themeText then display.remove( themeText) end
themeText = display.newText( tostring(_G.catText), 0, 0, _G.msgFont , 34 )
themeText.anchorX, themeText.anchorY = 0, 0
themeText.x, themeText.y = leftIndent, (woodPanel.y - woodPanel.height) + 20
themeText:setFillColor( colorConverter(236, 210, 135, 1) )
return true
end
–[[
SCENARIO:
I start the app with the sql query showing all rows in db without any limit, like this:
“SELECT * FROM birds”
The function over called functionSqlView() produces sql queries based on what buttons i decide to click on.
Let´s say I hit two buttons, then my sql query could look like this:
“SELECT * FROM birds WHERE WINGS = ‘Long’ AND TAIL = ‘Short’”
WHAT COMES HERE IS WHAT I REALLY DONT KNOW!!
- I GOT TO TRAVERSE THE RIGHT ROWS FROM THE DB
- THE LIST OF BIRDS IN THE TABLEVIEW WILL SHOW LESSER AND LESSER ENTRIES
THE MORE I NARROW THE SEARCH.
–]]
function updateSql(event)
tableView:deleteAllRows() – FIRST I LIKE TO REMOVE THE ROWS AND THAT WORKS OF COURSE
for i = 1, #dbTable do
local isCategory = false
local rowHeight = 70
local rowColor = {
default = { colorConverter(114, 72, 30, 0) },
}
local lineColor = { colorConverter(20, 20, 0, 0) }
– Insert the row into the tableView
tableView:insertRow
{
– isCategory = isCategory,
rowHeight = rowHeight,
rowColor = rowColor,
lineColor = lineColor,
}
end
end
– THIS ONE WORKS NICE AND IT TAKES YOU OVER TO A SPEC PAGE AND SO ON…
local function birdSpec(self, event)
if event.phase == “began” then
if _G.debug == 1 then print("** birdSpec: \n\n") end – DEBUG
_G.specPool.name = self.specName
_G.specPool.latin = self.specLatin
_G.specPool.info = self.specInfo
composer.gotoScene( “playSpecScene” )
elseif event.phase == “cancelled” or event.phase == “ended” then
end
return true
end
function onRowRender( event )
local phase = event.phase
local row = event.row
row.bg = display.newRoundedRect( 0, 0, display.contentWidth -48, 50, 12 )
row.bg.strokeWidth = 0
row.bg:setFillColor( colorConverter(236, 210, 135, 1) )
row.bg.alpha = 1
row.bg.anchorX = 1
row.bg.anchorY = 0
row.bg.x = display.contentWidth
row.bg.y = 10
row:insert( row.bg )
row.title = display.newText( row, dbTable[row.index].NAME, 0, 0, _G.msgFont , 24 )
row.title.x = leftIndent + 10
row.title.anchorX = 0
row.title.y = row.contentHeight * 0.15+ row.bg.y
row.title:setFillColor( colorConverter(114, 72, 30, 1) )
row.ingress = display.newText( row, dbTable[row.index].LATIN, 0, 0, _G.msgFont , 14 )
row.ingress.x = leftIndent + 10
row.ingress.anchorX = 0
row.ingress.y = row.title.y +20
row.ingress:setFillColor( colorConverter(114, 72, 30, 1) )
row.image = display.newImageRect(row, tostring(imagePath … dbTable[row.index].NAME) … “.jpg”, 45,45 )
row.image:addEventListener( “touch”, row.image)
row.image.touch = birdSpec
row.image.anchorX = 1
row.image.anchorY = 0.25
row.image.x = display.viewableContentWidth - row.image.width/2
row.image.y = row.title.y
– PARSE VARIABLE TO THE GLOBAL SPECPOOL TABLE
row.image.specName = dbTable[row.index].NAME
row.image.specLatin = dbTable[row.index].LATIN
row.image.specInfo = dbTable[row.index].INFO
end
– “scene:create()”
function scene:create( event )
local sceneGroup = self.view
--Bird.group = Bird.group
bg = display.newImageRect( bgImage, _W, _H, 375, 667, true )
bg.anchorX, bg.anchorY = 0, 0
bg.alpha = 1
sceneGroup:insert(bg)
leftRib = display.newImageRect( “leftRib.png”, 48, 793, true )
leftRib.anchorX, leftRib.anchorY = 0, 0
leftRib.alpha = 1
sceneGroup:insert(leftRib)
woodPanel = display.newImageRect( “woodPanel.png”, 375, 131, true )
woodPanel.x, woodPanel.y = display.contentWidth/2, display.contentHeight
woodPanel.anchorX, woodPanel.anchorY = 0.5, 1
woodPanel.alpha = 1
sceneGroup:insert(woodPanel)
themeText = display.newText( tostring(_G.catText), 0, 0, _G.msgFont , 18 )
themeText.anchorX, themeText.anchorY = 0, 0
themeText.x, themeText.y = leftIndent, (woodPanel.y - woodPanel.height) + 30
themeText:setFillColor( colorConverter(236, 210, 135, 1) )
sceneGroup:insert(themeText)
end
– “scene:show()”
function scene:show( event )
local sceneGroup = self.view
local phase = event.phase
if ( phase == “will” ) then
composer.removeHidden( )
elseif ( phase == “did” ) then
– load the data into a table array
– WHEN I FIRE IT UP THE FIRST TIME IT WILL SHOW ALL ENTRIES
if sql == nil then sql = “SELECT * FROM birds” print(“Initiated SQL *”) end
count = 0
for row in db:nrows(sql) do
count = count +1
dbTable[count]={}
dbTable[count].NAME = row.NAME
dbTable[count].LATIN = row.LATIN
dbTable[count].INFO = row.INFO
end
tableView = widget.newTableView
{
top = 0,
width = _W+1,
height = display.contentHeight,
hideBackground = true,
isBounceEnabled = true,
listener = tableViewListener,
onRowRender = onRowRender,
}
for i = 1, #dbTable do
local isCategory = false
local rowHeight = 70
local rowColor = {
default = { colorConverter(114, 72, 30, 0) },
}
local lineColor = { colorConverter(20, 20, 0, 0) }
– Insert the row into the tableView
tableView:insertRow
{
– isCategory = isCategory,
rowHeight = rowHeight,
rowColor = rowColor,
lineColor = lineColor,
}
end
sceneGroup:insert(tableView)
end
end
– “scene:hide()”
function scene:hide( event )
local sceneGroup = self.view
local phase = event.phase
if ( phase == “will” ) then
elseif ( phase == “did” ) then
if tableView and tableView:getNumRows()>1 then tableView:deleteAllRows()
print(“TableView iterated and deleted from scene hide in playGame”)
end
_G.catText = “”
updateCatText()
end
end
function scene:destroy( event )
local sceneGroup = self.view
end
– Listener setup
scene:addEventListener( “create”, scene )
scene:addEventListener( “show”, scene )
scene:addEventListener( “hide”, scene )
scene:addEventListener( “destroy”, scene )
return scene
[/LUA]
Hope anyone can help me
I understand the problem now. I, however; don’t see where your creating your buttons and populating mySqlView. Though I think I have enough here to answer the basic question.
You’re over complicating your “functionSqlView” function. I would drop all the table stuff and just deal with strings. Lets start with the assumption that your buttons can produce three pieces of information: Key (“WING”, “TAIL”, etc.), Value (“Long”, “Short”, etc.) and condition (“AND”, “OR”, nil). I would create a table call attributes and for each entry, have it contain these three values:
local birdAttributesQuery = {}
birdAttributesQuery[#birdAttributesQuery + 1] = { key = “WING”, value = “Long”, condition = “AND” },
birdAttributesQuery[#birdAttributesQuery + 1] = { key = “TAIL”, value = “Short”, condition = nil }
The #birdAttributesQuery + 1 will auto increment the table.
Then you could build your functionSqlView() to do:
local function functionSqlView() local query = "SELECT \* FROM birds WHERE" for i = 1, #birdAttributesQuery do query = query .. " " .. birdsAttributesQuery[i].key .. " = " .. birdAttributesQuery[i].value if birdAttributesQuery[i].condition then query = query .. " " .. birdAttributesQuery[i].condition end end -- -- now execute your query -- delete the previous tableView rows -- loop over the returned query -- repopulate the table values you use to populate the tableView row in onRowRender() -- insert a new tableView row (triggering the onRowRender) -- end end
Your onRowRender() and a bunch of your functions should come above this in your code (so they are forwardly declared properly to help with the globals…
Rob
Thanx for the answer Rob
I have no trouble with the building and executing sql queries. My trouble is how I remove the rows that does NOT meet my criterias and how they will be removed from the display and the respective table. I have been reading mouch about widgets and making this work in a composer environment like I want. If I´m thinking loud here I like to think like this:
I iterate through the db file in ipairs with the sql query string as argument - this gives me the rows that meet the criterias that I ask for
NOW: How do I remove the ones that shouldn´t be there?
I have really tried for some time now in composer ( I see that other coders like Ingemar use api calls that I cant find in the api list, why?)
I think this is were I’m having a disconnect. You remove all the rows from the tableview. This gives you an empty tableView. Then if your SQL query returns just the rows you want, insert the rows into the tableView using the data returned from the query.
If you see Ingemar use API calls that you can’t find, that means he’s using some library either of his creation or of someone else. He could also have taken the open source version of our widgets and modified them with extra features.
Rob
This is JUST what im having trouble doing Rob, I have the query string, I have deleted all the rows, but I cant populate with the new query. It wont work for me 
What and where do I put what you sugest in your most recent post?
Maybe something like:
local function functionSqlView() local query = "SELECT \* FROM birds WHERE" for i = 1, #birdAttributesQuery do query = query .. " " .. birdsAttributesQuery[i].key .. " = " .. birdAttributesQuery[i].value if birdAttributesQuery[i].condition then query = query .. " " .. birdAttributesQuery[i].condition end end tableView:deleteAllRows() -- FIRST I LIKE TO REMOVE THE ROWS AND THAT WORKS OF COURSE count = 0 dbTable = {} for row in db:nrows( query ) do count = count +1 dbTable[count]={} dbTable[count].NAME = row.NAME dbTable[count].LATIN = row.LATIN dbTable[count].INFO = row.INFO local isCategory = false local rowHeight = 70 local rowColor = { default = { colorConverter(114, 72, 30, 0) }, } tableView:insertRow { -- isCategory = isCategory, rowHeight = rowHeight, rowColor = rowColor, lineColor = lineColor, } end endI try the new code and I must say that I like the auto increment tweak you did on birdAttributesQuery and that you define the value so clear as you did here Rob! Mine works to but Its a littlest more “foggy” haha
but I just get no rows at all, is this because I have to force onRowrender to render? (My debug system tells me that it do not trigger onRowrender after I click.
What is the best practice on forcing row rendering?
It stopps just after deleting the rows and nothing more happens
The on screen rows should auto render when they are inserted. any thing going on in your logs?
Nothing going on in the logs, uhh, this drives me nuts really! man. I cant get this to work at all. It all stops when I try to update the rows.
I sendt you a zip here the other day, can you please see if its just me or a bug or something?
Can you zip up your latest with the suggestions I made above?