Ok, I'll fix the SQL_NULL_DATA problem and let you know.
Thanks,
Jeff
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of [EMAIL PROTECTED]
> Sent: Wednesday, September 05, 2001 9:10 AM
> To: [EMAIL PROTECTED]
> Subject: RE: :ODBC-0.29 developers release
>
>
> 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
>
