{"id":11596,"date":"2018-09-28T22:21:10","date_gmt":"2018-09-28T20:21:10","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/"},"modified":"2025-10-24T09:26:17","modified_gmt":"2025-10-24T07:26:17","slug":"a-sqlite-extension-for-gawk-part-ii","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/","title":{"rendered":"A SQLite extension for gawk (part II)"},"content":{"rendered":"

Welcome to part II of a three-part article on extending gawk with a SQLite binding. Part I is here<\/a>. Part II is followed by Part III<\/a>, which give some explanations for the code presented here and shows how to use the extension with a stress test.
\nHere, I’ll list the source code of the extension and give instructions to compile and use it in gawk. Beware though that the code should be taken with several grains of salt, actually a whole wheelbarrow of it, because it has been only superficially tested. Some more serious testing is required in order to trust it entirely. So, caveat emptor !
\nI assume the source code of gawk is already installed (see for example the instructions here<\/a>). We still need the SQLite source code. Go here<\/a> and download the amalgamation zip file<\/a>. Unzip it somewhere and then copy the file sqlite3.c to the gawk extension directory, ~\/dmgawk\/gawk-4.2.1\/extension. Compile it with the command below:<\/p>\n

\ngcc -c sqlite3.c -DHAVE_READLINE -fPIC -lpthread -ldl\n<\/pre>\n
Now, edit the file Makefile.am and add the references to the new extension, as shown below:
\n
\nvi Makefile.am
\npkgextension_LTLIBRARIES =
\n        filefuncs.la
\n...
\n        sqlite_gawk.la                                                   <-----
\n 
\nnoinst_LTLIBRARIES =
\n...
\ntime_la_SOURCES       = time.c
\ntime_la_LDFLAGS       = $(MY_MODULE_FLAGS)
\ntime_la_LIBADD        = $(MY_LIBS)
\nsqlite_gawk_la_SOURCES  = sqlite_gawk.c                                  <-----
\nsqlite_gawk_la_LDFLAGS  = $(MY_MODULE_FLAGS)                             <-----
\nsqlite_gawk_la_LIBADD   = $(MY_LIBS) -lpthread -ldl -lreadline           <-----
\n<\/strong>
\n...
\n<\/code>
\nSave and quit; that\u2019s all for the make file;
\nWe are still in the extension directory. Let\u2019s edit the interface sqlite_gawk.c now and insert the code below;
\nvi sqlite_gawk.c<\/p>\n
\n\/*\n * sqlite-gawk.c - an interface to sqlite() library;\n * Cesare Cervini\n * dbi-services.com\n * 8\/2018\n*\/\n#ifdef HAVE_CONFIG_H\n#include <config.h>\n#endif\n\n#include <stdio.h>\n#include <assert.h>\n#include <stdlib.h>\n#include <string.h>\n#include <unistd.h>\n\n#include <sys\/types.h>\n#include <sys\/stat.h>\n\n#include \"gawkapi.h\"\n\n\/\/ extension;\n#include <time.h>\n#include <errno.h>\n#include <limits.h>\n#include <sys\/time.h>\n#include <sys\/resource.h>\n#include <sys\/types.h>\n#include <regex.h>\n#include <sqlite3.h>\n\n#include \"gettext.h\"\n#define _(msgid)  gettext(msgid)\n#define N_(msgid) msgid\n\nstatic const gawk_api_t *api;   \/* for convenience macros to work *\/\nstatic awk_ext_id_t ext_id;\nstatic const char *ext_version = \"an interface to sqlite3: version 1.0\";\n\nint plugin_is_GPL_compatible;\n\n\/* internal structure and variables *\/\n\/*\ninternally stores the db handles so an integer is returned to gawk as the index of a array;\n*\/\n#define MAX_DB 100\nstatic unsigned nb_sqlite_free_handles = MAX_DB;\nstatic sqlite3 *sqlite_handles[MAX_DB];\n\n\/* init_sqlite_handles *\/\n\/*\nsince gawk does not know pointers, we use integers as db handles, which are actually indexes into a table of sqllite db handles;\ninitializes the sqlite_handles array to null pointers and resets the number of free handles;\ncalled at program start time;\n*\/\nstatic awk_bool_t init_sqlite_handles(void) {\n   for (unsigned i = 0; i < MAX_DB; i++)\n      sqlite_handles[i] = NULL;\n   nb_sqlite_free_handles = MAX_DB;\n\n   register_ext_version(ext_version);\n\n   return awk_true;\n}\n\n\/*\nreadfile() and writefile() functions for blob I\/Os from\/to file into\/from memory;\ne.g.:\n   INSERT INTO chickenhouse my_blob = readfile('chicken_run.mp4');\n   SELECT writefile("'Mary Poppins Returns.mp4'", my_blob) FROM children_movies;\nthey are taken directly from the source file sqlite3.c, i.e. sqlite-s shell program;\nthose functions are later registered in do_sqlite_open() via a call to sqlite_create_function() for use as extending SQL functions;\nto be done while opening a db in do_sqlite_open() and for each opened db where the extended functions must be available, i.e. all for short;\n*\/\n\n\/\/ ------------------------------------------- begin of imported functions from sqllite3.c ---------------------------------------------\n\/*\n** This function is used in place of stat().  On Windows, special handling\n** is required in order for the included time to be returned as UTC.  On all\n** other systems, this function simply calls stat().\n*\/\nstatic int fileStat(\n  const char *zPath,\n  struct stat *pStatBuf\n){\n  return stat(zPath, pStatBuf);\n}\n\n\/*\n** Set the result stored by context ctx to a blob containing the \n** contents of file zName.\n*\/\nstatic void readFileContents(sqlite3_context *ctx, const char *zName){\n  FILE *in;\n  long nIn;\n  void *pBuf;\n\n  in = fopen(zName, "rb");\n  if( in==0 ) return;\n  fseek(in, 0, SEEK_END);\n  nIn = ftell(in);\n  rewind(in);\n  pBuf = sqlite3_malloc( nIn );\n  if( pBuf && 1==fread(pBuf, nIn, 1, in) ){\n    sqlite3_result_blob(ctx, pBuf, nIn, sqlite3_free);\n  }else{\n    sqlite3_free(pBuf);\n  }\n  fclose(in);\n}\n\n\/*\n** Implementation of the "readfile(X)" SQL function.  The entire content\n** of the file named X is read and returned as a BLOB.  NULL is returned\n** if the file does not exist or is unreadable.\n*\/\nstatic void readfileFunc(\n  sqlite3_context *context,\n  int argc,\n  sqlite3_value **argv\n){\n  const char *zName;\n  (void)(argc);  \/* Unused parameter *\/\n  zName = (const char*)sqlite3_value_text(argv[0]);\n  if( zName==0 ) return;\n  readFileContents(context, zName);\n}\n\n\/*\n** This function does the work for the writefile() UDF. Refer to \n** header comments at the top of this file for details.\n*\/\nstatic int writeFile(\n  sqlite3_context *pCtx,          \/* Context to return bytes written in *\/\n  const char *zFile,              \/* File to write *\/\n  sqlite3_value *pData,           \/* Data to write *\/\n  mode_t mode,                    \/* MODE parameter passed to writefile() *\/\n  sqlite3_int64 mtime             \/* MTIME parameter (or -1 to not set time) *\/\n){\n  if( S_ISLNK(mode) ){\n    const char *zTo = (const char*)sqlite3_value_text(pData);\n    if( symlink(zTo, zFile)<0 ) return 1;\n  }else\n  {\n    if( S_ISDIR(mode) ){\n      if( mkdir(zFile, mode) ){\n        \/* The mkdir() call to create the directory failed. This might not\n        ** be an error though - if there is already a directory at the same\n        ** path and either the permissions already match or can be changed\n        ** to do so using chmod(), it is not an error.  *\/\n        struct stat sStat;\n        if( errno!=EEXIST\n         || 0!=fileStat(zFile, &sStat)\n         || !S_ISDIR(sStat.st_mode)\n         || ((sStat.st_mode&0777)!=(mode&0777) && 0!=chmod(zFile, mode&0777))\n        ){\n          return 1;\n        }\n      }\n    }else{\n      sqlite3_int64 nWrite = 0;\n      const char *z;\n      int rc = 0;\n      FILE *out = fopen(zFile, \"wb\");\n      if( out==0 ) return 1;\n      z = (const char*)sqlite3_value_blob(pData);\n      if( z ){\n        sqlite3_int64 n = fwrite(z, 1, sqlite3_value_bytes(pData), out);\n        nWrite = sqlite3_value_bytes(pData);\n        if( nWrite!=n ){\n          rc = 1;\n        }\n      }\n      fclose(out);\n      if( rc==0 && mode && chmod(zFile, mode & 0777) ){\n        rc = 1;\n      }\n      if( rc ) return 2;\n      sqlite3_result_int64(pCtx, nWrite);\n    }\n  }\n\n  if( mtime>=0 ){\n#if defined(AT_FDCWD) && 0 \/* utimensat() is not universally available *\/\n    \/* Recent unix *\/\n    struct timespec times[2];\n    times[0].tv_nsec = times[1].tv_nsec = 0;\n    times[0].tv_sec = time(0);\n    times[1].tv_sec = mtime;\n    if( utimensat(AT_FDCWD, zFile, times, AT_SYMLINK_NOFOLLOW) ){\n      return 1;\n    }\n#else\n    \/* Legacy unix *\/\n    struct timeval times[2];\n    times[0].tv_usec = times[1].tv_usec = 0;\n    times[0].tv_sec = time(0);\n    times[1].tv_sec = mtime;\n    if( utimes(zFile, times) ){\n      return 1;\n    }\n#endif\n  }\n\n  return 0;\n}\n\n\/*\n** Argument zFile is the name of a file that will be created and\/or written\n** by SQL function writefile(). This function ensures that the directory\n** zFile will be written to exists, creating it if required. The permissions\n** for any path components created by this function are set to (mode&0777).\n**\n** If an OOM condition is encountered, SQLITE_NOMEM is returned. Otherwise,\n** SQLITE_OK is returned if the directory is successfully created, or\n** SQLITE_ERROR otherwise.\n*\/\nstatic int makeDirectory(\n  const char *zFile,\n  mode_t mode\n){\n  char *zCopy = sqlite3_mprintf(\"%s\", zFile);\n  int rc = SQLITE_OK;\n\n  if( zCopy==0 ){\n    rc = SQLITE_NOMEM;\n  }else{\n    int nCopy = (int)strlen(zCopy);\n    int i = 1;\n\n    while( rc==SQLITE_OK ){\n      struct stat sStat;\n      int rc2;\n\n      for(; zCopy[i]!='\/' && i<nCopy; i++);\n      if( i==nCopy ) break;\n      zCopy[i] = '';\n\n      rc2 = fileStat(zCopy, &sStat);\n      if( rc2!=0 ){\n        if( mkdir(zCopy, mode & 0777) ) rc = SQLITE_ERROR;\n      }else{\n        if( !S_ISDIR(sStat.st_mode) ) rc = SQLITE_ERROR;\n      }\n      zCopy[i] = '\/';\n      i++;\n    }\n\n    sqlite3_free(zCopy);\n  }\n\n  return rc;\n}\n\n\/*\n** Set the error message contained in context ctx to the results of\n** vprintf(zFmt, ...).\n*\/\nstatic void ctxErrorMsg(sqlite3_context *ctx, const char *zFmt, ...){\n  char *zMsg = 0;\n  va_list ap;\n  va_start(ap, zFmt);\n  zMsg = sqlite3_vmprintf(zFmt, ap);\n  sqlite3_result_error(ctx, zMsg, -1);\n  sqlite3_free(zMsg);\n  va_end(ap);\n}\n\n\/*\n** Implementation of the "writefile(W,X[,Y[,Z]]])" SQL function.  \n** Refer to header comments at the top of this file for details.\n*\/\nstatic void writefileFunc(\n  sqlite3_context *context,\n  int argc,\n  sqlite3_value **argv\n){\n  const char *zFile;\n  mode_t mode = 0;\n  int res;\n  sqlite3_int64 mtime = -1;\n\n  if( argc4 ){\n    sqlite3_result_error(context, \n        \"wrong number of arguments to function writefile()\", -1\n    );\n    return;\n  }\n\n  zFile = (const char*)sqlite3_value_text(argv[0]);\n  if( zFile==0 ) return;\n  if( argc>=3 ){\n    mode = (mode_t)sqlite3_value_int(argv[2]);\n  }\n  if( argc==4 ){\n    mtime = sqlite3_value_int64(argv[3]);\n  }\n\n  res = writeFile(context, zFile, argv[1], mode, mtime);\n  if( res==1 && errno==ENOENT ){\n    if( makeDirectory(zFile, mode)==SQLITE_OK ){\n      res = writeFile(context, zFile, argv[1], mode, mtime);\n    }\n  }\n\n  if( argc>2 && res!=0 ){\n    if( S_ISLNK(mode) ){\n      ctxErrorMsg(context, \"failed to create symlink: %s\", zFile);\n    }else if( S_ISDIR(mode) ){\n      ctxErrorMsg(context, \"failed to create directory: %s\", zFile);\n    }else{\n      ctxErrorMsg(context, \"failed to write file: %s\", zFile);\n    }\n  }\n}\n\/\/ ------------------------------------------- end of imported functions from sqllite3.c ---------------------------------------------\n\n\/* get_free_sqlite_handle *\/\n\/*\nlooks for a free slot in sqlite_handles;\nreturn its index if found, -1 otherwise;\n*\/\nstatic unsigned get_free_sqlite_handle(void) {\n   if (0 == nb_sqlite_free_handles) {\n       fprintf(stderr, \"maximum of open db [%d] reached, no free handles !n\", MAX_DB);\n       return -1;\n   }\n   for (unsigned i = 0; i < MAX_DB; i++)\n      if (NULL == sqlite_handles[i])\n         return i;\n   \/\/ should never come so far;\n   return -1;\n}\n\n\/* do_sqllite_open *\/\n\/* returns -1 if error, a db handle in the range 0 .. MAX_DB - 1 otherwise; *\/\nstatic awk_value_t *\ndo_sqlite_open(int nargs, awk_value_t *result, struct awk_ext_func *unused) {\n   awk_value_t db_name;\n   short int ret;\n\n   assert(result != NULL);\n\n   unsigned int db_handle = get_free_sqlite_handle();\n   if (-1 == db_handle)\n      return make_number(-1, result);\n\n   if (get_argument(0, AWK_STRING, &db_name)) {\n      sqlite3 *db;\n\n      ret = sqlite3_open(db_name.str_value.str, &db);\n\n      if (ret) {\n         char error_string[1000];\n         sprintf(error_string, "sqlite3_open(): cannot open database [%s], error %sn", db_name.str_value.str, sqlite3_errmsg(db));\n         fprintf(stderr, "%sn", error_string);\n         update_ERRNO_string(_(error_string));\n         ret = -1;\n      }\n      else {\n         sqlite_handles[db_handle] = db;\n         nb_sqlite_free_handles--;\n         ret = db_handle;\n\n         \/\/ register the extension functions readfile() and writefile() for blobs;\n         ret = sqlite3_create_function(db, "readfile", 1, SQLITE_UTF8, 0, readfileFunc, 0, 0);\n         if (ret == SQLITE_OK) {\n            ret = sqlite3_create_function(db, "writefile", -1, SQLITE_UTF8, 0, writefileFunc, 0, 0);\n            if (SQLITE_OK != ret)\n               fprintf(stderr, "%sn", "could not register function writefile()");\n         }\n         else if (SQLITE_OK != ret)\n            fprintf(stderr, "%sn", "could not register function readfile()");\n      }\n   }\n   else {\n      update_ERRNO_string(_("sqlite3_open(): missing parameter database name"));\n      ret = -1;\n   }\n\n   return make_number(ret, result);\n}\n\n\/* do_sqllite_close *\/\n\/* returns -1 if error, 0 otherwise; *\/\nstatic awk_value_t *\ndo_sqlite_close(int nargs, awk_value_t *result, struct awk_ext_func *unused) {\n   awk_value_t db_handle;\n   int ret;\n\n   assert(result != NULL);\n\n   if (get_argument(0, AWK_NUMBER, &db_handle)) {\n      sqlite3_close(sqlite_handles[(int) db_handle.num_value]);\n      sqlite_handles[(int) db_handle.num_value] = NULL;\n      nb_sqlite_free_handles++;\n      ret = 0;\n   }\n   else {\n      update_ERRNO_string(_("sqlite3_close(): missing parameter database handle"));\n      ret = -1;\n   }\n   return make_number(ret, result);\n}\n\n\/* do_sqllite_exec *\/\n\/*\nreturns -1 if error, 0 otherwise;\nsqlite_exec is overloaded;\nif 2 parameters, usual DML\/DDL statements;\nif 6 parameters, then incremental blob I\/O;\nsqlite_exec(db, db_name, table, column, rowid, readfile(file_name))\nor\nsqlite_exec(db, db_name, table, column, rowid, writefile(file_name))\nimplements sqlite3'c shell readfile()\/writefile() syntax with incremental blob I\/Os;\nExample of usage:\nfirst, get the rowid of the row that contains the blob to access;\n   sqlite_select(db, \"select rowid from <table> where <condition>\", array)\nthen, call the sqlite_exec function with the tuple (<db_name>, <table>, <blob_column>, <rowid>) and the action to do, either readfile() or writefile();\n   sqlite_exec(db, <db_name>, '<table>', '<blob_column>', array[0][\"rowid\"], readfile(file_name))\n   sqlite_exec(db, <db_name>, '<table>', '<blob_column>', array[0][\"rowid\"], writefile(file_name))\ne.g.:\n   rc = sqlite_exec(my_db, \"main\", \"test_with_blob\", \"my_blob\", a_test[0][\"rowid\"], \"readfile(\/home\/dmadmin\/setup_files\/documentum.tar)\")\nnote how the file name is not quoted;\nin case of readfile(), if the blob's size changes, an update of the blob filled with zero-byte bytes and with the new size is first performed, then the blob is reopened;\nsee doc here for incremental blob I\/Os: https:\/\/sqlite.org\/c3ref\/blob_open.html;\nint sqlite3_blob_open(sqlite3*, const char *zDb, const char *zTable, const char *zColumn, sqlite3_int64 iRow, int flags, sqlite3_blob **ppBlob);\nint sqlite3_blob_reopen(sqlite3_blob *, sqlite3_int64);\nint sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int iOffset);\nint sqlite3_blob_write(sqlite3_blob *, const void *z, int n, int iOffset);\nint sqlite3_blob_close(sqlite3_blob *);\n*\/\nstatic awk_value_t *\ndo_sqlite_exec(int nargs, awk_value_t *result, struct awk_ext_func *unused) {\n   awk_value_t db_handle;\n   int ret = 1;\n\n   assert(result != NULL);\n\n   if (!get_argument(0, AWK_NUMBER, &db_handle)) {\n      fprintf(stderr, \"in do_sqlite_exec, cannot get the db handle argumentn\");\n      ret = -1;\n      goto end;\n   }\n   if (2 == nargs) {\n      awk_value_t sql_stmt;\n      if (!get_argument(1, AWK_STRING, &sql_stmt))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the sql_stmt argumentn\");\n         ret = -1;\n         goto end;\n      }\n      char *errorMessg = NULL;\n      ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], sql_stmt.str_value.str, NULL, NULL, &errorMessg);\n      if (SQLITE_OK != ret) {\n         fprintf(stderr, \"in do_sqlite_exec, SQL error %s while executing [%s]n\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sql_stmt.str_value.str);\n         sqlite3_free(errorMessg);\n         ret = -1;\n         goto end;\n      }\n   }\n   else if (6 == nargs) {\n      awk_value_t arg, number_value;\n      char *db_name = NULL, *table_name = NULL, *column_name = NULL, *file_stmt = NULL, *file_name = NULL;\n\n      if (!get_argument(1, AWK_STRING, &arg))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the db_name argumentn\");\n         ret = -1;\n         goto abort;\n      }\n      db_name = strdup(arg.str_value.str);\n\n      if (!get_argument(2, AWK_STRING, &arg))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the table_name argumentn\");\n         ret = -1;\n         goto abort;\n      }\n      table_name = strdup(arg.str_value.str);\n\n      if (!get_argument(3, AWK_STRING, &arg))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the column_name argumentn\");\n         ret = -1;\n         goto abort;\n      }\n      column_name = strdup(arg.str_value.str);\n      \n      if (!get_argument(4, AWK_NUMBER, &number_value))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the rowid argumentn\");\n         ret = -1;\n         goto abort;\n      }\n      long int rowid = number_value.num_value;\n      \n      if (!get_argument(5, AWK_STRING, &arg))  {\n         fprintf(stderr, \"in do_sqlite_exec, cannot get the readfile()\/writefile() argumentn\");\n         ret = -1;\n         goto abort;\n      }\n      file_stmt = strdup(arg.str_value.str);\n      \n      unsigned short bRead2Blob;\n      char *RE_readfile = \"^readfile\\(([^)]+)\\)$\";\n      char *RE_writefile = \"^writefile\\(([^)]+)\\)$\";\n      regex_t RE;\n      regmatch_t pmatches[2];\n\n      if (regcomp(&RE, RE_readfile, REG_EXTENDED)) {\n         fprintf(stderr, \"in do_sqlite_exec, error compiling REs %sn\", RE_readfile);\n         ret = -1;\n         goto abort;\n      }\n      if (regexec(&RE, file_stmt, 2, pmatches, 0)) {\n         \/\/ no call to readfile() requested, try writefile();\n         regfree(&RE);\n         if (regcomp(&RE, RE_writefile, REG_EXTENDED)) {\n            fprintf(stderr, \"in do_sqlite_exec, error compiling REs %sn\", RE_writefile);\n            ret = -1;\n            goto abort;\n         }\n         if (regexec(&RE, file_stmt, 2, pmatches, 0)) {\n            fprintf(stderr, \"in do_sqlite_exec, error executing RE %s and RE %s against %s;nneither readfile(file_name) nor writefile(file_name) was foundn\", RE_readfile, RE_writefile, file_stmt);\n            ret = -1;\n            goto abort;\n         }\n         else bRead2Blob = 0;\n      }\n      else bRead2Blob = 1;\n      file_name = strndup(file_stmt + pmatches[1].rm_so, pmatches[1].rm_eo - pmatches[1].rm_so);\n      regfree(&RE);\n      sqlite3_blob *pBlob;\n      if (bRead2Blob) {\n         ret = sqlite3_blob_open(sqlite_handles[(int) db_handle.num_value], db_name, table_name, column_name, rowid, 1, &pBlob);\n         if (SQLITE_OK != ret) {\n            fprintf(stderr, \"in do_sqlite_exec, at reading blob, with parameters: db_name=%s, table_name=%s, column_name=%s, rowid=%ld, file statement=%s, error in sqlite3_blob_open %sn%sn\",\n                            db_name, table_name, column_name, rowid, file_stmt,\n                            sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n            ret = -1;\n            goto abort;\n         }\n\n         FILE *fs_in = fopen(file_name, \"r\");\n         if (NULL == fs_in) {\n            fprintf(stderr, \"in do_sqlite_exec, error opening file %s for readingn\", file_name);\n            ret = -1;\n            goto local_abort_w;\n         }\n\n         \/\/ will the blob size change ?\n         fseek(fs_in, 0, SEEK_END);\n         unsigned long file_size = ftell(fs_in);\n         rewind(fs_in);\n         unsigned long blobSize = sqlite3_blob_bytes(pBlob);\n         if (file_size != blobSize) {\n            \/\/ yes, must first update the blob with the new size and reopen it;\n            char stmt[500];\n            char *errorMessg = NULL;\n            sprintf(stmt, \"update %s set %s = zeroblob(%ld) where rowid = %ld\", table_name, column_name, file_size, rowid);\n            ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], stmt, NULL, NULL, &errorMessg);\n            if (SQLITE_OK != ret) {\n               fprintf(stderr, \"in do_sqlite_exec, SQL error %s while changing the blob's size through [%s]:n%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), stmt, errorMessg);\n               sqlite3_free(errorMessg);\n               ret = -1;\n               goto local_abort_w;\n            }\n            ret = sqlite3_blob_reopen(pBlob, rowid);\n            if (SQLITE_OK != ret) {\n               fprintf(stderr, \"in do_sqlite_exec, error while reopening the blob: %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n               ret = -1;\n               goto local_abort_w;\n            }\n         }\n\n         \/\/ let's work with a 10 MiB large buffer;\n         unsigned long BUFFER_SIZE = 10 * 1024 * 1024;\n         char *pBuffer = (char *) malloc(sizeof(char) * BUFFER_SIZE);\n         unsigned long nbBytes;\n         unsigned long offset = 0;\n         while ((nbBytes = fread(pBuffer, sizeof(char), BUFFER_SIZE, fs_in)) > 0) {\n            ret = sqlite3_blob_write(pBlob, pBuffer, nbBytes, offset);\n            if (SQLITE_OK != ret) {\n               fprintf(stderr, \"in do_sqlite_exec, sqlite3_blob_write, error %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n               ret = -1;\n               free(pBuffer);\n               goto local_abort_w;\n            }\n            offset += nbBytes;\n         }\n         free(pBuffer);\nlocal_abort_w:\n         fclose(fs_in);\n         ret = sqlite3_blob_close(pBlob);\n         if (SQLITE_OK != ret) {\n            fprintf(stderr, \"in do_sqlite_exec, sqlite3_blob_close, error %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n            ret = -1;\n         }\n      }\n      else {\n         ret = sqlite3_blob_open(sqlite_handles[(int) db_handle.num_value], db_name, table_name, column_name, rowid, 0, &pBlob);\n         if (SQLITE_OK != ret) {\n            fprintf(stderr, \"in do_sqlite_exec at writing blob, error %d in sqlite3_blob_open with parameters: db_name=%s, table_name=%s, column_name=%s, rowid=%ld, file statement=%sn\",\n                            ret,\n                            db_name, table_name, column_name, rowid, file_stmt);\n            ret = -1;\n            goto abort;\n         }\n         unsigned long BUFFER_SIZE = 10 * 1024 * 1024;\n         char *pBuffer = (char *) malloc(sizeof(char) * BUFFER_SIZE);\n         unsigned long offset = 0;\n         FILE *fs_out = fopen(file_name, \"w\");\n         if (NULL == fs_out) {\n            fprintf(stderr, \"in do_sqlite_exec, error %d opening file %s for writingn\", errno, file_name);\n            ret = -1;\n            goto local_abort_r;\n         }\n         unsigned long blobSize = sqlite3_blob_bytes(pBlob);\n         if (BUFFER_SIZE >= blobSize) {\n            ret = sqlite3_blob_read(pBlob, pBuffer, blobSize, offset);\n            if (SQLITE_OK != ret) {\n               fprintf(stderr, \"in do_sqlite_exec, sqlite3_blob_read, error %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n               ret = -1;\n               goto local_abort_r;\n            }\n            unsigned long nbBytes = fwrite(pBuffer, sizeof(char), BUFFER_SIZE, fs_out);\n            if (nbBytes < blobSize) {\n               fprintf(stderr, "in do_sqlite_exec, error in fwrite()n");\n               ret = -1;\n               goto local_abort_r;\n            }\n         }\n         else {\n            unsigned long nbBytes;\n            while ((nbBytes = (blobSize <= BUFFER_SIZE ? blobSize : BUFFER_SIZE)) > 0) {\n               ret = sqlite3_blob_read(pBlob, pBuffer, nbBytes, offset);\n               if (SQLITE_OK != ret) {\n                  fprintf(stderr, \"in do_sqlite_exec, sqlite3_blob_read, error %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n                  ret = -1;\n                  goto local_abort_r;\n               }\n               ret = fwrite(pBuffer, sizeof(char), nbBytes, fs_out);\n               if (ret < nbBytes) {\n                  fprintf(stderr, \"in do_sqlite_exec, error in fwrite()n\");\n                  ret = -1;\n                  goto local_abort_r;\n               }\n               offset += nbBytes;\n               blobSize -= nbBytes;\n            }\n         }\nlocal_abort_r:\n         fclose(fs_out);\n         free(pBuffer);\n         ret = sqlite3_blob_close(pBlob);\n         if (SQLITE_OK != ret) {\n            fprintf(stderr, \"in do_sqlite_exec, processing of writefile(), sqlite3_blob_close, error %sn%sn\", sqlite3_errmsg(sqlite_handles[(int) db_handle.num_value]), sqlite3_errstr(ret));\n            ret = -1;\n         }\n      }\nabort:\n      free(db_name);\n      free(table_name);\n      free(column_name);\n      free(file_stmt);\n      free(file_name);\n   }\n   else {\n      fprintf(stderr, \"in do_sqlite_exec, unsupported number of parameters in statement while processing [%d]n\", nargs);\n      ret = -1;\n   }\nend:\n   return make_number(ret, result);\n}\n\nstatic unsigned max(unsigned n1, unsigned n2) {\n   if (n1 > n2)\n      return n1;\n   else return n2;\n}\n\n\/\/ this struct is used to pass parameters to the callbacks;\ntypedef struct DISPLAYED_TABLE {\n   char *sqlStmt;\n\n   unsigned short bHeaderPrinted;\n   char *COL_SEPARATOR;\n   char *ELLIPSIS;\n   unsigned short len_ellipsis;\n   unsigned short MAX_WIDTH;\n   unsigned short MIN_WIDTH;\n\n   unsigned nb_columns;\n   unsigned *max_col_widths;\n   unsigned *actual_col_widths;\n   char *col_overflow_action;\n   char **headers;\n   unsigned widest_column;\n\n   char *size_list;  \/\/ list of blank- or comma-separated column widths;\n\n   unsigned long NR;\n   unsigned short bStoreOrDisplay;\n   awk_array_t gawk_array;\n\n   unsigned short bEpilog;\n} DISPLAYED_TABLE;\n\nvoid cleanup(DISPLAYED_TABLE *dt) {\n   if (dt -> sqlStmt)\n      free(dt -> sqlStmt);\n   if (dt -> max_col_widths)\n      free(dt -> max_col_widths);\n   if (dt -> actual_col_widths)\n      free(dt -> actual_col_widths);\n   for (unsigned i = 0; i < dt -> nb_columns; i++) {\n      if (dt -> headers)\n         free(dt -> headers[i]);\n   }\n   if (dt -> headers)\n      free(dt -> headers);\n   if (dt -> size_list)\n      free(dt -> size_list);\n}\n\n\/\/ strip the trailing blanks so they are not counted toward the column width;\n\/\/ and returns the number of characters from the beginning;\n\/\/ former version attempted to insert a  terminator but it caused error when the string resides in code area (e.g. SELECT sqlite_version()) because modifications are not allowed there for obvious reasons;\nunsigned getUsefulLen(char * str) {\n   unsigned len = strlen(str);\n   char *p = str + len - 1;\n   while (' ' == *p && p > str) p--;\n   if (' ' != *p)\n      len = p - str + 1;\n   else\n      len = 0;\n   return(len);\n}\n\nchar *fillStr(char *S, char ch, unsigned max_len) {\n   S[max_len] = '';\n   for (char *p = S; max_len; p++, max_len--)\n      *p = ch;\n   return S;\n}\n\n\/* select_callback_raw *\/\n\/\/ displays the data without truncation nor cleaning up tainling blanks;\n\/\/ columns are separated by dt -> COL_SEPARATOR, which is useful to import data as CSV;\nstatic int select_callback_raw(void *vdt, int nb_columns, char **column_values, char **column_names) {\n   DISPLAYED_TABLE *dt = (DISPLAYED_TABLE *) vdt;\n\n   if (dt -> bEpilog) {\n      printf(\"%ld rows selectedn\", dt -> NR);\n      cleanup(dt);\n      return 0;\n   }\n\n   if (!dt -> bHeaderPrinted) {\n      \/\/ header has not been printed yet, print it and afterwards print the first row;\n      for (unsigned i = 0; i < nb_columns; i++)\n         printf("%s%s", column_names[i], i  COL_SEPARATOR : \"\");\n      printf(\"n\");\n      dt -> bHeaderPrinted = 1;\n   }\n\n   for (unsigned i = 0; i < nb_columns; i++)\n      printf("%s%s", column_values[i], i  COL_SEPARATOR : \"\");\n   printf(\"n\");\n   dt -> NR++;\n   return 0;\n}\n\n\/* select_callback_draft *\/\n\/*\ndisplay the data in maximum 15-character wide columns, with possible truncation, in which case an ellipsis (...) is appended;\nat the end, the optimum widths for each column are listed so they can be passed as a string list in the call to sqlite_select(,, \"....\") to avoid truncation;\nthis output is convenient as a quick draft;\n*\/\nstatic int select_callback_draft(void *vdt, int nb_columns, char **column_values, char **column_names) {\n   DISPLAYED_TABLE *dt = (DISPLAYED_TABLE *) vdt;\n\n   char col_str[dt -> MAX_WIDTH + 1];\n\n   if (dt -> bEpilog) {\n      printf(\"%ld rows selectedn\", dt -> NR);\n\n      printf(\"nOptimum column widthsn\");\n      printf(\"=====================n\");\n      printf(\"for query: %sn\", dt -> sqlStmt);\n      for (unsigned i = 0; i < dt > nb_columns; i++)\n         printf(\"%-*s  %dn\", dt -> widest_column + 5, dt -> headers[i], dt -> max_col_widths[i]);\n\n      cleanup(dt);\n      return 0;\n   }\n\n   if (!dt -> bHeaderPrinted) {\n      \/\/ header has not been printed yet, print it and afterwards print the first row;\n      dt -> nb_columns = nb_columns; \n      dt -> max_col_widths = (unsigned *) malloc(sizeof(unsigned) * nb_columns);\n      dt -> actual_col_widths = (unsigned *) malloc(sizeof(unsigned) * nb_columns);\n      dt -> headers = (char **) malloc(sizeof(char *) * nb_columns);\n\n      char *header_line = NULL;\n\n      for (unsigned i = 0; i &t; nb_columns; i++) {\n         char *tmp_s;\n         unsigned len = strlen(column_names[i]);\n         dt -> max_col_widths[i] = len;\n         dt -> widest_column = max(dt -> widest_column, len);\n         if (len > dt -> MAX_WIDTH) {\n            \/\/ column overflow, apply a truncation with ellipsis;\n            dt -> actual_col_widths[i] = dt -> MAX_WIDTH;\n            strncpy(col_str, column_names[i], dt -> MAX_WIDTH - dt -> len_ellipsis);\n            col_str[dt -> MAX_WIDTH - dt -> len_ellipsis] = '';\n            strcat(col_str, dt -> ELLIPSIS);\n            tmp_s = col_str;\n         }\n         else if (len %lt; dt -> MIN_WIDTH) {\n            dt -> actual_col_widths[i] = dt -> MIN_WIDTH;\n            tmp_s = column_names[i];\n         }\n         else {\n            dt -> actual_col_widths[i] = len;\n            tmp_s = column_names[i];\n         }\n         printf(\"%-*s%s\", dt -> actual_col_widths[i], tmp_s, i  COL_SEPARATOR : \"\");\n         dt -> headers[i] = strdup(column_names[i]);\n      }\n      printf(\"n\");\n\n      for (unsigned i = 0; i < nb_columns; i++) {\n         header_line = (char *) realloc(header_line, sizeof(char) * dt -> actual_col_widths[i]);\n         fillStr(header_line, '-', dt -> actual_col_widths[i]);\n         printf(\"%s%s\", header_line, i  COL_SEPARATOR : \"\");\n      }\n      printf(\"n\");\n      free(header_line);\n\n      dt -> bHeaderPrinted = 1;\n   }\n   \/\/ header has been printed, print the rows now;\n   for (unsigned i = 0; i < nb_columns; i++) {\n      char *tmp_s;\n      unsigned len = getUsefulLen(column_values[i]);\n      dt -> max_col_widths[i] = max(dt -> max_col_widths[i], len);\n      if (len > dt -> actual_col_widths[i]) {\n         strncpy(col_str, column_values[i], dt -> actual_col_widths[i] - dt -> len_ellipsis);\n         col_str[dt -> actual_col_widths[i] - dt -> len_ellipsis] = '';\n         strcat(col_str, dt -> ELLIPSIS);\n         tmp_s = col_str;\n      }\n      else {\n         tmp_s = column_values[i];\n      }\n      printf(\"%-*.*s%s\", dt -> actual_col_widths[i], dt -> actual_col_widths[i], tmp_s, i  COL_SEPARATOR : \"\");\n   }\n   printf(\"n\");\n   dt -> NR++;\n   return 0;\n}\n\n\/* printConstrained *\/\n\/\/ prints the row's column in constrained column widths;\nstatic void printConstrained(DISPLAYED_TABLE *dt, char **data, unsigned nb_columns) {\n   \/\/ let's replicate the data because they will be modified locally;\n   char **ldata = (char **) malloc(sizeof(char *) * nb_columns);\n   for (unsigned i = 0; i < nb_columns; i++)\n      ldata[i] = strndup(data[i], getUsefulLen(data[i]));\n   unsigned bWrapOccured;\n   do {\n      bWrapOccured = 0;\n      for (unsigned i = 0; i < nb_columns; i++) {\n         char *col_str = NULL;\n         unsigned len = strlen(ldata[i]);\n         dt -> actual_col_widths[i] = dt -> max_col_widths[i];\n         if (len > dt -> max_col_widths[i]) {\n            \/\/ column width overflow, apply the requested action: either wrap-around, truncate with ellipsis or truncate without ellipsis;\n            if ('e' == dt -> col_overflow_action[i]) {\n               if (dt -> max_col_widths[i] < dt -gt&; len_ellipsis)\n                  dt -> actual_col_widths[i] = dt -> len_ellipsis;\n               col_str = strndup(ldata[i], dt -> actual_col_widths[i] - dt -> len_ellipsis);\n               col_str[dt -> actual_col_widths[i] - dt -> len_ellipsis] = '';\n               strcat(col_str, dt -> ELLIPSIS);\n               sprintf(ldata[i], \"%*s\", len, \" \");\n            }\n            else if ('t' == dt -> col_overflow_action[i]) {\n               col_str = strndup(ldata[i], dt -> actual_col_widths[i]);\n               sprintf(ldata[i], \"%*s\", len, \" \");\n            }\n            else if ('w' == dt -> col_overflow_action[i]) {\n               col_str = strndup(ldata[i], dt -> actual_col_widths[i]);\n               \/\/ shift the column names by as many printed characters;\n               \/\/ the new column names will be printed at the next cycle of the inner loop \n               unsigned j;\n               for (j = dt -> actual_col_widths[i]; j < len; j++)\n                  ldata[i][j - dt -> actual_col_widths[i]] = ldata[i][j];\n               ldata[i][len - dt -> actual_col_widths[i]] = '';\n               bWrapOccured = 1;\n            }\n         }\n         else {\n            col_str = strdup(ldata[i]);\n            \/\/ no wrap-around necessary here but prepare the str for the next cycle just in case;\n            sprintf(ldata[i], \"%*s\", len, \" \");\n         }\n         printf(\"%-*s%s\", dt -> actual_col_widths[i], col_str, i  COL_SEPARATOR : \"\");\n         free(col_str);\n      }\n      printf(\"n\");\n   } while (bWrapOccured);\n   for (unsigned i = 0; i < nb_columns; i++)\n      free(ldata[i]);\n   free(ldata);\n}\n\n\/* select_callback_sized *\/\n\/\/ displays the columns within predetermined sizes, wrap-around if overflow;\nstatic int select_callback_sized(void *vdt, int nb_columns, char **column_values, char **column_names) {\n   DISPLAYED_TABLE *dt = (DISPLAYED_TABLE *) vdt;\n\n   if (dt -> bEpilog) {\n      printf(\"%ld rows selectedn\", dt -> NR);\n      cleanup(dt);\n      return 0;\n   }\n\n   if (!dt -> bHeaderPrinted) {\n      \/\/ header has not been printed yet, print it and afterwards print the first row;\n      dt -> actual_col_widths = (unsigned *) malloc(sizeof(unsigned) * nb_columns);\n      if (dt -> nb_columns < nb_columns) {\n         unsigned last_width = dt -> max_col_widths[dt -> nb_columns - 1];\n         fprintf(stderr, \"warning: missing column sizes, extending the last provided one %dn\", last_width);\n         dt -> max_col_widths = (unsigned *) realloc(dt -> max_col_widths, sizeof(unsigned) * nb_columns);\n         dt -> col_overflow_action = (char *) realloc(dt -> col_overflow_action, sizeof(char) * nb_columns);\n         char last_overflow_action = dt -> col_overflow_action[dt -> nb_columns - 1];\n         for (unsigned i = dt -> nb_columns; i &t; nb_columns; i++) {\n            dt -> max_col_widths[i] = last_width;\n            dt -> col_overflow_action[i] = last_overflow_action;\n         }\n         dt -> nb_columns = nb_columns;\n      }\n      else if (dt -> nb_columns > nb_columns) {\n         fprintf(stderr, \"warning: too many columns widths given, %d vs actual %d, ignoring the %d in excessn\", dt -> nb_columns, nb_columns, dt -> nb_columns - nb_columns);\n         dt -> nb_columns = nb_columns;\n      }\n      printConstrained(dt, column_names, nb_columns);\n\n      char *header_line = NULL;\n      for (unsigned i = 0; i < nb_columns; i++) {\n         header_line = (char *) realloc(header_line, sizeof(char) * dt -> actual_col_widths[i]);\n         fillStr(header_line, '-', dt -> actual_col_widths[i]);\n         printf(\"%s%s\", header_line, i < nb_columns - 1 ? dt -> COL_SEPARATOR : \"\");\n      }\n      printf(\"n\");\n      free(header_line);\n      dt -> bHeaderPrinted = 1;\n   }\n   printConstrained(dt, column_values, nb_columns);\n   dt -> NR++;\n   return 0;\n}\n\n\/* select_callback_array *\/\n\/*\nreturs the database rows into the gawk associative array passed as parameter;\nits structure is as follows:\narray[0] = sub-array_0\narray[1] = sub-array_1\n...\narray[count-1] = sub-array_count-1\nwhere the sub-arrays are associative arrays too with structure:\nsub-array0[col1] = value1\nsub-array0[col2] = value2\n...\nsub-array0[coln] = valuen\nsub-array1[col1] = value1\n...\nsub-array1[coln] = valuen\n...\nsub-arraym[col1] = value1\n...\nsub-arraym[coln] = valuen\nSaid otherwise, the returned array is an array of associative arrays whose first dimension contains the rows and second dimension contains the columns, \ni.e. it' a table of database rows or an array of hashes, or a list of dictionaries;\nin perl linguo, it's an array of hashes;\nin python, it would be an array of dictionaries;\n*\/\nstatic int select_callback_array(void *vdt, int nb_columns, char **column_values, char **column_names) {\n   DISPLAYED_TABLE *dt = (DISPLAYED_TABLE *) vdt;\n\n   if (dt -> bEpilog) {\n      printf(\"%ld rows selectedn\", dt -> NR);\n      cleanup(dt);\n      return 0;\n   }\n\n   awk_array_t row;\n   awk_value_t value;\n   awk_value_t row_index;\n   awk_value_t col_index, col_value;\n\n   if (!dt -> bHeaderPrinted) {\n      \/\/ create the main array once;\n      \/\/ doesn't work; keep the code in case a fix is found;\n      \/\/db_table = create_array();\n      \/\/value.val_type = AWK_ARRAY;\n      \/\/value.array_cookie = db_table; \n   \n      \/\/ add it to gawk's symbol table so it appear magically in gawk's script namespace;\n      \/\/if (!sym_update(dt -> array_name, &value)) \n      \/\/   fatal(ext_id, \"in select_callback_array, creation of table array %s failedn\", dt -> array_name);\n      \/\/db_table = value.array_cookie;\n\n      \/\/ nothing special to do here;\n      dt -> bHeaderPrinted = 1;\n   }\n   char index_str[50];\n   unsigned len = sprintf(index_str, \"%ld\", dt -> NR);\n   make_const_string(index_str, len, &row_index);\n\n   \/\/ create the sub-array for each row;\n   \/\/ indexes are the column names and values are the column values;\n   row = create_array();\n   value.val_type = AWK_ARRAY;\n   value.array_cookie = row;\n   if (! set_array_element(dt -> gawk_array, &row_index, &value))\n      fatal(ext_id, \"in select_callback_array, creation of row array %ld failedn\", dt -> NR);\n   row  = value.array_cookie;\n\n   for (unsigned i = 0; i  < nb_columns; i++) {\n      make_const_string(column_names[i], strlen(column_names[i]), &col_index);\n      make_const_string(column_values[i], strlen(column_values[i]), &col_value);\n      if (! set_array_element(row, &col_index, &col_value))\n         fatal(ext_id, \"in select_callback_array, assigned value %s to index %s at row %ld failedn\", column_values[i], column_names[i], dt -> NR);\n   }\n\n   dt -> NR++;\n   return 0;\n}\n\n\/* do_sqllite_select *\/\n\/*\ngeneric select entry point;\npossible invocations:\nCase: call profile:                                          --> action;\n   0: sqlite_select(db, sql_stmt)                            --> draft output, default fixed width columns, with needed column widths list at the end;\n   1: sqlite_select(db, sql_stmt, \"\")                        --> raw output, no truncation, | as default separator;\n   2: sqlite_select(db, sql_stmt, \"separator-string\")        --> raw output, no truncation, use given string as separator;\n   2: sqlite_select(db, sql_stmt, \"list-of-columns-widths\")  --> fixed sized column output, a w|t|e suffix is allowed for wrapping-around or truncating too large columns without or with ellipsis;\n   3: sqlite_select(db, sql_stmt, dummy, gawk_array)         --> raw output into the gawk associative array gawk_array;\nthe appropriate callback will be called based on the invocation's profile;\nreturns -1 if error, 0 otherwise;\n*\/\nstatic awk_value_t *\ndo_sqlite_select(int nargs, awk_value_t *result, struct awk_ext_func *unused) {\n   awk_value_t db_handle, sql_stmt, col_sizes;\n   int ret = 0;\n\n   assert(result != NULL);\n\n   if (!get_argument(0, AWK_NUMBER, &db_handle)) {\n      fprintf(stderr, \"in do_sqlite_select, cannot get the db handle argumentn\");\n      ret = -1;\n      goto quit;\n   }\n   if (!get_argument(1, AWK_STRING, &sql_stmt)) {\n      fprintf(stderr, \"do_sqlite_select, cannot get the sql_stmt argumentn\");\n      ret = -1;\n      goto quit;\n   }\n   DISPLAYED_TABLE dt;\n   dt.sqlStmt = strdup(sql_stmt.str_value.str);\n   dt.bHeaderPrinted = 0;\n   dt.COL_SEPARATOR = \"  \";\n   dt.ELLIPSIS = \"...\"; dt.len_ellipsis = strlen(dt.ELLIPSIS); \n   dt.MAX_WIDTH = 15;\n   dt.MIN_WIDTH = dt.len_ellipsis + 5;\n   dt.nb_columns = 0;\n   dt.max_col_widths = NULL;\n   dt.actual_col_widths = NULL;\n   dt.col_overflow_action = NULL;\n   dt.headers = NULL;\n   dt.widest_column = 0;\n   dt.size_list = NULL;\n   dt.NR = 0;\n   dt.bStoreOrDisplay = 1;\n   dt.gawk_array = NULL;\n   dt.bEpilog = 0;\n\n   unsigned short bCase;\n   unsigned short bFoundSeparator = 0;\n   char *errorMessg = NULL;\n\n   if (4 == nargs) {\n      bCase = 3;\n      awk_value_t value;\n      if (!get_argument(3, AWK_ARRAY, &value))\n         fatal(ext_id, \"in do_sqlite_select, accessing the gawk array parameter failedn\");\n      dt.gawk_array = value.array_cookie;\n      clear_array(dt.gawk_array);\n   }\n   else if (get_argument(2, AWK_STRING, &col_sizes)) {\n      if (0 == strlen(col_sizes.str_value.str))\n         \/\/ raw, unformatted output;\n         bCase = 1;\n      else {\n         \/\/ columns are output with constrained widths and possible wrapping-around or truncation with\/without ellipsis;\n         bCase = 2;\n         char *width_str, *tmp_str, *next_tok_iter;\n         long width_value;\n         tmp_str = strdup(col_sizes.str_value.str);\n         next_tok_iter = tmp_str;\n         while ((width_str = strtok(next_tok_iter, \" ,\/\"))) {\n            errno = 0;\n            char *overflow_action_suffix;\n            width_value = strtol(width_str, &overflow_action_suffix, 10);\n            if ((errno == ERANGE && (width_value == LONG_MAX || width_value == LONG_MIN)) ||\n                (errno != 0 && width_value == 0) ||\n                (width_value < 0)) {\n               if (0 == dt.nb_columns) {\n                  \/\/ let's take this as a separator for select_callback_raw();\n                  dt.COL_SEPARATOR = width_str;\n                  bFoundSeparator = 1;\n                  bCase = 0;\n               }\n               else {\n                  fprintf(stderr, \"invalid number in size string [%s], exiting ...n\", width_str);\n                  if (dt.nb_columns > 0) {\n                     free(dt.max_col_widths);\n                     free(dt.col_overflow_action);\n                  }\n                  free(tmp_str);\n                  ret = -1;\n                  goto quit;\n               }\n            }\n            else if (bFoundSeparator) {\n               \/\/ nothing else is accepted after a separator;\n               fprintf(stderr, \"separator [%s] must be the only parameter in raw output, exiting ...n\", dt.COL_SEPARATOR);\n               free(tmp_str);\n               ret = -1;\n               goto quit;\n            }\n            dt.max_col_widths = (unsigned *) realloc(dt.max_col_widths, sizeof(unsigned) * (dt.nb_columns + 1));\n            dt.col_overflow_action = (char *) realloc(dt.col_overflow_action, sizeof(char) * (dt.nb_columns + 1));\n            dt.max_col_widths[dt.nb_columns] = width_value;\n            if (NULL == overflow_action_suffix || ! *overflow_action_suffix)\n               dt.col_overflow_action[dt.nb_columns] = 'e';\n            else if ('t' == *overflow_action_suffix || 'w' == *overflow_action_suffix || 'e' == *overflow_action_suffix)\n               dt.col_overflow_action[dt.nb_columns] = *overflow_action_suffix;\n            else if (0 == dt.nb_columns) {\n               bCase = 0;\n               dt.COL_SEPARATOR = strdup(width_str);\n               bFoundSeparator = 1;\n               dt.nb_columns++;\n               break;      \n            }\n            else {\n               \/\/ allowed overflow suffix is one of t, w or e;\n               fprintf(stderr, \"invalid overflow action suffix [%c]; it must be one of w (wrap-around), t (truncation without ellipsis) or e (truncation with ellipsis), exiting ...n\", *overflow_action_suffix);\n               free(tmp_str);\n               ret = -1;\n               goto quit;\n            }\n            if ('e' == dt.col_overflow_action[dt.nb_columns] && width_value < dt.len_ellipsis) {\n               fprintf(stderr, "column [%d] has maximum width [%ld] and requests a truncation with ellipsis [%s] but a minimum width of [%d] characters is necessary for this, assuming that minimum widthn", dt.nb_columns, width_value, dt.ELLIPSIS, dt.len_ellipsis);\n               dt.max_col_widths[dt.nb_columns] = dt.len_ellipsis;\n            }\n            dt.nb_columns++;\n            next_tok_iter = NULL;\n         }\n         free(tmp_str);\n      }\n   }\n   else\n      \/\/ draft output, i.e. default column width, possible truncation, optimal column widths listed at the end;\n      bCase = 0;\n\n   switch (bCase) {\n      case 0: ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], sql_stmt.str_value.str, select_callback_draft, &dt, &errorMessg);\n              break;\n      case 1: if (!bFoundSeparator)\n                 \/\/ use default separator\n                 dt.COL_SEPARATOR = "|";\n              ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], sql_stmt.str_value.str, select_callback_raw, &dt, &errorMessg);\n              break;\n      case 2: ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], sql_stmt.str_value.str, select_callback_sized, &dt, &errorMessg);\n              break;\n      case 3: ret = sqlite3_exec(sqlite_handles[(int) db_handle.num_value], sql_stmt.str_value.str, select_callback_array, &dt, &errorMessg);\n              break;\n      default: fprintf(stderr, "programming error: did you not forget a case ?n");\n   }\n   if (SQLITE_OK == ret) {\n      dt.bEpilog = 1;\n      0 == bCase ? select_callback_draft(&dt, 0, NULL, NULL) :\n      1 == bCase ? select_callback_raw(&dt, 0, NULL, NULL) :\n      2 == bCase ? select_callback_sized(&dt, 0, NULL, NULL) :\n      3 == bCase ? select_callback_array(&dt, 0, NULL, NULL) :\n      0;\n   }\n   else {\n      fprintf(stderr, "do_sqlite_select, SQL error %s while executing [%s]n", errorMessg, sql_stmt.str_value.str);\n      sqlite3_free(errorMessg);\n   }\nquit:\n   return make_number(ret, result);\n}\n\n\/* these are the exported functions along with their min and max arities; *\/\n   static awk_ext_func_t func_table[] = {\n        {"sqlite_open", do_sqlite_open, 1, 1, awk_false, NULL},\n        {"sqlite_close", do_sqlite_close, 1, 1, awk_false, NULL},\n        {"sqlite_exec", do_sqlite_exec, 6, 2, awk_false, NULL},\n        {"sqlite_select", do_sqlite_select, 4, 2, awk_false, NULL},\n};\n\nstatic awk_bool_t (*init_func)(void) = init_sqlite_handles;\n\n\/* define the dl_load function using the boilerplate macro *\/\n\ndl_load_func(func_table, sqlite_gawk, "")\n<\/pre>\n
Quite the extension ! Sorry for this lengthy listing but there is a lot of stuff going on here.
\nNext, let’s make the awk and the new extension. Here are the incantations:
\n
\npwd
\n\/home\/dmadmin\/dmgawk\/gawk-4.2.1\/extension
\n.\/configure
\nmake
\ncd .libs; gcc -o sqlite_gawk.so -shared sqlite_gawk.o ..\/sqlite3.o -pthread
\n<\/code>
\nThat’s it. As said elsewhere, an additional sudo make install will install the new gawk and its extension to their canonical locations, i.e. \/usr\/local\/bin\/gawk for gawk and \/usr\/local\/lib\/gawk for the extensions. But for the moment, let’s test it; for this, we still need a test gawk script.
\nvi tsqlite.awk<\/p>\n
\n# test program for the sqlite_gawk, interface to sqlite3;\n# Cesare Cervini\n# dbi-services.com\n# 8\/2018\n\n@load \"sqlite_gawk\"\n\nBEGIN {\n   my_db = sqlite_open(\"\/home\/dmadmin\/sqlite-amalgamation-3240000\/test.db\")\n   print \"db opened:\", my_db\n\n   my_db2 = sqlite_open(\"\/home\/dmadmin\/sqlite-amalgamation-3240000\/test.db\")\n   print \"db opened:\", my_db2\n\n   sqlite_close(my_db)\n   sqlite_close(my_db2)\n\n   my_db = sqlite_open(\"\/home\/dmadmin\/sqlite-amalgamation-3240000\/test.db\")\n   print \"db opened:\", my_db\n\n   printf \"n\"\n\n   rc = sqlite_exec(my_db, \"CREATE TABLE IF NOT EXISTS test1(n1 NUMBER, s1 TEXT, s2 CHAR(100))\")\n   print \"return code = \", rc\n\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(100, \"hello1\", \"hello0101\")\")\n   print \"return code = \", rc\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(200, \"hello2\", \"hello0102\")\")\n   print \"return code = \", rc\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(300, \"hello3\", \"hello0103\")\")\n   print \"return code = \", rc\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(400, \"hello4\", \"hello0104\")\")\n   print \"return code = \", rc\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(400, \"hello5 with spaces       \", \"hello0105 with spaces              \")\")\n   print \"return code = \", rc\n   rc = sqlite_exec(my_db, \"INSERT INTO test1(n1, s1, s2) VALUES(400, \"hello6 with spaces        \", \"hello0106   \")\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT * FROM test1\";\n   split(\"\", a_test)\n   print \"sqlite_select(my_db, \" stmt \", 0, a_test)\"\n   rc = sqlite_select(my_db, stmt, 0, a_test)\n   dumparray(\"a_test\", a_test);\n   for (row in a_test) {\n      printf(\"row %d: \", row)\n      for (col in a_test[row])\n         printf(\"  %s = %s\", col, a_test[row][col])\n      printf \"n\"\n   }\n   printf \"n\"\n\n   # print in draft format;\n   stmt = \"SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1\"\n   print \"sqlite_select(my_db, \"\" stmt \"\")\"\n   rc = sqlite_select(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   # print in draft format;\n   stmt = \"SELECT sql FROM sqlite_master ORDER BY tbl_name, type DESC, name\"\n   print \"sqlite_select(my_db, \"\" stmt \"\", \"100\")\"\n   rc = sqlite_select(my_db, stmt , \"100\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   # print in draft format;\n   stmt = \"SELECT * FROM test1\"\n   print \"sqlite_select(my_db, \" stmt \")\"\n   rc = sqlite_select(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   # print in raw format with non default separator;\n   stmt = \"SELECT * FROM test1\"\n   print \"sqlite_select(my_db, \" stmt \", \"||\")\"\n   rc = sqlite_select(my_db, stmt, \"||\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   # now that we know the needed column widths, let's used them;\n   # trailing spaces are removed to compact the column somewhat;\n   stmt = \"SELECT * FROM test1\" \n   print \"sqlite_select(my_db, \" stmt \", \"3 18 21\")\"\n   rc = sqlite_select(my_db, stmt, \"3 18 21\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   # print in raw format, with default | separator;\n   stmt = \"SELECT * FROM test1\"\n   print \"sqlite_select(my_db, \" stmt \", \"\")\"\n   rc = sqlite_select(my_db, stmt, \"\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"INSERT INTO test1(n1, s1, s2) VALUES(400, \"hello6-with-spaces        \", \"hello0106-12345\")\" \n   print \"sqlite_exec(my_db, \" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT * FROM test1\"\n   print \"sqlite_select(my_db, \" stmt \", \"2e 15e 10w\")\"\n   rc = sqlite_select(my_db, stmt, \"2e 15e 10w\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT count(*) FROM test1\"\n   print \"sqlite_select(my_db,\" stmt \")\"\n   rc = sqlite_select(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"DELETE FROM test1\"\n   print \"sqlite_exec(my_db, \" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT count(*) FROM test1\"\n   print \"sqlite_select(my_db,\" stmt \")\"\n   rc = sqlite_select(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   rc = sqlite_exec(my_db, \"CREATE TABLE IF NOT EXISTS test_with_blob(n1 NUMBER, my_blob BLOB)\")\n   print \"return code = \", rc\n\n   rc = sqlite_exec(my_db, \"DELETE FROM test_with_blob\")\n   print \"return code = \", rc\n\n   stmt = \"INSERT INTO test_with_blob(n1, my_blob) VALUES(1, readfile(\"gawk-4.2.1.tar.gz\"))\" \n   print \"sqlite_exec(my_db,\" stmt \")\"\n   #rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   \n   stmt = \"SELECT n1, writefile('yy' || rowid, my_blob) FROM test_with_blob\" \n   print \"sqlite_select(my_db, \" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   # file too large, > 3 Gb, fails silently;\n   # do don't do it;\n   # stmt = \"INSERT INTO test_with_blob(n1, my_blob) VALUES(1000, readfile(\"\/home\/dmadmin\/setup_files\/documentum.tar\"))\" \n\n   # this one is OK at 68 Mb;\n   stmt = \"INSERT INTO test_with_blob(n1, my_blob) VALUES(1000, readfile(\"\/home\/dmadmin\/setup_files\/instantclient-basic-linux.x64-12.2.0.1.0.zip\"))\" \n   print \"sqlite_exec(my_db,\" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT n1, writefile('\"yy' || rowid || '\"', my_blob) FROM test_with_blob where n1 = 1000\" \n   print \"sqlite_select(my_db, \" stmt \")\"\n   #rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"INSERT INTO test_with_blob(n1, my_blob) VALUES(5000, readfile('\/home\/dmadmin\/dmgawk\/gawk-4.2.1\/extension\/sqlite_gawk.c'))\" \n   print \"sqlite_exec(my_db,\" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"UPDATE test_with_blob set my_blob = readfile('\/home\/dmadmin\/dmgawk\/gawk-4.2.1\/extension\/sqlite_gawk.c') where n1 = 1000\" \n   print \"sqlite_exec(my_db,\" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   # xx is a 999'000'000 bytes file; the import using a memory buffer with that size takes some time to complete;\n   # the incremental blob I\/Os below seem faster;\n   # to make one, use: dd if=\/dev\/zero of=xx count=990 bs=1000000\n   stmt = \"UPDATE test_with_blob set my_blob = readfile('\/home\/dmadmin\/dmgawk\/xx') where n1 = 1000\" \n   print \"sqlite_exec(my_db,\" stmt \")\"\n   rc = sqlite_exec(my_db, stmt)\n   print \"return code = \", rc\n   printf \"n\"\n\n   # this is needed to enforce typing of a_array to array;\n   # split(\"\", a_test)\n   delete(a_test)\n   print \"sqlite_select(db, \"select rowid from test_with_blob where n1 = 1000 limit 1\", 0, a_test)\"\n   sqlite_select(db, \"select rowid from test_with_blob where n1 = 1000 limit 1\", 0, a_test)\n   print \"after getting blob\"\n   dumparray(\"a_test\", a_test)\n   print \"sqlite_exec(my_db, 'main', 'test_with_blob', 'my_blob', \" a_test[0][\"rowid\"] \", writefile(~\/dmgawk\/my_blob_\" a_test[0][\"rowid\"] \"))\"\n   rc = sqlite_exec(my_db, \"main\", \"test_with_blob\", \"my_blob\", a_test[0][\"rowid\"], \"writefile(\/home\/dmadmin\/dmgawk\/my_blob_\" a_test[0][\"rowid\"] \")\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   #print \"sqlite_exec(my_db, 'main', 'test_with_blob', 'my_blob', \" a_test[0][\"rowid\"] \", readfile(\/home\/dmadmin\/setup_files\/documentum.tar))\"\n   #rc = sqlite_exec(my_db, \"main\", \"test_with_blob\", \"my_blob\", a_test[0][\"rowid\"], \"readfile(\/home\/dmadmin\/setup_files\/documentum.tar)\")\n   #rc = sqlite_exec(my_db, \"main\", \"test_with_blob\", \"my_blob\", a_test[0][\"rowid\"], \"readfile(\/home\/dmadmin\/setup_files\/patch.bin)\")\n   rc = sqlite_exec(my_db, \"main\", \"test_with_blob\", \"my_blob\", a_test[0][\"rowid\"], \"readfile(\/home\/dmadmin\/dmgawk\/xx)\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT n1, hex(my_blob) FROM test_with_blob where n1 = 2000 limit 1\" \n   stmt = \"SELECT n1, my_blob FROM test_with_blob where n1 = 2000 limit 1\" \n   stmt = \"SELECT n1, substr(my_blob, 1) FROM test_with_blob where n1 = 2000 limit 1\" \n   rc = sqlite_select(my_db, stmt)\n   rc = sqlite_select(my_db, stmt, \"10 100w\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   stmt = \"SELECT n1, replace(my_blob, 'n', '\\n') as 'noLF' FROM test_with_blob where n1 = 5000 limit 2\" \n   print \"sqlite_select(my_db,\" stmt \", 10, 100w)\"\n   rc = sqlite_select(my_db, stmt, \"10, 100w\")\n   print \"return code = \", rc\n   printf \"n\"\n\n   sqlite_close(my_db)\n\n   exit(0)\n}\n\nfunction dumparray(name, array, i) {\n   for (i in array)\n      if (isarray(array[i]))\n         dumparray(name \"[\"\" i \"\"]\", array[i])\n      else\n         printf(\"%s[\"%s\"] = %sn\", name, i, array[i])\n      }\n<\/pre>\n
To execute the test:
\n
\nAWKLIBPATH=gawk-4.2.1\/extension\/.libs gawk-4.2.1\/gawk -f tsqlite.awk
\ndb opened: 0
\ndb opened: 0
\ndb opened: 0
\n 
\nreturn code =  0
\nreturn code =  0
\nreturn code =  0
\nreturn code =  0
\nreturn code =  0
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1, 0, a_test)
\n6 rows selected
\na_test[\"0\"][\"n1\"] = 100
\na_test[\"0\"][\"s1\"] = hello1
\na_test[\"0\"][\"s2\"] = hello0101
\na_test[\"1\"][\"n1\"] = 200
\na_test[\"1\"][\"s1\"] = hello2
\na_test[\"1\"][\"s2\"] = hello0102
\na_test[\"2\"][\"n1\"] = 300
\na_test[\"2\"][\"s1\"] = hello3
\na_test[\"2\"][\"s2\"] = hello0103
\na_test[\"3\"][\"n1\"] = 400
\na_test[\"3\"][\"s1\"] = hello4
\na_test[\"3\"][\"s2\"] = hello0104
\na_test[\"4\"][\"n1\"] = 400
\na_test[\"4\"][\"s1\"] = hello5 with spaces
\na_test[\"4\"][\"s2\"] = hello0105 with spaces
\na_test[\"5\"][\"n1\"] = 400
\na_test[\"5\"][\"s1\"] = hello6 with spaces
\na_test[\"5\"][\"s2\"] = hello0106
\nrow 0:   n1 = 100  s1 = hello1  s2 = hello0101
\nrow 1:   n1 = 200  s1 = hello2  s2 = hello0102
\nrow 2:   n1 = 300  s1 = hello3  s2 = hello0103
\nrow 3:   n1 = 400  s1 = hello4  s2 = hello0104
\nrow 4:   n1 = 400  s1 = hello5 with spaces         s2 = hello0105 with spaces
\nrow 5:   n1 = 400  s1 = hello6 with spaces          s2 = hello0106
\n 
\nsqlite_select(my_db, \"SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1\")
\nname
\n--------
\ntest
\ntest1
\ntest_...
\n3 rows selected
\n1 columns displayed
\n 
\nOptimum column widths
\n=====================
\nfor query: SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1
\nname       14
\nreturn code =  0
\n 
\nsqlite_select(my_db, \"SELECT sql FROM sqlite_master ORDER BY tbl_name, type DESC, name\", \"100\")
\nsql
\n----------------------------------------------------------------------------------------------------
\nCREATE TABLE test(a1 number)
\nCREATE TABLE test1(n1 NUMBER, s1 TEXT, s2 CHAR(100))
\nCREATE TABLE test_with_blob(n1 NUMBER, my_blob BLOB)
\n3 rows selected
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1)
\nn1        s1        s2
\n--------  --------  --------
\n100       hello1    hello...
\n200       hello2    hello...
\n300       hello3    hello...
\n400       hello4    hello...
\n400       hello...  hello...
\n400       hello...  hello...
\n6 rows selected
\n3 columns displayed
\n 
\nOptimum column widths
\n=====================
\nfor query: SELECT * FROM test1
\nn1       3
\ns1       18
\ns2       21
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1, \"||\")
\nn1      ||s1      ||s2
\n--------||--------||--------
\n100     ||hello1  ||hello...
\n200     ||hello2  ||hello...
\n300     ||hello3  ||hello...
\n400     ||hello4  ||hello...
\n400     ||hello...||hello...
\n400     ||hello...||hello...
\n6 rows selected
\n3 columns displayed
\n 
\nOptimum column widths
\n=====================
\nfor query: SELECT * FROM test1
\nn1       3
\ns1       18
\ns2       21
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1, \"3 18 21\")
\nn1   s1                  s2
\n---  ------------------  ---------------------
\n100  hello1              hello0101
\n200  hello2              hello0102
\n300  hello3              hello0103
\n400  hello4              hello0104
\n400  hello5 with spaces  hello0105 with spaces
\n400  hello6 with spaces  hello0106
\n6 rows selected
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1, \"\")
\nn1|s1|s2
\n100|hello1|hello0101
\n200|hello2|hello0102
\n300|hello3|hello0103
\n400|hello4|hello0104
\n400|hello5 with spaces       |hello0105 with spaces
\n400|hello6 with spaces        |hello0106
\n6 rows selected
\nreturn code =  0
\n 
\nsqlite_exec(my_db, INSERT INTO test1(n1, s1, s2) VALUES(400, \"hello6-with-spaces        \", \"hello0106-12345\"))
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT * FROM test1, \"2e 15e 10w\")
\ncolumn [0] has maximum width [2] and requests a truncation with ellipsis [...] but a minimum width of [3] characters is necessary for this, assuming that minimum width
\nn1   s1               s2
\n---  ---------------  ----------
\n100  hello1           hello0101
\n200  hello2           hello0102
\n300  hello3           hello0103
\n400  hello4           hello0104
\n400  hello5 with ...  hello0105
\n                      with space
\n                      s
\n400  hello6 with ...  hello0106
\n400  hello6-with-...  hello0106-
\n                      12345
\n7 rows selected
\nreturn code =  0
\n 
\nsqlite_select(my_db,SELECT count(*) FROM test1)
\ncount(*)
\n--------
\n7
\n1 rows selected
\n1 columns displayed
\n 
\nOptimum column widths
\n=====================
\nfor query: SELECT count(*) FROM test1
\ncount(*)       8
\nreturn code =  0
\n 
\nsqlite_exec(my_db, DELETE FROM test1)
\nreturn code =  0
\n 
\nsqlite_select(my_db,SELECT count(*) FROM test1)
\ncount(*)
\n--------
\n0
\n1 rows selected
\n1 columns displayed
\n 
\nOptimum column widths
\n=====================
\nfor query: SELECT count(*) FROM test1
\ncount(*)       8
\nreturn code =  0
\n 
\nreturn code =  0
\nreturn code =  0
\nsqlite_exec(my_db,INSERT INTO test_with_blob(n1, my_blob) VALUES(1, readfile(\"gawk-4.2.1.tar.gz\")))
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT n1, writefile('yy' || rowid, my_blob) FROM test_with_blob)
\nreturn code =  0
\n 
\nsqlite_exec(my_db,INSERT INTO test_with_blob(n1, my_blob) VALUES(1000, readfile(\"\/home\/dmadmin\/setup_files\/instantclient-basic-linux.x64-12.2.0.1.0.zip\")))
\nreturn code =  0
\n 
\nsqlite_select(my_db, SELECT n1, writefile('\"yy' || rowid || '\"', my_blob) FROM test_with_blob where n1 = 1000)
\nreturn code =  0
\n 
\nsqlite_exec(my_db,INSERT INTO test_with_blob(n1, my_blob) VALUES(5000, readfile('\/home\/dmadmin\/dmgawk\/gawk-4.2.1\/extension\/sqlite_gawk.c')))
\nreturn code =  0
\n 
\nsqlite_exec(my_db,UPDATE test_with_blob set my_blob = readfile('\/home\/dmadmin\/dmgawk\/gawk-4.2.1\/extension\/sqlite_gawk.c') where n1 = 1000)
\nreturn code =  0
\n 
\nsqlite_exec(my_db,UPDATE test_with_blob set my_blob = readfile('\/home\/dmadmin\/dmgawk\/xx') where n1 = 1000)
\nreturn code =  0
\n 
\nsqlite_select(db, \"select rowid from test_with_blob where n1 = 1000 limit 1\", 0, a_test)
\n1 rows selected
\nafter getting blob
\na_test[\"0\"][\"rowid\"] = 1
\nsqlite_exec(my_db, 'main', 'test_with_blob', 'my_blob', 1, writefile(~\/dmgawk\/my_blob_1))
\nreturn code =  0
\n 
\nsqlite_exec(my_db, 'main', 'test_with_blob', 'my_blob', 1, readfile(\/home\/dmadmin\/setup_files\/documentum.tar))
\nreturn code =  0
\n 
\nsqlite_select(my_db)
\nreturn code =  0
\n 
\nsqlite_select(my_db,SELECT n1, replace(my_blob, 'n', 'n') as 'noLF' FROM test_with_blob where n1 = 5000 limit 2, 10, 100w)
\nn1          noLF
\n----------  ----------------------------------------------------------------------------------------------------
\n5000        \/*n * sqlite-gawk.c - an interface to sqlite() library;n * Cesare Cervinin * dbi-services.comn *
\n             8\/2018n*\/n#ifdef HAVE_CONFIG_Hn#include n#endifnn#include n#include n#include n#include n#include nn#include n#inc
\n            lude nn#include \"gawkapi.h\"nn\/\/ extension;n#include n#include n#
\n            include n#include n#include n#include n#inclu
\n            de n#include  nn#include \"gettext.h\"n#define _(msgid)  gettext(msgid)n#defi
\n            ne N_(msgid) msgidnnstatic const gawk_api_t *api;   \/* for convenience macros to work *\/nstatic a
\n            wk_ext_id_t ext_id;nstatic const char *ext_version = \"an interface to sqlite3: version 1.0\";nnint
\n             plugin_is_GPL_compatible;nn\/* internal structure and variables *\/n\/*ninternally stores the db h
\n...
\n            c)(void) = init_sqlite_handles;nn\/* define the dl_load function using the boilerplate macro *\/nn
\n            dl_load_func(func_table, sqlite_gawk, \"\")nn
\n1 rows selected
\nreturn code =  0
\n<\/code>
\nThat was a very long second part. If you are still there, please turn now to part Part III<\/a> for some explanation of all this.<\/p>\n","protected":false},"excerpt":{"rendered":"
Welcome to part II of a three-part article on extending gawk with a SQLite binding. Part I is here. Part II is followed by Part III, which give some explanations for the code presented here and shows how to use the extension with a stress test. Here, I’ll list the source code of the extension […]<\/p>\n","protected":false},"author":40,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368,525],"tags":[],"type_dbi":[],"class_list":["post-11596","post","type-post","status-publish","format-standard","hentry","category-development-performance","category-enterprise-content-management"],"acf":[],"yoast_head":"\nA SQLite extension for gawk (part II) - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQLite extension for gawk (part II)\" \/>\n<meta property=\"og:description\" content=\"Welcome to part II of a three-part article on extending gawk with a SQLite binding. Part I is here. Part II is followed by Part III, which give some explanations for the code presented here and shows how to use the extension with a stress test. Here, I’ll list the source code of the extension […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-28T20:21:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-24T07:26:17+00:00\" \/>\n<meta name=\"author\" content=\"Middleware Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Middleware Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"45 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/\"},\"author\":{\"name\":\"Middleware Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d8563acfc6e604cce6507f45bac0ea1\"},\"headline\":\"A SQLite extension for gawk (part II)\",\"datePublished\":\"2018-09-28T20:21:10+00:00\",\"dateModified\":\"2025-10-24T07:26:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/\"},\"wordCount\":341,\"commentCount\":0,\"articleSection\":[\"Development & Performance\",\"Enterprise content management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/\",\"name\":\"A SQLite extension for gawk (part II) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-09-28T20:21:10+00:00\",\"dateModified\":\"2025-10-24T07:26:17+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d8563acfc6e604cce6507f45bac0ea1\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-sqlite-extension-for-gawk-part-ii\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQLite extension for gawk (part II)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d8563acfc6e604cce6507f45bac0ea1\",\"name\":\"Middleware Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g\",\"caption\":\"Middleware Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/middleware-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"A SQLite extension for gawk (part II) - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/","og_locale":"en_US","og_type":"article","og_title":"A SQLite extension for gawk (part II)","og_description":"Welcome to part II of a three-part article on extending gawk with a SQLite binding. Part I is here. Part II is followed by Part III, which give some explanations for the code presented here and shows how to use the extension with a stress test. Here, I’ll list the source code of the extension […]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/","og_site_name":"dbi Blog","article_published_time":"2018-09-28T20:21:10+00:00","article_modified_time":"2025-10-24T07:26:17+00:00","author":"Middleware Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Middleware Team","Est. reading time":"45 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/"},"author":{"name":"Middleware Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1"},"headline":"A SQLite extension for gawk (part II)","datePublished":"2018-09-28T20:21:10+00:00","dateModified":"2025-10-24T07:26:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/"},"wordCount":341,"commentCount":0,"articleSection":["Development & Performance","Enterprise content management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/","url":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/","name":"A SQLite extension for gawk (part II) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-09-28T20:21:10+00:00","dateModified":"2025-10-24T07:26:17+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-sqlite-extension-for-gawk-part-ii\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A SQLite extension for gawk (part II)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d8563acfc6e604cce6507f45bac0ea1","name":"Middleware Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ddcae7ba0f9d1a0e7ae707f0e689e4a9c95bb48ec49c8e6d9cc86d43f4121cb6?s=96&d=mm&r=g","caption":"Middleware Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/middleware-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11596"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11596\/revisions"}],"predecessor-version":[{"id":41169,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11596\/revisions\/41169"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11596"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}