Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

SQL Query Error

by DrAxeman (Beadle)
on Sep 09, 2005 at 16:49 UTC ( #490645=perlquestion: print w/ replies, xml ) Need Help??
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.
#!/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%' ");
The "SOFT" column is from the software table.
Any ideas?

Comment on SQL Query Error
Select or Download Code
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%'
      That's what it looks like. Not sure how to work around it.

      '\%Secure Path\%' gives the same error.
      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.
        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

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.

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); }
    This will have the same effect as the CREATE TABLE AS SELECT , but does it in separate steps.
      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

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://490645]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (15)
As of 2014-07-31 18:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (250 votes), past polls