On Wed Mar 02 18:41:48 2011, THILO wrote:
Show quoted text> > there are no functions only procedures which return a value (your
> > definition of a function) and procedures which don't return a value.
>
> Is that a different mechanism from OUT parameters?
no.
{? call myproc(?)}
bind_param_inout(1, \my $procreturn);
bind_param_inout(1, \my $outparam);
Show quoted text> > > The preferred interface would be to offer :fetch for everything
that
Show quoted text> > > returns a single result set.
> >
> > You don't know there is a single result-set.
>
> Why not?
> If the procedure has a single select it will always return a single
> result set, no?
What I meant was you cannot examine the procedure via ODBC to know
whether you are going to 0 or more result-sets.
Show quoted text> The whole point of these :annotations is to give ProcedureCall some
> extra information (that cannot be determined by the module, but that
the
Show quoted text> user knows) about how to handle the function.
In that case, that is fine - if a user know it returns N result-sets.
Show quoted text> If you specify nothing, it will have to do some default behaviour
> (maybe just support OUT parameters), but for the sample you gave with
> the two result sets, I think I'd like to use say :fetch[[[]]]
> to get an array with two results, each of them an array of rows.
You could do that but you'll have to change
DBIx::ProcedureCall::DRIVER to return the prepared/executed sth and
DBIx::ProcedureCall::__fetch and the callers to pass the original sth
instead of the thing you get back as the function return value as the
"fetch" at the moment seems to use the function return value.
Also DBIx::ProcedureCall::__run_procedure would need changing to support
fetch as a procedure which does not return a value (as in a function)
can still produce result-sets.
Show quoted text> For really complex cases (or large data that needs to be streamed),
> we'd have to return the statement handle directly and leave it to the
> application to handle it.
yup
Show quoted text> Maybe call it :handle
ok
Show quoted text> > > I'd just want :fetch to work for "normal" cases.
> >
> > ok but I'm not sure what a normal case is.
>
> Naive definition of normal: a procedure that contains a single select
> and returns a single result set (of reasonably small size). For those,
> the same :fetch we have now should work well. Plus OUT parameters.
with the alterations I outline above.
Show quoted text> Extended "normal" case (needs some new :fetch types): Returns multiple
> result sets, which can be turned into array-of-fetches.
ok
Show quoted text> For those cases, it should not be necessary for application code to
> touch the statement handle.
>
>
agreed.
Attached a new ODBC.pm and ProcedureCall.pm hacked (and it is a hack
simply to demonstrate) to make a simple single select in a procedure
work (it changes sub arguments so it will break Oracle.pm/Postgres.pm as
it stands). Also I only tested it briefly using CLI which fails for
procedure returning a result-set but not returning like a function
because of:
# any fetch implies function
if ( grep /^fetch/, keys %attr ) {
$attr{'function'} = 1;
$attr{'fetch'} = 1;
}
in DBIx::ProcedureCall::run.
Also, to properly support output parameters in procedures that may
return result-sets the ODBC.pm would need to repeatedly call
odbc_more_results. I didn't do anything about this because it depends on
whether you are fetching or not and fetching from a procedure cannot
happen right now.
create procedure drop_me_4 @ival int as
select @ival;
perl -Iblib/lib -Iblib/arch -MDBIx::ProcedureCall::CLI -e 'function'
'drop_me_4:fetch[[]]' 1
executed function 'drop_me_4:fetch[[]]'.
Show quoted text------ result -----------
$VAR1 = [
[
'1'
]
];
1 rows returned
------------------------
Martin
--
Martin J. Evans
Wetherby, UK
