This discussion is archived
0 Replies Latest reply: Sep 26, 2012 11:03 PM by 943610 RSS

Pro*C: case OCI_FO_END: EXEC SQL ALTER SESSION -> access violation

943610 Newbie
Currently Being Moderated
I'm trying to reexecute Alter session commands after failover, like in this post:
Re: OCI failover callback in a OCCI program
But EXEC SQL ALTER SESSION called from the callback throws access violation.
Is it because the callback is called from EXEC SQL SELECT and EXEC SQL is not reentrant?
sb4 callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
{
switch (fo_event)
    {
...
    case OCI_FO_END:
        {
        printf(" Failover ended ...resuming services\n");
        EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss';
        break;
        }
...
      ntdll.dll!NtRaiseException()  + 0x12 bytes     
      [Frames below may be incorrect and/or missing, no symbols loaded for ntdll.dll]     
      orasql11.dll!sqlbs2()  + 0x1dd bytes     
      orasql11.dll!sssqluga()  + 0x2952 bytes     
      orasql11.dll!sssqluga()  + 0x43c2 bytes     
      orasql11.dll!sssqluga()  + 0x19d49 bytes     
      orasql11.dll!sqlbuf()  + 0xa6f bytes     
      orasql11.dll!sqlcxt()  + 0x86 bytes     
->     test-rac.exe!callback_fn()  Line 298 + 0x53 bytes     C
      oraclient11.dll!61f6614a()      
      msvcr80.dll!free(void * pBlock=0x0152cf6f)  Line 110     C
      msvcr80.dll!free(void * pBlock=0x03c4bd08)  Line 115 + 0x5 bytes     C
      oran11.dll!nsbnt()  + 0x90f3 bytes     
      oran11.dll!nsbrecv()  + 0x43 bytes     
      oran11.dll!nioqrc()  + 0x191 bytes     
      03c002b0()     
      ntdll.dll!RtlUlonglongByteSwap()  + 0xdcbd bytes     
      ntdll.dll!RtlUlonglongByteSwap()  + 0xd2ca bytes     
      feeefeee()     
Full code:
test-rac.pc:
/* ++++++++++++++++++++ Begin PROC Code ++++++++++++++++++++ */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include <sql2oci.h>

#include "win32-test-rac.h"

#define FAILOVER_S "Application Failover Context"
/* Define constants for VARCHAR lengths. */
#define UNAME_LEN 20
#define PWD_LEN 40
#define DB_LEN 40

VARCHAR username[UNAME_LEN]; /* VARCHAR is an Oracle-supplied struct */
varchar password[PWD_LEN]; /* varchar can be in lower case also. */
varchar db[DB_LEN]; /* was 32 */ /* varchar can be in lower case also. */

static sb4 checkerr();
static sb4 register_callback();
static sb4 callback_fn();
static int retries = -1;
static int max_retries = 20;
int static fail_over_already_happend = 0;
EXEC SQL INCLUDE SQLDA;

void do_sql();
int do_dml();

/* Declare error handling function. */
void sql_error( char *msg )
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
}

static
int post_connect() {
  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error int post_connect()--\n");
  EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss';
}

int main(int argc, char **argv)
{
int i = 0,status=0,len=0,r=0;
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCIServer *srvhp;
OCIFocbkStruct failover;
text vers_str[1024];
int mode = 1;

if ( argc < 5 )
    {
    printf("\nUsage: %s username passwd tns SELECT/DML [MAXRETRIES] \n",
        argv[0]);
    printf(" : %s scott tiger RACTEST SELECT 20 \n",argv[0]);
    printf(" : %s scott tiger RACTEST DML 20 \n",argv[0]);
    exit(-1);
    }
if ( argv[4][0] == 'D' )
    mode = 2;
if ( argc == 6)
    max_retries = atoi(argv[5]);
strncpy((char *) username.arr, argv[1], UNAME_LEN);
username.len = (unsigned short) strlen((char *) username.arr);
strncpy((char *) password.arr, argv[2], PWD_LEN);
password.len = (unsigned short) strlen((char *) password.arr);
strncpy((char *)db.arr, argv[3], DB_LEN);
db.len = (unsigned short) strlen((char *) db.arr);
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
EXEC SQL CONNECT :username IDENTIFIED BY :password using :db;
post_connect();
if ( sqlca.sqlcode != 0 )
    {
    printf("Error druing PROC connect - exiting \n", status);
    exit(-1);
    }
printf("\n++ Connected to ORACLE as user: %s to service %s\n",
username.arr,db.arr);
/* Cleanup dml - if necassary */
EXEC SQL delete from emp where empno in ( 1111,1112);
EXEC SQL COMMIT;
status = SQLEnvGet((void *)0,&envhp);
if (status)
    {
    printf("Error occurred in getting OCI env handle: %ld\n", status);
    exit(-1);
    }
if (status = OCIHandleAlloc((void *)envhp, (void **)&errhp,
    (unsigned int)OCI_HTYPE_ERROR, (unsigned int )0, (void **) 0))
    {
    printf("Error occurred in getting OCI err handle: %ld\n", status);
    exit(-1);
    }
status = SQLSvcCtxGet((void *)0, (unsigned char *)0, (int)0, &svchp);
if (status)
    {
    printf("Error occurred in getting OCI Service Context handle: %ld\n",
    status);
    exit(-1);
    }
status = OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
    (dvoid *)&srvhp, (ub4*)0, OCI_ATTR_SERVER, errhp);
if (status)
    {
    printf("Error occurred in getting OCI server handle: %ld\n", status);
    exit(-1);
    }

checkerr(errhp, OCIServerVersion ((dvoid *) svchp, (OCIError *) errhp,
    vers_str, (ub4) sizeof(vers_str),OCI_HTYPE_SVCCTX ));
printf("++ OCI environmment intialized: \n%s\n", vers_str);
if (register_callback(srvhp, errhp,&failover))
    {
    printf("Error registering callback function", status);
    exit(-1);
    }
printf("++ Failover Callback Fn registerd\n", vers_str);
if ( mode == 1 )
    do_sql();
else
    {
    r = -1;
    do
        {
        int r = do_dml();
        printf("++ Return value from do_dml: %d\n", r);
        if ( r == 0)
            {
            EXEC SQL COMMIT;
            break;
            }
        else if ( r == -25402 )
            {
            printf("++ Must rollback/Redo DMLs: SQLCODE: %d\n", r);
            EXEC SQL ROLLBACK;
            }
        else
            {
            printf("++ Fatal error in do_dml .. exiting : %d\n", r);
            exit(-1);
            }
        } while ( r != 0 );
    }
printf("\n");
    /* Disconnect from ORACLE. */
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_SUCCESS);
}

