Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: Question of SQL and an array

by jZed (Prior)
on Nov 27, 2007 at 17:44 UTC ( #653319=note: print w/ replies, xml ) Need Help??

in reply to Question of SQL and an array

You are confused about the difference between an array and an arrayref and a "$" and an "@". "@row->[0]" doesn't mean anything, that's not how either arrays or arrayrefs are referenced. You need something like this, but this is only a guess since you don't really tell us what you want to do.

my $m_dbh = DBI->connect("DBI:mysql:...); my $a_dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $a_dbh->func( 'cars', 'CSV', 'testperl.csv', 'ad_catalog'); my $pats = $m_dbh->selectcol_arrayref(" SELECT PAT FROM patentes "); my $pats_str = join ',',@$pats; my $a_sth = $a_dbh->prepare(" SELECT * FROM cars WHERE PAT IN ($pats_str) "); $a_sth->execute(); while(my $row = $a_sth->fetch){ print "@$row\n"; }

Comment on Re: Question of SQL and an array
Download Code
Re^2: Question of SQL and an array
by ArmandoG (Sexton) on Nov 27, 2007 at 18:25 UTC
    Hi you almost got it is very simple but since english is my
    second or third language is getting hard, look you almost
    got it, I want to do this, from a MYSQL table call
    PATENTES get ONLY the field PAT, to do this I have
    to do this :
    my $dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost", "root", "xyz123", {'RaiseError' => 1}); my $sth = $dbh->prepare("SELECT PAT FROM patentes"); $sth->execute(); while ((my @row) = $sth->fetchrow_array()) { print "@row->[0]\n"; }
    so far so good, at this point I have @row->[0] that has all the data from the field PAT of the table PATENTES.

    Now with these array I want to use it in another file this is a CSV datafile and it has a PAT field too, so I want to extract the data from this file using PAT and if posible to create a new CSV file with the name of the field PAT using INTO OUTFILE, I try to do this but did not work:
    my $dbh = DBI->connect("DBI:mysql:database=proyecto;host=localhost", "root", "xyz123", {'RaiseError' => 1}); my $sth = $dbh->prepare("SELECT PAT FROM patentes"); $sth->execute(); while ((my @row) = $sth->fetchrow_array()) { my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $dbh->func( 'cars', 'CSV', 'testperl.csv', 'ad_catalog'); my $sth = $dbh->prepare("SELECT * INTO OUTFILE @row->[0].csv FROM ca +rs WHERE pat = @row->[0]); $sth->execute(); }
    Hope this is more exact
      It's not your English that is confusing me, it's your perl :-). You are getting your $dbh's and $sth's mixed up -- you need to name them differently, so for example $m_dbh is your MySQL dbh and $a_dbh is your AnyData dbh. But I also still don't understand what your goal is. You want to get everything in the CSV file that has a PAT equal to a PAT in the MySQL database, right? Then what do you want to do with that data?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://653319]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (10)
As of 2014-07-10 12:10 GMT
Find Nodes?
    Voting Booth?

    When choosing user names for websites, I prefer to use:

    Results (207 votes), past polls