Save lua table to mysql file

Other
#1

Hi,

I am using a sql file to save my app’s persistent data, I have a lua table in my program now containing indicized data, my question is: is it possible to save the table straight to the mysql file and retrieve it with its containt as any other data saved to the mysql file?

Or is there another way to do this?

Thanks!!!

#2

The easiest thing to do would be to serialize the table using:

local stringVersionOfMyTable =  json.encode(yourTable)

Then you can just store that string in your SQL database.  When reading it back just decode it:

myTable = json.decode(stringVersionStoredInDatabase)

Rob

#3

Hi Rob,

Yes that’s what I figured, in lack of a more direct method not involving json I’ll try this. Thanks!

#4

Hi Rob,

I’m trying to do as you said but it’s not working, maybe it’s a syntax problem. Would you mind giving it a look?

this the the line I have been using to save to the mysql file the other data I need:

local sql = "INSERT INTO info (name, data, datetime)                    VALUES('" .. txt .. "','" .. currentNumber .. "', '" .. dateTime .. "')"                    db:exec(sql); 

while this is the same string with the new tabledata meant to be saved (and previously serialized):

local tableFactor = {x = operateBy, y = OPERATEbY}                       local stringTableFactor =  json.encode(tableFactor)                       print (stringTableFactor)      local sql = "INSERT INTO info (name, data, datetime, tableoffactors)    VALUES('" .. txt .. "','" .. currentNumber .. "', '" .. dateTime .. "', '" ..stringTableFactor.. "')"                         db:exec(sql);

The string table ‘stringTableFactor’ prints something like this : {“y”:10,“x”:1}

Everything worked and saved fine 'till I added the json string to be saved in the command line, now it doesn’t save anything and returns no errors.

Do you notice anything wrong in the code?

Thaks a lot!!!

#5

Can you print out the value of the sql variable and make sure the quotes are working the way you expect them to?

Rob

#6

I get this:

INSERT INTO info (name, data, datetime, tableoffactors) VALUES('test','0', 'Sun Jan 11 18:43:48 2015', '{"y":10,"x":1}')

not being familiar with sql syntax I don’t if it’s correct behavior…

 

#7

That looks right.

What error are you getting?

#8

That’s the point I get no error, but in the sql file there’s no new row new added. It’s like the function just fails to perform. And everything was working ok before the addition of the new value to the row when saved…

#9

Got it. It was simply I was using and older mysql file which wasn’t initialized with the new value yet. Basically trashed the old file and now it’s working with new one…

But now I’m not being able to retrieve the table. When I try through this command

local sql = [[SELECT data, name from info WHERE datetime = "]] .. tostring(row.t3.text) .. [["]] for row in db:nrows(sql) do     result = row end print (result.tableoffactors)

I get printed ‘nil’, with tableoffactors being the table we previously saved formatted with json and then supposed to be a string ( it shows like this the sql file : {“y”:“40”,“x”:“2”} )

Where could posibly be the mistake, now??

#10

You should use a table printing function and see what result is.   See:  http://coronalabs.com/blog/2014/09/02/tutorial-printing-table-contents/

When you print out the result table I’m going to bet it only has two members:  “data” and “name”.  You never retrieve tableoffactors from the row.

Rob

#11

I am not trying to retrieve the whole sql row in this case. Here I’m retrieving the sql row value ‘tableoffactors’, which supposedly is a lua table encoded into a json string. So stored to sql as a string basically.

But when I try to load it and decode it through json.decode (result.tableoffactors) I get the error : json.decode called with nil string

and in the sql file the string in question presents itself like this :

{“y”:10,“x”:1}

So where’s the error?

EDIT: -----

also trying to intialize the table before encoding it to json without a key-pair returns the same error. It present a strange behavior though: sometimes it saves the values contained in the table with double quotes and sometimes it doesn’t:

[1,10]

[“4”,“30”]

[4,“10”]

May be a clue pointing to the mistake??
 

#12

When you do:  SELECT field1, field2 FROM …  It only grabs the fields named “field1” and “field2” from the table.  You only asked it to get “data” and “name”.  Therefore your “result” table will have:

result.data

result.name

and no other fields.  If you expect that table to have a member named “tableoffactors”, you need to change your select statement to:

SELECT data, name, tableoffactors FROM …

Rob

#13

There it is!

Sorry, I didn’t understand you meant this when you said

You never retrieve tableoffactors from the row.

you were right, now it works…

Thanks a lot Rob!

A general question if you wish to answer:

In cases like this where you need to save several tables containing similar data, but each table having a different purpose, is it good practice to inglobate all those tables into a single one and save only this big table to the sql file and then load it every time and retrieve from it the specific sub-table you need? Or is it better keep each table as a single compartment and save each of them to a different VALUE in the sql file??

#14

The easiest thing to do would be to serialize the table using:

local stringVersionOfMyTable =  json.encode(yourTable)

Then you can just store that string in your SQL database.  When reading it back just decode it:

myTable = json.decode(stringVersionStoredInDatabase)

Rob

#15

Hi Rob,

Yes that’s what I figured, in lack of a more direct method not involving json I’ll try this. Thanks!

#16

Hi Rob,

I’m trying to do as you said but it’s not working, maybe it’s a syntax problem. Would you mind giving it a look?

this the the line I have been using to save to the mysql file the other data I need:

local sql = "INSERT INTO info (name, data, datetime)                    VALUES('" .. txt .. "','" .. currentNumber .. "', '" .. dateTime .. "')"                    db:exec(sql); 

while this is the same string with the new tabledata meant to be saved (and previously serialized):

local tableFactor = {x = operateBy, y = OPERATEbY}                       local stringTableFactor =  json.encode(tableFactor)                       print (stringTableFactor)      local sql = "INSERT INTO info (name, data, datetime, tableoffactors)    VALUES('" .. txt .. "','" .. currentNumber .. "', '" .. dateTime .. "', '" ..stringTableFactor.. "')"                         db:exec(sql);

The string table ‘stringTableFactor’ prints something like this : {“y”:10,“x”:1}

Everything worked and saved fine 'till I added the json string to be saved in the command line, now it doesn’t save anything and returns no errors.

Do you notice anything wrong in the code?

Thaks a lot!!!

#17

Can you print out the value of the sql variable and make sure the quotes are working the way you expect them to?

Rob

#18

I get this:

INSERT INTO info (name, data, datetime, tableoffactors) VALUES('test','0', 'Sun Jan 11 18:43:48 2015', '{"y":10,"x":1}')

not being familiar with sql syntax I don’t if it’s correct behavior…

 

#19

That looks right.

What error are you getting?

#20

That’s the point I get no error, but in the sql file there’s no new row new added. It’s like the function just fails to perform. And everything was working ok before the addition of the new value to the row when saved…