How do I view sqlite db.

Solar2D General Questions/Discussion
#1

I have a sqlite db i created. It is stored in /Users/rvergin/Desktop/Corona Projects/astrotools/usersaves.sqlite. 

When I open the db using the Firefox Sqlite Manager I see no records in the db. I see another db stored in /Users/rvergin/Library/Application Support/Corona Simulator/11-F62A6B8239F29B02FFFEDA74E4CACC85/Documents/usersaves.sqlite

and the sqlite manager cannot get there. Where does the db really get stored? How can I access it? 

#2

On the Corona SImulator, on the menu go to File > Show Project Sandbox

Assuming your code stores it in the Resources Directory

local path = system.pathForFile(“data.db”, system.DocumentsDirectory)

Then, the database should be in the Documents Folder.

I use SQLite Database Browser (free standalone application) to view it.

Of course, it might be your code’s fault and the reason you see no records is because you didnt properly insert the data into the database

#3

http://sqlitebrowser.org/

If you’re only reading it, the DB can be anywhere in your game’s folder structure.  

Ex: For a game called bob, you could make a folder called db and put  your ‘usersaves.sqlite’ there

bob\

  |

  | —\db\usersaves.sqlite

local sqlite3 = require "sqlite3" local path = system.pathForFile("bob/usersaves.sqlite", system.ResourceDirectory) db = sqlite3.open( path )

If you’re going to modify and save to the DB, it needs to be in the documents directory:

