any better format to insert the data into sqlite ?

Solar2D General Questions/Discussion
#1

i’m very confuse when I want to insert so many field’s data into sqlite, any better format to insert the data into sqlite ?

please suggest!

[code]
local tablefill =[[INSERT INTO test VALUES (NULL, ‘]]…testvalue[1]…[[’,’]]…testvalue[2]…[[’);]]
[import]uid: 22631 topic_id: 27197 reply_id: 327197[/import]

#2

Hi,

SQL is a strict language and the general way to insert data into a row is the way you describe it. Since that sqlite doesn’t support stored procedures you have to stick with this way to add data.

But i suggest that you add the fields so that it makes it more clear to work with the queries.

  
[[INSERT INTO gamedata (opened,PO) VALUES(0,0);]];

Joakim [import]uid: 81188 topic_id: 27197 reply_id: 110449[/import]

#3

hi Joakim,

thanks!

can you help check with code any wrong, can’t insert into table

[code]
local tablefill =[[INSERT INTO mathsquerylib (id,cate,grade,desc_chn,desc_eng,desc_hk,ans,math_sign
VALUES (NULL, ‘]]…cate…[[’,’]]…grade…[[’,’]]…desc_chn…[[’,’]]…desc_eng…[[’,’]]…desc_hk…[[’,’]]…ans…[[’,’]]…math_sign…[[’);]]

local chk_db =db:exec( tablefill )
if chk_db ~= 0 then
print(“can’t insert!”…chk_db)
end
[import]uid: 22631 topic_id: 27197 reply_id: 110474[/import]

#4

What error do you get in the console…there could be several of problems and I can’t figure that out just by the code…

[import]uid: 81188 topic_id: 27197 reply_id: 110493[/import]

#5

One approach when debugging sql syntax is to print the whole query to the console. Copy that statement and paste it into a query analyzer and run it against the db. Then you will see if it works. I am using Base but there are several of other freeware database managers.

Joakim [import]uid: 81188 topic_id: 27197 reply_id: 110495[/import]

#6

i’m very confuse when I want to insert so many field’s data into sqlite, any better format to insert the data into sqlite ?

Do you have a bunch of data in an excel sheet or a text file (CSV?) that you want to import? If so that is easy to do with a SQL lite browser. I use:

http://sqlitebrowser.sourceforge.net/

Or are you saying there is a lot of data you want your user to populate dynamically? If so you pretty much have to write out the insert statements.

About:

local tablefill =[[INSERT INTO mathsquerylib (id,cate,grade,desc\_chn,desc\_eng,desc\_hk,ans,math\_sign VALUES (NULL, ']]..cate..[[',']]..grade..[[',']]..desc\_chn..[[',']]..desc\_eng..[[',']]..desc\_hk..[[',']]..ans..[[',']]..math\_sign..[[');]]

I’m not sure what you’re trying to do here. It looks like you’re trying to concat variables.

Can’t you use a String for an db statement rather than a table? I haven’t worked on my corona app in awhile, but I think I remember using strings instead of tables, ie something like:

local insertStatement = "insert into mathsquerylib(id,cate,grade,desc\_chn,desc\_eng,desc\_hk,ans,math\_sign) VALUES (NULL, '"..cate.. "','" ..grade.."','"..desc\_chn.."','"..desc\_eng.."','"..desc\_hk.."','"..ans.."','"..math\_sign.. "'")

I’ll check for you in a few hours when I get home. JKrassman gave some good advice, when debugging a SQL statement like this, print it out to the console and see exactly what you are passing to the SQL engine. When I run into trouble, I also write the actual SQL in whatever client I’m using (ie SQLDataBrowser, Aqua Data Studio, SQL Server Manager) and then write it in my code, print it out, and compare the two.
[import]uid: 112807 topic_id: 27197 reply_id: 110527[/import]