I work a lot with SQLite databases and use FileMaker to a lesser extent. In earlier releases, FileMaker's Execute SQL functions could NOT access SQLite. Is this still the case?
The "Execute SQL" script step works with any ODBC compliant database. So if you have an ODBC driver for SQLite then it would work. Other approaches is to expose the SQLite database through any kind of API (REST?) and interact with it that way.
Mac ODBC: MySQL, PostgreSQL and SQLite connectivity for Mac OS X by Actual Technologies
This ODBC driver lists SQLite as supported, but note that it is not listed as an ESS source, so you may by limited to only Execute SQL functions, if that will suffice.
Which ODBC data sources/drivers are supported with External SQL Data Sources? | FileMaker
Execute SQL (script step) for: INSERT, UPDATE, & DELETE
Import (script step) for: SELECT
These work well.
ExecuteSQL() - the function - works with Native FileMaker tables (including imports from xDBC sources) or "approved" ESS table references.
Execute SQL - the script step - is for INSERT, UPDATE, & DELETE on any external source that has a valid ODBC/JDBC driver. (NOTE: this is not the 'live connection' with ESS.)
These are two different things!
SQLite is not a database like MS SQL or Oracle. It's not based on an engine that is a server. See SQLite Home Page
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world.
See also About SQLite
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.
More than often, SQLite is used in embedded software were some form of database is needed to store data instead of text files. It lets you use SQL queries to get and set data. The in-process-library means that SQLite is in fact code that you add to a project a compile to an application.
As an example, SQLite is available for apps running on iOS.
What kind of projects are you using SQLite for ?
See (and vote!) this idea:
Connection to SQL-lite (like Bento had)
edited: thanks, planteg!
planteg wrote: Hi SQLite is not a database like MS SQL or Oracle. It's not based on an engine that is a server.
SQLite is not a database like MS SQL or Oracle. It's not based on an engine that is a server.
In and by itself that is not a problem. There are odbc drives for instance for Excel files and text files, those are server-less too.
I often use SQLite for lightweight web services that need to receive data and store it temporarily until my FM routines can get around to grabbing it and cleaning out the SQLite records. I use REST calls to my web services for that though, not xDBC.
your link opens the same question under a different number .
You can use MBS FileMaker Plugin and the SQL functions.
The plugin includes SQLite library for all platforms including the SEE option for encryption.
You can connect to a database locally, run queries, import data or run queries to export data.
Monkeybread Software - MBS FileMaker Plugin: SQL Connection
MBS Blog - Tip of the day: Connect to MySQL and run a query
to connect to SQLite, please pass native file path to connect function and type="SQLite".
SQLite IS in fact a database. I believe what you mean is it is not a Client/Server database. It does have an engine, it's just embedded in the application so no separate server is necessary. And it is basically single-user, although multiple users can access (and Update) the file at the same time, it doesn't have record level locking, it locks the entire file making it a really poor choice for multi user databases.
All that being said, for many of the projects I work on, it is the PERFECT choice.
Hi Christian, remember me from the Xojo forum. I have your Xojo plugins. Looks like I may be getting your FM plug-ins also.
I agree with you, I said it's not like MS SQL in that there is no server part.
If you need SQLite as server, check the CubeSQL software:
cubeSQL » high performance DBMS based on sqlite database
Two approaches I would consider:
(1) As Wim suggested, create a simple REST service that FMP communicates with (using INSERT FROM URL script step). Then, once you have the data in the REST service, use the JDBC driver to do "CRUD" with SQL Lite. If you use SpringBoot, for example, you don't even need to write CRUD code! It's all ORM magic. SpringBoot still lets you use SQL, if you want, but it's thorough an abstracted JDBC object. The good news: even using JDBC (instead of a ORM "save" method) you still don't have to write any voluminous JDBC boilerplate logic.
(2) You could write a standalone Java program that would interact with both FileMaker and SQL Lite using each DB's JDBC drivers. You can also generically map table and field names (using DBMetadata classes) so you don't have to hard-code anything other than field FMP <--> SQL Lite field types. I've done that generic mapping with MySQL and FMP and other DBs.
Connect To The SQLite Database Using SQLite JDBC Driver
wow, thanks for the info !
In regards to some previous comments, although its quite true SQLite is not a database server, recent versions of SQLite do support multiple processes accessing the same database file (probably need to use it in serialized mode to do this). So it could, for instance, be used as middleware between a web app and FileMaker.
The bBox plug-in also includes SQLite functions. A trivial meta example would be (assuming function calls are in a Set Variable):
$ref = bBox_SQLiteOpen ("/tmp/sqlite.db")
$result = bBox_SQLiteExec ($ref, "SELECT * FROM scratchTable")
SQLite indeed supports multiple processes opening the same database file.
That works fine and when one writes, the whole file is locked for all others.
Retrieving data ...