Sunday, September 29, 2024

SQLite with Gliimly


This example will create a service that inserts key and value into SQLite table. It's tested from command line.

Create a directory and then switch to it:
mkdir sqlite
cd sqlite

Setup SQLite database in file "mydata.db":
echo 'drop table if exists key_value; 
create table if not exists key_value (key varchar(50) primary key, value varchar(100));' | sqlite3 mydata.db

Create configuration file "mydb" that describes the SQLite database "mydata.db":
echo "$(pwd)/mydata.db"> mydb

Create the application:
sudo mgrg -i -u $(whoami) sqlite

Create file insert.gliim:
vim insert.gliim

Copy and paste to it (note use of database configuration "mydb" in @mydb):
 %% /insert public
     get-param key
     get-param value
     run-query @mydb = "insert into key_value(key,value) values ('%s', '%s')" input key, value error err affected-rows aff_rows no-loop
     @Error <<p-out err>>, affected rows <<p-num aff_rows>>
 %%

Compile the application - we specify that file "mydb" is describing SQLite database:
gg -q --db=sqlite:mydb

Run the application by executing this service from command line. Note passing the input parameters "key" and "value" to it:
gg -r --req="/insert/key=1/value=one" --exec --silent-header

The output is:
Error 0, affected rows 1

Verify data inserted:
echo -e ".headers off\n.mode line\nselect key "Key", value "Value" from key_value"|sqlite3 mydata.db

The result:
Key = 1
Value = one