Re: Null values in result record are overwritten with previous non-null data when using a prepared query more than once

2009/6/16 Buurman, H.A. (Herbert) <[email protected]>

> Hello all,
>
>  This issue seems to happen from 0.82 (stable) onwards to today's nightly
> build.
>  Software used:
>    Perl 5.10.0 x86_64-linux-gnu-thread-multi
>    DBI v1.607
>    DBI::DBD v12.010405
>    DBD::ODBC v1.17
>    UnixODBC 2.2.11
>    FreeTDS 0.82 (stable)
>    TDS protocol version 8.0
>    Microsoft SQL Server Enterprise Edition v9.0.2047 (x86)
>  When using the above-mentioned software on the following table (DDL):
>        CREATE TABLE [dbo].[freetds_test] (
>          [identifier] char(1) COLLATE SQL_Latin1_General_CP850_CI_AS NOT
> NULL,
>          [value] int NULL,
>          PRIMARY KEY CLUSTERED ([identifier])
>        )
>        ON [PRIMARY]
>        GO
>  With data:
>        identifier      value
>        1               17
>        2               Null
>
>  And this code:
>    my $db_options = {
>        PrintError => 1,
>        RaiseError => 0,
>        LongReadLen => 200,
>        LongTruncOk => 1,
>        AutoCommit => 0,
>    };
>    my $db = DBI->connect
> ("dbi:ODBC:$DSNName",$db_username,$db_password,$db_options);
>    my @Identifiers = ('1', '2');
>    my $FreeTDS_Query = $db->prepare('SELECT Identifier, Value FROM
> freetds_test with(nolock) WHERE Identifier = ?') or
>      print("Error preparing query: " . $db->errstr . "\n");
>    my $Item_ref;
>    foreach (@Identifiers) {
>        $FreeTDS_Query->execute($_);
>        $Item_ref = $FreeTDS_Query->fetchrow_hashref();
>        foreach my $key (keys(%{$Item_ref})) {
>            print("$key => $Item_ref->{$key}, ");
>        }
>        print("\n");
>    }
>    $FreeTDS_Query->finish();
>    $db->disconnect();
>
>  Which prints the following output:
>        Value => 17, Identifier => 1,
>        Value => 17, Identifier => 2,
>
>  As you can see, the Null-value for the 2nd identifier is missing...
>  instead, the previous non-null value is returned.
>
>  This doesn't happen if the query is prepared and finished inside the
>  loop, but that's not really what I want :)
>
>  Is this a known issue or is there something to work around this?
>  (While still preparing the query outside the loop and with the use of
>  placeholders).
>
> Kind regards,
> H. Buurman
>

Hi,
  I tried to reproduce problem but without success... I tested from 0.82 to
current development but I got always right results, my environment

$ cat mao.pl
use strict;
use DBI;
my $DSNName = 'TESTSRV';
my $db_username = 'test';
my $db_password = 'test';
my $db_options = {
        PrintError => 1,
        RaiseError => 0,
        LongReadLen => 200,
        LongTruncOk => 1,
        AutoCommit => 0,
    };
    my $db = DBI->connect
("dbi:ODBC:$DSNName",$db_username,$db_password,$db_options);

$db->do("CREATE TABLE #freetds_test (
          [identifier] char(1) COLLATE SQL_Latin1_General_CP850_CI_AS NOT
NULL,
          [value] int NULL,
          PRIMARY KEY CLUSTERED ([identifier])
        )");

$db->do("INSERT INTO #freetds_test(identifier, value) VALUES(1,17)");
$db->do("INSERT INTO #freetds_test(identifier, value) VALUES(2,NULL)");
$db->do("INSERT INTO #freetds_test(identifier, value) VALUES(3,65432)");


    my @Identifiers = ('1', '2', '3');
    my $FreeTDS_Query = $db->prepare('SELECT Identifier, Value FROM
#freetds_test with(nolock) WHERE Identifier = ?') or
      print("Error preparing query: " . $db->errstr . "\n");
    my $Item_ref;
    foreach (@Identifiers) {
        $FreeTDS_Query->execute($_);
        $Item_ref = $FreeTDS_Query->fetchrow_hashref();
        foreach my $key (keys(%{$Item_ref})) {
            print("$key => $Item_ref->{$key}, ");
        }
        print("\n");
    }
    $FreeTDS_Query->finish();
    $db->disconnect();

$ TDSDUMP=stdout perl mao.pl | head -4
16:57:51.750274 (log.c:196):Starting log file for FreeTDS 0.83.dev.20090617
        on 2009-06-17 16:57:51 with debug flags 0x6fff.
16:57:51.750780 (iconv.c:336):tds_iconv_open(0x81cfba8, ISO-8859-1)
16:57:51.751347 (iconv.c:197):local name for ISO-8859-1 is ISO-8859-1

$ perl mao.pl
Value => 17, Identifier => 1,
Value => , Identifier => 2,
Value => 65432, Identifier => 3,

$ rpm -qa | grep -i odbc
php-odbc-5.2.6-2.fc8
unixODBC-devel-2.2.12-5.fc8
unixODBC-2.2.12-5.fc8
mysql-connector-odbc-3.51.14r248-2.fc8
perl-DBD-ODBC-1.15-1.rf

$ rpm -qa | grep DBI
perl-DBI-1.58-2.fc8

$ perl -v

This is perl, v5.8.8 built for i386-linux-thread-multi

I'll try with a x86_64 machine.

You could try to enable ODBC logging adding these settings to
/etc/odbcinst.ini

[ODBC]
Trace           = Yes
TraceFile               = /tmp/sql.log
ForceTrace              = No
Pooling         = No

(remember to disable when done, they slow down ODBC a lot!)

freddy77