I have encoded a lua table to JSON using json.encode, but for the life of me, I can’t find out how to store it as a BLOB. Here is my current code, which is completely wrong
[lua]
db:exec[[CREATE TABLE league (schedule BLOB, day INTEGER);]]
local schedule = json.encode(sg:generateSchedule());
db:exec([[INSERT INTO league VALUES (]] … schedule …[[,1);]]);
[/lua]
Can someone give me pointers, I can’t find anything on the Internet to help
I’ve done a lot more research, and I’ve come up with this, but it still doesn’t work. Can someone lead me in the right direction?
[lua]
db:exec[[CREATE TABLE league (schedule BLOB, day INTEGER);]]
local schedule = json.encode(sg:generateSchedule());
print(“JSON CODE:\n” …schedule);
local stmt = db:prepare[[INSERT INTO league (schedule, day) VALUES (?,1);]];
stmt:bind_blob(1,schedule):exec(); --Error happens here (attempt to index a number value)
[/lua]
Why do you want to store it as a blob, that is super overkill blobs/images etc. are about the worst thing to ever be included inside of a database. In your example above I see no reason at all for a blob field…
Is there a reason you just don’t store it as a varchar/text field an then just local value = json.encode(row.schedule) etc.
The only reason I try to store it as a blob was this tutorial: http://coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/
I assumed that it was the proper procedure
Well anyways, I FINALLY found the solution after scouring the internet, ther just doesn’t seem to be too many advance SQLite lua resources/tutorials/examples.
Solution 1 (no BLOBS used):
[lua]db:exec[[CREATE TABLE league (schedule TEXT, day INTEGER);]]
local sched = json.encode(sg:generateSchedule());
local stmt= db:prepare[[INSERT INTO league (schedule, day) VALUES( ?, ?)]];
stmt:bind_values( sched, 1)
stmt:step();[/lua]
Solution 2 (WITH BLOBS):
[lua]
db:exec[[CREATE TABLE league (schedule TEXT, day INTEGER);]]
local sched = json.encode(sg:generateSchedule());
local stmt= db:prepare[[INSERT INTO league (schedule, day) VALUES( ?, ?)]];
stmt:bind_values( nil, 1)
stmt:bind_blob(1, sched)
stmt:step();
[/lua]
Strange thing is, both of these solutions actually store a blob in the database:
So I guess the only question left is when should I even use a BLOB field when I can just use a TEXT field instead?
BLOB stands for “Binary Large OBject”. The intent for BLOB’s is to store big chunks of binary data without having it on the file system. Now I’ve been around the developer block for some 35 years and there are very few reasons to store large chunks of binary data in a database. It’s usually better to store a path/URL to an image, audio cllp etc. and not pollute the database with big chunks of data that it can’t do anything with other than retrieve.
Now for an app like Shazam and if the database has stored procedures, then perhaps the stored procedures in the database could scan their audio samples faster than opening and closing files on the file system. But in most cases, you’re not analyzing the binary data on the fly like that.
The idea behind JSON is something called serialization, which basically it takes a table of data which might have binary content and turns it into a string which can be easily stored in a varchar or text type field, quickly written to a flat file in the file system or exchange to and from a server where text data is much better than binary data.
Now when you look at your table of data, if your records have things like :
Name
Age
Address
Phone
and you encoded it with JSON you end up with a string like: { “Name”: "Fred, “Age”:“10”, “Address”: “1 Infinite Loop”, “Phone”: “555-555-1212” } for each record. But you need to ask yourself, would I like to say give me everyone who’s between 20 and 30 years old? If so, having all of this packed into a string becomes much more difficult to work with. Databases are about fields of data. If you have data like this your DB should have a Name field, an Age Field, etc. And you insert your records in the database without putting them in JSON.
Rob
I’ve done a lot more research, and I’ve come up with this, but it still doesn’t work. Can someone lead me in the right direction?
[lua]
db:exec[[CREATE TABLE league (schedule BLOB, day INTEGER);]]
local schedule = json.encode(sg:generateSchedule());
print(“JSON CODE:\n” …schedule);
local stmt = db:prepare[[INSERT INTO league (schedule, day) VALUES (?,1);]];
stmt:bind_blob(1,schedule):exec(); --Error happens here (attempt to index a number value)
[/lua]
Why do you want to store it as a blob, that is super overkill blobs/images etc. are about the worst thing to ever be included inside of a database. In your example above I see no reason at all for a blob field…
Is there a reason you just don’t store it as a varchar/text field an then just local value = json.encode(row.schedule) etc.
The only reason I try to store it as a blob was this tutorial: http://coronalabs.com/blog/2012/04/03/tutorial-database-access-in-corona/
I assumed that it was the proper procedure
Well anyways, I FINALLY found the solution after scouring the internet, ther just doesn’t seem to be too many advance SQLite lua resources/tutorials/examples.
Solution 1 (no BLOBS used):
[lua]db:exec[[CREATE TABLE league (schedule TEXT, day INTEGER);]]
local sched = json.encode(sg:generateSchedule());
local stmt= db:prepare[[INSERT INTO league (schedule, day) VALUES( ?, ?)]];
stmt:bind_values( sched, 1)
stmt:step();[/lua]
Solution 2 (WITH BLOBS):
[lua]
db:exec[[CREATE TABLE league (schedule TEXT, day INTEGER);]]
local sched = json.encode(sg:generateSchedule());
local stmt= db:prepare[[INSERT INTO league (schedule, day) VALUES( ?, ?)]];
stmt:bind_values( nil, 1)
stmt:bind_blob(1, sched)
stmt:step();
[/lua]
Strange thing is, both of these solutions actually store a blob in the database:
So I guess the only question left is when should I even use a BLOB field when I can just use a TEXT field instead?
BLOB stands for “Binary Large OBject”. The intent for BLOB’s is to store big chunks of binary data without having it on the file system. Now I’ve been around the developer block for some 35 years and there are very few reasons to store large chunks of binary data in a database. It’s usually better to store a path/URL to an image, audio cllp etc. and not pollute the database with big chunks of data that it can’t do anything with other than retrieve.
Now for an app like Shazam and if the database has stored procedures, then perhaps the stored procedures in the database could scan their audio samples faster than opening and closing files on the file system. But in most cases, you’re not analyzing the binary data on the fly like that.
The idea behind JSON is something called serialization, which basically it takes a table of data which might have binary content and turns it into a string which can be easily stored in a varchar or text type field, quickly written to a flat file in the file system or exchange to and from a server where text data is much better than binary data.
Now when you look at your table of data, if your records have things like :
Name
Age
Address
Phone
and you encoded it with JSON you end up with a string like: { “Name”: "Fred, “Age”:“10”, “Address”: “1 Infinite Loop”, “Phone”: “555-555-1212” } for each record. But you need to ask yourself, would I like to say give me everyone who’s between 20 and 30 years old? If so, having all of this packed into a string becomes much more difficult to work with. Databases are about fields of data. If you have data like this your DB should have a Name field, an Age Field, etc. And you insert your records in the database without putting them in JSON.
Rob