int do_dml()
{
char v_inst[16];
char v_host[16];
char sql_text[255];
int v_count;

EXEC SQL WHENEVER SQLERROR goto :sql_error;
EXEC SQL select INSTANCE_NAME, HOST_NAME into :v_inst, :v_host from v$instance;
EXEC SQL select count(*) into :v_count from emp;
printf("++ DML: %s %s Records in emp : [%d] \n", v_inst, v_host, v_count);
EXEC SQL
    INSERT into emp values (1111,'TEST1', 'TEST_JOB', null, null, 0,0,10);
if ( !fail_over_already_happend )
    {
    printf("++ Insert part 1 done \n");
    printf("++ Press <CR> after shutdown abort for Inst: %s \n", v_inst);
    getchar();
    }
EXEC SQL
    INSERT into emp values (1112,'TEST2', 'TEST_JOB', null, null, 0,0,10);
EXEC SQL COMMIT;
EXEC SQL select count(*) into :v_count from emp;
printf("++ Commit done - Records in emp : [%d] \n", v_count);
return 0;

sql_error:
printf("++ Error during insert: %d \n", sqlca.sqlcode);
return sqlca.sqlcode;
}

void do_sql()
{
char v_inst[32];
char v_host[32];
char sql_text[255];
struct SQLDA *sqlda_out;
int i=0;
short dtype;
int nullok;
char varname[31];
int fetch=0;
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error int do_sql()--\n");
EXEC SQL select INSTANCE_NAME, HOST_NAME
into :v_inst, :v_host from v$instance;
printf("++ SELECT: %s %s\n", v_inst, v_host);
sqlda_out = (struct SQLDA*)sqlald(15, 30, 0 );
if (sqlda_out == (struct SQLDA*)NULL)
    {
    printf("\nError allocating memory for SQLDA\n");
    exit(1);
    }
strcpy(sql_text,"Select ename , to_date('2012-01-02 15:23:44','yyyy-mm-dd HH24:mi:ss') from scott.emp");
EXEC SQL PREPARE sql_stmt from :sql_text;
EXEC SQL DECLARE c CURSOR FOR sql_stmt;
EXEC SQL OPEN c;
EXEC SQL DESCRIBE SELECT LIST FOR sql_stmt into sqlda_out;
printf("Number of bind values %d \n", sqlda_out->F);
sqlda_out->N = sqlda_out->F;
for (i = 0; i < sqlda_out->F; i++)
    {
    sqlnul(&sqlda_out->T, &sqlda_out->T[i], &nullok );
switch (sqlda_out->T[i] )
{
case 1:
//printf("char\n");
break;
case 12:
sqlda_out->L[i] = 20;
break;
default:
printf("datatype not handled, %d[%d] \n", sqlda_out->T[i], sqlda_out->L[i]);
exit(1);
}
/* allocate storage for select list values */
sqlda_out->V[i] = (char *)malloc(sqlda_out->L[i]);
/* allocate storage for indicator values */
sqlda_out->I[i] = (unsigned short *)malloc(sizeof(short*));
/* Convert all datatypes */
sqlda_out->T[i] = 1;
}
EXEC SQL FETCH c USING DESCRIPTOR sqlda_out;
fetch=0;
while (sqlca.sqlcode != 1403 )
{
for ( i = 0; i < sqlda_out->F; i++)
{
strncpy( varname, sqlda_out->V[i], sqlda_out->L[i]);
varname[sqlda_out->L[i]] = '\0';
printf("Fetched : ***%s*** \n", varname);
}
sleep(1);
EXEC SQL FETCH c USING DESCRIPTOR sqlda_out;
fetch++;
if (fetch==2)
{
printf("++ Press <CR> after shutdown abort for Inst: %s \n",v_inst);
//for(;;) sleep(1);
getchar();
EXEC SQL select INSTANCE_NAME, HOST_NAME into :v_inst, :v_host from v$instance;
printf("++ Instance check: %s %s\n", v_inst, v_host);
post_connect();
}
}
EXEC SQL CLOSE c;
}


