/*! \file db.pgc -   Database specific code for the cimsd daemon
 *
 *  \brief      This module contains utility functions for DB access
 *
 */

#include "cimsd.h"
#include "db.h"
#include <stdlib.h>
#include <unistd.h>

static char __attribute((unused)) rcsid[] = "$Id: db.pgc,v 1.93 2013/06/19 13:11:50 leif Exp $";


EXEC SQL INCLUDE sql3types;
#define SQL3_TEXT    -705    ///< This seems to be the type value for the text type
#define SQL3_OID      -26    ///< This seems to be the type value for OID
#define SQL3_BIGINT   -20    ///< This seems to be the type value for BIGINT
#define SQL3_TIMESTAMP_WITH_TIMEZONE  -1184   ///< This seems to be the type value for TIMESTAMP WITH TIMEZONE


#define MAX_IDENTS  20    ///< Max number of identifiers allowed
/// Array of identifier structs for registered db variables
static struct identifier_st identifier[MAX_IDENTS] =
{ // Removed static entris 080326
  { NULL, SQL_ID_NONE, { NULL } },*/
};

static bool inclRowIndex = false; ///< Indicates whether to include (auto) row indexing or not

static pthread_mutex_t dbSqlMtx = PTHREAD_MUTEX_INITIALIZER;
static pthread_mutex_t dbSelectMtx = PTHREAD_MUTEX_INITIALIZER;


/////////////////////////////////////////////////////////////////
//
//  Database Access
//

void showErrorLineFileStmt( int what, struct sqlca_t *sqlstat, int lineno, const char *file, const char *stmt )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \param   lineno - line number where error occurred
/// \param   file   - module where error occurred
/// \param   stmt   - SQL statement that caused the error
/// \return  void
{
  int  len  = sqlca.sqlerrm.sqlerrml + 90;
  char *buf = NULL;

  if( file )
    len += strlen( file );
  buf = (char *) malloc( len );

  sprintf( buf, "C:SQL\t%ld:%s\t%d:%s", sqlca.sqlcode, sqlca.sqlstate, sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc );
  if( lineno > 0 ) {
    char no[40];
    sprintf( no, "\tline:%d", lineno );
    strcat( buf, no );
  }
  if( file ) {
    char *name = (char *) malloc( strlen( file ) + 10 );
    if( name ) {
      sprintf( name, "\tfile:%s", file );
      strcat( buf, name );
      free( name );
    }
  }

  char org[4];
  strcpy( org, " * ");
  if (sqlca.sqlcode > 0)
    strcpy( org, "wrn" );
  if (sqlca.sqlcode < 0)
    strcpy( org, "DER" );
  if( DebugLevel > 4 || sqlca.sqlcode < 0 || (DebugLevel > 3 && sqlca.sqlcode != 0) )
    Logging( 0, "Sql", org, "%s (stmt=%s)\n", buf, stmt ? stmt : "?" );
#ifndef SKIP_CIMS_COMM
  if( what > 0 )
    AppSendData( appSock, buf );
#endif // SKIP_CIMS_COMM
  free( buf );
}

void showErrorLineFile( int what, struct sqlca_t *sqlstat, int lineno, const char *file )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \param   lineno - line number where error occurred
/// \param   file   - module where error occurred
/// \return  void
{
  showErrorLineFileStmt( what, sqlstat, lineno, file, NULL );
}

void showErrorLineStmt( int what, struct sqlca_t *sqlstat, int lineno, const char *stmt )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \param   lineno - line number where error occurred
/// \return  void
{
  showErrorLineFileStmt( what, sqlstat, lineno, NULL, stmt );
}

void showErrorLine( int what, struct sqlca_t *sqlstat, int lineno )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \param   lineno - line number where error occurred
/// \return  void
{
  showErrorLineFileStmt( what, sqlstat, lineno, NULL, NULL );
}

void Db_showErrorStmt( int what, struct sqlca_t *sqlstat, const char *stmt )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \return  void
{
  showErrorLineStmt( what, sqlstat, -1, stmt );
}

void Db_showError( int what, struct sqlca_t *sqlstat )
//
/// \brief   Report any errors back
/// \param   what - 0 = don't send it back, > 0 send it
/// \return  void
{
  showErrorLineStmt( what, sqlstat, -1, NULL );
}

//
/// \brief   Start an appropriate Transaction, possible retry if it fails
/// \param   thisDbConn - database handle
/// \param
/// \return  SQLErr - Ok or failure code
//
static SQLErr Db_StartTrans( const char *thisDbConn, int line, const char *trType )
{
  StartF( "Sql", __func__ );
  EXEC SQL BEGIN DECLARE SECTION;
  const char *emb_thisDbConn = thisDbConn;
  EXEC SQL END DECLARE SECTION;
  int retcode = SQLErr_Error;
  bool readonly;
  char trans[20];

  sscanf( trType, "%19s", trans );
  sscanf( trans, "%19[A-Z]", trans );
  Logging( 6, "Sql", " . ", "Got: %s in line %d for %s\n", trans, line, thisDbConn );
  if ( (readonly = strcmp( trans, "READONLYTRANS" ) == 0) || strcmp( trans, "READWRITETRANS" ) == 0 ) {
    int retries;
    for( retries = 0; retries < 3; retries ++ ) {
      if ( readonly )
        EXEC SQL AT :emb_thisDbConn START TRANSACTION READ ONLY;
      else
        EXEC SQL AT :emb_thisDbConn START TRANSACTION READ WRITE;
      Logging( 5, "Sql", " . ", "Executed 'START TRANSACTION %s' on %s, result = %d\n",
                  readonly ? "READ ONLY" : "READ WRITE", emb_thisDbConn, (int) sqlca.sqlcode );
      if ( ! sqlca.sqlcode ) {
        retcode = SQLErr_OK;
        break;
      }
      else {
        Logging( 0, "Sql", "***", "Start transaction on %s failed%s\n", emb_thisDbConn, retries < 3 ? ", retrying" : "" );
        showErrorLine( 0, &sqlca, line );
        EXEC SQL AT :emb_thisDbConn ROLLBACK;
        retcode = SQLErr_Error;
      }
      Db_Close( emb_thisDbConn );
      sleep( 1 );
      Db_Open( emb_thisDbConn, arguments.dbHost, arguments.dbPort, arguments.dbName,
              arguments.dbUser, arguments.dbPass );
    }
  }
  RETURNV( retcode );
}


