Hello everyone,

we are currently migrating a software to use Oracle 10 client (OCI)
instead of Oracle 9. A problem occurs that manifests itself in form of an
application crash and we boiled it down to an OCI snippet which doesnt work
anymore. Since I am puzzles I am posting it here, maybe I get some
inspiration by you guys...

I am posting the sample code further below but in a nut shell I am doing
this:

* Create a table ABC with a single int column.
* Create and prepare a statement INSERT INTO ABC VALUES(:1).
* Bind the :1 to a value, say 8. Execute the statement. Works wonderfully up
until here
* Bind the :1 of the same statement to NULL with the intention of inserting
a NULL value into the table. No NOT NULL constraint is present.
* Execute the statement again with the new bind --> crash!
* I am using MS Dev Studio 2005, C++, and at the time of the crash the call
stack is entirely messed up, no clue to the cause. The message will say:
"Unhandled exception at <memory address> in <appl name>: 0xC0000005,
Access vilation reading location 0x00000000.
* The very same code used to run perfectly well under Ora9 OCI client.
* The event occurs no matter what database server I use (9 or 10 have been
tested).
* The Ora Client is 10.2.0.x.
* If I insert the NULL value first and then 8, it works fine. If I insert
the NULL after the 8 with a new statement, not recycling the same prepared
statement, it works fine as well!

Thank you very much in advance,
AH


PS: Here is the sample code. The statement that will crash has been marked
with a comment.

OCIEnv* _ptrOCIEnvironment;
OCIError* _ptrOCIError;
OCISvcCtx* _ptrOCIServiceContext;
OCIStmt* _ptrOCIStatement;
sword swdReturnCode;
std::string cmd;

swdReturnCode = OCIEnvCreate(
(OCIEnv**)
&_ptrOCIEnvironment,
(ub4)
OCI_DEFAULT | OCI_THREADED,
(dvoid*) NULL,
(dvoid*(*) (dvoid*, size_t)) NULL,
(dvoid*(*) (dvoid*, dvoid*, size_t)) NULL,
(void(*) (dvoid*, dvoid*)) NULL,
(size_t) 0,
(dvoid**) NULL
);
swdReturnCode = OCIHandleAlloc(
(CONST dvoid*) _ptrOCIEnvironment,
(dvoid **) &_ptrOCIError,
(ub4) OCI_HTYPE_ERROR,
(size_t) 0,
(dvoid **) 0
);
swdReturnCode = OCILogon(
(OCIEnv*) _ptrOCIEnvironment,
(OCIError*) _ptrOCIError,
(OCISvcCtx**) &_ptrOCIServiceContext,
(unsigned char*) user.c_str(),
(ub4) strlen(user.c_str()),
(unsigned char*) pwd.c_str(),
(ub4) strlen(pwd.c_str()),
(unsigned char*) url.c_str(),
(ub4) strlen(url.c_str())
);

swdReturnCode = OCIHandleAlloc(
(dvoid *) _ptrOCIEnvironment, //environment handle
(dvoid **) &_ptrOCIStatement, //returned handle to the
statement
(ub4) OCI_HTYPE_STMT, //type of the handle to allocate
(size_t) 0, //amount of user memory to be allocated
(dvoid **) 0 //pointer to the user memory allocated.
);
cmd = "drop table abc";
swdReturnCode = OCIStmtPrepare(
(OCIStmt*) _ptrOCIStatement, //handle to the statement that
will be prepared
(OCIError*) _ptrOCIError, //error handle
(CONST OraText*) cmd.c_str(), //the SQL statement string
(ub4) strlen(cmd.c_str()), //length of the statement string
(ub4) OCI_NTV_SYNTAX, //syntax of the statement
(ub4) OCI_DEFAULT
);
swdReturnCode = OCIStmtExecute(
(OCISvcCtx*) _ptrOCIServiceContext, //service context handle
(OCIStmt*) _ptrOCIStatement, //statement handle
(OCIError*) _ptrOCIError, //error handle
(ub4) 1, //the number of times this statement is
executed
(ub4) 0, //the starting index from which the data in an
array bind is relevant for this multiple row execution
(CONST OCISnapshot*) NULL,
(OCISnapshot *) NULL,
(ub4) OCI_DEFAULT
);
cmd = "create table abc (a int)";
swdReturnCode = OCIStmtPrepare(
(OCIStmt*) _ptrOCIStatement, //handle to the statement that
will be prepared
(OCIError*) _ptrOCIError, //error handle
(CONST OraText*) cmd.c_str(), //the SQL statement string
(ub4) strlen(cmd.c_str()), //length of the statement string
(ub4) OCI_NTV_SYNTAX, //syntax of the statement
(ub4) OCI_DEFAULT
);
swdReturnCode = OCIStmtExecute(
(OCISvcCtx*) _ptrOCIServiceContext, //service context handle
(OCIStmt*) _ptrOCIStatement, //statement handle
(OCIError*) _ptrOCIError, //error handle
(ub4) 1, //the number of times this statement is
executed
(ub4) 0, //the starting index from which the data in an
array bind is relevant for this multiple row execution
(CONST OCISnapshot*) NULL,
(OCISnapshot *) NULL,
(ub4) OCI_DEFAULT
);
cmd = "INSERT INTO ABC VALUES(:1)";
swdReturnCode = OCIStmtPrepare(
(OCIStmt*) _ptrOCIStatement, //handle to the statement that
will be prepared
(OCIError*) _ptrOCIError, //error handle
(CONST OraText*) cmd.c_str(), //the SQL statement string
(ub4) strlen(cmd.c_str()), //length of the statement string
(ub4) OCI_NTV_SYNTAX, //syntax of the statement
(ub4) OCI_DEFAULT
);

