Jeff,
Thanks for the fixes - I have left them out of this email.
On 04-Sep-2001 Jeff Urlwin wrote:
>> Martin said:
>> [4] Simple bind_param_inout() to MS SQL Server seems to work fine.
>>
>> However, what I would have liked to be able to do is get a procedures
>> return status and I wasn't sure how I could do that. e.g
>>
>> create proc jenn2 @status integer output as
>> begin
>> set @status = 99 return @status
>> end
>>
>> works OK when doing:
>>
>> $sql = qq/ {call jenn2(?)} /;
>> my $result;
>> my $sth = $dbh->prepare( $sql );
>>
>> $result = '1';
>> $sth->bind_param_inout( 1, \$result, 100);
>> $sth->execute();
>> $sth->finish();
>> print "result = ", $result, "\n";
>>
>> but if the procedure is defined as:
>>
>> create proc jenn2 as begin
>> declare @status integer
>> set @status = 99
>> return @status
>> end
>>
>> then I cannot work out how to successfully get the return status from
>> "{? = call jenn2}" as it is only an output parameter and not
>> an input and
>> output parameter.
>>
>> I also recollect Jeff saying there was some issue with input/output
>> parameters and NULLs in Oracle so if anyone can send me the
>> code they were
>> using to test this I will try that out to MS SQL Server and a
>> few other ODBC
>> drivers.
>
> You should be able, as long as you declare it as a "function", not a
> procedure, return a value. Note, that my terminology is DEFINITELY Oracle
> centric. You can call it an inout parameter (with Oracle's driver, at
> least). That was an area I was uncomfortable with, originally, but I have
> tested:
> my $sth = $dbh->prepare("{ ? = call testfunc(?, ?) }");
> $sth->bind_param_inout(1, \$value, 50, SQL_INTEGER);
>
> Here is a code snipped I have not tested yet for SQL server, but supposedly
> should work...<G>
>
> $dbh->do('CREATE FUNCTION testfunc (@p1 int, @p2 int) RETURNS INT AS BEGIN
> RETURN (@p1+@p2) END');
>
> Your mileage may vary and should fit the above { ? = call testfunc(?, ?) }
Thanks. This seems to work fine for me now. I did:
my $sql = qq/ { ? = call testfunc(?, ?) } /;
my $sth = $dbh->prepare( $sql );
$result = '1';
my $one="1";
my $two="2";
$sth->bind_param_inout( 1, \$result, 100);
$sth->bind_param( 2, $one);
$sth->bind_param( 3, $two);
$sth->execute();
$sth->finish();
print "result = ", $result, "\n";
and got:
[martin@brimer martin]$ perl func.pl
result = 3
However, I have now managed to do it all with procedures - see below.
> Where I had problems was binding a NULL to a parameter. That seemed to
> cause a lot of issues for Oracle's ODBC driver.
>
> Please look at mytest\testinout.pl.
Done. Here is some code to MS SQL Server I have used:
my $sql;
$dbh->do("drop procedure myproc1");
$dbh->do("drop procedure myproc2");
$dbh->do("drop procedure myproc3");
$sql = qq /
create proc myproc1 \@status integer output as
begin
set \@status = \@Status + 99
return \@status
end /;
$dbh->do($sql);
$sql = qq /
create proc myproc2 \@status integer output, \@param varchar(64) as
declare \@cError integer
begin
set \@cError = 1
set \@Status = 2
if \@Status = 2
begin
exec \@Status = myproc1 \@Status
set \@cError = 3
end
return \@cError
end /;
$dbh->do($sql);
$sql = qq /
create proc myproc3 \@a integer, \@b integer output as
begin
if \@b is null
begin
set \@b = 0
end
set \@b = \@b + 1
return \@a * \@b + 1
end /;
$dbh->do($sql);
$sql = qq/ {call myproc1(?)} /;
my $result;
my $sth = $dbh->prepare( $sql );
$result = '1';
$sth->bind_param_inout( 1, \$result, 50);
$sth->execute();
$sth->finish();
print "result = ", $result, "\n";
$sql = qq/ { ? = call myproc2(?, ?) } /;
$sth = $dbh->prepare( $sql );
$result = '1';
my $p1=1;
my $p2="string";
$sth->bind_param_inout( 1, \$result, 50);
$sth->bind_param_inout( 2, \$p1, 50);
$sth->bind_param( 3, $p2);
$sth->execute();
$sth->finish();
print "result = ", $result, " p1 = ", $p1, " p2 = ", $p2, "\n";
#
#
#
$sql = qq/ { ? = call myproc3(?, ?) } /;
$sth = $dbh->prepare( $sql );
$result = '1';
$p1=10;
$p2=30;
$sth->bind_param_inout( 1, \$result, 50, SQL_INTEGER);
$sth->bind_param( 2, $p1, SQL_INTEGER);
$sth->bind_param_inout( 3, \$p2, 50);
$sth->execute();
$sth->finish();
print "result = ", $result, " p1 = ", $p1, " p2 = ", $p2, "\n";
$result = '1';
$p1=10;
$p2=undef;
$sth->bind_param_inout( 1, \$result, 50, SQL_INTEGER);
$sth->bind_param( 2, $p1, SQL_INTEGER);
$sth->bind_param_inout( 3, \$p2, 50);
$sth->execute();
$sth->finish();
print "result = ", $result, " p1 = ", $p1, " p2 = ", $p2, "\n";
It all works fine except the undef case at the end calling myproc3.
The code does in fact fall over in the OOB client (which I'd rather it didn't
and will make OOB stop this happening) but the actual problem appears to be in
DBD::ODBC. It appears that because the third parameter is defined as
SQL_NULL_DATA the call to SQLBindParameter looks like this:
SQLBindParameter(0x81bde60,3,2(::Input/Output),1,12,80,0,(nil),50,0x81bd584)
i.e. DataPtr (3rd last arg) is NULL and StrLen_or_IndPtr (last arg) is valid
but set to SQL_NULL_DATA (-1). This is fine for an INPUT-only parameter but now
there is nowhere to put the returned parameter data. I think the call to
SQLBindParameter should be as above but with DataPtr set to an area of memory
containing room for the specified 50 bytes (2nd last arg).
Undoubtably this would make Oracle fall over as well which would explain what
you were seeing.
>>
>> [5] I have tried the change submitted by Andrew Brown and I have
>> to admit to
>> having some reservations with it. If I understand correctly
>> what Andrew was
>> trying to achieve was multiple active statements in MS SQL
>> Server. i.e. you
>> can do something like:
>>
>> #$dbh->{SQL_ROWSET_SIZE} = 2;
>> my ($sql1, $sql2);
>> $sql1 = qq/ select * from bench_char /;
>> $sql2 = qq/ select * from bench_int /;
>>
>> my $sth1 = $dbh->prepare($sql1);
>> my $sth2 = $dbh->prepare($sql2);
>>
>> $sth1->execute();
>> $sth2->execute();
>> my @row;
>> @row = $sth1->fetchrow_array;
>> print @row, "\n";
>> @row = $sth2->fetchrow_array;
>> print @row, "\n";
>>
>> $sth1->finish();
>> $sth2->finish();
>>
>> With the $dbh->{SQL_ROWSET_SIZE} = 2 commented out MS SQL
>> Server returns:
>>
>> DBD::ODBC::st execute failed: [unixODBC][Microsoft][ODBC SQL Server
>> Driver]Connection is busy with results for another hstmt
>> (SQL-S1000)(DBD:
>> st_execute/SQLExecute err=-1) at perl_rowset.pl line 26.
>>
>> as you would expect but with $dbh->{SQL_ROWSET_SIZE} = 2
>> uncommented it
>> works. I suspect this is more by accident than design. My
>> concerns are:
>>
>> [a] forcing a cursor change could affect the result-set and
>> could in fact
>> mean the SQL cannot even be executed.
>> A trivial example would be setting a KEYSET cursor and
>> doing "select
>> 'hello' from table".
>>
>> [b] There is an assumption that setting SQL_ROWSET_SIZE will
>> have no effect
>> as SQL_ROWSET_SIZE only applies to SQLExtendedFetch and
>> DBD::ODBC never
>> calls SQLExtendedFetch. This is potentially flawed as an
>> ODBC driver
>> manager may map SQLFetch calls to SQLExtendedFetch and
>> SQL_ROWSET_SIZE
>> is passed through to the driver regardless (i.e. when an ODBC 3.0
>> application calls an ODBC driver this happens). This
>> could then cause
>> serious corruption.
>
> I believe, then, that we should document it. I'll paraphrase what you have
> here, but I'd like to leave it in, as it may be helpful to those who know
> how to use it and/or need it.
OK.
>>
>> I hope this proves helpful.
>
> It's very helpful. I'm working on .30 now, and started on the array
> binding.
Very much looking forward to this - if I can help let me know.
> Of course, as I suspected, the array binding is VERY tied into the
> same sets of calls as the rest, so the patch will take me a bit to apply, as
> I want apply by hand.
>
> BTW, I was wondering if at some point we should be using SQLExtendedFetch
> and have a (settable) CACHE of rows for people.
I would advise against using SQLExtendedFetch throughout - less of a problem if
it was only used from array bound fetching. Although most drivers have it many
don't support more than FETCH_NEXT for the direction.
> Your thoughts (and others) would be greatly appreciated. I realize there
> are some issues in supporting SQLExtendedFetch, however, shouldn't the
> driver manager hide it, if the driver itself doesn't support it? We
> *should* only need a 3.x driver manager, right?
I would say a 3.0 driver manager was a definite requirement however it does not
help if a driver does not have SQLExtendedFetch - there is no mapping from
SQLExtendedFetch to anything else. In addition, you cannot mix calls to
SQLFetch/SQLExtendedFetch/SQLFetchScroll on the same statement.
I don't want to do it myself because it depresses me too much but take a look
at PHP's php_odbc.c module and see the mess that has turned in to regarding
SQLExtendedFetch, cursors and driver-specific conditional compilation - just a
warning, I don't expect this to happen here for a moment.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
