AppleScript and Database Events (SQLite) - fetching and updating records/fields

Scripting Forums AppleScript | Mac OS X
#1

I have a SQLite database with 3 tables: Parts, Orders and Buyers. So potentially hundreds of records in each table. I simply need to access this database using AppleScript and Database Events.

It seems Database Events has no concept of Tables, so essentially each Table must be its own database. So in this case Parts, Orders and Buyers are all separate databases, right?

Also, for simplicity, let’s say Parts consists of only 3 fields:

  • Number (integer, primary key, unique, not null)
  • Name (text)
  • Supplier (text)

Trying a simple operation of fetching a part by part number, let’s say 25, fails:

tell application “Database Events”
tell database Parts
tell (first record where (value of field “Number” is 25))
set Pname to (value of field “Name”)
set Psupplier to (value of field “Supplier”)
end tell
end tell
end tell

error “Database Events got an error: Can’t get record 1 of database "~/Documents/Databases/Parts.db" whose value of field "Number" = 25. Invalid index.” number -1719

Simple stuff; what am I missing?

#2

Database events is useless.
I am using command line to access SQLite database
There are SQLite scripts that connect to SQLite using do shell script “sqlite3 …”

1 Like
#3

Robert is right about Database Events. It doesn’t play well with SQLite databases.

Instead I use SQLite Lib 1.0.0 and SQLite Lib2 1.1.0 from Shane Stanley.

Freeware | Late Night Software

SQLite Lib 1.0.0 and SQLite Lib2 1.1.0

SQLite Lib2 and SQLite Lib are libraries for fast and efficient SQLite programming from AppleScript, based on the well-known open-source FMDB Framework. If you ever need to access SQLite databases, or need a simple database for your scripts, one of these is the answer. Functionally similar, SQLite Lib2 requires macOS 10.11 or later and includes a terminology dictionary, whereas SQLite Lib uses traditional handlers, and works under macOS 10.10 and later. Note that scripts using SQLite Lib and SQLit Lib2 cannot be edited in Script Editor in Mojave or later because of security settings. You need to use Script Debugger. See Catalina Security and Script Libraries for Catalina installation details. (Updated July 17, 2020.)

#4

Thank you will check them out.

#5

Almost got SQLite Lib2 working…

error “The bundle “FMDBAS” couldn’t be loaded.” number -4960 from framework “FMDBAS”

(Ventura 13.3.1)

#6

Reading further and elsewhere, seems I need Script Debugger to get FMDBAS. Why? Seems overkill. Why can’t I simply add/load an FMDBAS library like I just added SQLite Lib2? Where can I get FMDBAS?

And if the only answer is get Script Debugger, I don’t need all that. But I see Script Debugger Lite is free. So, after all this, can I still simply run my Script from the systemwide script menu?

In any event, I cannot find a download for Script Debugger Lite anywhere on the Late Night Software site.

Script Debugger Lite download location please?

#7

Ok, never mind found it. Just let the trial run out and it turns to Lite.

About Script Debugger Lite

#8

Apple’s security rules. You were able to do that when FMDBAS was written, but it’s become progressively more difficult.

#9

Thank you, and for your patience too. I am new to all this so learning. And now I believe it is not possible to run any script containing SQLite Libs code straight from the “systemwide script menu” for these reasons.

So launch them from the Script Debugger, and maybe set that aside while they run?

#10

The solution is FastScripts – it will let you do it.

#11

Thanks re FastScripts.

Now more basic difficulties. My table Picks has field Pick, primary key, unique, type INTEGER.

Simple sequence:
set lastPick to query db BLOTHdb sql string “Select max (Pick) from Picks”
set nextPick to lastPick + 1

…then later

repeat until (lastPick = nextPick)
delay 1
set lastPick to query db BLOTHdb sql string “Select max (Pick) from Picks”
end repeat --This Pick

Background activity ultimately adds a row, so increments Max (Pick); Debugger shows lastPick and nextPick to be the same value (e.g. 494), but the repeat condition is never met so never exits the repeat block.

So I thought perhaps lastPick is fetched as type TEXT, so added “as integer”, which fails with

Can’t make “Select max (Pick) from Picks” into type integer.

Can’t be this hard.

#12

Presumably you just added it on the end, which is telling AppleScript to coerce the item before it – your query string. Use parens:

set lastPick to (query db BLOTHdb sql string “Select max (Pick) from Picks”) as integer