in reply to Re: DBD::SQLite
in thread Error querying DBD::SQLite

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

Replies are listed 'Best First'.
Re: Re: Re: DBD::SQLite
by perrin (Chancellor) on Apr 13, 2004 at 04:16 UTC
    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]
Re: Re: Re: DBD::SQLite
by Mr. Muskrat (Canon) on Apr 13, 2004 at 04:34 UTC

    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]
      And before version 4.1.0, it's also legal in MySQL to simply do: 
      SELECT 1+1;

      [download]

      Liz

[reply]
[d/l]
      I dont really see why SQLite should implement some arbitrary functionality just because people are used to it in some other databases. Especially as 1) its not in the standard, and 2) other databases do it differently (eg DB2 - 'VALUES(1+1)' gives the expected result there). Getting SQLite to implement it is like .. umm.. praising browsers for inventing their own tags etc.

      Also I dont really see the need for it, since you can select an expression from any known table to get that result.

      An expression function in DBI, which uses whatever each DBD locally implements, might be useful. But then, why bother, as if you're using DBI/Perl anyway, you can already do expressions.

      C.

[reply]
      The dummy table DUAL usage makes sense to me. But I was diving heavily into the docs for DBD::SQLite and SQLite the database yesterday and never saw mention of DUAL. Just to be sure before posting this followup I double-checked the SQLite documentation using a word search for /\bdual\b/i and didn't find any use of that word in SQLite's documentation, at least in the context of a dummy table.

      Nevertheless, I'm glad to have learned something new that I can apply to other DB implementations. And to your point, perhaps 'soon' the SQLite author will decide to implement the feature. :)


      Dave

[reply]

In Section Seekers of Perl Wisdom