The script should do the following:

  • user enters a request from the keyboard (I know this)
  • perl pushes query into mysql (i know too)
  • mysql executes the query - and here I need to somehow save the result of the SELECT and to the file line by line (that's what I'm asking about).

    3 answers 3

    Actually the program, let's call it mysql.pl

     #!/usr/bin/perl -w -- use warnings; use strict; use DBI; my $DSN = "DBI:mysql:database=test;host=localhost"; sub main { my $sql = $ARGV[0]; my $dbh = DBI->connect( $DSN, undef, undef, { RaiseError => 1, AutoCommit => 0 } ) or die "Connect error: " . $DBI::errstr; my $sth = $dbh->prepare( $sql ) or die "Prepare error: " . $dbh->errstr; $sth->execute or die "Execute error: " . $dbh->errstr; print join("\t", @{ $sth->{NAME} } ), "\n"; while ( my $row = $sth->fetch ) { print join("\t", map { defined($_) ? $_ : 'NULL'} @{ $row }), "\n"; } $sth->finish; $dbh->disconnect; } &main; 1; __END__

    Performs queries to the test database on localhost, and prints the results to stdout, the fields are separated by a tab, use this:

     % ./mysql.pl "select * from table" > output.file

    If something is not clear - ask

    • The first answer is beautiful. Just do not call the function main. Somehow not kosher. - dipp
    • Actually why not? main is the default package value, and therefore the main :: main () call looks pretty good. As far as I know the main function is not reserved anywhere and there are no recommendations for its use (as opposed to variables of type $ a, $ b, etc.) - chernomyrdin
    • With regards to "kosher", it would be possible to do everything correctly, it would turn out something like: gist.github.com/1269992 - chernomyrdin

    sqlsh is not needed - everything is done by mysql itself:

     echo 'SELECT 2*2, 3*3' | mysql # ΠΌΠΎΠΆΠ½ΠΎ Π΄ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ -u username -ppassword databasename

    Gives the answer, in which by default the fields (columns) are separated by a tab, and the records (lines) are a line break:

     2*2 3*3 4 9

      I would do this: I would put sqlsh and further

       sqlsh -d DBI:MYSQL:MYSERVER.DE.COM -u user -p password -i < my_run.sql содСрТимоС my_run.sql set multiline on; ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI'; ALTER SESSION SET CURRENT_SCHEMA = my_shema; set log-mode box; log queries result.txt; select * from dual; no log; exit;

      and in result.txt we see:

       || DUMMY | | X |
      • 1. it is super, thanks, by the way, and it is possible so to execute DDL? 2. but sqlsh it issues in wiki a fictitious format, with delimiters '|' - Nikolay Mishin
      • I'm just the result in fat throw in the wiki format as a table - Nikolay Mishin