Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Null values in result record are overwritten with previous non-null data when using a prepared query more than once
  • Date: Wed, 17 Jun 2009 17:06:22 +0200

2009/6/16 Buurman, H.A. (Herbert) <h.a.buurman AT interlogica.nl>

> 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




Archive powered by MHonArc 2.6.24.

Top of Page