OCIBind* pBindHandle = NULL;
double dbl = 8.0;
swdReturnCode = OCIBindByPos (
(OCIStmt*) _ptrOCIStatement,
(OCIBind**) &pBindHandle,
(OCIError*) _ptrOCIError,
(ub4) 1,
(dvoid*) &dbl,
(sb4) sizeof(double),
(ub2) SQLT_FLT,
(dvoid*) NULL,
(ub2*) NULL,
(ub2*) NULL,
(ub4) NULL,
(ub4*) NULL,
(ub4) OCI_DEFAULT
);
swdReturnCode = OCIStmtExecute(
(OCISvcCtx*) _ptrOCIServiceContext, //service context handle
(OCIStmt*) _ptrOCIStatement, //statement handle
(OCIError*) _ptrOCIError, //error handle
(ub4) 1, //the number of times this statement is
executed
(ub4) 0, //the starting index from which the data in an
array bind is relevant for this multiple row execution
(CONST OCISnapshot*) NULL,
(OCISnapshot *) NULL,
(ub4) OCI_DEFAULT
);
swdReturnCode = OCIBindByPos (
(OCIStmt*) _ptrOCIStatement,
(OCIBind**) &pBindHandle,
(OCIError*) _ptrOCIError,
(ub4) 1,
(dvoid*) NULL,
(sb4) sizeof(double),
(ub2) SQLT_FLT,
(dvoid*) NULL,
(ub2*) NULL,
(ub2*) NULL,
(ub4) NULL,
(ub4*) NULL,
(ub4) OCI_DEFAULT
);
swdReturnCode = OCIStmtExecute(
(OCISvcCtx*) _ptrOCIServiceContext, //service context handle
(OCIStmt*) _ptrOCIStatement, //statement handle
(OCIError*) _ptrOCIError, //error handle
(ub4) 1, //the number of times this statement is
executed
(ub4) 0, //the starting index from which the data in an
array bind is relevant for this multiple row execution
(CONST OCISnapshot*) NULL,
(OCISnapshot *) NULL,
(ub4) OCI_DEFAULT
);

swdReturnCode = OCIHandleFree(
(dvoid *) _ptrOCIStatement,
(ub4) OCI_HTYPE_STMT
);
OCILogoff(_ptrOCIServiceContext, _ptrOCIError);
OCIHandleFree((dvoid *) _ptrOCIError, (ub4) OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *) _ptrOCIEnvironment, (ub4) OCI_HTYPE_ENV);

On Fri, 9 May 2008 16:10:00 +0200, "AndrŽ Hartmann"
<andrehartmann@hotmail.com> wrote:

