The VMS SharkOpenVMS Notes: SQLite

  1. The information presented here is intended for educational use by qualified OpenVMS technologists.
  2. The information presented here is provided free of charge, as-is, with no warranty of any kind.
  3. Is this text too small? You have two options:
    1. hold down the CTRL key while rolling the mouse wheel (zoom-in, zoom-out)
    2. use your keyboard like so:
      • hit: CTRL with "-" key to zoom smaller
      • hit: CTRL with "+" key to zoom larger
      • hit: CTRL with zero key to reset zoom
 

Quick Index

Edit: 2016-12-07 (updated a few things)
Relational Database Caveat: (please read all items in this disclaimer before continuing)

SQLite (General Info)

  1. Sometimes installing an SQL-compliant storage engine 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 consider SQLite. Just as the %include <stdio.h> directive provides C/C++ with general i/o functions like printf(), SQLite provides a directive to enable SQL i/o functions.
  2. Adding SQLite to your C/C++ apps on OpenVMS is an order-of-magnitude easier than added ISAM via RMS
  3.  SQLite is available for many other environments like: Visual BASIC.NET, Run BASIC, Python, Ruby, C#, to only name a few of many.

Links:

SQLite Installation (on OpenVMS)

Notes:
  1. unzip (a third party app) must be installed to unzip on OpenVMS
  2. a C or C++ complier must be installed to do the build
  3. 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:

  1. 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.
     
  2. 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

  1. the official SQLite documentation states you must always invoke sqlite3_close() even if the previous call to sqlite3_open() failed
    references:comment: I believe this close also handles VFS housekeeping which affects locking
  2. there are three versions of open:
  3. there are two versions of close:
  4. rule #1 above pertains to all variations of open and all the variations of close.
  5. 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

  1. 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"
  2. 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.
  3. 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)
  4. 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
  5. 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.
  1. The first session will run properly.
  2.  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:

//========================================================================
// 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.

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.

RMS Locking Basics (from a DEC-BASIC perspective)

Closing comments: A large group of dedicated engineers crafted DLM and VMS/OpenVMS to do almost magical things not seen elsewhere. For example, if your process is terminated for any unexpected reason (unexpected disconnect, crash, etc.) an internal process called RUN-DOWN is activated to do various cleanup operations like closing still-open files, releasing locks, etc. Most VMS/OpenVMS application programmers take this stuff for granted until they later are required to work on other platforms (e.g. Linux, UNIX, Windows) 

Database Locking

Locking Trade Offs

Relational Databases

SQLite is a poor man's RDMS

Links

General

Tutorials

General C Programming
https://www.sqlite.org/quickstart.html https://www.sqlite.org/cintro.html
http://www.tutorialspoint.com/sqlite/ https://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm
http://zetcode.com/db/sqlite/ http://zetcode.com/db/sqlitec/
http://www.wassen.net/sqlite.html http://www.wassen.net/sqlite-c.html
http://www.sqlitetutorial.net/   
https://www.techonthenet.com/sqlite/   
https://blog.udemy.com/sqlite-tutorial/   

Back to OpenVMS
Back to Home
Neil Rieck
Kitchener - Waterloo - Cambridge, Ontario, Canada.