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 (AT) hotmail (DOT) com> wrote:

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

On Fri, 9 May 2008 16:10:00 +0200, "AndrŽ Hartmann"
<andrehartmann (AT) hotmail (DOT) com> wrote:

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

On Fri, 9 May 2008 16:10:00 +0200, "AndrŽ Hartmann"
<andrehartmann (AT) hotmail (DOT) com> wrote:

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

On Fri, 9 May 2008 16:10:00 +0200, "AndrŽ Hartmann"
<andrehartmann (AT) hotmail (DOT) com> wrote:

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