Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (7)
As of 2015-07-02 04:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (27 votes), past polls