On 19/12/2010 23:15, Darin Burleigh wrote:
Weird error SELECTing from SQL Server 2005 via ODBC, which shows under the
following
conditions:
* select contains 'left outer join' to a secondary table
* select contains an 'order by' clause
* the DBI->connect statement contains the option: 'odbc_SQL_ROWSET_SIZE' => 2
If the join doesn't match a row in the secondary table, *all* values return
are NULL, even the
values in the primary table, as in rows 2 and 3 below:
--
5 rows
1 ) plant=yellow coneflower fk=3
2 ) plant= fk=ERROR - not defined
3 ) plant= fk=ERROR - not defined
4 ) plant=elm-leafed goldenrod fk=3
5 ) plant=bottle gentian fk=5
--
If I comment out the join, or the order by, or the connect attribute,
or if I change the connect attribute value to '1', I get the expected result:
--
5 rows
1 ) plant=yellow coneflower fk=3
2 ) plant=obediant plant fk=100
3 ) plant=purple coneflower fk=
4 ) plant=elm-leafed goldenrod fk=3
5 ) plant=bottle gentian fk=5
---
FYI, I need the ROWSET option, because my application uses nested cursors.
Example code is show below.
I have run this successfully on SQL Server 2000, so I suspect that some option
related to SS 2005 may be the issue. Any clues would be appreciated.
System:
OS: Win2003 server
DB: SQL Server 2005, Developer edition
Perl: v 5.8.8, ActiveState
Modules: DBI 1.52, DBD::ODBC v1.22
Code:
use DBI;
print "connecting...\n";
my($dsn, $dbh, $db_user, $db_pwd);
$dsn = 'dbi:ODBC:plants';
$dbh = DBI->connect($dsn, $db_user, $db_pwd,
{'odbc_SQL_ROWSET_SIZE' => 2 } )
|| die("dbi connection failed: dsn=$dsn \n");
print "check: dbh=$dbh, err=" . $DBI::errstr . "\n";
$query = qq{ select plant.name as plant, plant.color_id
from plant
left outer join color on plant.color_id = color.color_id
order by plant.plant_id
};
$dbh->{TraceLevel} = 15;
$sth = $dbh->prepare( $query );
$sth->execute( );
$rows = $sth->fetchall_arrayref();
print int(@$rows) . " rows \n";
$n=0;
foreach ( @$rows ) {
$n++;
print "$n ) ";
print ' plant=' . $_->[0] . " fk=" . $_->[1] ;
if ( ! defined $_->[0] ) {
print "ERROR - not defined ";
}
elsif ( ! $_->[0] ) {
print "ERROR - no value ";
}
print "\n";
}
Darin Burleigh
Software Engineer
CDW LLC.
It is late at night for me (nearly 12pm) so I'm not going to suggest
I've taken in all the information you have provided fully - bare this in
mind. If you set odbc_SQL_ROWSET_SIZE it does not alter a single bit of
code in DBD::ODBC after setting this value in the driver so I'd be
extremely surprised if this was an issue in DBD::ODBC. I can see you've
run this successfully to another version of SQL Server or the SQL Server
ODBC driver and that makes me even more confident that the issue does
not exist in DBD::ODBC. Later versions of SQL Server support MAS
(multiple Active Statements) via the MARS_Connection attribute which is
usually set to Yes. Right now I'm not in a position to check this but if
you run DBD::ODBC's tests again it tries to detect if your
database/ODBC_driver supports MARS.
MS's drivers change over time and it is possible you need to adopt
different connect settings between different versions of SQL Server or
their ODBC Driver. It may prove useful to run DBD::ODBC's test suite
against both SQL Servers with VERBOSE set and post the results but I'm
still betting this is a driver or SQL Server difference rather than a
DBD::ODBC issues.
Martin
