Simple CRUD sample using Coronium and widget.newEditField

[ksan]

Over time, a lot of requests have been made for a simple CRUD database example. Recently I created a sample app with the purpose of demonstrating the power of two elements which I have had the privilege of testing and helping in the development cycle. The are : 

• Coronium developed by @develphant for the MySQL interaction
• widget.newEditField developed by @Atanas for the edit fields on the record detail page

For the purposes of demonstration, I wrote the app to use a MySQL database hosted on the Coronium server and there is no local data storage. This approach is quite an unlikely scenario in a typical mobile app, nevertheless it was chosen to keep the sample simple and to showcase the ease and power of Coronium in getting to connect to and manipulate web based databases. 

So what is Coronium? - Here’s how its developer, @develephant answers this question. 

_ A Lua / MySQL / MongoDB / PHP bridge for Corona SDK apps and games. _

The Coronium Cloud server is a tool-set for creating server-side components for your apps and games. Leveraging the Lua code that you already use in your Corona SDK projects you can easily call on MySQL, MongoDB, PHP, and server-side Lua to enhance your project.

In our example, we use Coronium to host a simple database table containing 4 fields. 

• First Name
• Last Name
• Date of Birth
• Role / Profile

When you run the sample, it connects to the Coronium server and pulls this information down to bring up a simple tableView which can be seen below. 

Clicking / tapping New Record brings us to our detail view which at its heart has the amazing widget.newEditField developed by @Atanas.

You can read more about the development of this 3rd party widget at by clicking here. You can get a [free] copy of this widget and other sample code by registering on : http://widgetstown.com/ . 

In the words of its developer, @Atanas, 

Widget.newEditField encapsulates edit field functionality for business type apps and addresses most of the shortcomings in the Corona newTextField

 

- allows you to display overlays and scenes on top of edit fields

- manages and moves edit fields automatically with groups

- supports 9-slices , 3-slices and roundect frames

- supports labels for edit fields

- error frame to indicate required fields to the end user

- allowedChars and maxChars to limit the user input

- supports icons and buttons for the edit fields

- supports clear button

- gives you an onSubmit event 

- supports custom sizes edit fields

- automatic font scaling

- automatic slide to unveil keyboard

- native mode if there are no scenes or overlays on top of the edit fields

- selection list functionality paired with a pickerList to select values from a pre defined list

- calibration support to adjust font scaling and offsets on android devices

Detail View

[ksan]

Enter data in the fields presented, make a profile choice (Dev, Tester or User) and hit Create Record to save the entry you made. This will look like the following. Now hit Back to return to the main listview.

Observe the new record you just added. This add happened realtime on the web based MySQL Database.

If you don’t believe me click here to go to the simple web application fronting this database and see real time the records you add, change & delete… It will look like the following : 

[ksan]

Going back to our mobile app, now tap the record you just added so as to go back to its detail view and edit the record details as you wish :

In this case the first name is updated to Albert. Now hit Update Record button and go back to the listview to observe the results of your update :

now, tap the record you just updated so as to go back to its detail view and observe the Delete Record button available to you :

Using Delete Record you can remove the record you added in this session and return back to the listView which now looks like it did before we Created and Updated a new record.

Thats it! This was a very simple example showcasing how much you can achieve with very little code combined with the power of Corona SDK, Coronium and widget.newEditField. I will run through the actual code involved further pointing out the simplicity and power of the components involved in a second post later on. Feel free to ask questions if you wish in this thread.

You can find the complete code for this sample at http://docs.coronium.io/a-mysql-demo/

You can register and download the widget.newEditField and other samples at http://widgetstown.com/

Thank you very much Corona Labs, @develephant and @Atanas for making it possible for us to create great business apps involving databases, web connectivity and data entry.

[Develephant]

Hi Kerem,

This really is a fantastic and full-featured demo project!  I really appreciate you taking the time to integrate this with Coronium.

Looking forward to your future Coronium based projects.

Best,

Chris

[rob]

Looks nice Kerem.

[ksan]

Thanks guys. It was my pleasure bringing it all together for a simple project. The actual code is very crude and has a lot that can be improved but I think it will be a good starting point for any newcomer who needs a web connected and database driven app in a rush… 

[Gremlin_Interactive]

What a nicely detailed post!

Thanks for sharing. I’m seriously considering using coronium for our next app project and these examples will certainly come in handy to get up and running.

Cheers

[ksan]

Thank you so much! Coming from you that compliment means the world. I know you are being too kind but I’ll take it. Thanks!!!

[ksan]

Enter data in the fields presented, make a profile choice (Dev, Tester or User) and hit Create Record to save the entry you made. This will look like the following. Now hit Back to return to the main listview.

Observe the new record you just added. This add happened realtime on the web based MySQL Database.

If you don’t believe me click here to go to the simple web application fronting this database and see real time the records you add, change & delete… It will look like the following : 

