[Info-vax] From index-sequential file to SQLite database

[David Jones]

On Monday, July 5, 2021 at 7:54:38 PM UTC-4, Arne Vajhøj wrote:
> This topic has come up a few times. 
> 
> Now I have written something about it. 
> 
> A conversion done by Python code running in JVM via Jython 
> using mapped Java classes: 
> 
> IS record--(map)--Java class--(automap)--Java class--(map)--DB tables 
> 
> Also showing some before code in: 
> * Cobol 
> * Pascal 
> and some after code in: 
> * C 
> * Pascal 
> * Python 
> * PHP 
> * Java (JDBC) 
> * Java (JPA) 

I first started using indexed files in Fortran, where it was sometimes as simple as adding a "KEY="
clause to the READ statement.

For SQLite applications, my C programs use an intermediate library that encapsulates the SQLite calls
in a statement storage object. The fiddly calls to sqlite3_bind... functions are handled by the execute()
method and those to sqlite3_column_... functions by the next_row() method:

   #include "statement_store.h"
   sqlite3 *db_cnx;
   sps_store sps;
   struct sps_context *ctx;
   int emp_num, dept_num, rc;
   char *first, *last;

   rc = sqlite3_open ( "payroll.db",  &db_cnx );
   sps = sps_create_store (db_cnx,  0);
   sps_define (sps, "fetch-emp-by-last",
              "SELECT * FROM employee WHERE last LIKE ?1 ORDER by last_name,first_name",  "s");

   ctx = sps_execute (sps,  "fetch-emp-by-last", "%");
   while (sps_next_row(ctx, "itti", &emp_num, &first, &last, &dept_num)) 
     {
        printf("%8d %-12s %-15s %5d\n", emp_num, first, last, dept_num);
        free ( first );
        free ( last );
     }

   if ( ctx->rc != SQLITE_DONE ) printf ( "Error fetching rows!\n" );
   printf ("Rows retrieved: %d\n",  ctx->count);
   rc = sps_rundown(ctx);