In my previous post I described the various methods of accessing SQLite databases from within Excel using microETL. Via comments on the post, Michael Römer suggested a change to how microETL loads into memory an external SQLite database (not only suggested, but also provided the C code changes to enable the change; thanks Michael).
The existing xLiteLoadUnLoad(filename[,unload]) function loads a SQLite file into “main” i.e. the primary database (which is usually a :memory: db) overwriting any existing data. Michael’s suggestion was to allow loading into another in-memory database with a different alias; thus keeping the main database intact but allowing the benefits of in-memory access to the externally attached database. This feature has now been added.
I’ve kept the existing xLiteLoadUnLoad as is, but added a new optional argument to the xLiteAttachDB function so.
- xliteAttachDB(databaseName,alias) becomes xliteAttachDB(databaseName,alias,[loadInMemory=False]). The optional loadInMemory argument defaults to FALSE, so acts like the old version (i.e. issues a standard SQLite Attach statement). But if set to TRUE; the function will first Attach a “:memory: database” named as the alias, then will load the external database file into that in-memory database. Once this happens the on-disk database is not referenced, so any changes will not be reflected back to disk. To enable changes to be persisted to disk, I’ve added another new function…
- xLiteDBSaveAs(alias,outDatabaseFile) will save a copy of the database named alias (with could be “main” if you wished to backup the default in-memory database) to the file outDatabaseFile. I’ve also added a …
- xLiteDetachDB(alias) to issue a SQLite DETACH statement. You might ask why not simply use the SQL() function to issue DETACH (or indeed ATTACH) statements? Statements such as ATTACH/DETACH cannot be issued by the SQL() functions as its pre-processor (for table() functionality) wraps SQL statements in a SAVEPOINT (nested SQL transaction). You can however use the fastSQL() or xliteRawSQL() functions to issue such commands.
To download the latest version see the http://www.gobansaor.com/microetl page.
For another method of loading SQLite databases within Excel/VBA see my new .NET-centric micro ETL tool http://blog.gobansaor.com/category/hammer/