Skip Menu |
 
rt.cpan.org will be shut down on March 1st, 2021.

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 46873
Status: open
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: HMBRAND [...] cpan.org
Cc: VLYON [...] cpan.org
AdminCc:

Bug Information
Severity: Important
Broken in: (no value)
Fixed in: (no value)



Subject: the {TYPE} attribute of statement handles is not numeric
Download (untitled) / with headers
text/plain 774b
/home/merijn 108 > perl -MDBI -wle'$_=DBI->connect ("dbi:Pg:",undef,undef)->prepare("select * from foo");$_->execute;print $_->{TYPE}[0]' 4 /home/merijn 109 > perl -MDBI -wle'$_=DBI->connect ("dbi:SQLite:dbname=foo",undef,undef)->prepare("select * from foo");$_- Show quoted text
>execute;print$_->{TYPE}[0]'
integer /home/merijn 110 > ->{TYPE} is supposed to be numeric (the ANSI value of the type) $dbh->type_info ($sth->{TYPE})->{TYPE_NAME} should be available to return the string representation of that type for the current database type Oracle, PostgreSQL, Unify and CSV all do it right DBI docs say: For example, to find the type name for the fields in a select statement you can do: @names = map { scalar $dbh->type_info ($_)->{TYPE_NAME} } @ { $sth->{TYPE} }
Download (untitled) / with headers
text/plain 404b
On Fri Jun 12 02:43:18 2009, HMBRAND wrote: Show quoted text
> ->{TYPE} is supposed to be numeric (the ANSI value of the type)
One problem with this is that each row fetched may return different types to what the column is defined as. A column defined as INTEGER might return a string like 'Hello'. So what ANSI type should $sth->{TYPE}[$column_index] return - the type for the whole column or for the next row fetched?
Subject: Re: [rt.cpan.org #46873] the {TYPE} attribute of statement handles is not numeric
Date: Tue, 29 Sep 2009 16:45:52 +0200
To: bug-DBD-SQLite [...] rt.cpan.org
From: "H.Merijn Brand" <h.m.brand [...] xs4all.nl>
On Tue, 29 Sep 2009 10:38:54 -0400, "Vernon Lyon via RT" <bug-DBD-SQLite@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=46873 > > > On Fri Jun 12 02:43:18 2009, HMBRAND wrote:
> > ->{TYPE} is supposed to be numeric (the ANSI value of the type)
> > One problem with this is that each row fetched may return different > types to what the column is defined as. A column defined as INTEGER > might return a string like 'Hello'.
Are you sure? Really? Is SQLite the only DB that allows this? Show quoted text
> So what ANSI type should $sth->{TYPE}[$column_index] return - the type > for the whole column or for the next row fetched?
I would say for the whole column. -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00, 11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3. http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
On Tue Sep 29 10:46:11 2009, h.m.brand@xs4all.nl wrote:
Show quoted text
> On Tue, 29 Sep 2009 10:38:54 -0400, "Vernon Lyon via RT"
> <bug-DBD-SQLite@rt.cpan.org> wrote:
>
> > <URL: https://rt.cpan.org/Ticket/Display.html?id=46873 >
> >
> > On Fri Jun 12 02:43:18 2009, HMBRAND wrote:
> > > ->{TYPE} is supposed to be numeric (the ANSI value of the type)
> >
> > One problem with this is that each row fetched may return different
> > types to what the column is defined as. A column defined as INTEGER
> > might return a string like 'Hello'.
>
> Are you sure? Really? Is SQLite the only DB that allows this?

Yes, "dynamic typing" or "typelessness" or "value-based type affinity" is a feature, not a bug, according to the SQLite docs.

However, I suggest that this isn't germane to this bug ticket:  ``$sqlite_sth->{TYPE} = [4]'' and ``$sth->{TYPE} = ['INTEGER']'' are both equally misleading if a programmer has forgotten or doesn't know that the underlying SQL DB is typeless.  However, the second construct violates the DBI spec and so breaks code (in particular, DBI::Shell's dbish).

Show quoted text
> > So what ANSI type should $sth->{TYPE}[$column_index] return - the type
> > for the whole column or for the next row fetched?
>
> I would say for the whole column.

The existing {TYPE} code in 1.28_1, whose results are not used, appears to pull a column's type affinity via sqlite3_column_type() passed through dbdimp.c:sqlite_type_to_odbc_type().  It may be more appropriate, however, to use sqlite_column_decltype(), already present in the relevant code path, and map the declared column type (a string) to ANSI/ODBC type numbers.  That way, IIUC, two columns declared CHAR and VARCHAR would not both be smashed into their SQLite3 type affinity, "TEXT".


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.