OpenVMS Notes: SQLite
- The information presented here is intended for educational use by qualified OpenVMS technologists.
- The information presented here is provided free of charge, as-is, with no warranty of any kind.
Relational Database Caveat: (please read all items in this disclaimer before continuing)
- Oracle-Rdb (OpenVMS) or Oracle-DB (all platforms) represent the BMW + Mercedes Benz of the enterprise database industry. You will not find anything better.
- However, most charities, students, universities and small businesses can only afford Volkswagens and this is where MySQL and MariaDB are found.
- If your organization has any kind of annual IS/IT budget then I suggest you stick with Oracle-Rdb (OpenVMS) or Oracle-DB (all platforms)
- If you do not have the skills to fix problems yourself, or cannot tolerate problems lasting days-to-weeks, then you should never depend upon open source software without some kind of support contract from a third party provider. Why? Open source developers do not worry about things like: "who answers the call when Joe is sick", "Joe is attending to family matters", "Joe is on vacation", or "Joe just perished in a traffic accident". People who sell support contracts do worry about such things. On top of this, I have seen boundary issues in open source software (like gSOAP) go unanswered for years. What "you may think is an important problem" might be ignored by the rest of the user community.
- If you want an SQL-compliant database but think a standalone storage engine is overkill then you should consider SQLite which provides library header files for C/C++
SQLite (General Info)
- Sometimes adding support for full-blown multi-user SQL-compliant storage engine (like MySQL or MariaDB) to your application will be overkill. If your program is written in C or C++ (or you don't mind using a C-wrapper with another language) then you might consider SQLite. Just as the %include <stdio.h> directive provides C/C++ programs with general i/o functions like printf(), SQLite provides a directive to enable SQL i/o functions.
- Adding SQLite to your C/C++ apps on OpenVMS is an order-of-magnitude easier than adding ISAM support via RMS
- caveat: although RMS is built into DEC-COBOL and DEC-BASIC, other so-called DEC languages require the developer to jump through hoops to add RMS support
- SQLite is available for many other environments like: Visual BASIC.NET, Run BASIC, Python, Ruby, and C# to only name a few of many.
- Caveat: despite all the internet chatter about SQLite, it is NOT ever going to replace MySQL or MariaDB. Why? The locking schemes required for multi-user access are too primitive (at least this is true for SQLite3; I do not know what was being considered for SQLite4 but that project was cancelled in 2017 and the lessons learned where rolled into SQLite3 according to most things I have read on the net.
- quote: SQLite is the Most Widely Deployed and Used Database Engine
Links:
- SQLite
- SQLite for OpenVMS
- https://sourceforge.net/projects/vms-ports/files/ then click SQLite3 to download
- https://sourceforge.net/p/vms-ports/discussion/portingprojects/thread/635b3b7e/
- SQLite Workbench (a PC-based GUI similar to MySQL Workbench)
SQLite Installation (on OpenVMS)
Notes:- unzip (a third party app) must be installed to unzip on OpenVMS
- a C or C++ compiler must be installed to do the build
- install in a local personal folder if you are only going to experiment with this. Otherwise, you might want to install this in a new folder under sys$library
(Army Instructions) Legend:
<sr> system response
<ur> user response
{yada} meta data describing some action
-------------------------------------------------------------------------
<sr> $ ! my DCL prompt
<ur> cre/dir [._sqlite] ! create a folder to play with sqlite3
<sr> $
<ur> unzip sqlite3_vms_012.zip -d [._sqlite] ! unzip into here (or wherever you want)
<sr> { ~ 185 files are created in 3 directories }
$
<ur> set def [._sqlite] ! move into folder where I unzipped sqlite3
<sr> $
<ur> @build_all.com ! does what it says
<sr> { hundreds of lines are displayed }
$
<ur> @setup ! define a few logical names + DCL symbols
<sr> $
SQLite CLI Demos
Notes:
- The CLI (Command Line Interpreter) will allow you to create/access/maintain SQLite tables on your system via interactive
command prompts. To the best of my knowledge, everything you do from the CLI is also possible from C/C++ but you should still
learn how to use the CLI so that you do can solve problems without needing to write maintenance programs.
- The CLI will also prove to you that SQLite was built and installed properly on your system (this will be your first thought when a C/C++ program doesn't work properly)
CLI-Demo-01 (First use after initial build/install)
note: the Green text in this first demo is only seen in the OpenVMS version of SQLite when process logical name SQLITE3_VMS_OPTIONS is non-blank. You will also see it in the demo "C" programs below ------------------------------------------------------------------------- <sr> $ <ur> show log/process sqlite3_vms_options <sr> "SQLITE3_VMS_OPTIONS" = "TRACE:0" (LNM$PROCESS_TABLE) = "DEFAULT_VFS:OPENVMS" = "LOCK_METHOD:0" = "PERF_TIMER:1" $ ------------------------------------------------------------------------- <ur> sqlite3 ! fire up the CLI <sr> Initialization option - TRACE : 0 ! Initialization option - DEFAULT_VFS : OPENVMS Initialization option - LOCK_METHOD : 0 Initialization option - PERF_TIMER : 1 OpenVMS VFS module initializing... --RMS native implementation 1.3.2, override: getcwd,xFullPathname SQLite version 3.14.1 2016-08-11 18:53:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> ! the CLI prompt <ur> .help ! display sqlite help <sr> { ~ 75 lines of help are displayed } sqlite> ! the CLI prompt <ur> .databases ! view connected databases <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main sqlite> ! <ur> .tables ! any tables here? <sr> sqlite> ! no... <ur> .schema sqlite_master ! but there is a master table <sr> CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text ); sqlite> ! <ur> .quit ! quit the CLI (.exit is a synonym) <sr> ELAPSED: 0 00:04:31.08 CPU: 0:00:00.01 BUFIO: 95 DIRIO: 6 FAULTS: 14 $ ! my DCL prompt
CLI-Demo-02 (create a database then add two tables)
<sr> $ ! my DCL prompt <ur> sqlite3 neil-test-001.db ! create (if doesn't exit) a test database <sr> Initialization option - TRACE : 0 Initialization option - DEFAULT_VFS : OPENVMS Initialization option - LOCK_METHOD : 0 Initialization option - PERF_TIMER : 1 OpenVMS VFS module initializing... --RMS native implementation 1.3.2, override: getcwd,xFullPathname SQLite version 3.14.1 2016-08-11 18:53:32 Enter ".help" for usage hints. sqlite> ! <ur> .databases ! need info about databases <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main /csmis$user3/admcsm/neil/_sqlite/neil-test-001.db sqlite> <ur> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); <sr> sqlite> <ur> CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); <sr> sqlite> <ur> .tables ! show tables (should be two) <sr> COMPANY DEPARTMENT ! (yep) sqlite> ! <ur> .quit ! <sr> ELAPSED: 0 00:05:19.34 CPU: 0:00:00.01 BUFIO: 139 DIRIO: 63 FAULTS: 247 $ ! my DCL prompt
CLI-Demo-03 (reopen database)
at this point you can reopen the an existing database three ways: Method-01 <sr> $ ! <ur> sqlite3 neil-test-001.db ! use filename during first command <sr> { 10-lines of text } sqlite> <ur> .databases <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main /csmis$user3/admcsm/neil/_sqlite/neil-test-001.db sqlite> ! <ur> .tables ! <sr> COMPANY DEPARTMENT ! sqlite> ! Method-02 <sr> $ <ur> sqlite3 ! open the CLI without a filename <sr> { 10-lines of text } sqlite> ! <ur> .open neil-test-001.db ! this produces the same result as above <sr> sqlite> ! <ur> .databases ! <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main /csmis$user3/admcsm/neil/_sqlite/neil-test-001.db sqlite> ! <ur> .tables ! <sr> COMPANY DEPARTMENT ! sqlite> ! Method-03 <sr> $ ! <ur> sqlite3 ! open the CLI without a filename <sr> { 10-lines of text } sqlite> ! <ur> attach 'neil-test-001.db' as 'neil-01'; ! this produces a different result <sr> sqlite> ! <ur> .databases ! <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main 2 neil-01 /csmis$user3/admcsm/neil/_sqlite/neil-test-001.db sqlite> ! <ur> .tables ! <sr< neil-01.COMPANY neil-01.DEPARTMENT ! notice the alias prefix? sqlite> !
CLI-Demo-04 (attach a database then drop a table)
<sr> $ ! my DCL prompt <ur> sqlite3 <sr> Initialization option - TRACE : 0 Initialization option - DEFAULT_VFS : OPENVMS Initialization option - LOCK_METHOD : 0 Initialization option - PERF_TIMER : 1 OpenVMS VFS module initializing... --RMS native implementation 1.3.2, override: getcwd,xFullPathname SQLite version 3.14.1 2016-08-11 18:53:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> <ur> ATTACH DATABASE 'neil-test-001.db' As 'neil01'; ! attach to database with alias <sr> sqlite> <ur> .databases ! see attached databases <sr> seq name file --- --------------- ---------------------------------------------------------- 0 main 2 neil01 /csmis$user3/admcsm/neil/_sqlite/neil-test-001.db sqlite> <ur> .tables ! show tables <sr> neil01.COMPANY neil01.DEPARTMENT ! sqlite> ! <ur> drop table DEPARTMENT; ! drop table DEPARTMENT <sr> sqlite> ! <ur> detach neil01; ! <sr> sqlite> ! <ur> .exit ! <sr> ELAPSED: 0 00:11:53.70 CPU: 0:00:00.04 BUFIO: 97 DIRIO: 56 FAULTS: 253 $ !
CLI-Demo-05 (insert some data)
<sr> $ <ur> sqlite3 <sr> Initialization option - TRACE : 0 Initialization option - DEFAULT_VFS : OPENVMS Initialization option - LOCK_METHOD : 0 Initialization option - PERF_TIMER : 1 OpenVMS VFS module initializing... --RMS native implementation 1.3.2, override: getcwd,xFullPathname SQLite version 3.14.1 2016-08-11 18:53:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> <ur> ATTACH DATABASE 'neil-test-001.db' As 'neil01'; <sr> sqlite> ----------------------------------------------------------- type-1 inserts (formal) ----------------------------------------------------------- <ur> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'Finland', 20000.00 ); <sr> sqlite> <ur> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Sweden', 15000.00 ); <sr> sqlite> ----------------------------------------------------------- type-2 inserts (informal) ----------------------------------------------------------- <ur> INSERT INTO COMPANY VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); <sr> sqlite> <ur> INSERT INTO COMPANY VALUES (4, 'Mark', 25, 'Canada', 65000.00 ); <sr> sqlite> ----------------------------------------------------------- fetch data in natural order ----------------------------------------------------------- <ur> select * from COMPANY; <sr> 1|Paul|32|Finland|20000.0 2|Allen|25|Sweden|15000.0 3|Teddy|23|Norway|20000.0 4|Mark|25|Canada|65000.0 sqlite> ----------------------------------------------------------- fetch data collated by column "name" ----------------------------------------------------------- <ur> select * from COMPANY order by name; <sr> 2|Allen|25|Sweden|15000.0 4|Mark|25|Canada|65000.0 1|Paul|32|Finland|20000.0 3|Teddy|23|Norway|20000.0 sqlite>
caveat: informal inserts from C/C++ will only work as the original column order does not change.
CLI-Demo-06 (oddities with .schema)
Note: these oddities are not peculiar to the OpenVMS version of SQLite. I saw them in the Windows version as well
Method-01 (works properly) <sr> $ ! <ur> sqlite3 neil-test-001.db ! use filename during first command <sr> { 10-lines of text } ! sqlite> ! <ur> .schema %z% ! any tables containing a 'z'? <sr> sqlite> ! no <ur> .schema %c% ! any tables containing a 'c'? <sr> CREATE TABLE COMPANY( ! yes ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); sqlite> Method-02 (does not work properly) <sr> $ ! <ur> sqlite3 ! use filename during first command <sr> { 10-lines of text } ! sqlite> ! <ur> ATTACH DATABASE 'neil-test-001.db' As 'neil01'; ! <sr> sqlite> ! <ur> .schema %c% ! any tables containing a 'c'? <sr> sqlite> ! no <ur> .schema ! display all table schemas <sr> sqlite> ! none
Caveat: so it appears that .schema only works on the main database
CLI-Demo-07 (adding an index)
Caveat: indexes are not required for very small databases but they can really help speed up the collated displaying of large databases (or inserting into large databases with a constraint of UNIQUE)
<sr> $ !
<ur> sqlite3 neil-test-001.db ! use filename during first command
<sr> { 10-lines of text }
sqlite>
<ur> .indexes ! any indexes here?
<sr> sqlite> ! nope
<ur> create index nameidx on COMPANY (name); !
<sr> sqlite> !
<ur> .indexes ! and indexes here?
<sr> nameidx ! yep, one.
sqlite> !
SQLite Programming Demos
Programming Caveats:
SQLite
- the official SQLite documentation states you must always invoke sqlite3_close() even
if the previous call to sqlite3_open() failed
references:- https://www.sqlite.org/quickstart.html (contains a very small c demo)
- https://www.sqlite.org/cintro.html
- https://www.sqlite.org/c3ref/open.html
- https://www.sqlite.org/c3ref/close.html
- there are three versions of open:
- sqlite3_open()
- sqlite3_open16()
- sqlite3_open_v2()
- there are two versions of close:
- sqlite3_close()
- sqlite3_close_v2()
- rule #1 above pertains to all variations of open and all the variations of close.
- function sqlite3_exec() is a wrapper function containing several others. For anything other than single-application use you may need to call the components directly.
VMS/OpenVMS
- All so-called DEC Languages will up-case external symbol names by default. To turn off this behavior in C/C++ you must compile with command line switch "/names=as_is"
- In the Unix/Linux world where C/C++ are king, exiting a program with "0" means "all is well" while any other value indicates "something has gone wrong.
- In VMS and OpenVMS all the "program exit codes" are expected to handle the lowest 3-bits like so:
DCL Severity Bits Decimal Binary prefix meaning 0 000 -w- warning (program exited without a code) 1 001 -s- success 2 010 -e- error 3 011 -i- informational (success with information) 4 100 -f- fatal 5 101 -?- undefined 6 110 -?- undefined 7 111 -?- undefined 8 100 -w- warning (whole 8-code sequence repeats) - Since the majority of VMS/OpenVMS programs exit to DCL (the command shell associated with this OS), these are sometimes referred to as DCL codes
- Bits above the severity bits are referred to as identity and facility. Here are examples of the first four 8-code exits
Decimal Value Meaning 0 %NONAME-W-NOMSG, Message number 00000000 8 %SYSTEM-W-ACCVIO, access violation, reason mask=!XB, virtual address=!XH, PC=!XH, PS=!XL 16 %SYSTEM-W-BADPARAM, bad parameter value 24 %SYSTEM-W-EXQUOTA, process quota exceeded
Pgm-Demo-01 (open a database)
//========================================================================
// title : sqlite-pgm-demo-01.c
// author : Neil Rieck
// created : 2016-10-31
// notes : 1) this demo will create a database if it does not exist
// : 2) official documentation instructs to always call sqlite3_close()
// even if the associated sqlite3_open() failed
// ref: https://www.sqlite.org/quickstart.html
// ref: https://www.sqlite.org/cintro.html
// ref: https://www.sqlite.org/c3ref/open.html
// platform: OpenVMS-8.4 on Itanium
// build : $cc sqlite-pgm-demo-01.c /names=as_is /include=SQLITE3_INCLUDE
// : $link sqlite-pgm-demo-01, SQLITE3_INCLUDE:sqlite3_build.olb/lib/sel
// ver who when what
// 1 NSR 161031 1. original effort
// NSR 161113 2. updated the build instructions
//========================================================================
#include <stdio.h> // for general i/o in c/c++
#include <sqlite3.h> // for sqlite i/o in c/c++
//
const char *db_name = "NEIL-TEST-001.DB"; // program and db must be in same directory
//
//========================================================================
// main()
//========================================================================
int main(int argc, char* argv[])
{
sqlite3 *db = 0;
char *zErrMsg = 0;
int rc;
int dcl;
//
// main
//
printf("-i-program start: %s\n",argv[0]);
//
// open the database (one will be created if it doesn't yet exist)
//
printf("-i-opening database: %s\n",db_name); //
rc = sqlite3_open(db_name, &db); //
if( rc ){
fprintf(stderr, "-e-can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db); // docs say to always do this
// return(rc);
// which is worse? exiting in the middle of code or using goto?
goto fini;
}else{
fprintf(stderr, "-i-database opened successfully\n");
}
//
// gracefully close the database
//
printf("-i-closing database\n");
sqlite3_close(db);
//
// adios
//
fini:
if (rc==0){ // if SQLite success then
dcl = 1; // DCL-success
}else{
dcl = 2; // DCL-error
}
printf("-i-program exiting with DCL status: %d\n",dcl);
return (dcl); // pass exit code to DCL
}
Pgm-Demo-02 (read from a database)
//========================================================================
// title : sqlite-pgm-demo-02.c
// author : Neil Rieck
// created : 2016-10-31
// notes : this demo will fetch data from a database
// platform : OpenVMS-8.4 on Itanium
// vms-build: $cc sqlite-pgm-demo-02.c /names=as_is /include=SQLITE3_INCLUDE
// : $link sqlite-pgm-demo-02, SQLITE3_INCLUDE:sqlite3_build.olb/lib/sel
// ver who when what
// 1 NSR 161031 1. original effort
// NSR 161113 2. updated the build instructions
//========================================================================
#include <stdio.h> // for general i/o in c/c++
#include <sqlite3.h> // for sqlite i/o in c/c++
//
const char *db_name = "NEIL-TEST-001.DB"; // program and db must be in same directory
//
//========================================================================
// callback (executed whenever requested data is returned)
//========================================================================
static int callback(void *cbk_msg, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)cbk_msg);
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0; // signal all is well;
}
//========================================================================
// main
//========================================================================
int main(int argc, char* argv[])
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
int dcl;
char sql[255];
const char *cbk_msg = "in Callback function";
//
// main
//
printf("-i-program start: %s\n",argv[0]);
//
// Open database
//
printf("-i-opening database\n");
rc = sqlite3_open(db_name, &db);
if( rc ){
fprintf(stderr, "-e-can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db); // docs say to always do this
// return(rc);
// which is worse? exiting in the middle of code or using goto?
goto fini;
}else{
fprintf(stderr, "-i-database successfully\n");
}
//
// Create SQL statement
//
sprintf(sql,"SELECT * FROM COMPANY ORDER BY NAME");
//
// Execute SQL statement
//
printf("-i-executing sql command: %s\n", sql);
rc = sqlite3_exec(db, sql, callback, (void*)cbk_msg, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "-e-SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "-i-SQL operation was successful\n");
}
printf("-i-closing database\n");
sqlite3_close(db);
//
// adios
//
fini:
if (rc==0){
dcl = 1; // DCL-success
}else{
dcl = 2; // DCL-error
}
printf("-i-program exiting with DCL status: %d\n",dcl);
return (dcl); // pass exit code to DCL
}
Pgm-Demo-03 (dealing with lock issues - part 1)
=== Create a new database for lock-testing purposes ===
<sr> $
<ur> sqlite3 NEIL-TEST-003.DB
<sr> sqlite3>
<ur> create table yada (testdata varchar(20));
<sr> sqlite>
<ur> create index idx_testdata on yada(testdata);
<sr> sqlite>
<ur> .exit
<sr> $
Note: run this program simultaneously from two interactive sessions.
- The first session will run properly.
- The second session will exit with "-e-error: 14, unable to open database file"
//========================================================================
// title : sqlite-pgm-demo-03.c (test database locking)
// author : Neil Rieck
// created : 2016-11-19
// notes : 1) this demo can either read or write the database
// : 2) run it simultaneously from two or more processes to test locking
// platform : OpenVMS-8.4 on Itanium
// vms-build: $cc sqlite-pgm-demo-03.c /names=as_is /include=SQLITE3_INCLUDE
// : $link sqlite-pgm-demo-03, SQLITE3_INCLUDE:sqlite3_build.olb/lib/sel
// ver who when what
// 1 NSR 161031 1. original effort (only writes the database)
// NSR 161113 2. updated the build instructions
// NSR 161122 3. added a second routine to just read the database
//========================================================================
#include <stdio.h> // for general i/o in c/c++
#include <sqlite3.h> // for sqlite i/o in c/c++
//
// declaring global variables in C is usually considered bad form
// but this is just a demo :-)
//
sqlite3 *db = 0;
char *zErrMsg = 0;
const char *db_name = "NEIL-TEST-003.DB"; // program and db must be in same directory
//
//===================================================================
// callback (executed whenever requested data is returned)
//===================================================================
static int callback(void *cbk_msg, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)cbk_msg);
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0; // signal all is well here
}
//===================================================================
// read_db
//===================================================================
int read_db(){
int rc;
char sql[255];
const char *cbk_msg = "Callback triggered from read_db()";
//
// Open database
//
printf("-i-opening database for read\n"); //
rc = sqlite3_open(db_name, &db); //
if( rc ){ //
fprintf(stderr, "-e-error: %ld, %s\n",rc, sqlite3_errmsg(db)); //
sqlite3_close(db); // docs say to always do this
// return(rc);
// which is worse? exiting in the middle of code or using goto?
goto fini;
}else{
fprintf(stderr, "Opened database successfully\n");
}
//
// Create SQL statement
//
sprintf(sql,"select rowid,testdata from YADA order by testdata");
//
// Execute SQL statement
//
printf("-i-executing sql command\n");
rc = sqlite3_exec(db, sql, callback, (void*)cbk_msg, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "-e-SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "-i-SQL operation was successful\n");
}
printf("-i-closing database\n");
sqlite3_close(db);
//
fini:;
return(rc);
}
//===================================================================
// write_db
//===================================================================
int write_db(){
int rc;
char pfx[10];
char sql[255];
const char *cbk_msg = "Callback triggered from write_db()";
//
printf("enter a single data prefix character (a-z,A-Z) ");
fgets(pfx, sizeof(pfx), stdin);
if (((pfx[0]>='A') && (pfx[0]<='Z')) || ((pfx[0]>='a') && (pfx[0]<='z'))){
}else{
pfx[0] = 'a';
}
printf("-i-using data prefix: %c\n",pfx[0]);
//
// Open database
//
printf("-i-opening database for modify\n"); //
rc = sqlite3_open(db_name, &db); //
if( rc ){ //
fprintf(stderr, "-e-error: %ld, %s\n",rc, sqlite3_errmsg(db)); //
return(rc); //
}else{
fprintf(stderr, "Opened database successfully\n");
}
for (int rec=0;rec<=99;rec++){ //
//
// Create SQL statement
//
char fakedata[20]; //
if (rec==0){
sprintf(sql, "pragma locking_mode;"); // first time
}else{
sprintf(fakedata, "%c%09d", pfx[0], rec); //
sprintf(sql, "%s%s%s",
"insert into YADA (testdata) values ('",
fakedata,
"');" );
}
//
// Execute SQL statement
//
printf("-i-executing sql command: %s\n",sql);
rc = sqlite3_exec(db, sql, callback, (void*)cbk_msg, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
break;
}else{
fprintf(stdout, "Operation done successfully\n");
}
}
printf("-i-closing database\n");
sqlite3_close(db);
return(rc);
}
//===================================================================
// main
//===================================================================
int main(int argc, char* argv[])
{
int rc;
int dcl;
char choice[BUFSIZ];
//
// main code
//
printf("-i-program start: %s\n",argv[0]);
printf("-?-Action? (r/w/q) ");
fgets(choice,sizeof(choice),stdin);
switch(choice[0]){
case 'r':
case 'R':
rc = read_db();
break;
case 'w':
case 'W':
rc = write_db();
break;
default:
rc = 0;
break;
}
//
// adios
//
if (rc==0){
dcl = 1; // DCL-success
}else{
dcl = 2; // DCL-error
}
printf("-i-program exiting with DCL status: %d\n",dcl);
return (dcl); // pass exit code to DCL
}
Pgm-Demo-04 (dealing with lock issues - part 2)
Notes:
- with this version I am using alternate (v2) open statements
- now both processes will be able to open the same db file without an error
- however, the first action (either "select" or "insert") on program #1 will result in a database lock which will cause the
second action (either "select" or "insert") on program #2 to fail with an error
- at this point we need some additional code to wait for the lock to be released
- this may require replacing wrapper routine sqlite3_exec() with:
- sqlite3_prepare (or sqlite3_prepare_v2)
- sqlite3_step
- sqlite3_finalize
then calling sqlite3_unlock_notify from prepare or step
- for some reason I don't yet understand, the OpenVMS version of the code below behaves slightly differently than the Windows-10 version of the program which I built using CODEBLOCKS-16.1
//========================================================================
// title : sqlite-pgm-demo-04.c (test database locking)
// author : Neil Rieck
// created : 2016-11-19
// notes : 1) this demo can either read or write the database
// : 2) run it simultaneously from two or more processes to test locking
// : 3) reference: http://www.sqlite.org/lockingv3.html
// platform : OpenVMS-8.4 on Itanium
// vms-build: $cc sqlite-pgm-demo-04.c /names=as_is /include=SQLITE3_INCLUDE
// : $link sqlite-pgm-demo-04, SQLITE3_INCLUDE:sqlite3_build.olb/lib/sel
// ver who when what
// 1 NSR 161031 1. original effort (only writes the database)
// NSR 161113 2. updated the build instructions
// NSR 161122 3. added a second routine to just read the database
//========================================================================
#include <stdio.h> // for general i/o in c/c++
#include <sqlite3.h> // for sqlite i/o in c/c++
//
// declaring global variables in C is usually considered bad form
// but this is just a demo :-)
//
sqlite3 *db = 0;
char *zErrMsg = 0;
//
// Windows file spec information:
//
// 1. c:\sqlite3\NEIL-TEST-003.DB"
//
const char *db_name9 = "c:\\sqlite3\\NEIL-TEST-003.DB";
//
// OpenVMS file spec information for the desired database:
// 1. CSMIS$USER3:[ADMCSM.NEIL._sqlite]NEIL-TEST-003.DB some logical names
// 2. kawc99$dka200:[csmis.usr.][admcsm.neil._sqlite]NEIL-TEST-003.DB all physical names
//
const char *db_name8 = "NEIL-TEST-003.DB";
const char *db_name7 = "//dka200/csmis/usr/admcsm/neil/_sqlite/NEIL-TEST-003.DB";
const char *db_name = "//csmis$user3/admcsm/neil/_sqlite/NEIL-TEST-003.DB";
//
//===================================================================
// callback (executed whenever requested data is returned)
//===================================================================
static int callback(void *cbk_msg, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)cbk_msg);
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0; // signal all is well here
}
//===================================================================
// read_db
//===================================================================
int read_db(){
int rc;
char sql[255];
const char *cbk_msg = "Callback triggered from read_db()";
//
// Open database (for read)
//
printf("-i-opening database for read\n"); //
// rc = sqlite3_open(db_name, &db); // for newbies
rc = sqlite3_open_v2(db_name, &db, SQLITE_OPEN_READONLY, NULL); // for professionals
if( rc ){ //
fprintf(stderr, "-e-error: %ld, %s\n",rc, sqlite3_errmsg(db)); //
sqlite3_close(db); // docs say to always do this
// return(rc);
// which is worse? exiting in the middle of code or using goto?
goto fini;
}else{
fprintf(stderr, "Opened database successfully\n");
}
//
// Create SQL statement
//
sprintf(sql,"select rowid,testdata from YADA order by testdata");
//
// Execute SQL statement
//
printf("-i-executing sql command\n");
rc = sqlite3_exec(db, sql, callback, (void*)cbk_msg, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "-e-SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}else{
fprintf(stdout, "-i-SQL operation was successful\n");
}
printf("-i-closing database\n");
sqlite3_close(db);
//
fini:;
return(rc);
}
//===================================================================
// write_db
//===================================================================
int write_db(){
int rc;
char pfx[10];
char sql[255];
const char *cbk_msg = "Callback triggered from write_db()";
//
printf("enter a single data prefix character (a-z,A-Z) ");
fgets(pfx, sizeof(pfx), stdin);
if (((pfx[0]>='A') && (pfx[0]<='Z')) || ((pfx[0]>='a') && (pfx[0]<='z'))){
}else{
pfx[0] = 'a';
}
printf("-i-using data prefix: %c\n",pfx[0]);
//
// Open database
//
printf("-i-opening database for modify\n"); //
// rc = sqlite3_open(db_name, &db); // for newbies
rc = sqlite3_open_v2(db_name, &db, //
SQLITE_OPEN_READWRITE , NULL); // for professionals (0)
// SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL); // for professionals (1)
// SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX, NULL); // for professionals (2)
if( rc ){ //
fprintf(stderr, "-e-error: %ld, %s\n",rc, sqlite3_errmsg(db)); //
sqlite3_close(db); // docs say to always do this
return(rc); //
}else{
fprintf(stderr, "Opened database successfully\n");
}
for (int rec=0;rec<=99;rec++){ //
//
// Create SQL statement
//
char fakedata[20]; //
if (rec==0){
sprintf(sql, "pragma locking_mode;"); // first time
}else{
sprintf(fakedata, "%c%09d", pfx[0], rec); //
sprintf(sql, "%s%s%s",
"insert into YADA (testdata) values ('",
fakedata,
"');" );
}
//
// Execute SQL statement
//
printf("-i-executing sql command: %s\n",sql);
rc = sqlite3_exec(db, sql, callback, (void*)cbk_msg, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
break;
}else{
fprintf(stdout, "Operation done successfully\n");
}
}
printf("-i-closing database\n");
sqlite3_close(db);
return(rc); //
}
//===================================================================
// main
//===================================================================
int main(int argc, char* argv[])
{
int rc;
int dcl;
char choice[BUFSIZ];
//
// main code
//
printf("-i-program start: %s\n",argv[0]);
printf("-i-database: %s\n",db_name);
printf("-?-Action? (r/w/q) ");
fgets(choice,sizeof(choice),stdin);
printf("\n");
switch(choice[0]){
case 'r':
case 'R':
rc = read_db();
break;
case 'w':
case 'W':
rc = write_db();
break;
default:
rc = 0;
break;
}
//
// adios
//
if (rc==0){
dcl = 1; // DCL-success
}else{
dcl = 2; // DCL-error
}
printf("-i-program exiting with DCL status: %d\n",dcl);
return (dcl); // pass exit code to DCL
}
A few reality checks
Right tool for the right job
Don't interpret my enthusiasm for SQLite as a religious crusade. While I feel SQLite may be the correct choice for single
applications and/or small systems, it should not be used in place of MySQL or MariaDB. To put it more bluntly: Comparing "MySQL
and MariaDB" to "SQLite" is like comparing an automobile to a bicycle. But bicycles do have their places in our society. 
- http://www.sqlite.org/whentouse.html (when to use)
Database Locking (from an ISAM perspective)
Click: Jump past this Ancient Stuff
Most of my database locking experience comes from more than 25 years of VMS/OpenVMS application programming where we employed a proprietary ISAM technology called RMS (Record Services Management) which relied on the DLM (Distributed Lock Manager) built into the OS. Many OpenVMS programmers never concerned themselves with the DLM until they executed the DCL commands "$monitor lock" or "$monitor cluster" then noticed the stats were off the scale. Oops! I had always noticed that UNIX + Linux systems of the day were required to handle locking a little differently.
- https://en.wikipedia.org/wiki/Distributed_lock_manager (the Cadillac of lock managers)
- http://apenwarr.ca/log/?m=201012#13 - flock(), fcntl(), and lockf()
- https://www.samba.org/samba/news/articles/low_point/tale_two_stds_os2.html
- http://www.sector7.com/vxtools/api/vxlckmgr.html (DLM for Linux)
RMS Locking Basics (from a DEC-BASIC perspective)
- Novice DEC-BASIC programmers first open an RMS file with no optional parameters but quickly realize that other applications will now find the file locked. In this instance, the DLM placed an exclusive lock which was then granted to the first opener.
- Programs can cooperate together (if you desire) by communicating their intentions though the DLM (which now acts like a
traffic cop)
- The "access" open modifier signals the DLM what the current process intends to do to the file
- "access read"
- indicates you wish to read the file
- "access modify"
- indicates you wish to read or write the file
- "access read"
- The "allow" open modifier signals the DLM what other processes may be allowed to do while the current process is connected
- "allow read"
- indicates you will allow other processes to read the file
- "allow modify"
- indicates you will allow other processes to read or write the file
- "allow read"
- The "access" open modifier signals the DLM what the current process intends to do to the file
- Examples:
- If you attempt to open a file using "access modify" and "allow modify" but another processes had already opened the file with "allow read" then you would be denied access by receiving BASIC-error: 138 ("?File is locked")
- If every programmer used the phrase "allow modify" then lock granularity would move from file-level to record-level
- In SQL terms, lock granularity would have moved from TABLE LEVEL to ROW LEVEL (in RMS we only store one table per file)
- This change will greatly reduce, but not eliminate, lock conflicts. It will also reduce DLM lock statistics.
- But now a new problem appears
- If you always open a file with modifier "access modify" but will only read records (perhaps you are generating a report), then each "get" will still be requesting then placing a lock which results in unnecessary DLM overhead (you have requested a lock which you never intended to use). Each read operation will slide the lock forward by one record -AND- could block other processes which are reading the same way. To fetch a record without engaging the DLM you must do something like this: "get, regardless"
- If you intend to read sequentially then modify occasionally, it is better to do it this way:
declare long he1, he2 ! handler errors (inner and outer) declare rfa rfa_23 ! record file address (a 48-bit integer) declare long phase ! map(demo) & string d23_name$ = 20 , & string d23_telephone$ = 10 , & string d23_address$ = 20 , & string d23_country_code$ = 2 ! when error in ! open "yada.dat" for input as #23 & ,organization indexed & ,map demo & ,primary key d23_name$ & ,access modify & ,allow modify ! while 1 ! loop until some error (or EOF) GET #23, regardless ! do not place a lock if (do we want to modify this one?) then when error in ! phase = 1 ! rfa_23 = GETRFA(23) ! get RFA of previous read phase = 2 ! GET #23, rfa rfa_23 ! place a lock (make the desired change) ! phase = 3 ! UPDATE #23 ! write the record use ! he2 = err ! print "-e-error:";he2;"in phase:";phase end when ! end if next use he1 = err print "-e-error:";he1 when when
Database Locking
- Changing lock granularity from TABLE LEVEL to ROW LEVEL will enable multiple access to the data but this comes with a cost which may, or may not, be important to your particular application.
- Consider the following examples:
- your database is a collection of email contacts so you do not care because you are just doing a quick lookup
- your database is a collection of bank accounts so you might care. Consider this contrived example:
- you have a database of 100 bank accounts each containing $100 for a total of $10,000
- process-1 begins a report to tally the whole database so starts scanning from account-1
- when process-1 is 50% of the way through the database, process-2 steps in to transfer $50 from account-25 to account-75 (perhaps this is some sort of payment)
- with an exclusive table lock in place, process-2 would never get access to the file until process-1 exits so process-1 would contain the correct tally
- without an exclusive table lock in place, process-1 would be unaware of the move from account-25 to account-75 so would tally $10,050 which is $50 too high
- Relational databases are relational in both time and space (huh?)
- This means that when process-1 begins any operation, an effective database snapshot takes place.
- It is not a true physical snapshot because what really happens is that all changes are now being logged in a snapshot area (sometimes referred to as rollback segments).
- So the relational database engine forces process-1 to use two data sets: the database and the snapshot data stored starting at the time process-1 began its transaction.
- Meanwhile, process-2 makes committed changes to the actual database (as it should) provided there are no row locks in place. If row-locks are in place, the change will be made in a few moments when the lock is released, but as far as process-2 is concerned the change has been made.
- Since SQLite does not employ a standalone storage engine, it does not support the snapshot functionality just described above (although it can do rollbacks).
- Now since a multi-table database is implemented as a single file, then is should come as no surprise that SQLite locks the whole database by default.
- You can relax locking but it may also come at a cost.
- Click here to see how SQLite behaves when two sessions attempt to access the same database (er, file)
Links
General
- https://en.wikipedia.org/wiki/SQLite (general-historical information)
- http://sqlite.com/
Tutorials
Back to HomeNeil Rieck
Waterloo, Ontario, Canada.