NAME
LRpt::CSVDumper - LReport csv dumper. Dumps results of selects to csv files.
SYNOPSIS
lcsvdmp.pl selects.txt
lcsvdmp.pl --ext=dat --path=data --sep=";" --conn_file=connection.txt --chunk_size=num selects.txt
DESCRIPTION
This module is a part of LRpt
(LReport) library. You should not use LRpt::CSVDumper
module directly in your code. Instead you should use lcsvdmp.pl tool, which is a simple wrapper around the module. lcsvdmp.pl looks like this:
use strict;
use LRpt::CSVDumper;
dump_selects( @ARGV );
lcsvdmp.pl is a program for dumping results of a group of selects to a group of csv files. Each select has its own file. Each file contains a header row, which contains names of all columns returned by a select and then all rows with the data. It connects to a database via connection file, which is expected to contain a perl code opening a connection to a database. See section "DATABASE CONNECTION FILE" for details.
Selects to be executed can be given either on standard input or in a file. See "SELECTS DEFINITION" for details.
COMMAND LINE OPTIONS
--ext
-
Extension of csv files created. Default is "txt"
--path
-
Path in which csv files are to be created. Default is a current directory
--sep
-
Fields separator in csv files. Default is tab
--conn_file
-
Path to a database connection file. Default is
conn_file.txt
. --help
-
Prints help screen
--chunk_size
-
Number of rows retrieved in one chunk. If not defined - default chunking size is used. If equal to zero, than no chunking is used (all rows are loaded to memory
selects.txt
-
File with selects to be executed.
METHODS
In this sections you will find a more or less complete listing of all methods provided by the package.
dump_selects
dump_selects( @ARGV );
Equivalent of a 'main' function. For meaning of parameters see COMMAND LINE OPTIONS.
set_params
set_params();
Parses command line parameters, checks environmental variables to gather all parameters needed to process.
open_db_connection
open_db_connection()
Opens a database connection. Loads a connection file and evals it.
run_selects
run_selects();
Run each select and dumps data to output file for each of them.
print_usage
print_usage();
Prints usage text
DATABASE CONNECTION FILE
A concept of a database connection file is supposed to provide tool's openness for unlimited number of database drivers. Instead of trying to predict several types of driver initialisation, it is expected, that a user will provide a code which opens a database connection for a given driver. This code will be then evaled by lcsvdmp.pl
.
The only thing, which is expected from this code snippet is that it assigns a reference to an opened database connection to a variable named $dbh
. Do not declare it with my
!
Example of the file:
$dbh = DBI->connect( "DBI:CSV:csv_sep_char=\t;f_dir=datafile/db",
{ RaiseError => 1 });
This is a simple case of connecting to database build from csv files.
But you can use more complex code. The example below, not only opens a connection but also configures ODBC driver:
use Win32::ODBC;
my $DBName = "mydb";
my $DBServer = "myserver";
my $DBUser = "pkaluski";
my $DBPassword = "password";
no strict;
Win32::ODBC::ConfigDSN( ODBC_CONFIG_DSN,
"Sybase ASE ODBC Driver",
"DSN=mydriver",
"Database=$DBName",
"InterfacesFileServerName=$DBServer");
use strict;
my $error = Win32::ODBC::Error();
if( $error ){
die $error;
}
$dbh = DBI->connect( "DBI:ODBC:BolekSybase", $DBUser, $DBPassword,
{RaiseError => 1, AutoCommit => 1});
SELECTS DEFINITION
Selects to be executed can be given either on standard input or in a file given in command line.
lcsvdmp.pl
expects the following format:
name: myselect1
select: select * from CUSTOMERS where id = 123
%%
name: myselect2
select: select * from INVOICES where customer_id = 123
%%
...
...
So it is basically a jar record format. For select myselect1
results will be saved in myselect1.txt
file (unless default extension is not overridden), myselect2
in myselect2.txt
.
If select's names are not given, lcsvdmp.pl
assigns them names following the pattern selectN
, where N
is an integer, starting from 0.
ENVIRONMENT
On launch, lcsvdmp.pl
looks for the following environmental variables in order to override defaults (in case according command line option is not given):
LRPT_CSV_FILE_EXT
-
Extension of created csv files
LRPT_CSV_FILE_PATH
-
Path, in which csv files should be created
LRPT_CSV_FIELD_SEPARATOR
-
Field separator in csv files
LRPT_CSV_CONNECTION_FILE
-
Path to a database connection file.
AUTHORS
Piotr Kaluski <pkaluski@piotrkaluski.com>
COPYRIGHT
Copyright (c) 2004-2006 Piotr Kaluski. Poland. All rights reserved.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.