Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

One Question about DBF

by padawan_linuxero (Scribe)
on Oct 18, 2007 at 15:42 UTC ( #645756=perlquestion: print w/replies, xml ) Need Help??

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

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???
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"; }

and this is the error message
DBD::XBase::db prepare failed: From specification expected near ` INTO + OUTFILE 'mydata.csv' FIELDS TERMIN...' at line 7. From specification expected near ` INTO OUTFILE 'mydata.csv' FIELDS TE +RMIN...' at line 7.

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.
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__

    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);
      YOU ARE MY HERO!!!!
      THANK YOU !!!!
      IT WORK GREAT!!!
      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
      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);
        Oh, sorry, yeah, after the $dbhA line put this:
        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_

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.

Re: One Question about DBF
by thezip (Vicar) on Oct 18, 2007 at 17:29 UTC


    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?

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (2)
As of 2020-07-04 23:30 GMT
Find Nodes?
    Voting Booth?

    No recent polls found