dragonchild has asked for the wisdom of the Perl Monks concerning the following question:

Before submitting to rt.cpan.org, I wanted to run it by y'all. 
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
    'dbi:SQLite:dbname=test.db',
    '', '',
    { PrintError => 0, RaiseError => 1, AutoCommit => 1 },
);

my $sth = $dbh->prepare("SELECT 1 FROM dual");

my @x = $sth->fetchrow_array;
$sth->finish;
print "@x\n";

[download]
But executing it gives an error: 
:!perl test.pl
dbih_setup_fbav: invalid number of fields: -1, NUM_OF_FIELDS attribute
+ probably not set right at test.pl line 14.

shell returned 2

[download]

This is using Cygwin 5.8.2 and ActivePerl 5.8.3. Exact same code. DBD::SQLite 0.31 (installed tonight). Anyone else see this? matts?

[jdporter - Added line of text between script and run.]

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

Replies are listed 'Best First'.
Re: DBD::SQLite
by Ovid (Cardinal) on Apr 13, 2004 at 03:57 UTC

    Don't feel bad. It's easy to miss. I idly thought about fixing the error message and submitting a patch, but I hate wading through C code :)

    Cheers,
    Ovid

    New address of my CGI Course.

[reply]
      My gods. I feel completely stupid. You'd think that after some 3.5 years of working with DBI nearly every day, I'd have prepare-execute-fetch down pat. *sighs*

      At least I'm not the only one. *grins* Though, I am curious - why did we both miss it and only for DBD::SQLite? Did we think that it was just a toy, so the standard methods weren't necessary? :-)

      ------
      We are the carpenters and bricklayers of the Information Age.

      Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

[reply]

        why did we both miss it and only for DBD::SQLite?

        I think the answer to that is simple: while this module is a gift from the database gods and I love it, that error message leaves a bit to be desired and is extremely misleading. I suspect that many people have looked at that and thought "wow, there's a bug in the C code" because that sure ain't a Perl error message.

        Cheers,
        Ovid

        New address of my CGI Course.

[reply]
Re: DBD::SQLite
by blokhead (Monsignor) on Apr 13, 2004 at 02:50 UTC
    Missing an $sth->execute ?

    blokhead

[reply]
[d/l]
Re: DBD::SQLite
by perrin (Chancellor) on Apr 13, 2004 at 03:02 UTC
    Are you sure SQLite supports "dual"? I thought that was an Oracle thing.
[reply]
      If I'm reading dragonchild's SQL properly, 'dual' is his table name. His SQL query says, "Select the column named 1 from the table named 'dual'". What's not to support?

      There is a missing $sth->execute() though, as someone else already identified. After you prepare the query, you have to execute it before $sth->fetchrow_array() begins populating.

      The code has one other potential problem which is unrelated to the error message. That is that $sth->fetchrow_array() fetches one row each time you invoke it. But the query prepared is asking for the column named "1" from all rows of "dual". That means that unless the database has only one row, you're doing a lot of work just to throw everything away except for the first row fetched. The more common idiom is: 

      while ( my @row = $sth->fetchrow_array() ) {
    # process the row of data
}

      [download]

      HTH


      Dave

[reply]
[d/l]
        Oracle has a special "virtual" table called dual, which is commonly used to select from when all you want to do is run a function, for example:

        select sqrt(25) + power(2,3) - round(4.645432, 1) from dual;

        [download]
        The SQL in the example, select 1 from dual, is often used to ping the database. It essentially does nothing at all except talk to the database and ask it to return the value 1. I don't think this is standard SQL stuff, but rather an Oracle-specific extension.
[reply]
[d/l]
[select]

        From the MySQL Manual - 14.1.7 SELECT Syntax:

        From MySQL 4.1.0 on, you are allowed to specify DUAL as a dummy table name in situations where no tables are referenced:
        mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

        [download]
        DUAL is purely a compatibility feature. Some other servers require this syntax.

        Update: If DBD::SQLite does not support this yet, the author might want to consider adding support soon.

[reply]
[d/l]

Back to Seekers of Perl Wisdom