freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

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

From: "Buurman, H.A. (Herbert)" <h.a.buurman AT interlogica.nl>
To: <freetds AT lists.ibiblio.org>
Subject: [freetds] Null values in result record are overwritten with previous non-null data when using a prepared query more than once
Date: Tue, 16 Jun 2009 10:17:16 +0200

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

<html>
<body>
<pre style="font-size: 7.5pt; font-family: Arial; color: black; font-style:
italic; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm; margin-bottom:
.0001pt"> <pre style="font-size: 7.5pt; font-family: Arial; color:
black; font-style: italic; margin-left: 0cm; margin-right: 0cm; margin-top:
0cm; margin-bottom: .0001pt">Disclaimer

Deze e-mail is verzonden door Interlogica B.V. De inhoud en eventuele
bijlagen zijn vertrouwelijk en uitsluitend bestemd voor de
geadresseerde. Interlogica B.V. verzoekt bij onjuiste adressering direct
contact met haar op te nemen (<a
href="mailto:info AT interlogica.nl";>mailto:info AT interlogica.nl</a>),
de naam van de afzender en de geadresseerde aan haar op te geven en
vervolgens dit e-mailbericht uit uw systeem te verwijderen.
Interlogica B.V. is niet aansprakelijk voor het overbrengen van virussen en
het is uw verantwoordelijkheid deze e-mail (en eventuele
bijlagen) te controleren. Interlogica B.V. (geregistreerd bij het
Handelsregister nr. 08067177) is gevestigd te Ecofactorij 20,
NL-7325 WC Apeldoorn.

This is an e-mail from Interlogica B.V. Its contents and any attachments are
confidential and intended solely for the use of the
individual to whom it is addressed. If received in error, please contact
Interlogica B.V. (<a
href="mailto:info AT interlogica.nl";>mailto:info AT interlogica.nl</a>)
quoting the name of the sender and the addressee, then delete it from your
system. Please note that Interlogica B.V. does not accept any
responsibility for viruses and it is your responsibility to scan the email
and any attachments (if any). Interlogica B.V.
(registered with the Chamber of Commerce no. 08067177) is resident at
Ecofactorij 20, NL-7325 WC Apeldoorn, The Netherlands.


 
</body>
</html>

[freetds] Null values in result record are overwritten with previous non-null data when using a prepared query more than once, Buurman, H.A. (Herbert), 06/16/2009
- Re: [freetds] Null values in result record are overwritten with previous non-null data when using a prepared query more than once, Frediano Ziglio, 06/17/2009
- <Possible follow-up(s)>
- Re: [freetds] Null values in result record are overwritten with previous non-null data when using a prepared query more than once, Buurman, H.A. (Herbert), 06/22/2009