Discussion, problem and solution with newTableView and database display and updating - CRUD

leonZak · November 12, 2013, 5:09pm

I had the need to do a small app that wasn’t a game, but a business style app. I needed to do a CRUD - Create, Read, Update and Delete on a sqlite table. I had the app running, then when I updated and the update included the new versions of the widgets the app quit. Specifically it was the newTableView and listing rows of information within.

I was creating a newTableView, then listing the rows of data in the view. Using onRowRender to display the information, then onRowTouch to call another storyboard screen with the details of the row selected. I forget exactly what I was doing with the old widget version but it didn’t work with the new one. I did a bunch of searching and found a few others that had the same problem but didn’t find anyone that had answers. I asked a few other about it but didn’t get anything suitable back.

So instead of following the advice - Corona isn’t made for business apps, use something else - I decided to figure out a way to do it. i’ve put together a demo of what I did on my blog, leonzak.com. It’s the complete project and hopefully will help others trying do similar things.

When you list rows in a newTableView the row.id is a number indicating the sequential order of the row touched. Row 1 returns row.id = 1, row2 returns row.id = 2, and so on. This isn’t very helpful if your rows are net in sequential order according to the table. If you sort the rows by say, company name, they have no relation to the position in the table. As an example with this data in the table in this physical order:

ID Company Contact Last Name

1 Alpha Co. Jackson

2 Better Co. Bellows

3 Candid Co. Anderson

If you retrieve this information from the table with “select * from companies” and then list this information to a newTableView and then touch a row, the listView row.id will indicate the row that was touched - in this case row 1 and it happens to coincide with the correct row in the table. If you then call another storyboard screen and pass this row.id you can pull up the details of the record with “select * from companies where id=1”

If you now change the sort order by using “select * from companies order by contact”, touching row 1 will still return row.id = 1 from the newTableView but if you pass that to the storyboard screen to retrieve detail you will get the wrong information.

I was unable to find a solution to this by searching the forums and I actually paid someone that was recommended by a former Corona associate and they couldn’t come up with how to get around this. Asked a few people that mentioned a similar problem by email and didn’t get any answers.

I came up with a solution - I got around this by declaring an array known to all screens called holdArray. Then when I run the loop that fills the newTableView I also place the row of returned information into the array. This way when a row is touched I pass the row.id to the detail storyboard. The detail storyboard uses it as an index into the holdArray and retrieves the actual table ID, then I can do a select that pulls the correct record for viewing and updating.

This isn’t anything near an elegant solution but it works. As I said earlier - I couldn’t find a better way. It would seem there is another way to pass the information as I was doing but it eluded me. One of my postings on this forum didn’t get any answers - or questions.

If anyone has a better way, or another way, I’d really like to hear it.

I’ve also attached the zip of the project to this post. It’s a working database crud project.

leon ….

mpappas · November 12, 2013, 6:03pm

I believe tableView rows have a field called “id” (separate from the tableView itself also having that field).

On tableView creation as you process the rows to put into the tableView, , you can set the id field as so

[lua]

              this.messageList:insertRow{
                        rowHeight=thisRowHeight,
                        isCategory=category,
                        lineColor={0.4,0.4,0,4,1.0},    --{96,96,96,255}, – myLineColor,
                        rowColor={ default=finalRowColor, over={0.5,0.5,0.5,0.5}}, – 128,128,128,128} },
                        id=tostring(idString), – Set the rows unique table of data…
                }
            end

[/lua]

Although I am using it just to save a remote filename, I believe I tried storing a table in there and it worked…

spradeep1979 · November 13, 2013, 7:30am

Just to add to what mpappas has mentioned above:

This is what the document says about insertRow method.

id (optional)
String. This is an optional id to assign to your TableView row. The default is the row’s index.

params (optional)
Table. This is a table that holds any custom parameters or data you want to assign to the row.

So, in your complist.lua file,

I modified showRows function as below - to assign the custom data we want to the row.

 list:insertRow { rowHeight = 20, rowColor = {220,200,20}, lineColor = {220,20,200}, params = { actualRowIndex = row.id, companyname = row.companyname, contactname = row.contact } }

I modified the onRowRender method as below, where I am using the custom data assigned in insertRow method. (commented out the original line and added another as shown)

-- local tstr = string.format("%-5s %-12s %10s", row.id, rowHoldCompany[row.id].companyname, rowHoldCompany[row.id].contact) local tstr = string.format("%-5s %-12s %10s", row.id, row.params.companyname, row.params.contactname)

I modified the onRowTouch method as below, where I am passing the custom data assigned to the row to the “compdetails” scene (commented out the original call to gotoCompanyDetails)

-- gotoCompanyDetails() sb.gotoScene("compdetails",{time=400,effect="fade",params={actualRowIndex = row.params.actualRowIndex}})

Then in the scene compdetails.lua, you can access the actualRowIndex in createScene method and assign it to a local variable.

