Save arrays inside a single row of a table in SQL

[Bombett12]

Hello everybody,

I hope that someone can help me,

I made a button that when pressed it create and display icons. Those icons can be dragged and moved everywhere up an image.

There is another button, a “save button”, that store the names of the icons (ex. “icon1,icon2,icon3…”) and the coordinates of the objects…

this is my code:

function saveImage(event) local json = require( "json" ) local path = system.pathForFile("data.db", system.DocumentsDirectory ) local db = sqlite3.open( path ) local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY autoincrement, ImageName, icons);]] db:exec( tablesetup ) for i=icons.numChildren, 1, -1 do local child = icons[i] local childData = { child.name, child.x, child.y } local potsEncoded = json.encode( childData ) print( potsEncoded ) local pedalName = bloccoTesto.text local insertQuery = [[INSERT INTO test VALUES (NULL, ']]..pedalName..[[', ']]..potsEncoded..[[');]] db:exec( insertQuery ) end local function onSystemEvent( event ) if event.type == "applicationExit" then if db and db:isopen() then db:close() end end end Runtime:addEventListener( "system", onSystemEvent )

the problem is that in the SQL table “test” , the data is stored like showed in the attachment,

how can i do to put all the elements of the icons in the same row ?

example

image1    |    [icon1, 124,150, icon2, 185.164]           

thank you very much 

[luaykanaan]

You are executing insertquery in each iteration of the for loop. Hence, the multiple rows. You need to execute only once for each image. As a matter of fact, you don’t need the whole for loop. Assuming that ‘icons’ is a table like this:

icons = { {name = "", x = "", y = ""}, {name = "", x = "", y = ""} };

Then all you have to do is:

. . db:exec( tablesetup ); local potsEncoded = json.encode( icons ); local pedalName = bloccoTesto.text local insertQuery = [[INSERT INTO test VALUES (NULL, ']]..pedalName..[[', ']]..potsEncoded..[[');]] db:exec( insertQuery ) . .

because json.encode will encode nested tables for you.

[Bombett12]

Thank you for your answer @luaykanaan,

but now there is an error:

{"\_proxy":"\<type 'userdata' is not supported by JSON.\>","\_class":{"removeEventListener":"\<type 'function' is not supported by JSON.\>","addEventListener":"\<type 'function' is not supported by JSON.\>","\_\_index":"\<reference cycle\>"},"removeSelf":"\<type 'function' is not supported by JSON.\>"}

[Bombett12]

I forget to say that “pots” is a Group

[rob]

You probably should not be trying to store Corona display objects in a database.  You can store image names, x, y, positions, etc. Basically text and numeric data but storing texture memory isn’t going to work.

I also would suggest not naming your database “test”.  Think about the data structure you want to store. Databases are great for storing many records and searching for a few records that you need. If you just want to store where images were last saved on the screen, a flat text file (perhaps with JSON data) of those filenames, x and y locations would be better than the overhead of using a database.

Rob

[Bombett12]

Thanks Rob, to store object names, x, y, positions is exactly what i want to do… I’m sorry beacause maybe I have not explain in the right way my problem, also  I noticed that in the code that i posted, there is a bit of confusion and many errors. So to have a more clear Idea of what i need i try to simplify:

with a button (add button) I’m able to add the object (pot) after created, they are added to a group:

local pots = display.newGroup() local function addPot( event ) pot = display.newImage( "pot.png", display.contentWidth \* 0.5, display.contentHeight \* 0.4 ) pot.name = "pot" ..pots.numChildren+1 pots:insert(pot) print(pot.name)

So for what I understand the objects are stored in a table in this way: 

pots = {{["name"] = "pot1" ,["x"] = "123",["y"] = "34"}, {["name"] = "pot2" ,["x"] = "123",["y"] = "34"}}

what I’m looking for is a way to get names and positions off all the objects(pot1, pot2 etc.)  and put it together inside one row of a SQL table. In the end I need a structure like this:

|                      Name                 |                            pots                                 |

            mypersonalGruop                 pot1, x, y   pot2, x ,y  pot3, x, y

[rob]

pots is a display.newGroup(), it’s more than a simple array. It’s a collection of display objects. That means you’re going to have references to the binary textures and other metadata that you don’t want to store.   You need to create a separate tracking array to track the specific elements that you care about.

Rob

[Bombett12]

Thanks Rob!

so now I’ve done like this:

