DBI / S::S / DBD::CSV ... related hackathon
Hi all, as I know some of us are located close together, I suggested a hackathon especially for Pure-Perl drivers, their tests and integration. My goals were: 1) having a lot of DBD::CSV tests (DBI related like ChopBlanks) moved or copied and maintained in DBI == a lot of tests simply test former DBD::File features, from times where DBD::File and DBD::CSV were much closer together and not so many other Pure-Perl DBD's in wildlife But now - there're DBD::DBM, DBD::AnyData, DBD::Sys, ... and upcoming DBD::Mem - which all should benefit from the impressive test cases written in DBD::CSV (and not only a turn later with next DBI release ^^) 2) let DBD::CSV use DBI::DBD to create tests for DBI::Nano, Gofer, different baackends/serializers ... == DBI::DBD has a neat feature function (dbd_edit_mm_attribs) to create tests using some different whatsoever: Prefix - means * p = DBI_PUREPERL=2 * g = DBI_AUTOPROXY='dbi:Gofer:transport=null;policy=pedantic' * n = DBI_SQL_NANO=1 All with restrictions for filename patterns etc. This function needs to be refactored to provide that functionality for other modules, too - and then used for DBD::DBM (create test combos for several backends and serializers), DBD::CSV (create test combos for S::S vs. Nano and Text::CSV vs. Text::CSV_XS), maybe SQL::Statement to create test combos for each available S::S using DBD (like currently done by looping e.g. t/02execute.t in SQL-Statement) We (Merijn and myself) discussed that roughly in IRC - but decided it would be a good idea to increase the count of affected/invited people (like end-users of our services as ribasushi or Dijkmat ^^). I suggested to find a location like the Unperfekthaus (http://www.unperfekthaus.de/) in reachable distance to all attendees. So at first I would be great if all interested persons can tell if they would like to join - let's say until end of January (for a date in March/April 2013) with a limit how far they're willing to travel. Everyone is invited (regardless of his/her personal participation) to suggest topics. Second step is to create a more or less complete plan for those who want which goals shall be reached (to approve whether there're prerequisites which needs to be fulfilled before ...). Thanks for attention, Jens
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference
cursor. However, sometimes the reference cursor is not opened and only
the procedure knows this. The problem is if I call the procedure from
DBD::Oracle and the cursor is not opened I get an Oracle error saying
the cursor is not executed:
test procedure:
procedure p_n2(pcur OUT SYS_REFCURSOR) AS
begin
pcur := NULL;
end;
example perl:
my $s = $h-prepare(q/begin mypkg.p_n2(?); end;/);
$s-bind_param_inout(1, \my $cursor, 100, {ora_type = ORA_RSET});
$s-execute; # errors
The error occurs because DBD::Oracle attempts to call dbd_describe on
the returned cursor (before perl land even sees it) and that code does
things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows
if the statement is not executed.
An easy solution is to just open an empty cursor if the procedure cannot
open a real one by doing something like:
open pcur for select 1 from dual;
but I don't like that as DBD::Oracle will make dozens of calls and do
quite a bit of work in dbd_describe which is wasting time and the
purpose of the change to my procedure is to speed this application up
not slow it down.
Martin, I agree that you have found a bug which ought to be fixed.
However I think that the bug is much more fundamental, and much
simpler than your analysis suggests.
The DBI convention for database null values is to represent them
by Perl undefined values. There is no reason why this convention
should not apply to result sets.
The perl code needs to test the indicator variable associated with
the supposed result set. If this variable indicates a null value,
all processing of actual value returned by Oracle should be skipped,
as with any other null value. The pre-created perl statement handle
should be left alone. It may me used in a subsequent execute of the
same statement with different bind values. The value returned to
perl should be undef, not a reference to the magic statement handle.
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/13 10:56, Charles Jardine wrote:
On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference
cursor. However, sometimes the reference cursor is not opened and only
the procedure knows this. The problem is if I call the procedure from
DBD::Oracle and the cursor is not opened I get an Oracle error saying
the cursor is not executed:
test procedure:
procedure p_n2(pcur OUT SYS_REFCURSOR) AS
begin
pcur := NULL;
end;
example perl:
my $s = $h-prepare(q/begin mypkg.p_n2(?); end;/);
$s-bind_param_inout(1, \my $cursor, 100, {ora_type = ORA_RSET});
$s-execute; # errors
The error occurs because DBD::Oracle attempts to call dbd_describe on
the returned cursor (before perl land even sees it) and that code does
things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows
if the statement is not executed.
An easy solution is to just open an empty cursor if the procedure cannot
open a real one by doing something like:
open pcur for select 1 from dual;
but I don't like that as DBD::Oracle will make dozens of calls and do
quite a bit of work in dbd_describe which is wasting time and the
purpose of the change to my procedure is to speed this application up
not slow it down.
Martin, I agree that you have found a bug which ought to be fixed.
However I think that the bug is much more fundamental, and much
simpler than your analysis suggests.
The DBI convention for database null values is to represent them
by Perl undefined values. There is no reason why this convention
should not apply to result sets.
The perl code needs to test the indicator variable associated with
the supposed result set. If this variable indicates a null value,
all processing of actual value returned by Oracle should be skipped,
as with any other null value. The pre-created perl statement handle
should be left alone. It may me used in a subsequent execute of the
same statement with different bind values. The value returned to
perl should be undef, not a reference to the magic statement handle.
Thanks for your comments Charles.
In effect I believe my second patch does what you describe. If you define an
output SYS_REFCURSOR in a procedure but don't open it you still get a cursor
back but it is only initialised and not executed. My second patch (the one I
prefer) looks at the state of the cursor and if it is initialised but not
executed it avoids creating a DBI sth and that output parameter is seen as
undef in perl land.
If I've misunderstood you please put me right.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/13 11:20, Martin J. Evans wrote:
On 15/01/13 10:56, Charles Jardine wrote:
On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference
cursor. However, sometimes the reference cursor is not opened and only
the procedure knows this. The problem is if I call the procedure from
DBD::Oracle and the cursor is not opened I get an Oracle error saying
the cursor is not executed:
test procedure:
procedure p_n2(pcur OUT SYS_REFCURSOR) AS
begin
pcur := NULL;
end;
example perl:
my $s = $h-prepare(q/begin mypkg.p_n2(?); end;/);
$s-bind_param_inout(1, \my $cursor, 100, {ora_type = ORA_RSET});
$s-execute; # errors
The error occurs because DBD::Oracle attempts to call dbd_describe on
the returned cursor (before perl land even sees it) and that code does
things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows
if the statement is not executed.
[snip]
Martin, I agree that you have found a bug which ought to be fixed.
However I think that the bug is much more fundamental, and much
simpler than your analysis suggests.
The DBI convention for database null values is to represent them
by Perl undefined values. There is no reason why this convention
should not apply to result sets.
The perl code needs to test the indicator variable associated with
the supposed result set. If this variable indicates a null value,
all processing of actual value returned by Oracle should be skipped,
as with any other null value. The pre-created perl statement handle
should be left alone. It may me used in a subsequent execute of the
same statement with different bind values. The value returned to
perl should be undef, not a reference to the magic statement handle.
Thanks for your comments Charles.
In effect I believe my second patch does what you describe. If you
define an output SYS_REFCURSOR in a procedure but don't open it you
still get a cursor back but it is only initialised and not executed. My
second patch (the one I prefer) looks at the state of the cursor and if
it is initialised but not executed it avoids creating a DBI sth and that
output parameter is seen as undef in perl land.
If I've misunderstood you please put me right.
When a variable of a REF CURSOR type is declared is is initially
atomically null. It does not refer to cursor until it is OPENed,
or set to a non-null value in some other way.
Are you saying that, in the case of a NULL variable, the indicator
variable does not indicate nullity?
--
Charles Jardine - Computing Service, University of Cambridge
c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
On 15/01/13 15:04, Charles Jardine wrote:
On 15/01/13 11:20, Martin J. Evans wrote:
On 15/01/13 10:56, Charles Jardine wrote:
On 11/01/13 16:04, Martin J. Evans wrote:
I am using DBD::Oracle and calling a procedure which returns a reference
cursor. However, sometimes the reference cursor is not opened and only
the procedure knows this. The problem is if I call the procedure from
DBD::Oracle and the cursor is not opened I get an Oracle error saying
the cursor is not executed:
test procedure:
procedure p_n2(pcur OUT SYS_REFCURSOR) AS
begin
pcur := NULL;
end;
example perl:
my $s = $h-prepare(q/begin mypkg.p_n2(?); end;/);
$s-bind_param_inout(1, \my $cursor, 100, {ora_type = ORA_RSET});
$s-execute; # errors
The error occurs because DBD::Oracle attempts to call dbd_describe on
the returned cursor (before perl land even sees it) and that code does
things like call OCIAttrGet for PARAM_COUNT etc which Oracle disallows
if the statement is not executed.
[snip]
Martin, I agree that you have found a bug which ought to be fixed.
However I think that the bug is much more fundamental, and much
simpler than your analysis suggests.
The DBI convention for database null values is to represent them
by Perl undefined values. There is no reason why this convention
should not apply to result sets.
The perl code needs to test the indicator variable associated with
the supposed result set. If this variable indicates a null value,
all processing of actual value returned by Oracle should be skipped,
as with any other null value. The pre-created perl statement handle
should be left alone. It may me used in a subsequent execute of the
same statement with different bind values. The value returned to
perl should be undef, not a reference to the magic statement handle.
Thanks for your comments Charles.
In effect I believe my second patch does what you describe. If you
define an output SYS_REFCURSOR in a procedure but don't open it you
still get a cursor back but it is only initialised and not executed. My
second patch (the one I prefer) looks at the state of the cursor and if
it is initialised but not executed it avoids creating a DBI sth and that
output parameter is seen as undef in perl land.
If I've misunderstood you please put me right.
When a variable of a REF CURSOR type is declared is is initially
atomically null. It does not refer to cursor until it is OPENed,
or set to a non-null value in some other way.
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.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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
Re: Problem with procedures returning a SYS_REFCURSOR which is not open/executed - possible fixes
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
