0

I has to load SQL file within the perl script and execute the SQL commands and show output in perl file only.

Scenario: test1.sql file contains SQL command in this format

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Now I want to load the test1.sql without using files and Execute this SQL and also shows output in the Perl script like table created

Improve this question
8
  • 1
    perlmaven.com/simple-database-access-using-perl-dbi-and-sql Commented May 13, 2015 at 5:58
  • Use Perl DBI unless you've got a solid reason not to do so. If you have a solid reason, you should have said that in the question in the first place, so you can't have a solid reason not to use DBI, so that is what you should use. It's not clear how you can avoid using files if the SQL statement is in a file. Information like 'table created' is trickier. Commented May 13, 2015 at 6:03
  • Could this solve your problem ? stackoverflow.com/questions/4217306/execute-sql-file-in-perl Commented May 13, 2015 at 6:05
  • No That wont works to me... Commented May 13, 2015 at 6:07
  • 1
    What won't work for you? DBI, or the suggested question? If you can't use DBI, you're largely on your own — that is the Perl way of executing SQL (to the extent there's ever one way of doing things in Perl). Have fun with an alternative, but you're going to need to research it. Amongst other things, you'll need to identify the DBMS you're using — DBI has the merit of working with many different DBMS via its DBD::YourDBMSHere drivers. Commented May 13, 2015 at 6:13

2 Answers 2

Reset to default
3

If I read your problem correctly, your biggest challenge is breaking apart the file into separate sql statements, SQL::SplitStatement is usefull but beware it's not bullet proof.

Here's an example of how you could accomplish this;

#!/usr/bin/env perl
use strict;
use warnings;
use diagnostics;

use DBI;
use File::Slurp;
use SQL::SplitStatement;

my $sql_blob = read_file( 'test1.sql' ) ;
my @sql_list = SQL::SplitStatement->new()->split($sql_blob);

my $dbh = DBI->connect( "dbi:mysql:my_database:localhost:3306", "username", "password" );
foreach my $sql (@sql_list) {
  print 'Executing ', $sql;
  $dbh->do($sql) or print "Can't do ", $dbh->errstr;
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Cant locate SQL/Splitstatement.pm Uncaught exception from user code: Can't locate SQL/SplitStatement.pm in @INC (@INC contains: C:/Dwimperl/p erl/site/lib C:/Dwimperl/perl/vendor/lib C:/Dwimperl/perl/lib .) at testsql.pl l ine 15.
You will need to install the module, from the command line run 'cpan SQL::SplitStatement'
1

The CPAN module DBIx::RunSQL maybe helpful:

use strict;
use warnings;
use DBIx::RunSQL;

my $test_dbh = DBIx::RunSQL->create(
    dsn     => 'dbi:SQLite:dbname=:memory:',
    sql     => 'test1.sql',
    force   => 1,
    verbose => 1,
);

Change the DBI dsn as your needs (also add user and password params)

Improve this answer

2 Comments

This is a little scary, looking at the source code it blindly splits on ';\n' - I would avoid anything that doesn't use a proper tokenizer.
To date, it suited my needs. In any case, the module author says about split_sql subroutine: "It is documented because it is not a very smart subroutine and you might want to override or replace it"

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.