>Hello everyone,
>
> we are currently migrating a software to use Oracle 10 client (OCI)
>instead of Oracle 9. A problem occurs that manifests itself in form of an
>application crash and we boiled it down to an OCI snippet which doesnt work
>anymore. Since I am puzzles I am posting it here, maybe I get some
>inspiration by you guys...
>
> I am posting the sample code further below but in a nut shell I am doing
>this:
>
>* Create a table ABC with a single int column.
>* Create and prepare a statement INSERT INTO ABC VALUES(:1).
>* Bind the :1 to a value, say 8. Execute the statement. Works wonderfully up
>until here
>* Bind the :1 of the same statement to NULL with the intention of inserting
>a NULL value into the table. No NOT NULL constraint is present.
>* Execute the statement again with the new bind --> crash!
>* I am using MS Dev Studio 2005, C++, and at the time of the crash the call
>stack is entirely messed up, no clue to the cause. The message will say:
> "Unhandled exception at <memory address> in <appl name>: 0xC0000005,
>Access vilation reading location 0x00000000.
>* The very same code used to run perfectly well under Ora9 OCI client.
>* The event occurs no matter what database server I use (9 or 10 have been
>tested).
>* The Ora Client is 10.2.0.x.
>* If I insert the NULL value first and then 8, it works fine. If I insert
>the NULL after the 8 with a new statement, not recycling the same prepared
>statement, it works fine as well!
>
> Thank you very much in advance,
>AH
>
>
>PS: Here is the sample code. The statement that will crash has been marked
>with a comment.
>
> OCIEnv* _ptrOCIEnvironment;
> OCIError* _ptrOCIError;
> OCISvcCtx* _ptrOCIServiceContext;
> OCIStmt* _ptrOCIStatement;
> sword swdReturnCode;
> std::string cmd;
>
> swdReturnCode = OCIEnvCreate(
> (OCIEnv**)
>&_ptrOCIEnvironment,
> (ub4)
>OCI_DEFAULT | OCI_THREADED,
> (dvoid*) NULL,
> (dvoid*(*) (dvoid*, size_t)) NULL,
> (dvoid*(*) (dvoid*, dvoid*, size_t)) NULL,
> (void(*) (dvoid*, dvoid*)) NULL,
> (size_t) 0,
> (dvoid**) NULL
> );
> swdReturnCode = OCIHandleAlloc(
> (CONST dvoid*) _ptrOCIEnvironment,
> (dvoid **) &_ptrOCIError,
> (ub4) OCI_HTYPE_ERROR,
> (size_t) 0,
> (dvoid **) 0
> );
> swdReturnCode = OCILogon(
> (OCIEnv*) _ptrOCIEnvironment,
> (OCIError*) _ptrOCIError,
> (OCISvcCtx**) &_ptrOCIServiceContext,
> (unsigned char*) user.c_str(),
> (ub4) strlen(user.c_str()),
> (unsigned char*) pwd.c_str(),
> (ub4) strlen(pwd.c_str()),
> (unsigned char*) url.c_str(),
> (ub4) strlen(url.c_str())
> );
>
> swdReturnCode = OCIHandleAlloc(
> (dvoid *) _ptrOCIEnvironment, //environment handle
> (dvoid **) &_ptrOCIStatement, //returned handle to the
>statement
> (ub4) OCI_HTYPE_STMT, //type of the handle to allocate
> (size_t) 0, //amount of user memory to be allocated
> (dvoid **) 0 //pointer to the user memory allocated.
> );
> cmd = "drop table abc";
> swdReturnCode = OCIStmtPrepare(
> (OCIStmt*) _ptrOCIStatement, //handle to the statement that
>will be prepared
> (OCIError*) _ptrOCIError, //error handle
> (CONST OraText*) cmd.c_str(), //the SQL statement string
> (ub4) strlen(cmd.c_str()), //length of the statement string
> (ub4) OCI_NTV_SYNTAX, //syntax of the statement
> (ub4) OCI_DEFAULT
> );
> swdReturnCode = OCIStmtExecute(
> (OCISvcCtx*) _ptrOCIServiceContext, //service context handle
> (OCIStmt*) _ptrOCIStatement, //statement handle
> (OCIError*) _ptrOCIError, //error handle
> (ub4) 1, //the number of times this statement is
>executed
> (ub4) 0, //the starting index from which the data in an
>array bind is relevant for this multiple row execution
> (CONST OCISnapshot*) NULL,
> (OCISnapshot *) NULL,
> (ub4) OCI_DEFAULT
> );
> cmd = "create table abc (a int)";
> swdReturnCode = OCIStmtPrepare(
> (OCIStmt*) _ptrOCIStatement, //handle to the statement that
>will be prepared
> (OCIError*) _ptrOCIError, //error handle
> (CONST OraText*) cmd.c_str(), //the SQL statement string
> (ub4) strlen(cmd.c_str()), //length of the statement string
> (ub4) OCI_NTV_SYNTAX, //syntax of the statement
> (ub4) OCI_DEFAULT
> );
> swdReturnCode = OCIStmtExecute(
> (OCISvcCtx*) _ptrOCIServiceContext, //service context handle
> (OCIStmt*) _ptrOCIStatement, //statement handle
> (OCIError*) _ptrOCIError, //error handle
> (ub4) 1, //the number of times this statement is
>executed
> (ub4) 0, //the starting index from which the data in an
>array bind is relevant for this multiple row execution
> (CONST OCISnapshot*) NULL,
> (OCISnapshot *) NULL,
> (ub4) OCI_DEFAULT
> );
> cmd = "INSERT INTO ABC VALUES(:1)";
> swdReturnCode = OCIStmtPrepare(
> (OCIStmt*) _ptrOCIStatement, //handle to the statement that
>will be prepared
> (OCIError*) _ptrOCIError, //error handle
> (CONST OraText*) cmd.c_str(), //the SQL statement string
> (ub4) strlen(cmd.c_str()), //length of the statement string
> (ub4) OCI_NTV_SYNTAX, //syntax of the statement
> (ub4) OCI_DEFAULT
> );
>
> OCIBind* pBindHandle = NULL;
> double dbl = 8.0;
> swdReturnCode = OCIBindByPos (
> (OCIStmt*) _ptrOCIStatement,
> (OCIBind**) &pBindHandle,
> (OCIError*) _ptrOCIError,
> (ub4) 1,
> (dvoid*) &dbl,
> (sb4) sizeof(double),
> (ub2) SQLT_FLT,
> (dvoid*) NULL,
> (ub2*) NULL,
> (ub2*) NULL,
> (ub4) NULL,
> (ub4*) NULL,
> (ub4) OCI_DEFAULT
> );
> swdReturnCode = OCIStmtExecute(
> (OCISvcCtx*) _ptrOCIServiceContext, //service context handle
> (OCIStmt*) _ptrOCIStatement, //statement handle
> (OCIError*) _ptrOCIError, //error handle
> (ub4) 1, //the number of times this statement is
>executed
> (ub4) 0, //the starting index from which the data in an
>array bind is relevant for this multiple row execution
> (CONST OCISnapshot*) NULL,
> (OCISnapshot *) NULL,
> (ub4) OCI_DEFAULT
> );
> swdReturnCode = OCIBindByPos (
> (OCIStmt*) _ptrOCIStatement,
> (OCIBind**) &pBindHandle,
> (OCIError*) _ptrOCIError,
> (ub4) 1,
> (dvoid*) NULL,
> (sb4) sizeof(double),
> (ub2) SQLT_FLT,
> (dvoid*) NULL,
> (ub2*) NULL,
> (ub2*) NULL,
> (ub4) NULL,
> (ub4*) NULL,
> (ub4) OCI_DEFAULT
> );
> swdReturnCode = OCIStmtExecute(
> (OCISvcCtx*) _ptrOCIServiceContext, //service context handle
> (OCIStmt*) _ptrOCIStatement, //statement handle
> (OCIError*) _ptrOCIError, //error handle
> (ub4) 1, //the number of times this statement is
>executed
> (ub4) 0, //the starting index from which the data in an
>array bind is relevant for this multiple row execution
> (CONST OCISnapshot*) NULL,
> (OCISnapshot *) NULL,
> (ub4) OCI_DEFAULT
> );
>
> swdReturnCode = OCIHandleFree(
> (dvoid *) _ptrOCIStatement,
> (ub4) OCI_HTYPE_STMT
> );
> OCILogoff(_ptrOCIServiceContext, _ptrOCIError);
> OCIHandleFree((dvoid *) _ptrOCIError, (ub4) OCI_HTYPE_ERROR);
> OCIHandleFree((dvoid *) _ptrOCIEnvironment, (ub4) OCI_HTYPE_ENV);
>
Hi,
I believe the correct way to set a null value when binding is to use
the indicator var and set it to 1 if you want the value set to be
null. What you are doing is certainly not the documented way and of
course you get undefined behavior. HTH.
-
Kevin English

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
http://www.usenet.com