RE: New DBI in-memory tables, heterogeneous operations, SQL-Statement

From: Paul Boutros (Paul.Boutros_at_utoronto.ca)
Date: 02/28/05 

To: "dbi-users" <dbi-users@perl.org>
Date: Sun, 27 Feb 2005 19:58:28 -0500

Hi Jeff,

I'm on:
AIX 5.2.0.0
Perl 5.8.0.0
DBI 1.46

I get an error on make test with:

$ perl Makefile.PL LIB=/db2blast/Perl
Checking if your kit is complete...
Looks good
Writing Makefile for SQL::Statement
$ make
cp lib/SQL/Dialects/ANSI.pm blib/lib/SQL/Dialects/ANSI.pm
cp lib/SQL/Parser.pm blib/lib/SQL/Parser.pm
cp lib/SQL/Statement/GetInfo.pm blib/lib/SQL/Statement/GetInfo.pm
cp lib/SQL/Statement.pm blib/lib/SQL/Statement.pm
cp lib/SQL/Dialects/CSV.pm blib/lib/SQL/Dialects/CSV.pm
cp lib/SQL/Eval.pm blib/lib/SQL/Eval.pm
cp lib/SQL/Dialects/AnyData.pm blib/lib/SQL/Dialects/AnyData.pm
cp lib/SQL/Statement/Functions.pm blib/lib/SQL/Statement/Functions.pm
Manifying blib/man3/SQL::Dialects::ANSI.3
Manifying blib/man3/SQL::Parser.3
Manifying blib/man3/SQL::Statement.3
Manifying blib/man3/SQL::Dialects::CSV.3
Manifying blib/man3/SQL::Eval.3
Manifying blib/man3/SQL::Dialects::AnyData.3
Manifying blib/man3/SQL::Statement::Functions.3
Target "makemakerdflt" is up to date.
$ make test
        PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_har
ness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01basics....ok 4/100SQL ERROR: Bad predicate: 'id'!

SQL ERROR: Bad predicate: 'id'!

# Looks like you planned 100 tests but only ran 4.
# Looks like your test died just after 4.
t/01basics....dubious
        Test returned status 255 (wstat 65280, 0xff00)
DIED. FAILED tests 5-100
        Failed 96/100 tests, 4.00% okay
Failed Test Stat Wstat Total Fail Failed List of Failed
---------------------------------------------------------------------------- 