--[[Before this point, write code to copy your original file from bob/ to the documents directory, then forever after that only access the documents directory version/copy. Either that, or create a blank one in documents when you first run your game. I'm not showing the code for either of these steps. --]] -- Accessing exiting DB in documents directory. local sqlite3 = require "sqlite3" local path = system.pathForFile("usersaves.sqlite", system.DocumentsDirectory) db = sqlite3.open( path )
#4

Here is what I am doing…

local sqlite3 = require (“sqlite3”)

local path = system.pathForFile ( “usersaves.sqlite”, system.DocumentsDirectory)

local usersavesdb = sqlite3.open(path)

local insertQuery = [[INSERT INTO usersaves VALUES (gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)]]

usersavesdb:exec(insertQuery)

io.close(path)

#5

Wait so you are able to open usersaves.sqlite, but for some reason, you see no records in the database when you open it? Is that what is happening.

If so, I assume that you are trying to insert text values into your database, therefore, your query won’t work:

[lua]

[[INSERT INTO usersaves VALUES (gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)]]

[/lua]

^Treat that query as an entire string without any context, the database won’t know what gdata.saveindex is because it is part of the string

What you should do is use parameterized queries:

[lua]

local stmt = db:prepare[[INSERT INTO usersaves VALUES( ?, ?, ?, ?, ?)]]

stmt:bind_values( gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)

stmt:step()

[/lua]

Further reading on parameterized queries in lua:

http://www.nessie.de/mroth/lua-sqlite3/documentation.html#ref11

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#methods_for_prepared_statements

#6

First, thanks for your reply.

I am now getting an error on 

stmt:bind_values(gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)

stating

attempt to index local ‘stmt’ (a nil value)

Second, where should this db be located?

Should I have a Documents folder in my project i.e.

 /Users/rvergin/Desktop/Corona Projects/astrotools/usersaves.sqlite.

should be

 /Users/rvergin/Desktop/Corona Projects/astrotools/DOCUMENTS/usersaves.sqlite.

…

​I moved the usersaves.sqlite db from the astrotools folder and into astrotools/Documents

deleted usersaves.sqlite from sandbox.

went back to insertquery 

code ran without errors

still no recs. 

file is in sandbox with zero bytes. 

#7

When I 

print(tostring(system.DocumentsDirectory))

I get

userdata: 0x104fa4f6a

not much help. 

#8

So my next question is 

IF I have a db that I don’t want changed by the user where is the resource directory I am supposed to put the db into?

#9

Ok. I found the resource directory

This directory refers to the core project directory which is the same location as the main.lua

#10

Oh you should probably save it in the DocumentsDirectory. That is usually the standard db procedure

#11

[lua]

local sqlite3 = require “sqlite3”
 
local path = system.pathForFile(“data.db”, system.DocumentsDirectory)
db = sqlite3.open( path )
    
–Generate the myteam, the team that the player chose to play for and any custom lineups
db:exec[[CREATE TABLE usersaves (saveindex INTEGER, savename TEXT, savedesc TEXT, savedate TEXT, contents TEXT);]]

–Test values to insert
local gdata = {saveindex = 1, savename = “Test save”, savedesc = “Random description”, savedate = “Random date”}
local contents = “Nothing”

local stmt = db:prepare[[INSERT INTO usersaves VALUES( ?, ?, ?, ?, ?)]]
stmt:bind_values( gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)
stmt:step()

db:close();

[/lua]

Are you missing anything from the sample code I have written above?

#12

ok progress being made.

I moves db back to resource dir (mentioned above). I found code to check if file exists and copy.

I copy db from res dir to doc dir if file does not exist in doc dir.

This works.

I downloaded the sqlite pro from app store.

I am able to view db and table now.

Still not able to insert.

I tried the insertquery from initial post.

I will try 14 thru 17 now.

#13

Ok. changed insertquery to stmt.

It works. WooHoo!!

Thank you astrizhong.

Have a great day.

#14

On the Corona SImulator, on the menu go to File > Show Project Sandbox

Assuming your code stores it in the Resources Directory

local path = system.pathForFile(“data.db”, system.DocumentsDirectory)

Then, the database should be in the Documents Folder.

I use SQLite Database Browser (free standalone application) to view it.

Of course, it might be your code’s fault and the reason you see no records is because you didnt properly insert the data into the database

#15

http://sqlitebrowser.org/

If you’re only reading it, the DB can be anywhere in your game’s folder structure.  

Ex: For a game called bob, you could make a folder called db and put  your ‘usersaves.sqlite’ there

bob\

  |

  | —\db\usersaves.sqlite

local sqlite3 = require "sqlite3" local path = system.pathForFile("bob/usersaves.sqlite", system.ResourceDirectory) db = sqlite3.open( path )

If you’re going to modify and save to the DB, it needs to be in the documents directory:

--[[Before this point, write code to copy your original file from bob/ to the documents directory, then forever after that only access the documents directory version/copy. Either that, or create a blank one in documents when you first run your game. I'm not showing the code for either of these steps. --]] -- Accessing exiting DB in documents directory. local sqlite3 = require "sqlite3" local path = system.pathForFile("usersaves.sqlite", system.DocumentsDirectory) db = sqlite3.open( path )
#16

Here is what I am doing…

local sqlite3 = require (“sqlite3”)

local path = system.pathForFile ( “usersaves.sqlite”, system.DocumentsDirectory)

local usersavesdb = sqlite3.open(path)

local insertQuery = [[INSERT INTO usersaves VALUES (gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)]]

usersavesdb:exec(insertQuery)

io.close(path)

#17

Wait so you are able to open usersaves.sqlite, but for some reason, you see no records in the database when you open it? Is that what is happening.

If so, I assume that you are trying to insert text values into your database, therefore, your query won’t work:

[lua]

[[INSERT INTO usersaves VALUES (gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)]]

[/lua]

^Treat that query as an entire string without any context, the database won’t know what gdata.saveindex is because it is part of the string

What you should do is use parameterized queries:

[lua]

local stmt = db:prepare[[INSERT INTO usersaves VALUES( ?, ?, ?, ?, ?)]]

stmt:bind_values( gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)

stmt:step()

[/lua]

Further reading on parameterized queries in lua:

http://www.nessie.de/mroth/lua-sqlite3/documentation.html#ref11

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#methods_for_prepared_statements

#18

First, thanks for your reply.

I am now getting an error on 

stmt:bind_values(gdata.saveindex, gdata.savename, gdata.savedesc, gdata.savedate, contents)

stating

attempt to index local ‘stmt’ (a nil value)

Second, where should this db be located?

Should I have a Documents folder in my project i.e.

 /Users/rvergin/Desktop/Corona Projects/astrotools/usersaves.sqlite.

should be

 /Users/rvergin/Desktop/Corona Projects/astrotools/DOCUMENTS/usersaves.sqlite.

…

​I moved the usersaves.sqlite db from the astrotools folder and into astrotools/Documents

deleted usersaves.sqlite from sandbox.

went back to insertquery 

code ran without errors

still no recs. 

file is in sandbox with zero bytes. 

#19

When I 

print(tostring(system.DocumentsDirectory))

I get

userdata: 0x104fa4f6a

not much help. 

#20

So my next question is 

IF I have a db that I don’t want changed by the user where is the resource directory I am supposed to put the db into?