On 15/01/2013 22:20, Martin J. Evans wrote:
On 15/01/2013 17:52, Charles Jardine wrote:
On 15/01/13 16:01, Martin J. Evans wrote:
On 15/01/13 15:04, Charles Jardine wrote:
Are you saying that, in the case of a NULL variable, the indicator
variable does not indicate nullity?
I am saying when you have:
procedure fred(pcur OUT SYS_REFCURSOR) as
begin
pcur := NULL;
end;
then the output parameter DBD::Oracle sees appears to be a valid Oracle
statement as you can call OCIAttrGet for OCI_ATTR_STMT_STATE and it
works. The following snippet illustrates this:
OCIAttrGet_stmhp_stat2(imp_sth, (OCIStmt*)phs->desc_h,
&stmt_state, 0,
OCI_ATTR_STMT_STATE, status);
The desc_h in the parameters must be an OCIStmt * or surely this would
not work.
In this case the REF CURSOR variable in question is explicitly null.
I would expect the value returned via OCI to be accompanied by an
indicator variable with a value of -1,indicating a null value.
If this is the case, the value of the output variable is,
to quote the OCI manual, 'unchanged'. It should be ignored.
I am suggesting that the indicator variable should be tested before
the looking at the value. If indicator is -1, the value could be
the cursor returned by a previous execution of the same statement
handle.
Thanks Charles.
I think your explanation means DBD::Oracle is even more broken than I
thought wrt to output cursors.
Looks like I need to do a bit more reading. Thanks for the pointer.
Out of interest I looked at the code a little more and see the
descriptor in the D::O's phs is freed and reallocated pre-execute. The
parameter is also rebound. In pp_exec_rset it does (note my comment
labelled MJE):
if (pre_exec) { /* pre-execute - allocate a statement handle -
MJE it does not do this now */
sword status;
/* extproc deallocates everything for us */
if (is_extproc)
return 1;
/* MJE what is the following test supposed to be - always
true? */
if (!phs->desc_h || 1) { /* XXX phs->desc_t != OCI_HTYPE_STMT) */
if (phs->desc_h) {
OCIHandleFree_log_stat(imp_sth, phs->desc_h,
phs->desc_t, status);
phs->desc_h = NULL;
}
phs->desc_t = OCI_HTYPE_STMT;
OCIHandleAlloc_ok(imp_sth, imp_sth->envhp, &phs->desc_h,
phs->desc_t, status);
}
phs->progv = (char*)&phs->desc_h;
phs->maxlen = 0;
OCIBindByName_log_stat(imp_sth, imp_sth->stmhp, &phs->bndhp,
imp_sth->errhp,
(text*)phs->name,
(sb4)strlen(phs->name),
phs->progv,
0,
(ub2)phs->ftype,
NULL, /* using &phs->indp triggers ORA-01001 errors! */
NULL,
0,
0,
NULL,
OCI_DEFAULT,
status);
However, as you said the phs->indp is -1 as you said. I will correct
my change and make it check indp first.
As you know there is a lot of OCI code in DBD::Oracle and I'm not
familiar with it all by a long way so I'm always grateful for any
pointers/help.
Martin
hmm, unfortunately, the indp always seems to be -1 (The selected value
is null, and the value of the output variable is unchanged) even when an
opened cursor is returned as indp is not passed to OCIBindByName because
(see above code) it is commented out in the OCIBindByName because it
apparently "triggers ORA-01001 errors" but we've no idea why - sigh. So
I assume somewhere else set indp to -1, it certainly wasn't OCIBindByName.
I see loads of code setting indp so I created an indp2 in the phs and
passed it to OCIBindByName above and it is always 0 (Oracle assigned an
intact value to the host variable) whether a null cursor is returned or
not. It also did not seem to trigger ORA-01001 (invalid cursor) errors.
Also the test suite works with the indp set in the OCIBindByName. What a
PITA. I really wish when people write code like this they comment why
better.
So it would seem resurrecting the phs->indp in the OCIBindByName does
not currently give me -1. Starting to wish I never started this. My
current change is better (in that it at least works whereas the previous
code did not at all) but you seem to suggest it is incomplete and that
concerns me. However, I've not been able to see what you suggested
should happen. I've already proved this speeds our application up a lot
compared with having to put a daft select 1 from dual in to just make
DBD::Oracle work so it would be a shame to fall at the last hurdle. Any
other ideas Charles?
Bear in mind I cannot be getting a value from a previous execute as my
test code only does one execute so perhaps when you bind a SYS_REFCURSOR
you get a valid stmt handle back even if it is not executed. It seems
this must be the case since I can call OCIAtrrGet(OCI_ATTR_STMT_STATE)
on it after only one execute and it returns OCI_STMT_STATE_INITIALIZED.
As always, most grateful for you taking the time to follow and comment
on this.
Martin
