Question of SQL and an array

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

UPDATE: 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";
   }
[download]

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();

}
[download]

Hope this is more exact ---------------------------------------------------------
good morning I have a array like 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"

}
[download]

This givesme a list of the data that I need
but now I need to extract data from a CSV file
using the data from the array above
How can I do that?
I thought this way:

my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
  $dbh->func( 'cars', 'CSV', 'testperl.csv', 'ad_catalog');
  my $sth = $dbh->prepare("SELECT * FROM cars WHERE PAT =@row->[0]");
  $sth->execute();
[download]

but did not work can someone give me an Idea? thanks

Comment on Question of SQL and an array Select or Download Code

Replies are listed 'Best First'.
Re: Question of SQL and an array by moritz (Cardinal) on Nov 27, 2007 at 16:01 UTC
but did not work is not a good error description. What didn't work, and what did you expect to happen?	[reply]
Re^2: Question of SQL and an array by ArmandoG (Sexton) on Nov 27, 2007 at 16:40 UTC
Sorry about that there are two datafile that has data, the first one is call PATENTES the datafile looks like this `PAT NAME DATA1 0001 JOE JAVA 12546789 0002 JOHN JRE 12546789 0003 JACK JAVA 12546789` [download] and the other datafile is the one with the general data the datafile looks like this: `PAT COMPANY PHONE ADDRESS 0001 BEE CO. 8569658965 123 EVERGREEN 0001 AVC CO. 9878528852 321 EVERRED 0001 BEE CO. 8569658965 123 EVERGREEN 0002 TREE CO. 1255648596 696 LOUIS 0002 TEE CO. 4587895254 720 LOUIS 0003 THEE CO. 9568569856 696 LOUIS 0003 TTHE CO. 1985698596 686 MISIS` [download] So basiclly I am looking for a way to get the data out of the CSV using the data in the datafile PATENTES I thought using an array to get the data since PATENTES datafile has no duplicates, I hope this can bring more light to the problem	[reply] [d/l] [select]
Re: Question of SQL and an array by Anonymous Monk on Nov 27, 2007 at 16:04 UTC
Hi, There is a module called DBD::CSV I believe which might be just what you are looking for. It's worth a look anyway. J.C.	[reply]
Re: Question of SQL and an array by jZed (Prior) on Nov 27, 2007 at 17:44 UTC
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"; }` [download]	[reply] [d/l]
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"; }` [download] 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(); }` [download] Hope this is more exact	[reply] [d/l] [select]
Re^3: Question of SQL and an array by jZed (Prior) on Nov 27, 2007 at 18:38 UTC
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?	[reply]
Re: Question of SQL and an array by chrism01 (Friar) on Nov 28, 2007 at 01:03 UTC
As mentioned, fetchrow_array() returns an array (not an array_ref), so access the first element as $row[0], not @row->[0]	[reply]
Re^2: Question of SQL and an array by ArmandoG (Sexton) on Nov 28, 2007 at 17:14 UTC
Hi I try like you said #!?usr/bin/perl -w use DBI; 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"; my $a_dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $a_dbh->func( 'cars', 'CSV', 'testperl.csv', 'ad_catalog'); my $a_sth = $a_dbh->prepare("SELECT * INTO OUTFILE $row[0].csv FROM +cars WHERE pat = $row[0]"); $a_sth->execute(); } [download] And giving me this error message: `SQL ERROR: Bad table or column name 'INTO' is a SQL reserved word! DBD::AnyData::db prepare failed: SQL ERROR: Bad table or column name ' +INTO' is a SQL reserved word! [for Statement "SELECT * INTO OUTFILE 0447.csv FROM cars WHERE pat = +0447"] at finaltest.pl line 16. DBD::AnyData::db prepare failed: SQL ERROR: Bad table or column name ' +INTO' is a SQL reserved word! [for Statement "SELECT * INTO OUTFILE 0447.csv FROM cars WHERE pat = +0447"] at finaltest.pl line 16.` [download] can you help me please? thank you	[reply] [d/l] [select]