Re: Crash of OCIStmtExecute when binding to NULL

On Fri, 9 May 2008 16:10:00 +0200, "AndrŽ Hartmann" <andrehartmann_at_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