[ksan]

Going back to our mobile app, now tap the record you just added so as to go back to its detail view and edit the record details as you wish :

In this case the first name is updated to Albert. Now hit Update Record button and go back to the listview to observe the results of your update :

now, tap the record you just updated so as to go back to its detail view and observe the Delete Record button available to you :

Using Delete Record you can remove the record you added in this session and return back to the listView which now looks like it did before we Created and Updated a new record.

Thats it! This was a very simple example showcasing how much you can achieve with very little code combined with the power of Corona SDK, Coronium and widget.newEditField. I will run through the actual code involved further pointing out the simplicity and power of the components involved in a second post later on. Feel free to ask questions if you wish in this thread.

You can find the complete code for this sample at http://docs.coronium.io/a-mysql-demo/

You can register and download the widget.newEditField and other samples at http://widgetstown.com/

Thank you very much Corona Labs, @develephant and @Atanas for making it possible for us to create great business apps involving databases, web connectivity and data entry.

[Develephant]

Hi Kerem,

This really is a fantastic and full-featured demo project!  I really appreciate you taking the time to integrate this with Coronium.

Looking forward to your future Coronium based projects.

Best,

Chris

[rob]

Looks nice Kerem.

[ksan]

Thanks guys. It was my pleasure bringing it all together for a simple project. The actual code is very crude and has a lot that can be improved but I think it will be a good starting point for any newcomer who needs a web connected and database driven app in a rush… 

[Gremlin_Interactive]

What a nicely detailed post!

Thanks for sharing. I’m seriously considering using coronium for our next app project and these examples will certainly come in handy to get up and running.

Cheers

[ksan]

Thank you so much! Coming from you that compliment means the world. I know you are being too kind but I’ll take it. Thanks!!!

[jeremiahflickinger]

Hi There,

I’m very new at this but what you’ve put together here is great.

At this point I’m just trying to get an understanding of how this works, before attempting to scale up into an app I’m working on. I’m not sure what all details you need, so I posted everything I thought might be helpful. If you need anything else, just let me know.

Thanks in advance, and sorry for the time waster as I’m sure it’s a dumb newbie question…I’ve read through all the materials on the Coronium Wiki and watched all the video but still lost. Feel like I’m missing an important setup step.

Details:

• I’ve setup a droplet on Digital Ocean, and successfully followed the instructions on the Coronium homepage. 
• I then downloaded the mod_coronium.lua file from the Coronium Cloud Administration page.
• I also grabbed the sample code @ksan posted.
• UserData is the name of the MySQL database I’m using
• userMgmt is the name of the table

Using the mod_coronium file from the Coronium Admin page, I get the following error message when attempting to submit data to my MySQL database.

“TestProject/mod_coronium.lua:540: attempt to call local ‘_callback’ (a string value)”

Using the mod_coronium file from @ksan’s sample project, I get a different error message.

“TestProject/mod_coronium.lua:479: attempt to index local ‘result’ (a nil value)”

Here is my code:

main.lua

local widget = require ("widget") local composer = require ("composer") mc = require( "mod\_coronium" ) local start = composer.gotoScene( "scene1") mc:init({ appId = "data.settleit.com", apiKey = " --verified I have the right API key in here-- " })

serverLua/insert.lua

local in\_data = coronium.input() local question = coronium.utils.mysqlString( in\_data.question ) local responseA = coronium.utils.mysqlString ( in\_data.responseA ) local responseB = coronium.utils.mysqlString ( in\_data.responseB ) local q = string.format( "INSERT INTO userMgmt ( question, responseA, responseB ) VALUES ( %s, %s, %s )", question, responseA, responseB ) local answer = coronium.mysql.query( { database = "UserData"}, q) coronium.output( answer )

serverLua/main.lua

local Main = {} function Main.init() local globals = coronium.globals --mysql connection globals.connection\_table = { database = "UserData", user = "coronium", password = "appserver", host = "127.0.0.1", port = 3306 } end return Main

scene4.lua

--tapNextB is a submit but pulls data that has been temporarily stored in a table. local function tapNextB (event) mc:run( "insert", question, responseA, responseB) composer.gotoScene ("scene1") end question = tables.question responseA = tables.responseA responseB = tables.responseB

My project file structure looks like this:

Main Project Directory (TestProject)

main.lua

mod_coronium.lua

tables.lua

scene1.lua

…

scene4.lua

–Subfolder (serverLua)

    api_key.lua

    insert.lua

    main.lua

Thanks again!

[Develephant]

Hi,

 

What version of Coronium are you running?

 

Also, when using “run” the parameters need to be contained in a table.  So… in your case you’d want to do something like:

 

mc:run( "insert", { question = question, questionA = questionA, questionB = questionB } )

 which should resolve the “local ‘_callback’ (a string value)” error.

 

