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.