char *NextSql( char *stmt, char **q )
{
  StartF( "Sql", __func__ );
  char *p, *pe;
  char  prev = '\0';
  bool look = true;
  bool found = false;
  if ( stmt )
    *q = p = stmt;
  else
    p = ++(*q);
  for ( ; **q; (*q)++ ) {
    if ( **q == '\'' && prev != '\\' )
      look = ! look;
    if ( look && **q == ';' ) {
      **q = '\0';
      found = true;
      break;
    }
    prev = **q;
  }
  if ( found ) {
    while ( ( *p != '\0' ) && ( *p == ' ' || *p == '\t' || *p == '\n' || *p == '\r' ) )
      p++;
    pe = p + strlen( p ) - 1;
    while ( ( pe >= p ) && ( *pe == ' ' || *pe == '\t' || *pe == '\n' || *pe == '\r' ) ) {
      *pe = '\0';
      pe--;
    }
    RETURNV( p )
  } else
    RETURNV( NULL );
}

int Db_ExecuteSql( const char *ThisDbConn, const int RunTrans, struct sqlca_t *sqlstat, const char *sql, ... )
//
/// \brief   Execute one or more 'not-select' SQL statements
///
///          The sql statements are build using the sql parameter and any additional
/// parameters. The resulting statements are 'sent' one by one to the database using an already
/// opened link.
/// \param   RunTrans - run the sql in a transaction
/// \param   sql:  the statements
/// \param   ...:  open ended (stdargs) arguments
/// \return  int:  >=0: ok, number rows processed, -1: error - details in cimsd.log
{
  StartF( "Sql", __func__ );
  EXEC SQL BEGIN DECLARE SECTION;
  char *stmt;
  char *pstr = NULL;
  const char *_thisDbConn = ThisDbConn;
  EXEC SQL END DECLARE SECTION;

  int n;
  int  rescode = 0;
  size_t size = 100 + strlen( sql );
  va_list ap;
  char *saveptr;

  if( (stmt = (char *)malloc (size)) == NULL ) {
    LogErrNo( "Sql", "malloc() error" );
    free( stmt );
    RETURNV( -1 );
  }
  pthread_mutex_lock( &dbSqlMtx );
  while (1) {
    /* Try to print in the allocated space. */
    va_start(ap, sql);
    n = vsnprintf( stmt, size, sql, ap );
    va_end(ap);
    /* If that worked, return the string. */
    if( n > -1 && n < size )
      break;
    /* Else try again with more space. */
    if( n > -1 )    /* glibc 2.1 */
      size = n + 1; /* precisely what is needed */
    else           /* glibc 2.0 */
      size *= 2;  /* twice the old size */
    if ((stmt = (char *)realloc( stmt, size )) == NULL ) {
      LogErrNo( "Sql", "realloc() error" );
      free( stmt );
      pthread_mutex_unlock( &dbSqlMtx );
      RETURNV( -1 );
    }
  }
  pthread_mutex_unlock( &dbSqlMtx );

  {
    int l = strlen( stmt );
    if ( l > 0 && ';' != stmt[ l - 1 ] )
      strcat( stmt, ";" );
  }

  Logging( 4, "ESql", " * ", "%s, Sql-stmt: %s\n", ThisDbConn, stmt ); // Log level increased from 3 to 4 080314

  if ( RunTrans != NOTRUNTRANS ) {
    Db_StartTrans( _thisDbConn, __LINE__, "READWRITETRANS" );
    rescode = sqlca.sqlcode;
    if ( rescode ) {
      *sqlstat = sqlca;
      free( stmt );
      RETURNV( -1 );
    }
  }

  int doExecuteSql()
  {
    StartF( "Sql", __func__ );
    //Logging( 3, "ESql", " * ", "%s(): %s, Sql-stmt: %s\n", __func__, ThisDbConn, pstr ); // Log removed 080314
    EXEC SQL AT :_thisDbConn EXECUTE IMMEDIATE :pstr;
    *sqlstat = sqlca;
    int rescode = sqlca.sqlcode;
    if ( rescode < 0 ) {
      Logging( 0, "ESql", "***", "Execute sql stmt immediate failed - doing ROLLBACK\n" );
      Db_showErrorStmt( 0, NULL, pstr );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      RETURNV( -1 );
    }
    if( rescode > 0 ) { /// No rows processed
      rescode = 0;
      Db_showError( 0, NULL );
    } else {
      rescode = sqlca.sqlerrd[2];
      if ( rescode < 1 )
        rescode = 1;
    }
    RETURNV( rescode );
  } // doExecuteSql()

  char *p = NextSql( stmt, &saveptr );
  Logging( 5, "Sql", " . ", "NextSql returned: '%s'\n", p );
  while ( p && *p ) {
      if( pstr )
        free( pstr );
      pstr = (char *)malloc( strlen( p ) + 2 );
      strcpy( pstr, p );
      strcat( pstr, ";" );
      if ( 0 > ( rescode = doExecuteSql() ) )
        break;
      p = NextSql( NULL, &saveptr );
  }
  free( pstr );
  pstr = NULL;
  free( stmt );

  if ( RunTrans != NOTRUNTRANS && rescode >= 0 ) {
    EXEC SQL AT :_thisDbConn COMMIT;
    Logging( 9, "Sql", "{ }", "%s: COMMIT on %s\n", __func__, _thisDbConn );
    if( sqlca.sqlcode ) {
      Logging( 0, "ESql", "***", "Commit after execute immediate failed\n" );
      Db_showError( 0, NULL );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      RETURNV( -1 );
    }
  }

  RETURNV( rescode );
}


