SQLite with Linx

SQLite is a great alternative if you need a quick database. It’s surprisingly capable and will run on most systems. (Runs well on Windows for ARM).

The SQL itself is also quite similar to what we’re used to in MSSQL and MySQL.

To set it up, first install the ODBC drivers for SQLite. There are a couple to choose from, but I use this: SQLite ODBC Driver . For Windows 11 ARM I downloaded the http://www.ch-werner.de/sqliteodbc/sqliteodbc_w64.exe file.

Next, download and install SQLite Studio to create your DB: https://sqlitestudio.pl/

Finally, extract everything into a folder. I used C:\SQLite

I also created a folder in there for my Databases: C:\SQLite\DB

The connection string in Linx then just points to my database that I created:

Driver={SQLite3 ODBC Driver};Database=C:\SQLite\DB\test.db;

One important note I discovered is that the ODBC driver is not completely happy with Decimal types. So if you’ve got a Decimal type in your SQL statement, you could get an exception with no error message. To get past this, simply add an empty string to your decimal and SQLite will accept it. For example the below SQL in Linx:

insert into FinanceTable (
	Name,
	CashValue)
	
	values
	(@{NameOfTransaction},
	@{DecimalValueOfTransaction+""})

Note the +"" in the SQL.