The library uses SQLite3 — the 2 refers to the library version.
To be clear, macOS includes an SQLite3 library, libsqlite3.dylib. This is what the OS uses, but it has a C interface, and can’t be used directly from AS. The FMDB framework wraps that C-code in Objective-C. However, it’s not ideal for ASObjC: using it would involve repeat loops for results, and some of it is async. So the FMDBAS framework extends it and makes it more useable from AppleScript. This is then packed in the library bundle, which adds terminology and some other AppleScript house-keeping.
But you’re ultimately calling the same SQLite3 code as anything on your Mac.
So for instance, I have a library with a queryDB handler as follows:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
use OBUtility : script "OB Utilities" version "1.0"
use script "SQLite Lib2" version "1.1.1"
use script "Myriad Tables Lib" version "1.0.10"
on queryDB(thisDB)
set theQuery to ""
repeat
-- the following function calls a handler from another library that uses Dialog Toolkit Plus to get the user to input text into a text field
set theResult to returnOBValue of OBUtility without backButton given prompt:"Enter your query.", title:"Query Database", cancelButtonName:"Done", nextButtonName:"Query", defaultValue:theQuery
if userCancelled of theResult then exit repeat
set theQuery to valueReturned of theResult
try
set queryResult to query db thisDB sql string theQuery
if queryResult is {} then set queryResult to {{}}
displayTable for queryResult with hiddenCancelButton and emptySelectionAllowed given prompt:"Here are the results of your query.", title:"Query Result", OKButtonName:"Done"
on error errorMessage number errorNumber
display alert "Invalid query" message "Your query " & theQuery & " threw an error." & return & return & "The error message was:" & return & errorMessage
-- by now thisDB will no longer be a valid reference to an open database
-- as SQLite Lib2 will have automatically closed it
-- is there a way to reopen the db using the old reference to the open database (without knowing the path to the db)?
end try
end repeat
end queryDB
on displayTable -- with a bunch of parameters
-- displays the given table data using Myriad Tables Lib
end displayTable
That handler gets passed a reference to an open db, but doesn’t have knowledge of the file path to the db.
Is there a way that the handler can re-open the db in the event of an error so that the handler and script can continue to run?
The other thing is that after the db is closed on error, the variable that previously contained the reference to the open db is not set to missing value. I have a closeDB() handler that runs before the applet quits and it checks to see if the db reference is missing value (because that’s what it’s set to before the db is opened), and if it’s not missing value it tries to close the db. But if it’s been closed automatically by Sqlite Lib2 on error then there’s no way of me testing that it seems. It would help if the library set the old references to missing value somehow so I could test to see if it’s still open or not.
Not without knowing the path. Just pass the path as well as the db.
The value returned is actually a script object with a property called realDb, which contains the ASObjC reference to the database. This property is set to missing value when the database is closed.
Not sure I completely understand this, but I have an applet that opens the db on launch, stays open to do stuff repeatedly with the db, and then closes it on quit. Is that going to be a problem? Will that cause memory leaks? It doesn’t seem to be causing any problems so far, but it’s still being developed and hasn’t been thoroughly put through its paces yet. Opening and closing the db before and after each query would take a whole lot of re-working so I’m hoping that won’t be necessary.
I was doing it with debugging off. Embarrassingly, I’ve never worked out how to use SD debugging so I continue to use my cave-person debugging methods.
Okay, works as expected now. I thought readDB was a funny name for the property when I was typing it and something in the back of my head was raising a flag because I’ve seen realDB written quite a few times but didn’t catch it.
What UTI can I use to identify the database files created by SQLite Lib2? If I do:
choose file of type {“public.database”}
I’m not able to see the database files created by SQLite Lib2.
Same goes if I try the uti “vnd.sqlite3”.
Incidentally I’ve downloaded from the Mac App Store the app Ridill SQLIte which can open, view and query sqlite databases and it correctly identifies the files created by SQLite Lib2 as sqlite databases and has become the default app to open them and not other databases. I think it did that without intervention by me but it’s possible that I did an open with that app on the first one but have forgotten it.
Alternatively, the other way of approaching this would be to ask how I would go about associating a file with a particular UTI? Let’s say I tinkered around with my applet’s info.plist file to export my own custom UTI, how would I tell macOS that a particular file conformed to the UTI? Is there a way I can tell the OS that the files I’ve created with SQLite Lib2 conform to public.database?
Also, I just had an issue where there was an execution error when updating my db. That threw an error which was caught by my try on error handler, which tried to rollback the db. That attempted rollback threw an error which looked to be because the library had already closed the db on the first execution error and so the reference to theDB passed to the rollback command in the on error clause was missing value.
Just another argument in my attempt to persuade Shane to revert SQLite Lib2 to the older behaviour where it didn’t automatically close a db when an error is encountered.
I thought readDB was a funny name for the property when I was typing it and something in the back of my head was raising a flag because I’ve seen realDB written quite a few times but didn’t catch it.