---
t/01basics.t  255 65280   100   96  96.00%  5-100
Failed 1/1 test scripts, 0.00% okay. 96/100 subtests failed, 4.00% okay.
make: 1254-004 The error code from the last command is 2.
Stop.
$
> -----Original Message-----
> From: Jeff Zucker [mailto:jeff@vpservices.com]
> Sent: Saturday, February 26, 2005 3:21 PM
> To: dbi-users; dbi-dev
> Subject: New DBI in-memory tables, heterogeneous operations,
> SQL-Statement
>
>
> [cc to dbi-dev but please reply to dbi-users or to me]
>
> I'm making available a pre-release version of SQL::Statement with many
> new features including vastly improved parens parsing (thanks Dean
> Arnold), column aliases (thanks Robert Rothenberg), new built-in
> functions including SOUNDER() and REGEX(),support for user-defined
> functions, in-memory tables, and heterogeneous operations across all DBI
> data sources.
>
> This release will be of interest to all DBI users, in addition to users
> of SQL::Statement and the DBDs which subclass it.  Why?  Because it
> supports the use of any of the SQL::Statement DBDs (including DBD::File
> which comes with DBI) to create temporary in-memory tables and to
> perform heterogeneous operations using *any* DBI data sources.  This
> means you can do a join on a PostgreSQL table and a MySQL table.  Or do
> a join query on two XBase tables even though XBase itself doesn't
> support joins.  Or load a selection from an Oracle table into a
> temporary in-memory table, cache that table, then perform SQL queries on
> it without going back to Oracle.
>
> DBD::File and the other SQL::Statement subclassing DBDs can now be used
> as utilities to perform multiplexing and heterogeneous operations.  They
> function somewhat similarly to DBD::Multiplex, but they use *per-table*
> multiplexing.  In other words, once a DBI connection has been associated
> with an in-memory table, the data from that connection can be used
> simply by referring to the table's name in a SQL statement.
>
> All of these features are currently working (knock on wood) and
> available for testing.  Please grab the pre-release version at
> http://www.vpservices.com/jeff/programs/SQL-Statement-1.10.tar.gz.
> (This is a brand new version so re-grab if you grabbed it from the
> PerlMonks posting). See the included SQL::Statement::Function.pm for
> documentation of built-in and user-defined functions.  Here's the POD
> section on in-memory tables and heterogeneous operations.  Even if you
> don't grab the tarball, I'd appreciate comments on the proposed syntax
> shown below.
>
> =head2 In-memory tables & heterogeneous operations
>
> You can now use any DBD that subclasses SQL::Statement (e.g. DBD::File,
> DBD::DBM, DBD::CSV) to create temporary in-memory tables from perl data
> structures or from DBI heterogeneous queries.  This is the SQL syntax to
> create the in-memory table:
>
>  CREATE RAM TABLE tblname LIKE ?
>
> The placeholder can be replaced by an AoA (a reference to an array of
> array references) or by a DBI database handle with additional arguments.
>
> In the case of an AoA, the first row of the AoA must be the column names
> of the table.  Here's an example in which the column names are 'id' and
> 'word':
>
>  $dbh->do( "CREATE RAM TABLE Japh LIKE ?", {}, [
>            [qw( id word    )],
>            [qw( 1  Hacker  )],
>            [qw( 2  Perl    )],
>            [qw( 3  Another )],
>            [qw( 4  Just    )],
>  ]);
>
> Once the in-memory RAM table is created, it can be accessed from SQL
> like any other table, for example, using the RAM table 'Japh' created by
> the statement above:
>
>  printf "%s\n", join ' ',@{ $dbh->selectcol_arrayref("
>      SELECT word FROM Japh ORDER BY id DESC
>  ")};
>  #
>  # outputs "Just Another Perl Hacker"
>
> In the case of DBI heterogeneous queries, the placeholder should be
> replaced by a) any valid DBI database handle ($dbh) and b) any valid
> data-returning SQL query that can be executed by that database handle.
>
> For example, if you have a PostgreSQL table called 'phrase' that has
> columns 'id' and 'phrase', this will create a temporary in-memory table
> containing a selected subset of that table:
>
>  $dbh->do(
>     "CREATE RAM TABLE Pg_phrases LIKE ?", {},
>      DBI->connect('dbi:Pg:dbname=test1'),
>      "SELECT id,phrase from phrase WHERE id < 10"
>  );
>
> If the heterogeneous query requires placeholders, those may be appended
> to the placeholders from the original statement.  For example, the
> statement above, done with a placeholder in the Pg query would look like
> this:
>
>  $dbh->do(
>     "CREATE RAM TABLE Pg_phrases LIKE ?", {},
>      DBI->connect('dbi:Pg:dbname=test1'),
>      "SELECT id,phrase from phrase WHERE id < ?",
>      10
>  );
>
> The "10" (and values following it) will become the value(s) for the
> placeholder in the SELECT statement to the PostgreSQL table.
>
> As with AoA in-memory tables, once a heterogeneous query in-memory table
> is created, it can be used in a SQL statement like any other table.
> Here's an example that creates one in-memory table from a heterogeneous
> query to a PostgreSQL table, then creates a second in-memory table from
> a heterogeneous query to an XBase table, then does a query joining the
> two in-memory tables:
>
>  # 1. connect to any DBD that subclasses SQL::Statement
>  #
>  my $dbh  = DBI->connect('dbi:File(RaiseError=1):');
>
>  # 2. create a RAM table from a heterogeneous query to PostgreSQL
>  #
>  $dbh->do(
>     "CREATE RAM TABLE Pg_phrases LIKE ?", {},
>      DBI->connect('dbi:Pg:dbname=test1'),
>      "SELECT id,phrase from phrase WHERE id < 10"
>  );
>
>  # 3. create a RAM table from a heterogeneous query to XBase
>  #
>  $dbh->do(
>     "CREATE RAM TABLE Xb_phrases LIKE ?", {},
>      DBI->connect('dbi:XBase:./'),
>      "SELECT id,phrase from phrase WHERE id < 10",
>  );
>
>  # 4. use the RAM tables in SQL statements ...
>  #
>  my $sth = $dbh->prepare("SELECT * FROM Pg_phrases NATURAL JOIN
> Xb_phrases");
>  $sth->execute;
>  $sth->dump_results;
>
> SQL that is supplied as a placeholder in the CREATE RAM TABLE statement
> follows the syntax for whatever datasource is specified (e.g. PostgreSQL
> in #2 above and XBase in #3 above).  SQL that is run from the
> SQL-Statement DBD (e.g. #4 above) follows SQL syntax of that DBD
> (DBD::File in the example).
>
> =cut
>
> Thanks in advance for your comments and suggestions.
>
> --
> Jeff
>
>

Relevant Pages

  • RE: Question with DBI versus PERL
    ... Question with DBI versus PERL ... statement at once (if I parse my SQL file) my pl/sql will work? ... All spool statements will turn to Perl print() calls. ... I want to know how can i run this sql script with DBI. ...
    (perl.dbi.users)
  • RE: how to invoke .sql file from dbi
    ... recommended changing all the SQL*Plus scripts into DBI calls. ... The sql statements are SELECT statements and we are expecting plain (no ... to just point the dbi to a sql file and dbi taking care of it. ...
    (perl.dbi.users)
  • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
    ... T> expected a mature library like DBI to behave like this. ... I learned plenty about the perl side of things from her posts. ... closely represent the SQL idea of a null. ... trivially easy to perform bulk loads using the DB's particular ...
    (comp.lang.perl.misc)
  • RE: Newline inserted?
    ... Are you processing individual SQL statements (does your file contain SQL ... DBI is not your best bet to do this. ... When I attempt to load this file into Oracle 10g using the DBI I get the ...
    (perl.dbi.users)