Bug #20917 for DBD-ODBC: CLOB Query returns undef when CLOB text value is empty string

Wed Aug 09 14:04:03 2006 D-Beaudet [...] NGA.GOV - Ticket created

Subject:	CLOB Query returns undef when CLOB text value is empty string
Date:	Wed, 9 Aug 2006 14:03:39 -0400
To:	<bug-DBD-ODBC [...] rt.cpan.org>
From:	"Beaudet, David P." <D-Beaudet [...] NGA.GOV>

[ select myclob from mytable where othercolumn = 12345 ] where the myclob value is empty string ('') rather than NULL results in an undefined value being returned for the column instead of the empty string, effectively making it indistinguishable from cases where myclob is NULL. I'm using UnixODBC on Fedora Core 5 with SQL Server 2000 backend. ------------------------------------------------------------------------ ----------- Dave Beaudet d-beaudet@nga.gov (202) 312-2755 ------------------------------------------------------------------------ ----------- "There is no way to peace. Peace is the way."

Wed Jul 18 09:48:13 2007 bohica [...] ntlworld.com - Correspondence added

Seems to work for me: create table lob (clob1 varchar(max), blob1 varbinary(max)); insert into lob values ('', NULL); perl -e 'use Data::Dumper;use DBI DBI->connect("dbi:ODBC:sqls","xx","yyy");$s = $h->selectall_arrayref("select * from lob");print Dumper($s);' $VAR1 = [ [ '', undef ] ]; with DBD::ODBC 1.14 and DBI 1.58. Perhaps you could be more specific, tell me versions, show schema and provide code.

Wed Jul 18 09:48:15 2007 The RT System itself - Status changed from 'new' to 'open'

Wed Jul 18 10:49:34 2007 D-Beaudet [...] NGA.GOV - Correspondence added

Subject:	RE: [rt.cpan.org #20917] CLOB Query returns undef when CLOB text value is empty string
Date:	Wed, 18 Jul 2007 10:48:51 -0400
To:	<bug-DBD-ODBC [...] rt.cpan.org>
From:	"Beaudet, David P." <D-Beaudet [...] NGA.GOV>

I'll try to dig up more info today on this. Show quoted text

-----Original Message----- From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] Sent: Wed 7/18/2007 9:48 AM To: Beaudet, David P. Subject: [rt.cpan.org #20917] CLOB Query returns undef when CLOB text value is empty string <URL: http://rt.cpan.org/Ticket/Display.html?id=20917 > Seems to work for me: create table lob (clob1 varchar(max), blob1 varbinary(max)); insert into lob values ('', NULL); perl -e 'use Data::Dumper;use DBI DBI->connect("dbi:ODBC:sqls","xx","yyy");$s = $h->selectall_arrayref("select * from lob");print Dumper($s);' $VAR1 = [ [ '', undef ] ]; with DBD::ODBC 1.14 and DBI 1.58. Perhaps you could be more specific, tell me versions, show schema and provide code.

Download winmail.dat
application/ms-tnef 3.2k

Message body not shown because it is not plain text.

Tue Oct 09 05:25:17 2007 bohica [...] ntlworld.com - Status changed from 'open' to 'stalled'

Wed Apr 14 14:20:33 2010 bohica [...] ntlworld.com - Correspondence added

As I've nothing on this in over 6 months I am going to close this rt. If you have a problem with that let me know and I'll reopen it. Martin -- Martin J. Evans Wetherby, UK On Wed Jul 18 10:49:34 2007, D-Beaudet@NGA.GOV wrote: Show quoted text

> > I'll try to dig up more info today on this. > > -----Original Message----- > From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Wed 7/18/2007 9:48 AM > To: Beaudet, David P. > Subject: [rt.cpan.org #20917] CLOB Query returns undef when CLOB text > value is empty string > > > <URL: http://rt.cpan.org/Ticket/Display.html?id=20917 > > > Seems to work for me: > > create table lob (clob1 varchar(max), blob1 varbinary(max)); > insert into lob values ('', NULL); > > perl -e 'use Data::Dumper;use DBI > DBI->connect("dbi:ODBC:sqls","xx","yyy");$s = > $h->selectall_arrayref("select * from lob");print Dumper($s);' > $VAR1 = [ > [ > '', > undef > ] > ]; > > with DBD::ODBC 1.14 and DBI 1.58. > > Perhaps you could be more specific, tell me versions, show schema and > provide code. >

Wed Apr 14 14:20:34 2010 The RT System itself - Status changed from 'stalled' to 'open'

Wed Apr 14 14:20:50 2010 bohica [...] ntlworld.com - Status changed from 'open' to 'rejected'

Wed Apr 14 14:28:29 2010 D-Beaudet [...] NGA.GOV - Correspondence added

Subject:	RE: [rt.cpan.org #20917] CLOB Query returns undef when CLOB text value is empty string
Date:	Wed, 14 Apr 2010 14:28:11 -0400
To:	<bug-DBD-ODBC [...] rt.cpan.org>
From:	"Beaudet, David" <D-Beaudet [...] NGA.GOV>

That's fine, I've not had the time to research further and found a workaround. Show quoted text

> -----Original Message----- > From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Wednesday, April 14, 2010 2:21 PM > To: Beaudet, David > Subject: [rt.cpan.org #20917] CLOB Query returns undef when CLOB text > value is empty string > > <URL: https://rt.cpan.org/Ticket/Display.html?id=20917 > > > As I've nothing on this in over 6 months I am going to close this rt.

If Show quoted text

> you have a problem with that let me know and I'll reopen it. > > Martin > -- > Martin J. Evans > Wetherby, UK > > On Wed Jul 18 10:49:34 2007, D-Beaudet@NGA.GOV wrote:

> > > > I'll try to dig up more info today on this. > > > > -----Original Message----- > > From: Martin J Evans via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > > Sent: Wed 7/18/2007 9:48 AM > > To: Beaudet, David P. > > Subject: [rt.cpan.org #20917] CLOB Query returns undef when CLOB

text Show quoted text

> > value is empty string > > > > > > <URL: http://rt.cpan.org/Ticket/Display.html?id=20917 > > > > > Seems to work for me: > > > > create table lob (clob1 varchar(max), blob1 varbinary(max)); > > insert into lob values ('', NULL); > > > > perl -e 'use Data::Dumper;use DBI > > DBI->connect("dbi:ODBC:sqls","xx","yyy");$s = > > $h->selectall_arrayref("select * from lob");print Dumper($s);' > > $VAR1 = [ > > [ > > '', > > undef > > ] > > ]; > > > > with DBD::ODBC 1.14 and DBI 1.58. > > > > Perhaps you could be more specific, tell me versions, show schema

and Show quoted text

> > provide code. > >

Wed Apr 14 14:28:30 2010 The RT System itself - Status changed from 'rejected' to 'open'

Thu Apr 15 03:56:18 2010 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'