local pots = display.newGroup() potGroup = {} local function addPot( event ) pot = display.newImage( "pot.png", display.contentWidth \* 0.5, display.contentHeight \* 0.4 ) pot.name = "pot" ..pots.numChildren+1 pots:insert(pot) potGroup[#potGroup+1] = {pot.name, pot.x, pot.y} print(pot.name) end

after the object creation, I’ve another function that let me touch and move the objects…

last problem (I hope) is that when i try to save all in the SQL table, pot.x and pot.y are stored iwith the start position and not with the last

any advice?

[rob]

You will have to store the start position. pot.x and pot.y will change when moved, so you need to capture the values and save them for what you want.

Rob

[luaykanaan]

You are executing insertquery in each iteration of the for loop. Hence, the multiple rows. You need to execute only once for each image. As a matter of fact, you don’t need the whole for loop. Assuming that ‘icons’ is a table like this:

icons = { {name = "", x = "", y = ""}, {name = "", x = "", y = ""} };

Then all you have to do is:

. . db:exec( tablesetup ); local potsEncoded = json.encode( icons ); local pedalName = bloccoTesto.text local insertQuery = [[INSERT INTO test VALUES (NULL, ']]..pedalName..[[', ']]..potsEncoded..[[');]] db:exec( insertQuery ) . .

because json.encode will encode nested tables for you.

[Bombett12]

Thank you for your answer @luaykanaan,

but now there is an error:

{"\_proxy":"\<type 'userdata' is not supported by JSON.\>","\_class":{"removeEventListener":"\<type 'function' is not supported by JSON.\>","addEventListener":"\<type 'function' is not supported by JSON.\>","\_\_index":"\<reference cycle\>"},"removeSelf":"\<type 'function' is not supported by JSON.\>"}

[Bombett12]

I forget to say that “pots” is a Group

[rob]

You probably should not be trying to store Corona display objects in a database.  You can store image names, x, y, positions, etc. Basically text and numeric data but storing texture memory isn’t going to work.

I also would suggest not naming your database “test”.  Think about the data structure you want to store. Databases are great for storing many records and searching for a few records that you need. If you just want to store where images were last saved on the screen, a flat text file (perhaps with JSON data) of those filenames, x and y locations would be better than the overhead of using a database.

Rob

[Bombett12]

Thanks Rob, to store object names, x, y, positions is exactly what i want to do… I’m sorry beacause maybe I have not explain in the right way my problem, also  I noticed that in the code that i posted, there is a bit of confusion and many errors. So to have a more clear Idea of what i need i try to simplify:

with a button (add button) I’m able to add the object (pot) after created, they are added to a group:

local pots = display.newGroup() local function addPot( event ) pot = display.newImage( "pot.png", display.contentWidth \* 0.5, display.contentHeight \* 0.4 ) pot.name = "pot" ..pots.numChildren+1 pots:insert(pot) print(pot.name)

So for what I understand the objects are stored in a table in this way: 

pots = {{["name"] = "pot1" ,["x"] = "123",["y"] = "34"}, {["name"] = "pot2" ,["x"] = "123",["y"] = "34"}}

what I’m looking for is a way to get names and positions off all the objects(pot1, pot2 etc.)  and put it together inside one row of a SQL table. In the end I need a structure like this:

|                      Name                 |                            pots                                 |

            mypersonalGruop                 pot1, x, y   pot2, x ,y  pot3, x, y

[rob]

pots is a display.newGroup(), it’s more than a simple array. It’s a collection of display objects. That means you’re going to have references to the binary textures and other metadata that you don’t want to store.   You need to create a separate tracking array to track the specific elements that you care about.

Rob

[Bombett12]

Thanks Rob!

so now I’ve done like this:

local pots = display.newGroup() potGroup = {} local function addPot( event ) pot = display.newImage( "pot.png", display.contentWidth \* 0.5, display.contentHeight \* 0.4 ) pot.name = "pot" ..pots.numChildren+1 pots:insert(pot) potGroup[#potGroup+1] = {pot.name, pot.x, pot.y} print(pot.name) end

after the object creation, I’ve another function that let me touch and move the objects…

last problem (I hope) is that when i try to save all in the SQL table, pot.x and pot.y are stored iwith the start position and not with the last

any advice?

[rob]

You will have to store the start position. pot.x and pot.y will change when moved, so you need to capture the values and save them for what you want.

Rob