sb4 callback_fn(svchp, envhp, fo_ctx, fo_type, fo_event )
dvoid * svchp;
dvoid * envhp;
dvoid *fo_ctx;
ub4 fo_type;
ub4 fo_event;
{
switch (fo_event)
{
case OCI_FO_BEGIN:
{
retries = 1;
fail_over_already_happend = 1;
printf("++ Failing Over ... Please stand by \n");
printf("++ Failover type was found to be %s \n",
((fo_type==OCI_FO_NONE) ? "NONE"
:(fo_type==OCI_FO_SESSION) ? "SESSION"
:(fo_type==OCI_FO_SELECT) ? "SELECT"
: "UNKNOWN!"));
//printf(" Failover Context is :%s\n",(fo__ctx?(char *)fo__ctx:"NULL POINTER!"));
break;
}
case OCI_FO_ABORT:
{
printf(" Failover aborted. Failover will not take place.\n");
break;
}
case OCI_FO_END:
{
printf(" Failover ended ...resuming services\n");
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss';
//post_connect();
break;
}
case OCI_FO_REAUTH:
{
printf(" Failed over user. Resuming services\n");
/* Application can check the OCI_ATTR_SESSION attribute of
the service handle to find out the user being re-authenticated.
After this, the application can replay any ALTER SESSION
commands associated with this session. These must have
been saved by the application in the fo__ctx
*/
break;
}
case OCI_FO_ERROR:
{
printf("++ Retry [%d]: Failover error gotten. Sleeping...\n",retries);
sleep(3);
retries++;
if (retries < max_retries )
return OCI_FO_RETRY;
else
{
printf("++ MaxRetry count reached [%d] - exiting ! \n",max_retries);
exit(-1);
}
break;
}
default:
{
printf("Bad Failover Event: %d.\n", fo_event);
break;
}
}
return 0;
}