Ex:

have this line at the beginning of createScene method

actualRowIndex = event.params.actualRowIndex

you need to have a forward declaration for actualRowIndex at the beginning of compdetails.lua.

After that, you can use “actualRowIndex” variable in place of sb.state.companyIndex global variable you are using.

I have verified this and it works. This way, you do not have to maintain the rowHoldCompany array and other global variables.

Just as a note:

In case you need to access variables between scenes/modules, best way is as described in http://www.coronalabs.com/blog/2013/05/28/tutorial-goodbye-globals. I follow that and it works very well and avoids the use of globals. I use mydata.lua for defining functions that I want to call in different scenes too.

leonZak · November 13, 2013, 11:02pm

mpappas had the right direction but spradeep1979’s explanation really laid it out, thank you.

I’ve rewritten my database test project and am attaching a copy of the new one (mytest2.zip) , as well, with a bit more explanation it’s leonzak.com.

I also changed from the storyboard.state globals to using mydata.lua. Although the storyboard.state was recommend a few times just after storyboards were introduced I like the mydata.lua method much better. It would make storing settings used throughout the program to and from a table very easy - it then wouldn’t be a far step to let the user change fonts, colors and other things to their liking and then storing them in a db table, then pull them back out each time - but not have to constantly hit the db.

Hope this helps someone else out.

[sharedmedia=core:attachments:941]

mpappas · November 12, 2013, 6:03pm

I believe tableView rows have a field called “id” (separate from the tableView itself also having that field).

On tableView creation as you process the rows to put into the tableView, , you can set the id field as so

[lua]

              this.messageList:insertRow{
                        rowHeight=thisRowHeight,
                        isCategory=category,
                        lineColor={0.4,0.4,0,4,1.0},    --{96,96,96,255}, – myLineColor,
                        rowColor={ default=finalRowColor, over={0.5,0.5,0.5,0.5}}, – 128,128,128,128} },
                        id=tostring(idString), – Set the rows unique table of data…
                }
            end

[/lua]

Although I am using it just to save a remote filename, I believe I tried storing a table in there and it worked…

spradeep1979 · November 13, 2013, 7:30am

Just to add to what mpappas has mentioned above:

This is what the document says about insertRow method.

id (optional)
String. This is an optional id to assign to your TableView row. The default is the row’s index.

params (optional)
Table. This is a table that holds any custom parameters or data you want to assign to the row.

So, in your complist.lua file,

I modified showRows function as below - to assign the custom data we want to the row.

 list:insertRow { rowHeight = 20, rowColor = {220,200,20}, lineColor = {220,20,200}, params = { actualRowIndex = row.id, companyname = row.companyname, contactname = row.contact } }

I modified the onRowRender method as below, where I am using the custom data assigned in insertRow method. (commented out the original line and added another as shown)

-- local tstr = string.format("%-5s %-12s %10s", row.id, rowHoldCompany[row.id].companyname, rowHoldCompany[row.id].contact) local tstr = string.format("%-5s %-12s %10s", row.id, row.params.companyname, row.params.contactname)

I modified the onRowTouch method as below, where I am passing the custom data assigned to the row to the “compdetails” scene (commented out the original call to gotoCompanyDetails)

-- gotoCompanyDetails() sb.gotoScene("compdetails",{time=400,effect="fade",params={actualRowIndex = row.params.actualRowIndex}})

Then in the scene compdetails.lua, you can access the actualRowIndex in createScene method and assign it to a local variable.

Ex:

have this line at the beginning of createScene method

actualRowIndex = event.params.actualRowIndex

you need to have a forward declaration for actualRowIndex at the beginning of compdetails.lua.

After that, you can use “actualRowIndex” variable in place of sb.state.companyIndex global variable you are using.

I have verified this and it works. This way, you do not have to maintain the rowHoldCompany array and other global variables.

Just as a note:

In case you need to access variables between scenes/modules, best way is as described in http://www.coronalabs.com/blog/2013/05/28/tutorial-goodbye-globals. I follow that and it works very well and avoids the use of globals. I use mydata.lua for defining functions that I want to call in different scenes too.

leonZak · November 13, 2013, 11:02pm

mpappas had the right direction but spradeep1979’s explanation really laid it out, thank you.

I’ve rewritten my database test project and am attaching a copy of the new one (mytest2.zip) , as well, with a bit more explanation it’s leonzak.com.

I also changed from the storyboard.state globals to using mydata.lua. Although the storyboard.state was recommend a few times just after storyboards were introduced I like the mydata.lua method much better. It would make storing settings used throughout the program to and from a table very easy - it then wouldn’t be a far step to let the user change fonts, colors and other things to their liking and then storing them in a db table, then pull them back out each time - but not have to constantly hit the db.

Hope this helps someone else out.

[sharedmedia=core:attachments:941]