"DBIx::RunSQL->createARGS""DBIx::RunSQL->runARGS"
Runs the SQL commands and returns the database handle. In list context, it returns the database handle
and the suggested exit code.
• "sql" - name of the file containing the SQL statements
The default is "sql/create.sql"
If "sql" is a reference to a glob or a filehandle, the SQL will be read from that. notimplemented
If "sql" is undefined, the $::DATA or the 0 filehandle will be read until exhaustion. notimplemented
This allows one to create SQL-as-programs as follows:
#!/usr/bin/perl -w -MDBIx::RunSQL -e 'create()'
create table ...
If you want to run SQL statements from a scalar, you can simply pass in a reference to a scalar
containing the SQL:
sql => \"update mytable set foo='bar';",
• "dsn", "user", "password", "options" - DBI parameters for connecting to the DB
• "dbh" - a premade database handle to be used instead of "dsn"
• "force" - continue even if errors are encountered
• "verbose" - print each SQL statement as it is run
• "verbose_handler" - callback to call with each SQL statement instead of "print"
• "verbose_fh" - filehandle to write to instead of "STDOUT"
"DBIx::RunSQL->run_sql_fileARGS"
my $dbh = DBI->connect(...)
for my $file (sort glob '*.sql') {
DBIx::RunSQL->run_sql_file(
verbose => 1,
dbh => $dbh,
sql => $file,
);
};
Runs an SQL file on a prepared database handle. Returns the number of errors encountered.
If the statement returns rows, these are printed separated with tabs.
• "dbh" - a premade database handle
• "sql" - name of the file containing the SQL statements
• "fh" - filehandle to the file containing the SQL statements
• "force" - continue even if errors are encountered
• "verbose" - print each SQL statement as it is run
• "verbose_handler" - callback to call with each SQL statement instead of "print"
• "verbose_fh" - filehandle to write to instead of "STDOUT"
• "output_bool" - whether to exit with a nonzero exit code if any row is found
This makes the function return a nonzero value even if there is no error but a row was found.
• "output_string" - whether to output the (one) row and column, without any headers
• "formatter" - see the "<formatter"> option of "->format_results"
• "rotate" - rotate the table by 90° , outputting columns as rows
• "null" - string to replace SQL "NULL" columns by
"DBIx::RunSQL->run_sqlARGS"
my $dbh = DBI->connect(...)
DBIx::RunSQL->run_sql(
verbose => 1,
dbh => $dbh,
sql => \@sql_statements,
);
Runs an SQL string on a prepared database handle. Returns the number of errors encountered.
If the statement returns rows, these are printed separated with tabs, but see the "output_bool" and
"output_string" options.
• "dbh" - a premade database handle
• "sql" - string or array reference containing the SQL statements
• "force" - continue even if errors are encountered
• "verbose" - print each SQL statement as it is run
• "verbose_handler" - callback to call with each SQL statement instead of "print"
• "verbose_fh" - filehandle to write to instead of "STDOUT"
• "output_bool" - whether to exit with a nonzero exit code if any row is found
This makes the function return a nonzero value even if there is no error but a row was found.
• "output_string" - whether to output the (one) row and column, without any headers
• "formatter" - see the "<formatter"> option of "->format_results"
• "rotate" - rotate the table by 90° , outputting columns as rows
• "null" - string to replace SQL "NULL" columns by
"DBIx::RunSQL->format_results%options"
my $sth= $dbh->prepare( 'select * from foo' );
$sth->execute();
print DBIx::RunSQL->format_results( sth => $sth );
Executes "$sth->fetchall_arrayref" and returns the results either as tab separated string or formatted
using Text::Table if the module is available.
If you find yourself using this often to create reports, you may really want to look at Querylet instead.
• "sth" - the executed statement handle
• "formatter" - if you want to force "tab" or "Text::Table" usage, you can do it through that
parameter. In fact, the module will use anything other than "tab" as the class name and assume that
the interface is compatible to "Text::Table".
• "no_header_when_empty" - don't print anything if there are no results
• "rotate" - rotate the table by 90° , outputting columns as rows
• "null" - string to replace SQL "NULL" columns by
Note that the query results are returned as one large string, so you really do not want to run this for
large(r) result sets.
"DBIx::RunSQL->split_sqlARGS"
my @statements= DBIx::RunSQL->split_sql( <<'SQL');
create table foo (name varchar(64));
create trigger foo_insert on foo before insert;
new.name= 'foo-'||old.name;
end;
insert into foo name values ('bar');
SQL
# Returns three elements
This is a helper subroutine to split a sequence of (semicolon-newline-delimited) SQL statements into
separate statements. It is documented because it is not a very smart subroutine and you might want to
override or replace it. It might also be useful outside the context of DBIx::RunSQL if you need to split
up a large blob of SQL statements into smaller pieces.
The subroutine needs the whole sequence of SQL statements in memory. If you are attempting to restore a
large SQL dump backup into your database, this approach might not be suitable.
"DBIx::RunSQL->parse_command_line"
my $options = DBIx::RunSQL->parse_command_line( 'my_application', \@ARGV );
Helper function to turn a command line array into options for DBIx::RunSQL invocations. The array of
command line items is modified in-place.
If the reference to the array of command line items is missing, @ARGV will be modified instead.
"DBIx::RunSQL->handle_command_line"
DBIx::RunSQL->handle_command_line( 'my_application', \@ARGV );
Helper function to run the module functionality from the command line. See below how to use this function
in a good self-contained script. This function passes the following command line arguments and options
to "->create":
--user
--password
--dsn
--sql
--quiet
--format
--force
--verbose
--bool
--string
--rotate
--null
In addition, it handles the following switches through Pod::Usage:
--help
--man
If no SQL is given, this function will read the SQL from STDIN.
If no dsn is given, this function will use " dbi:SQLite:dbname=db/$appname.sqlite " as the default
database.
See also the section PROGRAMMER USAGE for a sample program to set up a database from an SQL file.
Install Now
PNG Icons