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

I'm trying to run a query and I can't figure out what's wrong here. I've consulted some people and they say my query looks right. The query I'm having problems with is the second one. Using a WHERE and a JOIN is giving me the following error:
Use of uninitialized value in substitution iterator at /usr/lib/perl5/
+site_perl/5.8.6/SQL/Parser.pm line 1596.

[download]
#!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;

# Connect to the database, (the directory containing our csv file(s))
my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");

# Associate our csv disk file with the table names
$dbh->{'csv_tables'}->{'info'} = { 'file' => "psinfooutputfile.csv"};
$dbh->{'csv_tables'}->{'hosts'} = { 'file' => "wmioutputfile.csv"};
$dbh->{'csv_tables'}->{'hostinfo'} = { 'file' => "hosts.csv"};
$dbh->{'csv_tables'}->{'saninfo'} = { 'file' => "sanhosts.csv"};
$dbh->{'csv_tables'}->{'software'} = { 'file' => "softwareinfo.csv"};

######
$dbh->do("DROP TABLE IF EXISTS hostsinfo");

$dbh->do("
    CREATE TABLE hostinfo
        AS SELECT hosts.IP, Manufacturer, Model, OS, OSSP, RAM, CPUSpe
+ed, CPUCount, CPUType
        FROM hosts
        LEFT JOIN info ON hosts.IP = info.IP
");

# Problem Query
$dbh->do("
     CREATE TABLE saninfo
         AS SELECT hostinfo.IP, Manufacturer, Model, OS, OSSP, RAM, CP
+USpeed, CPUCount, CPUType, SOFT
         FROM hostinfo
         JOIN software ON hostinfo.IP = software.IP
         WHERE SOFT LIKE '%Secure Path%'
");

[download]
The "SOFT" column is from the software table.
Any ideas?

Replies are listed 'Best First'.
Re: SQL Query Error
by jZed (Prior) on Sep 09, 2005 at 17:23 UTC
    Sorry, you appear to have found a bug which only gets activated with a WHERE clause used with a CREATE TABLE AS clause. I'll look into it. In the meantime, the workaround is to create and populate the table in separate steps, like this: 
    $dbh->do("
CREATE TABLE saninfo
  (IP TEXT, Manufacturer TEXT, Model TEXT, OS TEXT,
   OSSP TEXT, RAM TEXT, CPUSpeed INT, CPUCount INT,
   CPUType TEXT, SOFT TEXT)
");
my $select = $dbh->prepare("
    SELECT hostinfo.IP, Manufacturer, Model, OS, OSSP,
           RAM, CPUSpeed, CPUCount, CPUType, SOFT
      FROM hostinfo
      JOIN software ON hostinfo.IP = software.IP
     WHERE SOFT LIKE '%Secure Path%'
");
my $insert = $dbh->prepare("
    INSERT INTO saninfo VALUES(?.?.?.?.?.?.?.?.?.?)
");
$select->execute;
while (my @row = $select->fetchrow_array) {
    $insert->execute(@row);
}

    [download]
    This will have the same effect as the CREATE TABLE AS SELECT , but does it in separate steps.
[reply]
[d/l]
      Does this database support "INSERT INTO table1 SELECT x, y, z FROM table2"? If so, you could save yourself bringing all of the data from the db into perl and back to the db and keep it all on the server. Definately much faster...

      thor

      Feel the white light, the light within
      Be your own disciple, fan the sparks of will
      For all of us waiting, your kingdom will come

[reply]
        No, it doesn't yet support subselects with INSERT. And even when it does, it will not be any faster. Since these are CSV tables (there is no "server"), the same work is being done in my example above as would be done by INSERT with a subselect.
[reply]
Re: SQL Query Error
by runrig (Abbot) on Sep 09, 2005 at 16:59 UTC
    It doesn't look like SQL::Statement supports that particular join clause.

    Update: Ignore me. Re-reading...join type is optional. Nevermind.

[reply]
Re: SQL Query Error
by mifflin (Curate) on Sep 09, 2005 at 16:53 UTC
    is it possible that perl is trying to interpolate Secure as a hash in?... 
    WHERE SOFT LIKE '%Secure Path%'

    [download]
[reply]
[d/l]
      No that isn't what is happening. The % sigils are correctly interpreted as SQL wildcards. See my answer below for what is really going on.
[reply]
        Not that I don't believe you, but how is that not being interpretted as a hash? It's in a double quoted string which should make it try to interpolate.

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

[reply]
      That's what it looks like. Not sure how to work around it.

      '\%Secure Path\%' gives the same error.
[reply]

Back to Seekers of Perl Wisdom