MicroETL makes two SQLite instances available to Excel. The main database is the App database, by default an in-memory instance; the second is a Helper instance, again by default in-memory, which is used to hold logs and to enable SQLite to perform certain activities that would not be possible without two separate database instances. The main commands such as SQL() deal exclusively with the App instance, and for the most part that’s the only database that a microETL user needs to be aware of.
Within the microETL project, VBA routines can gain access to the App instance via the gAppDB global variable (gHelperDB for the Helper instance) while Python routines use the global dictionary element connDict(“App”) or connDict(“xlite”) for the App database (and connDict(“Helper”) for the Helper).
SQLScript (and in-cell formulas) can gain access to the Helper instance via the xLiteSQL(inSQL,outRange,NoHeader,PasteOver,useHelperDB) command, by setting useHelperDB to True. (The SQL() function is a thin-wrapper on this function to handle positional substitution tokens (:1, :2 …), the main functionality,such as the table() preprocessor commands and output to a range, are provided by xLiteSQL.)
These default in-memory databases are blank databases that are instantiated in memory at workbook start-up and any tables or data created during the workbook session are discarded at shutdown. The standard use-case assumes that any data to be saved or fetched will come from Excel or perhaps via external CSVs or corporate database connections as would be the case with a normal Excel workbook, i.e. the main use for SQLite is assumed to be a provider of a SQL layer on top of Excel not a primary data-store.
However, SQLite is a superb data-store, one which is used by 1000s of applications (quite likely many of apps on your smart-phone or iPad use SQLite as their database). Its ACID nature and small foot-print makes it an ideal means of storing client-side information that would otherwise need to be communicated to a “proper” server-side database such as MySQL or SQL Server.
MicroETL supports the use of disk-based databases for both the main App and companion Helper instances via:
- xliteAttachDB(databaseName,alias); this command will attach a database to the App database and assign it an alias. This is a wrapper on the SQLite Attach command http://www.sqlite.org/lang_attach.html. Note: the SQL Attach command can not be used via the SQL() command, but can be used by the “raw” fastSQL() function.
- xLiteLoadUnLoad(filename[,unload]); this function is both useful & dangerous, it will replace the main (App) database’s contents with the contents of the SQLite file provided by the first parameter. Typically the target database is the default empty in-memory (:memory:) database so no harm done; but if the default database was a file-based database, whose contents you wished to preserve, this might not be what you wanted. The second parameter if set to TRUE (FALSE being the default) works in the opposite direction i.e. replaces the specified database’s contents with those of the main (App) database.
- Setting a custom property of xLiteDB to a SQLite filename in a driver workbook, this database will then be used as the default App database. As well as file based databases, the xLiteDB property can be set to “:temp:” or “:memory:”, :temp: is like :memory: but will use RAM & a temporary file while :memory: only uses RAM. The driver workbook is the first workbook to initiate microETL, so be careful of multiple workbooks with different App “expectations” being accessed in the same Excel instance.
- The similar custom property of xLiteHelperDB controls what Helper (i.e. Logging) database to use.
- Another option is to use the custom properties xLiteAttachDB & xLiteAttachDBAlias to auto-attach a database at startup
- Yet another method is provided by the custom properties xLiteDBSeed and xLiteHelperDBSeed. Does the the equivalent of xLiteLoadUnLoad(databaseName,FALSE) at startup. There’s an xLiteClose() function which release’s resources held by the App & Helper databases and if they were seeded via xLiteDBSeed or XLiteHelperDBSeed, will save back any changes to the seed databases.
As per Michael’s suggestions below, I’ve add the ability to attach an external database into an “aliased” :memory: database, see http://blog.gobansaor.com/2011/05/20/attach-a-sqlite-database-into-excels-memory-via-microetl/