Use a try block:
set theDb to open db in file "~/desktop/TestDB.db" with can create
try
-- do your stuff here
on error errMess number errNum
-- in case it's still open
try
close db theDb
end try
error errMess number errNum
end try
The scenario where it’s coming up for me the most is I’m trying to learn how to use it, so I’m stepping through a script, and then seeing what happens, making an edit and running again.
Is it possible to send a paused script into an error handler?
I don’t think so.
I still don’t understand why you can’t use a try block. (And I’m not being difficult — once we lose a reference to the open database, there’s nothing we can do.)
If I’m stepping through a script and I need to stop and make an edit, a try block doesn’t help. That only helps if there’s an Apple Event error in the script.
The script below is what I’ve been using to test. It finishes before the commit or close command, so every time it runs it leaves the db open.
Using the db as a property doesn’t help.
I tried using the close command in the expressions pane of the result and variable inspector, no joy.
maybe the next thing would be to insert deliberate dialogs that I can cancel to generate the errors.
use AppleScript version "2.5" -- El Capitan (10.11) or later
use script "SQLite Lib2" version "1.0.0"
use scripting additions
property theDb : ""
set theList to {¬
{"Christopher", "Froome", "Britain", 1}, ¬
{"Fabio", "Aru", "Italy", 2}, ¬
{"Romain", "Bardet", "France", 3}, ¬
{"Rigoberto", "Uran", "Colombia", 4}, ¬
{"Jakob", "Fuglsang", "Denmark", 5}}
-- create and open db
set theDb to open db in file "~/desktop/Test3DB.db" with can create
-- create table, clearing it if necessary
try
update db theDb sql string "drop table if exists standings"
update db theDb sql string "create table standings (firstname, lastname, country, position integer)"
on error errMess number errNum
-- in case it's still open
try
close db theDb
end try
error errMess number errNum
end try
-- enter data
begin transaction db theDb
repeat with anEntry in theList
update db theDb sql string "insert into standings values (?, ?, ?, ?)" with arguments anEntry
end repeat
return
When a data base is open where does it live? Is there a process or an app? If I leave a bunch of DBs open will I run out of memory?
Do they close by themselves if idle for a time?
You need to put all your code in the try block, and you need to always have your own close call.
use AppleScript version "2.5" -- El Capitan (10.11) or later
use script "SQLite Lib2" version "1.0.0"
use scripting additions
property theDb : ""
-- create and open db
set theDb to open db in file "~/desktop/Test6DB.db" with can create
-- create table, clearing it if necessary
try
-- do *all* your stuff here
close db theDb -- always!
on error errMess number errNum
-- in case it's still open
try
close db theDb
end try
error errMess number errNum
end try
No, they don’t close themselves. You’re unlikely to run out of memory, but there’s probably a limit to how many a process (SD or an applet) can open.
The typical use is that an app opens a database and leaves it open. That doesn’t work for AppleScript because we have no way of keeping a global reference to the database — it effectively leaks. So the answer is to make sure you close every time, or regularly quit and relaunch.
I’m looking at the batch import options. It looks like I can import CSV and Tab separated values, but is there a way to directly import an appleScript list or an appleScript record?
If not, that would be a feature request.
Nevermind, I think I get it
I’ve uploaded version 1.1.0 of SQLite Lib2. There’s no change in SQL functionality, but the library now tries to close a database when a command generates an error. This should mean less having to quit and relaunch when errors happen writing scripts.
1 Like
The library is brilliant, Shane, thank you! I’m now struggling to focus as there are about 17 different projects I want to use it in and I want to start them all at once.
Can I please make a feature request? You have the date columns parameter to convert dates (which are stored in sqlite 3 as integers, reals or text) into AppleScript date objects. Could you please add a boolean columns parameter to convert boolean values (which sqlite 3 stores as integers) into AppleScript boolean values? Integers can of course be used to the same effect as boolean values if you know to expect them, but for integrating with existing scripts it can be difficult where the existing script expects a boolean value. And I could loop through the query results and convert integers to booleans in my own script logic, though when dealing with result type list of records I’d have to do it separately for each query result rather than using a generic handler to do that, but I’m hoping there’s some magic you could use on your end to do that in a more performant way.
Unless does anyone have any tips for converting integers to booleans in a list of records?
I’ll take it on notice, but don’t hold your breath 
. Changing the underlying Objective-C code makes for all sorts of complications in terms of what happens when a previous version of the framework is already loaded by another script.
Version 1.1.0 introduced the following change: When a command results in an error, the library will attempt to close the database and clear the database variable before the error is returned to your script.
Could I please request that in future versions that behaviour be optional or something? It causes problems for me.
I don’t think an error with one command is necessarily fatal to the whole script. In one of my scripts, for instance, I have a feature that allows a user to enter an sqlite query in a dialog, and then SQLite Lib uses that as the sql string parameter to run a query command and display the results using Myriad Tables Lib. It runs in a repeat loop so you can run as many queries on the same db as you like. If one query fails, I just display an error alert and the user should then be allowed to just run another query, but since v1.1.0 the db is closed and the variable that points to it cleared thus any subsequent queries throw an error. There are other similar examples.
I suppose a work around for me might be to have a function to reopen the db which I can call from the on error part of a try block if the error code indicates that its an error with SQLite Lib. But to make that elegant would involve lots of try blocks and conditional error handling, or in other words much more code. 
In contrast, the problem that is being solved by the change in v1.1.0 never seemed like much of a problem to me. I called a function in the on quit handler which would close the db if it was open and I basically wrapped the contents of the on run handler in a try block which would call the on quit handler if a fatal error was encountered, so either way the clean up function would get called.
Is there some reason you can’t trap the error, and reopen the database in such cases?
I could, but the reasons I don’t are twofold.
First, my applet keeps track of where the database is saved and provides a menu of things that can be done with it, but most of the logic is in my own custom libraries which include lots of different handlers that in turn call into SQLiteLib2. The handlers in my custom libraries each have a parameter so that the applet can pass them a reference to the open database. so to catch the error and reopen the database I’d have to catch the error in the applet, because only it knows the location of the database, and then reopen the database and store the reference to it in the variable in the applet. That’s not as graceful for the user as, for instance, catching the error in the specific handler where it is thrown because throwing the error up to the applet means the user is effectively yanked out of whatever context thery were just in.
And second, to do it gracefully would mean having to test for and catch db errors in lots of places in my code, whereas the code required to ensure the db is closed if a fatal error is thrown is a lot less.
Really the redeeming part is to try and write code that won’t throw an error in SQLiteLib, and I’m getting better at doing that, which is why it’s taken me a while to make the feature request, but it would be nice to have the reassurance that my script can go on even if an error is encountered.
I’ve had a look at the code, and making it an option is a bit tricky. I’ll keep it in mind for the next update, although honestly I don’t have one planned at the moment.
You can, however, execute the query handler directly. You will need to include framework use statements:
use framework "Foundation"
use framework "FMDBAS"
And then, assuming scriptDb is the variable containing the Db:
set theResult to scriptDb's realDb's executeQueryAS:sqlString withArguments:withArgs dateColumns:dateCols boolColumns:boolCols rowsAs:rowType
The rowType variable can be an empty string, which matches a return type of list of lists. The other parameters should be self-explanatory.
You’ll need to make a copy of the FMDBAS framework somewhere the script can see it, too.
There are two issues with this script.
First, after stepping through it a few times in debug mode, it won’t compile, but if I switch out of debug mode it will.
Second, I can’t get it to work. I’m getting and “unknown database error 10000” whatever that is.
Here’s what I’m trying to do:
I have a list of about 10k email addresses (with name and subject).
I have an email account that gets hundreds of emails per day, most from senders in that list. I want to compare a list of new emails to the 10k names, and delete all the ones that are in the long list.
(For various reasons I can’t use normal junkmail filters).
When it’s up and running I’ll query the db for messages that have the same emailAddress and senderName and flag those for deletion. (I’m doing it now with appleScript lists and it takes about 20 minutes).
use AppleScript version "2.5" -- El Capitan (10.11) or later
use script "SQLite Lib2" version "1.0.0"
use scripting additions
set newEmailData to {{"hft@em.harborfreight.com", "Harbor Freight Tools ", "Big Purchases Made Easy with Equal Monthly Payments"}, ¬
{"officedepot@e.officedepot.com", "\"Office Depot\" ", "👨👩👧👦Go Big For Your Dads & Grads🎓"}, ¬
{"no_reply@email.apple.com", "Apple ", "Your receipt from Apple."}, ¬
{"specials@reader.macsales.com", "OWC Larry ", "☀️🖥️Macs + More! Save BIG on Apple Laptops, iMacs, iPhones, drives, upgrades + so much more…"}, ¬
{"specials@reader.macsales.com", "OWC Larry ", "☀️🖥️Macs + More! Save BIG on Apple Laptops, iMacs, iPhones, drives, upgrades + so much more…"}, ¬
{"noreply@redditmail.com", "Reddit ", "\"Days before Rep. Mike Nearman helped protest...\""}, ¬
{"News@email.americastestkitchen.com", "\"America's Test Kitchen\" ", "\"What's for dinner?\" It just tastes better without the pressure."}, ¬
{"RYOBI.News@email.ryobitools.com", "\"RYOBI\" ", "RYOBI DAYS Deals on Combo Kits!"}, ¬
{"newsdigest@insideapple.apple.com", "Apple News ", "He wanted to put on a Nas concert. He ended up being held captive."}, ¬
{"extras@antiquesroadshow.wgbh.org", "\"Antiques Roadshow\" ", "Deadline Monday: Last chance to enter the 2021 Appraisal Contest!"}}
repeat with x from 1 to 10
--set item 3 of item x of newEmailData to (x as text)
end repeat
set emailDBPath to "~/Desktop/email-2.db"
try
set emailDB to open db in file emailDBPath with can create
update db emailDB ¬
sql string ("drop table if exists test")
update db emailDB sql string "create table standings (EmailAddress, SenderName, messageSubject)"
update db emailDB ¬
sql string ("create table test (EmailAddress, SenderName, messageSubject)")
--begin transaction db emailDB
batch update db emailDB ¬
sql string "insert into standings values (?, ?, ?)" with arguments newEmailData
close db emailDB
on error errMess number errNum
close db emailDB
display dialog errMess & " (" & errNum & ")"
error number -128
end try
I’m guessing you meant to also have a “drop table if exists standings” in there.
The 10000 is just AppleScript’s work.
embarrassingly, after I posted that I figured out that in that script “text” and “standings” are the names of the table in the db.
I would love to see more examples of SQL syntax with sql lite 2.
I have gone to https://sqlite.org/ and gotten lots of syntax examples but translating those into SQLL2 syntax is problematic.
I’m not sure what information belongs in the sql string and what should go with arguments, for example.
Also, do any of the pragma or view commands function?