/// \brief   Check the DB Table for a field
/// \param   column - name of field to look for
/// \param   table  - what table to search
/// \return  e_id_type - an SQL type of column if found
static e_id_type Check4ColumnInTable( const char *ThisDbConn, const char *column, const char *table )
{
  StartF( "Sql", __func__ );
  EXEC SQL BEGIN DECLARE SECTION;
  char field[200];
  int  fnull;
  int  rescode;
  const char *colName = column;
  const char *tableName = table;
  const char *_thisDbConn = ThisDbConn;
  EXEC SQL END DECLARE SECTION;

  Logging( 5, "Sql", " . ", "Select from table information_schema.columns table_name=%s column_name=%s\n",
                                  tableName, colName );

  EXEC SQL AT :_thisDbConn SELECT data_type
                             INTO :field :fnull
                           FROM information_schema.columns
                           WHERE table_name = :tableName
                           AND   column_name = :colName;
  rescode = sqlca.sqlcode;
  if( rescode < 0 ) {
    Logging( 0, "Sql", "***", "Select from table information_schema.columns failed\n" );
    Db_showError( 0, NULL );
    EXEC SQL AT :_thisDbConn ROLLBACK;
    Db_showError( 0, NULL );
    RETURNV( SQL_ID_ERROR );
  }

  if( ! rescode && ! fnull ) {
    if( strstr( field, "int" ) )
      RETURNV( SQL_ID_INTEGER )
    else if( strstr( field, "char" ) || strstr( field, "text" ) )
      RETURNV( SQL_ID_CHAR )
    else if( strstr( field, "bool" ) )
      RETURNV( SQL_ID_BOOLEAN );
  }

  RETURNV( SQL_ID_NONE );
}

typedef int (*SaveDataCallback)( const char *ThisDbConn, int ctrlid, int MultiUpd, e_sql_type type, char *data, char *sql, const char **table, char **colList, char **valueList, char **where );

/**
 * \brief Generated database connection name, placing result in param ResConnName
 * \param ResConnName will be used for store of resulting connection name
 * \param ResConnNameLen is length of result buffer
 * \param Name is the unique name for this database connection
 */
void DbGenerateConnName(char *ResConnName, int ResConnNameLen, const char *Name)
{
  if(ResConnName && ResConnNameLen > 1)
  {
    snprintf(ResConnName, ResConnNameLen - 1, "%s_%05d_%08lx", Name, Util_getpid(), pthread_self());
    ResConnName[ResConnNameLen - 1] = '\0';
  }
}

///
/// \brief   Open a database (\ref DBNAME).
/// \return  void
///
SQLErr Db_Open(const char *ThisDbConn, const char *Host, int Port, const char *name, const char *User, const char *Pass)
{
  SQLErr Ok = SQLErr_OK;
  char *_dbname = NULL;
  EXEC SQL BEGIN DECLARE SECTION;
  const char *dbname = _dbname;
  const char *dbuser = User;
  const char *_thisDbConn = ThisDbConn;
  EXEC SQL END DECLARE SECTION;

  StartF( "Sql", __func__ );
// #include "src/interfaces/ecpg/ecpglib/extern.h"

  // Create db name string
  int Len = strlen(Host) + strlen(name) + /* port */ 5 + /* Div */ 5;
  _dbname = (char *) malloc(Len);
  if(!_dbname)
  {
    Logging(0, "Sql", "***", "%s: Out of memory\n", __func__);
    Ok = SQLErr_NotOpen;
  }
  else
  {
    snprintf(_dbname, Len - 1, "%s@%s:%d", name, Host, Port);
    dbname = _dbname;

    Logging(3, "Sql", " * ", "%s: Opening db-connection: %s\n", __func__, ThisDbConn);
    Logging(5, "Sql", " * ", "%s: db/user: %s/%s\n", __func__, dbname, dbuser);

    EXEC SQL CONNECT TO :dbname AS :_thisDbConn USER :dbuser;
    if( sqlca.sqlcode ) {
      Logging( 0, "Sql", "***", "%s%s(): Cannot open db-connection: %s%s\n", COLOUR_RED, __func__, ThisDbConn, COLOUR_NORMAL );
      Db_showError( 0, NULL );
      RETURNV( SQLErr_NotOpen );
    }
    EXEC SQL AT :_thisDbConn SET AUTOCOMMIT TO OFF;
    if( sqlca.sqlcode ) {
      Logging( 0, "Sql", "***", "Cannot deactivate autocommit on db-connection: %s\n", ThisDbConn );
      Db_showError( 0, NULL );
      Db_Close( ThisDbConn );
      Db_showError( 0, NULL );
      RETURNV( SQLErr_NotOpen );
    }
    free(_dbname);
    // This is very PostgreSQL specific !!
    Logging( 4, "PGsq", "opn", "%s(): PGsql backend - %d @ %s\n", __func__, PQbackendPID( ECPGget_PGconn( ThisDbConn ) ), ThisDbConn );
  }

  RETURNV(Ok);
}

