SQLite newbie question

[jeffInt]

Am I correct in thinking that using SQLite within RR creates a memory resident database?

If so is there a way to save the database off to disk? If we can do this there must also be a way to reload it?

Regards

Jeff

[BvG]

Your assumption is wrong. sqlite in rev always uses a file, that has to be saved somewhere, it can't do it's thing only in memory.

[jeffInt]

I can't be totally off the mark in my assumption that RR creates a sqlite database in memory or the following should not work;

on mouseUp
global gConID
local tConID

put revOpenDatabase("sqlite",,,,,) into tConID /* Note no database name given */
if tConID is"" then
answer warning "Problem creating or accesing database!"
else
answer information "Memo connected, your connection ID is: " &tConID
put tConID into gConID
end if

end mouseUp

When this code runs I get an integer returned which I can use to create a table

on mouseUp
global gConID
if gConID is "" then
answer information "No database is connected"
exit mouseUp
end if

local tSQL, tResult
put"CREATE TABLE apps(appID integer primary key, appName text, appRegCode text, appUserID)" into tSQL
revExecuteSQL gConID, tSQL
put the result into tResult
/*handleRevDBerror tResult*/
if the result is not empty then
answer warning the result
exit mouseUp
end if
answer information "Number of tables added: "& tResult
end mouseUp

If I look in the directory where my stack resides I see no sqlite db file.

I imagine that I need to do one of the following;

Save the database - using what commands?

Export the database (as SQL?) - using what commands?

Cheers

Jeff

[paul_gr]

this will create a db file in the same folder as the stack.
You have to provide a path (just a filename will do) to get a file written to disk.

on mouseUp
global gConID
put revOpenDatabase("sqlite","lite_DB.db",,,,,,) into gConID
end mouseUp

Paul

[BvG]

put revOpenDatabase("sqlite",,,,,) into tConID /* Note no database name given */

interesting i didn't know that was possible, i need to remember that, possible that i could need it some when.

[jeffInt]

Hmm. If I use my code I can connect, add a table and disconnect. If I reconnect then the table must be added again. This tells me that this database resides in memory. There is no file in the stack directory.

If I attempt to open a database with a filename (as you suggest) my code reports the following error "Unable to open the database file". Perhaps there is something wrong with my setup. Or do I need to create the db file with another app?

I am using RR studio 3.0 if this has a bearing.

Cheers

Jeff

[paul_gr]

Have you had a look at the sample sqlite stack in your rev installation folder?
it's called SQLite Sampler.rev, and it's in the resources/examples folder.

Paul

PS, couple of sentences from "The definitive guide to sqlite"

"SQLite can also create in-memory databases. In most extensions, if you use :memory: or an empty string as the name for the database, it will create the database in RAM. The database will only be accessible to the connection that created it (it cannot be shared with other connections).
Furthermore, the database will only last for the duration of the connection. It is deleted from memory when the connection closes.
When you connect to a database on disk, SQLite opens a file, if it exists. If you try to open a file that doesn’t exist, SQLite will assume that you want to create a new database. In this case, SQLite doesn’t immediately create a new operating system file. It will only create a new file if you put something into the new database—create a table or view or other database object. If you just open a new database, do nothing, and close it, SQLite does not bother with creating a database file—it would just be an empty file anyway."

[jeffInt]

Yes I have tried to run the sqlite example stack. This gives me an error when I try the first "connect" button "Database error: Unable to open the database file.

Jeff

[paul_gr]

That's strange; I just tried the sample stack again using Rev Studio 3.0 and it works ok for me.

Paul

[jeffInt]

Hmm, perhaps something was not correctly installed. The RR app I am using is a downloaded version. I will perhaps re-install when I receive the CD copy.

Jeff

[jeffInt]

I have posted a request for help with RunRev, perhaps they can help clear the problem. I will post any further results as they arrive.

Jeff

[jeffInt]

OK more news.

When I originally installed RunRev I did so from a user account. I actually ran the exe file as administrator on this user account. Now when I attempt run the SQLite example stack on this user account I get the errors we have talked about.

If I log on as administrator the SQLite example works fine.... almost. When I run the example I do not see the db file created, even if I add tables. However, if I alter the code within the example to qualify the file path i.e. C:\Appreg.db everything is sublime.

I hope we have all learned from this!

Regards

Jeff

[BvG]

Yes, not having writing rights to a hard disk or specific folder will prevent you from creating files there