SQLite Question

[stickck]

I've been messing with the SQLite sample project and was wondering if it was better to have the program close the database file after each action or just leave it open until the Project closes (which is how the Sample Project is set up.) i'm going to have several users updating this database, but wasn't sure if it would cause errors (or not work at all) if I was to just leave the database open.


i'm thinking it would be better to have the db open when it needs to get or change info and then close as soon as it's done.

Also, is VACUUM supported in AMS? If so, how would I Script it?


ANY SUGGESTIONS WOULD BE GREAT.

Thanks!

Chris

[bule]

When dealing with multiple users on the one SQLite database, try this thread:
http://www.indigorose.com/forums/showthread.php?t=15122

About VACUUM action... it is not supported since APMS SQLite plugin is version 2.x, and VACUUM was introduced in the version 3.x of the SQLite, AFAIK...

[stickck]

Thanks!

I'll have to mess with it some more. I'll probably only have about 4 people at a time in the database. i dought that it will be a problem, but i would rather be safe then sorry. It already does a backup of the DB file when the program closes.

Thanks again bule!

Chris

[Dermot]

Actually VACUUM is supported. I use it like this. You need to include the name of any table in the database in the VACUUM command, but it will compact all tables.


-- Compact database

function CompactDatabase()

sDatabaseFile = "Your Database File"

-- Open the database
db = SQLite.Open(sDatabaseFile);

nLastError = Application.GetLastError();

if nLastError ~= SQLite.OK then
Dialog.Message("Error", "Error Opening File\r\n\r\n" ..SQLite.GetLastErrorString());
return
end

-- Get file size before we compact
FileSizeBefore = File.GetSize(sDatabaseFile)

-- Compact
SQLite.Query(db, "VACUUM Any_Table_Name")

-- Get file size after compaction
FileSizeAfter = File.GetSize(sDatabaseFile)

Msg = "Compaction complete\r\n\r\nFile size before compaction: "..FileSizeBefore.." Bytes\r\nFile size after compaction: "..FileSizeAfter.." Bytes"

Dialog.Message("Compaction Complete", Msg, MB_OK, MB_ICONINFORMATION, MB_DEFBUTTON1)

end

Personally I open the database when the app starts and keep it open until it closes. Have never had any problems, even with more than one user. The method I described in the post Bule refered to works very well.

[stickck]

Thanks! i thought i read somewhere that i could use Vacuum but i could find it again to save my life!


Thanks again!

chris

[Intrigued]

I never used Vacuum myself. This will come in handy to "tidy up the ship."

Thanks for asking (stickck) and Dermot for giving us an example.

:yes

[bule]

So the trick is to add any table when using VACUUM name, you say... nice!

[pjborg]

Nice. Thanks! :yes