SQLErr Db_Close(const char *ThisDbConn)
///
/// \brief   Close a database
/// \return  void
{
  StartF( "Sql", __func__ );
  EXEC SQL BEGIN DECLARE SECTION;
  const char *_thisDbConn = ThisDbConn;
  EXEC SQL END DECLARE SECTION;

  Logging( 3, "Sql", " * ", "%s: Closing db-connection: %s\n", __func__, ThisDbConn );
  EXEC SQL DISCONNECT :_thisDbConn;
  if( sqlca.sqlcode ) {
    Logging( 0, "Sql", "***", "Cannot close db-connection: %s\n", ThisDbConn );
    Db_showError( 0, NULL );
    RETURNV( SQLErr_NotOpen );
  }
  RETURNV( SQLErr_OK );
}

static SQLErr doSQLExec( const char *thisDbConn, char *paramStmt, int *rCount, SQLCallback func, const void *callbackParam )
{
  StartF( "Sql", __func__ );

  EXEC SQL BEGIN DECLARE SECTION;
  const char *_thisDbConn = thisDbConn;
        char *descname = NULL;
        char *execquery = NULL;
  char *stmt = paramStmt;
  int count;
  char name[120];
  int type,length,precision,scale;
  int indicator;
  bool boolvar;
  int  intvar;
  int intvars[100];
  float floatvar;
  double doublevar;
  char stringvar[4096];
  char *stringvars[100] = { 0 };
  float floatvars[100];
  double doublevars[100];
  EXEC SQL END DECLARE SECTION;

  int  line;
  int  inx;
  int  index;
  bool ok = true;
  bool allocExecDesc = false;
  int  localDebugLevel = 3;
  int  done = 0;
  int  retcode = SQLErr_OK;
  char cname[101][120];
  int  rowCount = 0;
  const char *notused1;
  char *notused2, *notused3, *notused4;

  _SQLColInfo *columns = NULL;

  Logging( 4, "Sql", " . ", "doSQLExec: stmt=%s\n", stmt );

  bool readWrite = strstr( stmt, "update" ) || strstr( stmt, "UPDATE" )
                || strstr( stmt, "insert" ) || strstr( stmt, "INSERT" )
                || strstr( stmt, "delete" ) || strstr( stmt, "DELETE" )
                || strstr( stmt, "create temp" ) || strstr( stmt, "CREATE TEMP" )
                || strstr( stmt, "drop view" ) || strstr( stmt, "DROP VIEW" );
  if( readWrite && strstr( stmt, "FOR UPDATE" ) )
    readWrite = false;
  if( readWrite ) {
    struct sqlca_t sqlstat;

    if ( (rowCount = Db_ExecuteSql( thisDbConn, NOTRUNTRANS, &sqlstat, stmt )) < 0 ) {  //  NB!! DELETEing and UPDATEing 0 rows are OK
      Logging( 0, "Sql", "***", "Executing the SQL failed (%ld)\n", sqlstat.sqlcode ); // Changed %d -> %ld 080407
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      if( rCount )
        *rCount = sqlstat.sqlcode;  //  Hack! Return DB error code in '# of rows'
      RETURNV( SQLErr_Error );
    }

  }
  else {  // begin of select

    int res;
    if( (res = pthread_mutex_trylock( &dbSelectMtx )) == EBUSY || (res != 0 && errno == EBUSY) ) {
      Logging( 0, "Sql", "ERR", "%s%s(%d): Select Mutex already locked, this will probably DEADLOCK!!%s\n", COLOUR_RED, __func__, __LINE__, COLOUR_NORMAL );
      // pthread_mutex_lock( &dbSelectMtx );
    }

    if( strstr( stmt, "checkmark" ) != 0 )
      Logging( 4, "Sql", "...", "%s(): Got a checkmark SELECT\n", __func__ );

    Logging( 6, "Sql", " . ", "doSQLExec: @ %s BEGIN stmt=%s\n", _thisDbConn, stmt );
    if( descname )
      free( descname );
    descname = (char *) malloc( strlen( thisDbConn ) + 10 );
    if( descname ) {
      strcpy( descname, thisDbConn );
      strcat( descname, "_desc" );
    }
    Logging( 6, "Sql", " . ", "doSQLExec: @ %s BEGIN descname=%s\n", _thisDbConn, descname );
    if( execquery )
      free( execquery );
    execquery = (char *) malloc( strlen( thisDbConn ) + 10 );
    if( execquery ) {
      strcpy( execquery, thisDbConn );
      strcat( execquery, "_query" );
    }
    Logging( 6, "Sql", " . ", "%s(%d): @ %s BEGIN execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );

    // EXEC SQL SET CONNECTION :_thisDbConn;

    if( ! allocExecDesc ) {
      Logging( 6, "Sql", " . ", "doSQLExec: @ %s ALLOCATE descname=%s\n", _thisDbConn, descname );
      EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname; line = __LINE__;
    }
    if ( sqlca.sqlcode == 0 )
      allocExecDesc = true;
    else
      ok = false;

    if( ok ) {
      Logging( 6, "Sql", " . ", "%s(%d): @ %s PREPARE execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );
      EXEC SQL AT :_thisDbConn PREPARE :execquery FROM :stmt; line = __LINE__;
      if ( sqlca.sqlcode ) {
	showErrorLineFileStmt( 0, NULL, __LINE__, __FILE__, stmt );
	Logging( 6, "Sql", " . ", "%s(%d): @ %s DEALLOCATE execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );
	EXEC SQL DEALLOCATE PREPARE :execquery; line = __LINE__;
	if ( sqlca.sqlcode ) {
	  showErrorLineFileStmt( 0, NULL, __LINE__, __FILE__, stmt );
	}
	/*
	EXEC SQL AT :_thisDbConn COMMIT;
	Logging( 9, "Sql", "{ }", "%s(%d): COMMITed on %s\n", __func__, __LINE__, _thisDbConn );
	if ( sqlca.sqlcode ) {
	  Logging( 0, "Sql", "***", "Commit on %s failed\n", _thisDbConn );
	  showErrorLineFileStmt( 0, &sqlca, __LINE__, __FILE__, stmt );
	}
	*/
        Logging( 6, "Sql", " . ", "%s(%d): @ %s PREPARE execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );
	EXEC SQL AT :_thisDbConn PREPARE :execquery FROM :stmt; line = __LINE__;
	if ( sqlca.sqlcode ) {
	  // ok = false;
	  showErrorLineFileStmt( 0, NULL, __LINE__, __FILE__, stmt );
	  if( sqlca.sqlcode == -400 ) {
	    EXEC SQL AT :_thisDbConn ROLLBACK;
	    Logging( 9, "Sql", "{ }", "%s(%d): ROLLBACKed on %s\n", __func__, __LINE__, _thisDbConn );
	    if ( sqlca.sqlcode ) {
	      Logging( 0, "Sql", "***", "Commit on %s failed\n", _thisDbConn );
	      showErrorLineFileStmt( 0, &sqlca, __LINE__, __FILE__, stmt );
	    }
	    Logging( 6, "Sql", " . ", "%s(%d): @ %s PREPARE execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );
	    EXEC SQL AT :_thisDbConn PREPARE :execquery FROM :stmt; line = __LINE__;
	    if ( sqlca.sqlcode ) {
	      ok = false;
	      showErrorLineFileStmt( 0, NULL, __LINE__, __FILE__, stmt );
	    }
	  }
        }
      }
    }

    if( ok ) {
      EXEC SQL AT :_thisDbConn DECLARE execcurs SCROLL CURSOR FOR :execquery; line = __LINE__;
      if ( sqlca.sqlcode ) {
        showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
        ok = false;
      }
    }

    if( ok ) {
      EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
      if ( sqlca.sqlcode ) {
        showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
        ok = false;
      }
    }

    bool first = true;
    while( ok ) {

      if( first ) {
        EXEC SQL AT :_thisDbConn FETCH FIRST IN execcurs INTO SQL DESCRIPTOR :descname;
        first = false;
      }
      else
        EXEC SQL AT :_thisDbConn FETCH NEXT IN execcurs INTO SQL DESCRIPTOR :descname;
      if( sqlca.sqlcode > 0 ) {  // Done ?
        Logging( 5, "Sql", " . ", "%s(): Got %ld after FETCH (%d rows)\n", __func__, sqlca.sqlcode, rowCount );
        break;
      }
      else if ( sqlca.sqlcode < 0 ) {
        showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
        EXEC SQL AT :_thisDbConn CLOSE execcurs;
        ok = false;
        break;
      }

      EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname :count = COUNT;
      if ( sqlca.sqlcode ) {
        showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
        EXEC SQL AT :_thisDbConn CLOSE execcurs;
        ok = false;
        break;
      }

      if( !done ) {
        Logging( 8, "Sql", " . ", "%d Columns\n", count );
        count = min( count, 100 );
        columns = (_SQLColInfo *) malloc( ( count + 1 ) * sizeof( _SQLColInfo ) );
        if( ! columns ) {
          retcode = SQLErr_NoMem;
          break;
        }

        // Logging( 10, "Sql", " . ", "%s(): before loop of descriptor - type\n", __func__ );
        /* Walk through the columns, to get Name and Type */
        for( inx = 1; inx <= count; ++ inx ) {
          precision = 6;
          scale = 0;
          EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx
                :type = TYPE,
                :length = LENGTH,
                :precision = PRECISION, :scale=SCALE,
                :name=NAME;
          if ( sqlca.sqlcode ) {
            showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
            EXEC SQL AT :_thisDbConn CLOSE execcurs;
            ok = false;
            break;
          }
          name[(sizeof name) -1] = '\0';

          index = inx - 1;
          if( DebugLevel > localDebugLevel + 1 )
            Logging( 1, "Sql", " . ", "%d:%s\n", index, name );
          strncpy( cname[index], name, 120 - 1 );
          cname[index][119] = '\0';
          columns[index].Name = cname[index];

          if( precision > 10 )
            precision = 10;
          if( scale > 5 )
            scale = 5;

          switch( type ) {
          case SQL3_BOOLEAN:
          case SQL3_NUMERIC:
          case SQL3_DECIMAL:
          case SQL3_INTEGER:
          case SQL3_BIGINT:
          case SQL3_OID:
            columns[index].Type = SQLType_Long;
            break;
          case SQL3_SMALLINT:
            columns[index].Type = SQLType_Short;
            break;
          case SQL3_FLOAT:
            columns[index].Type = SQLType_Float;
            break;
          case SQL3_REAL:
            columns[index].Type = SQLType_Float;
            break;
          case SQL3_DOUBLE_PRECISION:
            columns[index].Type = SQLType_Double;
            break;
          case SQL3_CHARACTER:
            columns[index].Type = SQLType_Char;
            break;
          case SQL3_CHARACTER_VARYING:
          case SQL3_TIMESTAMP_WITH_TIMEZONE:
            columns[index].Type = SQLType_VarChar;
            break;
          case SQL3_DATE_TIME_TIMESTAMP:
            columns[index].Type = SQLType_TimeStamp;
            break;
          default:
            Logging( 6, "Sql", " . ", "Unknown type: %d:%s\n", type, name );
            columns[index].Type = SQLType_VarChar;
            break;
          }
        }
        if( ! ok )
          break;
        done = 1;
      }

      // Logging( 10, "Sql", " . ", "%s(): before loop of descriptor - getting values\n", __func__ );
      /* For each record, handle data and call the callback */
      for( inx = 1; inx <= count; ++inx ) {
        EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx
              :type = TYPE, :scale=SCALE, :precision = PRECISION,
              :name=NAME,
              :indicator=INDICATOR;
        if ( sqlca.sqlcode ) {
          showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
          EXEC SQL AT :_thisDbConn CLOSE execcurs;
          ok = false;
          break;
        }

        if( precision > 10 )
          precision = 10;
        if( scale > 5 )
          scale = 5;

        index = inx - 1;
        if( indicator == -1 ) {
          if( DebugLevel > localDebugLevel + 2 )
            Logging( 2, "Sql", " . ",  "NULL" );
          columns[index].Data = NULL;
        }
        else {
          switch( type ) {
          case SQL3_BOOLEAN:
            EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :boolvar=DATA;
            if ( sqlca.sqlcode ) {
              showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
              EXEC SQL AT :_thisDbConn CLOSE execcurs;
              ok = false;
              break;
            }

            if( DebugLevel > localDebugLevel + 2 )
              Logging( 2, "Sql", " . ",  boolvar ? "true" : "false" );
            columns[index].Data = &intvars[index];
            intvars[index] = boolvar;
            break;
          case SQL3_NUMERIC:
          case SQL3_DECIMAL:
            if( scale == 0 ) /* we might even print leading zeros "%0*d" */
            {
              EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :intvar=DATA;
              if ( sqlca.sqlcode ) {
                showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
                EXEC SQL AT :_thisDbConn CLOSE execcurs;
                ok = false;
                break;
              }

              if( DebugLevel > localDebugLevel + 2 )
                Logging( 2, "Sql", " . ",  "%*d ", precision, intvar );
              columns[index].Data = &intvars[index];
              intvars[index] = intvar;
            }
            else {
              EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :floatvar=DATA;
              if ( sqlca.sqlcode ) {
                showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
                EXEC SQL AT :_thisDbConn CLOSE execcurs;
                ok = false;
                break;
              }

              if( DebugLevel > localDebugLevel + 2 )
                Logging( 2, "Sql", " . ",  "%*.*f ", precision+1, scale, floatvar );
              columns[index].Data = &intvars[index];
              intvars[index] = floatvar;
            }
            break;
          case SQL3_BIGINT:
          case SQL3_INTEGER:
          case SQL3_SMALLINT:
          case SQL3_OID:
            EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :intvar=DATA;
            if ( sqlca.sqlcode ) {
              showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
              EXEC SQL AT :_thisDbConn CLOSE execcurs;
              ok = false;
              break;
            }

            if( DebugLevel > localDebugLevel + 2 )
              Logging( 2, "Sql", " . ",  "%d ", intvar );
            columns[index].Data = &intvars[index];
            intvars[index] = intvar;
            break;
          case SQL3_FLOAT:
          case SQL3_REAL:
            EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :floatvar=DATA;
            if ( sqlca.sqlcode ) {
              showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
              EXEC SQL AT :_thisDbConn CLOSE execcurs;
              ok = false;
              break;
            }

            if( DebugLevel > localDebugLevel + 2 )
              Logging( 2, "Sql", " . ",  "%.2f ", (double)floatvar );
            columns[index].Data = &floatvars[index];
            floatvars[index] = floatvar;
            break;
          case SQL3_DOUBLE_PRECISION:
            EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :doublevar=DATA;
            if ( sqlca.sqlcode ) {
              showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
              EXEC SQL AT :_thisDbConn CLOSE execcurs;
              ok = false;
              break;
            }

            if( DebugLevel > localDebugLevel + 2 )
              Logging( 2, "Sql", " . ",  "%.2f ", doublevar );
            columns[index].Data = &doublevars[index];
            doublevars[index] = doublevar;
            break;
          case SQL3_DATE_TIME_TIMESTAMP:
          case SQL3_TIMESTAMP_WITH_TIMEZONE:
          case SQL3_INTERVAL:
          case SQL3_CHARACTER:
          case SQL3_CHARACTER_VARYING:
          case SQL3_TEXT:
            EXEC SQL AT :_thisDbConn GET DESCRIPTOR :descname VALUE :inx :stringvar=DATA;
            if ( sqlca.sqlcode ) {
              showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
              EXEC SQL AT :_thisDbConn CLOSE execcurs;
              ok = false;
              break;
            }

            if( DebugLevel > localDebugLevel + 2 )
              Logging( 2, "Sql", " . ",  "'%s'", stringvar );
            if( stringvars[index] )
              free( stringvars[index] );
            stringvars[index] = (char *) malloc( strlen( stringvar ) + 1 );
            if( ! stringvars[index] ) {
              columns[index].Data = NULL;
            }
            else {
              strncpy( stringvars[index], stringvar, strlen( stringvar ) );
              stringvars[index][strlen( stringvar )] = '\0';
              columns[index].Data = stringvars[index];
            }
            break;
          default:
            Logging( 1, "Sql", " . ", "%s%s(): Unknown type: %d:%s%s\n", COLOUR_RED, __func__, type, name, COLOUR_NORMAL );
            columns[index].Data = NULL;
            break;
          }
          if( ! ok )
            break;
        }
      }
      if ( count > 0 && DebugLevel > localDebugLevel + 2 )
        Logging( 2, "Sql", " . ",  "\n" );

      // Optimize: move the 'func' check outside both for-loops, just count rows !  (LJ/090421)
      if( ok && func ) {
        int res;
        Logging( 8, "Sql", " . ", "%s(): about to call callback - 0x%08lx @ %s\n", __func__, (ulong) func, _thisDbConn );

        res = func( callbackParam, rowCount, count, columns );
        
        if( res ) {
          retcode = SQLErr_Abort;
          break;
        }
      }
      // Logging( 10, "Sql", " . ", "%s(): before null'ing stringvars\n", __func__ );
      for( index = 0; index < count; index ++ ) {
        if( stringvars[index] )
          free( stringvars[index] );
        stringvars[index] = NULL;
      }
      Logging( 10, "Sql", " . ", "%s(): clear done. Handled row %d\n", __func__, rowCount );
      rowCount ++;
    }
    inclRowIndex = false;

    if( ! ok ) {
      showErrorLineFileStmt( 0, NULL, line, __FILE__, stmt );
    /*
      if( allocExecDesc ) {
        EXEC SQL DEALLOCATE DESCRIPTOR execdesc;
        showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      }
      EXEC SQL AT :_thisDbConn ROLLBACK;
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      RETURNV( SQLErr_Error );
    */
    }

    if( ok ) {
      EXEC SQL AT :_thisDbConn CLOSE execcurs; line = __LINE__;
      if ( sqlca.sqlcode ) {
        ok = false;
        showErrorLineFileStmt( 0, NULL, __LINE__, __FILE__, stmt );
      }
    }
    if( execquery ) {
      Logging( 6, "Sql", " . ", "%s(%d): @ %s DEALLOCATE PREPARE execquery=%s\n", __func__, __LINE__, _thisDbConn, execquery );
      EXEC SQL SET CONNECTION :_thisDbConn;
      EXEC SQL DEALLOCATE PREPARE :execquery; line = __LINE__;
      if ( sqlca.sqlcode ) {
        // ok = false;
        showErrorLineFileStmt( 0, NULL, line, __FILE__, stmt );
//        Db_Close( _thisDbConn );
//        Db_SQLExec( _thisDbConn, NOTRUNTRANS, NULL, "CONNECTTODB", False, NULL, NULL, NULL );
      }
      free( execquery );
      execquery = NULL;
    }
    /*
    if ( sqlca.sqlcode ) {
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      EXEC SQL DEALLOCATE DESCRIPTOR execdesc;
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      RETURNV( SQLErr_Error );
    }
    */

    Logging( 6, "Sql", " . ", "%s(%d): @ %s DEALLOCATE descname=%s\n", __func__, __LINE__, _thisDbConn, descname );
    EXEC SQL DEALLOCATE DESCRIPTOR :descname; if( ok ) line = __LINE__;
    if ( ! ok || sqlca.sqlcode ) {
      showErrorLineFile( 0, NULL, line, __FILE__ );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      showErrorLineFile( 0, NULL, __LINE__, __FILE__ );
      retcode = SQLErr_Error;
    }

    if( descname ) {
      free( descname );
      descname = NULL;
    }
    if( execquery ) {
      free( execquery );
      execquery = NULL;
    }
    pthread_mutex_unlock( &dbSelectMtx );

    Logging( 6, "Sql", " . ", "doSQLExec: END stmt=%s (%d, %d rows)\n", stmt, retcode, rowCount );

  }  //  end of else "select"

  if( rCount )
    *rCount = rowCount;

  if(columns)
    free(columns);

  RETURNV( retcode );
}  // doSQLExec()

SQLErr Db_SQLExec( const char *ThisDbConn, const int RunTrans, struct sqlca_t *sqlstat, const char *sql, int secure, int *rCount, SQLCallback func, const void *callbackParam, ... )
//
/// \brief   Execute any SQL statement.
///          If it's a SELECT statement, do it using cursors and if non-null call the func for
///          each row selected.
/// \param   RunTrans - run the sql in a transaction
/// \param   sql  -     the SQL statement, may include "printf" format specifiers
/// \param   secure  -  is this to be parsed and possible WHERE added for limited access
/// \param   rCount   - pointer to a place to store number of rows affected, may be null
/// \param   func     - the callback function to be called for each row selected, may be null
/// \param   callbackParam - an optional parameter to directly pass on to the callback as 1st parameter if not null
/// \param   ThisDbConn    - what db connection to use
/// \param   ap       - an open ended list of parameters to be used in the sql parameter, if any
/// \return  SQLErr - either OK or an error number
{
  StartF( "Sql", __func__ );

  EXEC SQL BEGIN DECLARE SECTION;
  char *stmt = NULL;
  const char *_thisDbConn = ThisDbConn;

  // char field[50][200];
  // int  fnull[50];
  // char table[200] = { 0 };
  // int   colcount, rowcount;
  EXEC SQL END DECLARE SECTION;

  EXEC SQL BEGIN DECLARE SECTION;
  // const char *sql;
  // char db[1024];
  char *pstr = NULL;
  EXEC SQL END DECLARE SECTION;

  int  retcode = SQLErr_OK;
  va_list ap;

  if ( ThisDbConn == NULL ) {
    Logging( 0, "Sql", "***", "ThisDbConn is NULL - returning\n" );
    RETURNV( SQLErr_OK );
  }

  Logging( 5, "Sql", "***", "ThisDbConn=%s\n", ThisDbConn );

  int n;

  // sql = query;
  size_t size = 100 + strlen( sql );
  if( (stmt = (char *)malloc (size)) == NULL ) {
     Logging( 0, "Sql", " * ", "Db_SQLExec: Memory allocation error, needed %ld bytes\n", (long)size );
     RETURNV( SQLErr_NoMem );
  }

  /* Construct the statement (if "open ended") */
  pthread_mutex_lock( &dbSqlMtx );
  va_start( ap, callbackParam );
  while (1) {
    /* Try to print in the allocated space. */
    n = vsnprintf( stmt, size, sql, ap );
    /* If that worked, use the string. */
    if( n > -1 && n < size )
      break;
    /* Else try again with more space. */
    if( n > -1 )    /* glibc 2.1 */
      size = n + 10; /* precisely what is needed and a bit more */
    else           /* glibc 2.0 */
      size = 2*size + 10;  /* twice the old size and a bit more */
    if ((stmt = (char *)realloc( stmt, size )) == NULL ) {
      Logging( 0, "Sql", " * ", "Db_SQLExec: Memory allocation error, needed %ld bytes\n", (long)size );
      pthread_mutex_unlock( &dbSqlMtx );
      RETURNV( SQLErr_NoMem );
    }
  }
  va_end( ap );
  pthread_mutex_unlock( &dbSqlMtx );

  {
    int l = strlen( stmt );
    if( l >= size - 1 ) {
      if ((stmt = (char *)realloc( stmt, size + 2 )) == NULL ) {
        Logging( 0, "Sql", " * ", "Db_SQLExec: Memory allocation error, needed %ld bytes\n", (long)size );
        pthread_mutex_unlock( &dbSqlMtx );
        RETURNV( SQLErr_NoMem );
      }
    }
    if ( l > 0 && ';' != stmt[ l - 1 ] )
      strcat( stmt, ";" );
  }

  Logging( 3, "Sql", " . ", "Db_SQLExec: stmt=%s\n", stmt );

  if ( strstr( stmt, "DISCONNECTFROMDB" ) ) {
    retcode = Db_Close( ThisDbConn );
  }
  else if ( strstr( stmt, "CONNECTTODB" ) ) {
    retcode = Db_Open(ThisDbConn, arguments.dbHost, arguments.dbPort, arguments.dbName,
                      arguments.dbUser, arguments.dbPass);
  }
  else if ( strstr( stmt, "READONLYTRANS" ) || NULL != strstr( stmt, "READWRITETRANS" ) ) {
    retcode = Db_StartTrans( _thisDbConn, __LINE__, stmt );
  }
  else if ( strstr( stmt, "COMMITTRANS" ) ) {
    EXEC SQL AT :_thisDbConn COMMIT;
    Logging( 9, "Sql", "{ }", "%s: COMMIT on %s\n", __func__, _thisDbConn );
    if ( sqlca.sqlcode ) {
      Logging( 0, "Sql", "***", "Commit on %s failed\n", _thisDbConn );
      showErrorLineFileStmt( 0, &sqlca, __LINE__, __FILE__, stmt );
      EXEC SQL AT :_thisDbConn ROLLBACK;
      retcode = SQLErr_Error;
    }
  }
  else if ( strstr( stmt, "ROLLBACKTRANS" ) ) {
    EXEC SQL AT :_thisDbConn ROLLBACK;
    if ( sqlca.sqlcode ) {
      Logging( 0, "Sql", "***", "Rollback on %s failed\n", _thisDbConn );
      showErrorLineFile( 0, &sqlca, __LINE__, __FILE__ );
      retcode = SQLErr_Error;
    }
  }
  else {

    if ( RunTrans != NOTRUNTRANS ) {
      if ( RunTrans == RWRUNTRANS )
        retcode = Db_StartTrans( _thisDbConn, __LINE__, "READWRITETRANS" );
      else {
        // RunTrans == RUNTRANS
        bool readWrite = strstr( stmt, "update" ) || strstr( stmt, "UPDATE" )
                      || strstr( stmt, "updins" ) || strstr( stmt, "UPDINS" )
                      || strstr( stmt, "insert" ) || strstr( stmt, "INSERT" )
                      || strstr( stmt, "delete" ) || strstr( stmt, "DELETE" );
        retcode = Db_StartTrans( _thisDbConn, __LINE__, readWrite ? "READWRITETRANS" : "READONLYTRANS" );
      }
    }

    if( retcode == SQLErr_OK ) {
      SQLErr rescode = SQLErr_OK;
      char *saveptr;
      char *p = NextSql( stmt, &saveptr );
      while(p && *p) // Fixed missing check for string end 110623
      {
        if ( pstr )
          free( pstr );
        pstr = (char *)malloc( strlen( p ) + 4000 );
        strcpy( pstr, p );
        strcat( pstr, ";" );
        Logging( 8, "Sql", " . ", "%s(): about to call doSQLExec with callback - 0x%08lx\n", __func__, (ulong) func );
        if ( SQLErr_OK != ( rescode = doSQLExec( ThisDbConn, pstr, rCount, func, callbackParam ) ) )
          break;
        p = NextSql( NULL, &saveptr );
      }
      retcode = rescode;  // Added LJ/080610
      if ( pstr )
        free( pstr );
      pstr = NULL;

      if ( RunTrans != NOTRUNTRANS && rescode == SQLErr_OK ) {
        EXEC SQL AT :_thisDbConn COMMIT;
        Logging( 9, "Sql", "{ }", "%s: COMMIT on %s\n", __func__, _thisDbConn );
        if ( sqlca.sqlcode ) {
          Logging( 0, "Sql", "***", "Commit failed\n" );
          showErrorLineFile( 0, &sqlca, __LINE__, __FILE__ );
          EXEC SQL AT :_thisDbConn ROLLBACK;
          retcode = SQLErr_Error;
        }
      }
    }
  }

  if( stmt )
    free( stmt );

  RETURNV( retcode );
} // Db_SQLExec()