One Question about DBF

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

Hello!!!!
I have a question I am trying to obtain from a DBF table all the data,
before working with DBF I use to work with MySql and it
always work the program I have, I use something like this
to obtain the file SELECT * INTO OUTFILE 'myfile' FIELDS TERMINATED BY ','
But now using a DBF table did not work
Can someone point me in the right direction???
Thanks
this is the code so far:

#!/usr/bin/perl -w

use DBI;

my $dbh = DBI->connect("DBI:XBase:/Proyecto")
                                or die $DBI::errstr;
    my $sth = $dbh->prepare("SELECT * INTO OUTFILE 'mydata.csv' FIELDS
+ TERMINATED BY ',' FROM reg501")
                                or die $dbh->errstr();
    $sth->execute() or die $sth->errstr();

    while ((my @row) = $sth->fetchrow_array()) {
    $counter++;
   print "Reg:$counter\t@row\n";
  
  }
[download]

and this is the error message

DBD::XBase::db prepare failed: From specification expected near ` INTO
+ OUTFILE 'mydata.csv' FIELDS TERMIN...' at ConexionMyDbf2.pl line 7.
From specification expected near ` INTO OUTFILE 'mydata.csv' FIELDS TE
+RMIN...' at ConexionMyDbf2.pl line 7.
[download]

Comment on One Question about DBF Select or Download Code

Replies are listed 'Best First'.
Re: One Question about DBF by moritz (Cardinal) on Oct 18, 2007 at 15:46 UTC
I think the error message is quite clear: your SQL syntax is wrong, it expects a `FROM` where there is an `INTO` right now.	[reply] [d/l] [select]
Re: One Question about DBF by jZed (Prior) on Oct 18, 2007 at 17:22 UTC
As I explained to you in response to your last posting, that syntax is MySQL-specific. One way to go from Xbase (or any DBI accessible RDBMS) to CSV is to use ~~DBD::AnyData~~ DBD::CSV like this: `#!/usr/bin/perl use warnings; use strict; use DBI; my $dbhX = DBI->connect('dbi:XBase(RaiseError=1):'); my $dbhC = DBI->connect('dbi:CSV(RaiseError=1):'); my $select = $dbhX->prepare("SELECT * FROM inTable"); $select->execute(); $dbhC->do("CREATE TABLE outTable AS IMPORT(?)",{},$select); __END__` [download] Update : replaced my bad code with a working example. `my $dbhX = DBI->connect ( Xbase connection info); my $dbhA = DBI->connect( 'dbi:AnyData:' ); $dbhA->ad_catalog('outfile','CSV',$output_filename); my $sth = $dbhX->prepare( Select SQL ); $sth->execute(); $dbhA->do("CREATE TABLE outfile AS SELECT * FROM ?",{}, $sth);` [download]	[reply] [d/l] [select]
Re^2: One Question about DBF by padawan_linuxero (Scribe) on Oct 18, 2007 at 18:43 UTC
Hello jZed I try you code and put what a like to think are the correct parameters and did not work :o( Ok I am going very level with you I just need to get all data from a DBF table and put it in a CSV file here you can look at my code and please tell me what is wrong thanks `use DBI; my $dbhX = DBI->connect("DBI:XBase:/proyecto"); my $dbhA = DBI->connect( 'dbi:AnyData:' ); my $sth = $dbhX->prepare("SELECT * FROM reg501"); $sth->execute(); $dbhA->do("CREATE TABLE etstx AS SELECT * FROM ?",{}, $sth);` [download] TIA	[reply] [d/l]
Re^3: One Question about DBF by jZed (Prior) on Oct 18, 2007 at 18:48 UTC
Oh, sorry, yeah, after the $dbhA line put this: `$dbhA->ad_catalog('etstx','CSV','etstx.csv');` [download] The first param is the table name to use in your SQL, the second the format of the file the table will be stored in, the third the path/filename of the file. update : added the missing ad_	[reply] [d/l]
Re^4: One Question about DBF by padawan_linuxero (Scribe) on Oct 18, 2007 at 19:02 UTC
Re^5: One Question about DBF by jZed (Prior) on Oct 18, 2007 at 19:04 UTC
Some notes below your chosen depth have not been shown here
Re^2: One Question about DBF by padawan_linuxero (Scribe) on Oct 18, 2007 at 22:07 UTC
jZed!!! YOU ARE MY HERO!!!! THANK YOU !!!! :o) IT WORK GREAT!!!	[reply]
Re: One Question about DBF by sunadmn (Curate) on Oct 18, 2007 at 15:52 UTC
It just appears that you have a SQL syntax error there. Take a look at how you are constructing your query. Remember that from SQL server to SQL server the syntax is not always the same so one may work in MySQL but not let's say in Oracle. SUNADMN *USE PERL*	[reply]
Re: One Question about DBF by thezip (Vicar) on Oct 18, 2007 at 17:29 UTC
padawan_linuxero, You might want to try firing up the query tool for your database to manually execute your SQL query. This allows you to half-split the problem into an SQL problem, rather than an SQL + Perl + DBI problem. Granted, it might not especially effective for this particular problem, but in general, it is a very useful and powerful means of troubleshooting as it allows you to tweak a query to your heart's content prior to embedding it into your code. Once you are confident that your query performs as intended, you can proceed to the next coding phase, confident that your SQL is functioning correctly. For example, when I use PostgreSQL, I'll use the pgAdmin Query tool. If it's MSSQL Server, I'll use the MSSQL Query Analyzer. Once my query works perfectly there, It's just a cut-n-paste into my Perl code. Unfortunately, I don't know of the name of the name of the query tool for DBF databases, so you're on your own there... Where do you want them* to go today?*	[reply]


Think about Loose Coupling
	PerlMonks