Mysql – DBD::thesql: How to write with “SELECT * INTO file” to the home-directory

MySQLperl

is there a way to make this work?
(write the out_file to my home-directory)

#!/usr/bin/perl
use warnings;
use strict;
use File::Spec::Functions;
use File::HomeDir;
use DBI;

my $database = 'my_db';
my $table = 'my_table';
my $user = 'user';
my $passwd = 'password';

my $dbh = DBI->connect( "DBI:mysql:$database;", 
$user, $passwd, { RaiseError=>1, AutoCommit=>1 } );

$dbh->do( qq{ DROP TABLE IF EXISTS $table } );
$dbh->do( qq{ CREATE TABLE $table (
    artikel INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    haendler  CHAR(20) DEFAULT '' NOT NULL,
    preis   DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY( artikel, haendler ) ); } );

my $sth = $dbh->prepare( qq{ INSERT INTO $table 
    ( artikel, haendler, preis ) 
    VALUES( ?, ?, ? ) } );

$sth->execute( 1, 'Dotter', 1.35 );
$sth->execute( 2, 'Kahlo', 2.00 );
$sth->execute( 3, 'Schmidt', 4.30 );
$sth->execute( 3, 'Kahlo', 4.45 );


my $out_file = 'out_file.csv';
my $home_dir = File::HomeDir->my_documents;
$out_file = catfile $home_dir, $out_file;

$dbh->do( qq{ SELECT * INTO OUTFILE '$out_file' 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    ESCAPED BY '"' LINES TERMINATED BY '\n'
    FROM $table; } );

# DBD::mysql::db do failed: Can't create/write to file '/home/user/out_file.csv' (Errcode: 13) at ./mysql.pl line 44.

Best Answer

# DBD::mysql::db do failed: Can't create/write to file '/home/user/out_file.csv' (Errcode: 13) at ./mysql.pl line 44.

That error message is totally dumb, how typical of MySQL. This code number is not platform portable. (POSIX specifies only the symbol names, not the numbers!)

Software that is not programmed by total amateurs uses the strerror system call to provide a descriptive and localisable error message.

I had to dive into /usr/include/asm-generic/errno-base.h in order to look up the number; I here just assume you use Linux, too:

#define EACCES 13 /* Permission denied */

So there you have it, dangerstat guessed correctly.

Related Topic