SQL syntax help

Games
#1

Im trying to understand how to INSERT INTO and SELECT FROM a field from my table. Not sure what the problem is.

First I build the database

require("sqlite3")  
--Open data.db. If the file doesn't exist it will be created  
local path = system.pathForFile("data.db", system.DocumentsDirectory)  
db = sqlite3.open( path )

Set up my table

local tablesetup = "CREATE TABLE IF NOT EXISTS LEVEL\_INFO (LEVEL\_ID INTEGER PRIMARY KEY, HIGHSCORE INTEGER, LEVEL\_STATUS INTEGER, TROPHEY INTEGER)"  
db:exec( tablesetup )

Then try to insert data into a field (syntax?)

local testvalue = 99999  
local tablefill ="INSERT INTO LEVEL\_INFO(HIGH\_SCORE) WHERE LEVEL\_ID = '1' VALUES (NULL, '"..testvalue.."')"  
db:exec( tablefill )

And later try to read the field from another lua file by…

local db = sqlite3.open\_memory() local highScoredata = "SELECT FROM LEVEL\_INFO(HIGH\_SCORE) WHERE LEVEL\_ID = '1'" local highScore = db:exec( highScoredata ) print(highScore .. " highsScore") [import]uid: 28912 topic_id: 8610 reply_id: 308610[/import]

#2

Well Ive got the insertion of data into a specific field, and retrieving working seemingly well. I am having trouble accessing the table I create though when I move from my main.lua file, to my level2.lua file. I can access it with

  
local path = system.pathForFile("data.db", system.DocumentsDirectory)  
db = sqlite3.open( path )

but would have thought I should be able to access it with

local db = sqlite3.open\_memory()

Any ideas as to why I cant just retrieve and write data to the table without having to reopen the database? [import]uid: 28912 topic_id: 8610 reply_id: 30949[/import]

#3

I’m not too familiar with SQLite yet. But from what I know, you do have to open the
Database each time because when you move from module to module, the open path you
Created in the previous module is local to that specific module.

What you can do is create a external module with the sqlite database, execution, retrieve, and save to etc… Then call

[lua]

watever = require"watever"
inside each of your level.lua

– I hope this help.
– again I’m still new to sqlite too so this might not be the most efficient way of doing it, but that’s how I would do it [import]uid: 12455 topic_id: 8610 reply_id: 30992[/import]

#4

In your insert statement you are trying to insert 2 values (NULL and testvalue) and only specifying one column (HIGH_SCORE)

local testvalue = 99999  
local tablefill ="INSERT INTO LEVEL\_INFO(HIGH\_SCORE) WHERE LEVEL\_ID = '1' VALUES (NULL, '"..testvalue.."')"  
db:exec( tablefill )

[import]uid: 31262 topic_id: 8610 reply_id: 30997[/import]