How to Access Database Record Selected in One Scene, in New Scene

Getting Started Newbie Questions
#21

hi Rozan,

Rob’s suggestion is essentially a way to create a global data structure for your application which can be accessed by any other module/location in your app. even though there is only one variable returned, if that variable is a table then you can put as much as you want into it.

so, starting with the flow from scene one, you could use the structure to store just the index of the row, or store just the text to display in scene two, or whatever you want:

eg, pick one of the following:

mydata.selected\_index = 45 mydata.text = "this is the text of my record" mydata.record = \<the record from sql query\>

then, in scene 2, use use that value as needed. in the last two cases, there’s nothing to do except to display the data. in the first case (with index), you would run another SQL query to find the full record.

local sql = "select \* from News where idx = " .. mydata.selected\_index

again, you can structure it as you wish. it just depends on the data you want to pass between your scenes.

also, there’s an easier way to create a global variable to use for data passing, and you don’t even need to create a module file for it. this can be accomplished with the following line located in your main:

\_G.mydata = {}
#22

eg, pick one of the following:
 

mydata.selected_index = 45 mydata.text = “this is the text of my record” mydata.record = <the record from sql query>

then, in scene 2, use use that value as needed. in the last two cases, there’s nothing to do except to display the data. in the first case (with index), you would run another SQL query to find the full record.

I would probably choose the first option, but that’s because I don’t understand the second and third…  For mydata.text how would you delineate the fields in the record?  I am probably missing something really basic.  I would think that you could indicate that you want to pull the selected_index from the sqlite db, but then there would be some way to identify the field to print ( which is what I think you are doing in the second line).  Regarding the third option, can any sqlite query on my open database be run and assigned to mydata.record? Or is mydata.record more a command?  I’m still trying to wrap my head around items that are on the left side of the ='s sign.  

#23

you’ve only given a code example which uses the ‘summary’ element:

local details = display.newText(listRecs[idx].summary, 10, list.height - 70, display.contentWidth - 20, display.contentHeight, Helvetica, 14 )

since that was the only data you’ve actually shown to use, option 2 was supposed to be an example of saving that part of your data:

mydata.summary = listRecs[idx].summary

of course you could save more elements from the record if you’d like in that manner.

mydata.summary = listRecs[idx].summary mydata.date = listRecs[idx].date mydata.title = listRecs[idx].title

–== sqlite records

not having used sqlite myself for data storage, i figured that it would give back a list of table structures for each record. this is indicated in your code by the variable ‘a’:

local function loadData() local sql = "select \* from News" for a in db:nrows(sql) do listRecs[#listRecs+1] = { id = a.id, date = a.date, title = a.title, summary = a.summary, text = a.text } end end

since ‘a’ is *already* a Lua table, you ought to be able to simplify the above by just saving ‘a’ instead of creating a new table:

local function loadData() local sql = "select \* from News" for a in db:nrows(sql) do listRecs[#listRecs+1] = a end end

so option three is related to that, i.e., just saving the record (Lua table) from the results:

mydata.record = listRecs[idx\_of\_tapped\_row]

remember that the intent of ‘mydata’ is to share from scene one what you need in scene two. if that’s just one element of a row, like ‘summary’, then you could just save that (option 2). or, if you need the whole record, you can either save the table (option 3), or save the index of the row and perform a search in scene 2 using the row index to get the row (option 1).

again, the choice is yours depending on how you want to structure your code.

cheers,
dmc