Thanks -- my answers are mixed in.
>
> Sorry for not trying this out sooner but I have been on holiday.
> I cannot say I have exhausively tested it but here are a few comments:
>
> [1] dbdimp.c and ODBC.pm appear to be DOS files and so pod2man fails
> on them during make (at least it did for me).
>
Fixed.
> [2] In ODBC.pm my name appears to be down as Martin Brimer instead of
> Martin Evans (brimer is the name of my machine so I see where it
> came from).
Fixed -- sorry.
>
> [3] The make test ran to MS SQL Server and DB2 fine and some of
> my own tests
> ran fine.
Great!
>
> [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(?, ?) }
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.
>
> [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.
>
> I hope this proves helpful.
It's very helpful. I'm working on .30 now, and started on the array
binding. 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.
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?
THanks,
Jeff
