Can an Ignition Project connect to a SQLite database? If so, how?
SQLite isn’t packaged with Ignition as an exposed driver, so you’ll have to use the “Create new JDBC Driver…” procedure with a downloaded driver package. I believe it’s this one.
Thanks! I was able to get the status of the connection to “Valid”. However, when I can’t get the transaction group to write to the database. So I am wondering if the problem lies with the Translator, and needing something custom.
Did you ever resolve this issue? I was able to create a connection to sqlite that was Valid, but I cannot query the db I get an Error:SQL error or missing database (no such table: BASIC_SCHEDULES
even though I know that table exists.
It will be usefull if SQLITE connector was directly available for some user’s db in Ignition 7.9.X or 8.0
I was also able to get a valid connection, and it creates the sqlite file, but I wasn’t able to write data to it. Was anyone able to get SQLite working?
1 Like
Hello. Could You say please, how did You get the valid connection?
I have SQLite db file laying on the one Linux system, and Ignition running on the another Linux system, how should i establish connection between them?
Please help, examples would be appreciated.
SQLite is not designed for use over a network. As in, the developers created it to be local and have not intention of adding network support. The issue can be forced by using your OS’s network file sharing features, but that is buggy and slow and can interfere with other access to that DB.
If you need network access, you really need a full client-server DB implementation. If you need it to be free, use PostgreSQL, MariaDB, or MySQL. (In that order, in my not-so-humble opinion.)
2 Likes
SQLite is great, but for specific purposes. We use SQLite in Ignition to pre-load data down to equipment… We update the connection string to a new DB file and initialize it, then we load data into the SQLite DB (local file on the GW), then we close the connection and download the resulting DB to a client machine where that system now uses the SQLite DB file.
Locally SQLite is great, but not a LAN DB system. I think of it more as a relational ini file 
Anyway, once you create the SQLite connection, you need to init the DB file the first time and create your tables just like any other DB. The first time we open the SQLite file we also set some other preferences in SQLite related to indexing and keys, remember this is a regular DB just like a server, you want to config it for what your needs are.
2 Likes
Is there anyway you can give me a good example of this? I need pretty much the same thing. I need to be able to sync a remote database down to the local machine (only a single 3 or 4 column table) and then use that data on the local machine (as it will be outside the network coverage area).
Here is how I use it. This only works for a low volume of “hits” as it can only serve one request at a time.
Prereq: There is a SQLite DB connection on the gateway that remains disabled.
I use WebDev to request the SQLite DB file to an external system. The system sends a get request to WebDev.
As part of the request it calls a function with some code like below to generate the DB.
# Create a temp file
tempDB = system.file.getTempFile('db')
# Enable and the DB
system.db.setDatasourceConnectURL('sqlite', 'jdbc:sqlite:%s' % tempDB)
system.db.setDatasourceEnabled('sqlite', 1)
# Setup the DB, create tables, add indexes, etc...
sql = """
DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
`ID` NUMERIC,
`Result` REAL
);
"""
system.db.runUpdateQuery(sql, 'sqlite')
#
# Insert stuff!!
#
# Remove the connection to the DB so we can download it
system.db.setDatasourceEnabled('sqlite', 0)
# Return the server path to the DB file
return tempDB
Once the tempDB returns to the WebDev script, read the file and send it to the client.
# Strem the file to the client if all went well
return {'file': tempDB, 'contentType':'application/x-sqlite3'}
2 Likes