As far as Kerems example, are you connecting to your data source or the demo app?

 

A tip also is that you don’t need to provide any connection details except the DB name when connecting to the local database so something like this will work fine:

 

local answer = coronium.mysql.query( { database = "UserData" }, query )

 

Finally, in version 1.7+ there should be a  _boot  folder in the Lua folder.  This where the main.lua resides now, but again you don’t really need to set anything in there for the local database.

 

I’m going to be providing a programming guide soon to help explain some of these processes better.

 

Let me know if you have any other questions.  Be sure to check out the MySQL help as well.

 

Cheers,

Chris

[jeremiahflickinger]

Chris,

Thanks for the quick reply and detailed feedback. Much appreciated.

Regarding the version I’m on (1.8). When borrowing the mod_coronium file from Kerem’s example, I setup everything to point at my data source.

I realised I’ve obviously missed a big step with the cloud lua files. I’ve been trying all day to get logged into the files directory with FileZilla and FireFTP with absolutely no luck. I’ve tried every possible combinations of passwords, usernames, reset everything, started completely over from scratch…still no luck.

Once, I get it sorted - I’ll let you know if your suggestions resolve the other issues I was having…

Thanks again!

Jeremiah

[jeremiahflickinger]

Hi There,

I’m very new at this but what you’ve put together here is great.

At this point I’m just trying to get an understanding of how this works, before attempting to scale up into an app I’m working on. I’m not sure what all details you need, so I posted everything I thought might be helpful. If you need anything else, just let me know.

Thanks in advance, and sorry for the time waster as I’m sure it’s a dumb newbie question…I’ve read through all the materials on the Coronium Wiki and watched all the video but still lost. Feel like I’m missing an important setup step.

Details:

• I’ve setup a droplet on Digital Ocean, and successfully followed the instructions on the Coronium homepage. 
• I then downloaded the mod_coronium.lua file from the Coronium Cloud Administration page.
• I also grabbed the sample code @ksan posted.
• UserData is the name of the MySQL database I’m using
• userMgmt is the name of the table

Using the mod_coronium file from the Coronium Admin page, I get the following error message when attempting to submit data to my MySQL database.

“TestProject/mod_coronium.lua:540: attempt to call local ‘_callback’ (a string value)”

Using the mod_coronium file from @ksan’s sample project, I get a different error message.

“TestProject/mod_coronium.lua:479: attempt to index local ‘result’ (a nil value)”

Here is my code:

main.lua

local widget = require ("widget") local composer = require ("composer") mc = require( "mod\_coronium" ) local start = composer.gotoScene( "scene1") mc:init({ appId = "data.settleit.com", apiKey = " --verified I have the right API key in here-- " })

serverLua/insert.lua

local in\_data = coronium.input() local question = coronium.utils.mysqlString( in\_data.question ) local responseA = coronium.utils.mysqlString ( in\_data.responseA ) local responseB = coronium.utils.mysqlString ( in\_data.responseB ) local q = string.format( "INSERT INTO userMgmt ( question, responseA, responseB ) VALUES ( %s, %s, %s )", question, responseA, responseB ) local answer = coronium.mysql.query( { database = "UserData"}, q) coronium.output( answer )

serverLua/main.lua

local Main = {} function Main.init() local globals = coronium.globals --mysql connection globals.connection\_table = { database = "UserData", user = "coronium", password = "appserver", host = "127.0.0.1", port = 3306 } end return Main

scene4.lua

--tapNextB is a submit but pulls data that has been temporarily stored in a table. local function tapNextB (event) mc:run( "insert", question, responseA, responseB) composer.gotoScene ("scene1") end question = tables.question responseA = tables.responseA responseB = tables.responseB

My project file structure looks like this:

Main Project Directory (TestProject)

main.lua

mod_coronium.lua

tables.lua

scene1.lua

…

scene4.lua

–Subfolder (serverLua)

    api_key.lua

    insert.lua

    main.lua

Thanks again!

[Develephant]

Hi,

 

What version of Coronium are you running?

 

Also, when using “run” the parameters need to be contained in a table.  So… in your case you’d want to do something like:

 

mc:run( "insert", { question = question, questionA = questionA, questionB = questionB } )

 which should resolve the “local ‘_callback’ (a string value)” error.

 

As far as Kerems example, are you connecting to your data source or the demo app?

 

A tip also is that you don’t need to provide any connection details except the DB name when connecting to the local database so something like this will work fine:

 

local answer = coronium.mysql.query( { database = "UserData" }, query )

 

Finally, in version 1.7+ there should be a  _boot  folder in the Lua folder.  This where the main.lua resides now, but again you don’t really need to set anything in there for the local database.

 

I’m going to be providing a programming guide soon to help explain some of these processes better.

 

Let me know if you have any other questions.  Be sure to check out the MySQL help as well.

 

Cheers,

Chris