如何从perl脚本加载.sql文件并在perl脚本中执行sql命令

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

我必须在perl脚本中加载SQL文件并执行SQL命令并仅在perl文件中显示输出。

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

场景:test1.sql文件包含此格式的SQL命令

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

现在我想在不使用文件的情况下加载test1.sql并执行此SQL并在Perl脚本中显示输出,如表创建的

2 个解决方案

#1

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.

如果我正确地阅读了你的问题,你最大的挑战是将文件拆分成单独的sql语句,SQL :: SplitStatement是有用的,但要注意它不是防弹。

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;
}

#2

The CPAN module DBIx::RunSQL maybe helpful:

CPAN模块DBIx :: RunSQL可能有用:

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)

根据需要更改DBI dsn(还添加用户和密码参数)

#1