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.

cpanm LRpt

perl -MCPAN -e shell
install LRpt