/* callback function registration */
sb4 register_callback(srvhp, errhp,failover)
OCIServer srvhp; / the server handle */
OCIError errhp; / the error handle */
OCIFocbkStruct *failover;
{
sword ret;

//if (!(failover->fo__ctx = (dvoid *)malloc(strlen(FAILOVER_S) + 1)))
// {
// printf("Error - could not allocate failover context\n");
// return EXIT_FAILURE;
// }
// /* initialize the context. */
//strcpy((char *)failover->fo__ctx, FAILOVER_S);

failover->callback_function = &callback_fn;
/* do the registration */
ret = OCIAttrSet(srvhp, (ub4) OCI_HTYPE_SERVER,
failover, (ub4) 0, (ub4) OCI_ATTR_FOCBK, errhp);
if (checkerr(errhp, ret))
{
printf("Failed to set failover attribute\n");
return EXIT_FAILURE;
}
return EXIT_SUCCESS;
}

sb4 checkerr(ehndlp, status)
OCIError *ehndlp;
sword status;
{
text errbuf[512];
sb4 errcode = 0;

switch (status)
{
case OCI_SUCCESS:
return EXIT_SUCCESS;
case OCI_SUCCESS_WITH_INFO:
printf("Error - OCI_SUCCESS_WITH_INFO\n");
return EXIT_SUCCESS;
case OCI_NEED_DATA:
printf("Error - OCI_NEED_DATA\n");
return EXIT_SUCCESS;
case OCI_NO_DATA:
printf("Error - OCI_NODATA\n");
return EXIT_SUCCESS;
case OCI_ERROR:
if (ehndlp)
{
OCIErrorGet((dvoid *)ehndlp, (ub4) 1, (text *) NULL,
&errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
}
else
printf("Error - OCI_ERROR\n");
return EXIT_FAILURE;
case OCI_INVALID_HANDLE:
printf("Error - OCI_INVALID_HANDLE\n");
return EXIT_FAILURE;
case OCI_STILL_EXECUTING:
printf("Error - OCI_STILL_EXECUTE\n");
return EXIT_SUCCESS;
case OCI_CONTINUE:
printf("Error - OCI_CONTINUE\n");
return EXIT_SUCCESS;
default:
printf("Error - Unknown error\n");
return EXIT_FAILURE;
}
}
/* ++++++++++++++++++++ End PROC Code ++++++++++++++++++++ */
win32-test-rac.h:
#ifndef WIN32_TEST_RAC
#define WIN32_TEST_RAC

#ifdef __cplusplus
extern "C" {
#endif

void w32testrac_sleep(int seconds);

#ifdef __cplusplus
}
#endif

#if defined(_MSC_VER)
# define sleep w32testrac_sleep
#endif

#endif
win32-test-rac.c:
#include "win32-test-rac.h"
#include <windows.h>

void w32testrac_sleep(int seconds)
{
Sleep(seconds * 1000);
}
Edited by: 940607 on 26.09.2012 8:00

Edited by: 940607 on 26.09.2012 22:50

Edited by: 940607 on 26.09.2012 22:53

Edited by: 940607 on 26.09.2012 23:02                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points