Hi Guys, Here is a gdb dump of the backtrace at the server process crash. I have also included the code that generates these calls. As mentioned below this specific connection has been used many times before the crash. Also, we are aware of the thread caveat that only using a connection from one thread at a time. Therefore the "strange" connection name that includes both the process id and the thread id. This is for the code to make sure that a connection is only used in the thread it is meant to. Hope this can help in helping me ;-), Leif ----- Original Message ----- > On 05/06/2014 11:37 PM, Leif Jensen wrote: > > Hello Adrian, > > > > Thank you for your answer. I can post part of the code that > > makes these calls, but I'm not sure how much it would help. It > > is rather large function that makes these calls, and it is > > called all over the program. The part of the log posted is only > > a small excerpt of the use of the > > ApplDBConn_22854_f6adeb70_query, which has been used many many > > times before the log shown (167 in all to be exact ;-) ). > > Exactly. Something different happened at that point. The hard part > will > determining what that is. The next step would seem to run a debugger > on > the Postgres process to get more information. For a step by step guide > see here: > > https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD > > > > > Leif > > > > > > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx
Attachment:
gdb-bt
Description: Binary data
/*! \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, "[31m%s (stmt=%s)[m\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()
