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
