RE: how to invoke .sql file from dbi
- From: Peter.Loo@xxxxxxxxxxxxxxxxxxxxxxxx (Peter # PHX Loo)
- Date: Wed, 24 May 2006 08:28:16 -0700
Philip,
This is an excellent idea. I created a sub-routine to parse out the sql
file in to a string like Ravi had suggested like the following. It is
ugly, but has been working for awhile.
sub sub_readSQLFile($) {
print STDERR "\n***** sub_readSQLFile() *****\n\n";
my ($sqlFile) = @_;
print STDERR "SQL FILE IS: $sqlFile\n";
my ($stmtType, $sqlString);
unless (open(FH, "$sqlFile")) {
$MESSAGE = "sub_readSQLFile() - Unable to open SQL file
$sqlFile.";
$STATUS = $FAILURE;
sub_exit();
}
while (<FH>) {
chomp;
s/--.*$//;
next if /^\//;
next if /^\s*spool/i;
next if /^\s*--/;
next if /^\s*$/;
next if /^\s*exit/i;
next if /^\s*quit/i;
$sqlString .= " $_";
}
$sqlString =~ s/^\s+//;
$sqlString =~ s/\s+/ /g;
$sqlString =~ s/(\s+),/,/g; # <=== Newly added
$sqlString =~ s/(\s)*;(\s)*$//;
close(FH);
$stmtType = "insert" if $sqlString =~ /^\s+insert/i;
$stmtType = "select" if $sqlString =~ /^\s+select/i;
$stmtType = "delete" if $sqlString =~ /^\s+delete/i;
$stmtType = "update" if $sqlString =~ /^\s+update/i;
$stmtType = "plsql" if $sqlString =~ /^\s+begin/i;
print STDERR "RETURNING STATEMENT TYPE IS: $stmtType\n";
print STDERR "RETURNING SQL STRING IS: $sqlString\n\n";
return($stmtType, qq{$sqlString});
} #<=== (End of sub_readSQLFile)
Peter Loo
Wolters Kluwer Health
(602) 381-9553
-----Original Message-----
From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@xxxxxxxxxxx]
Sent: Wednesday, May 24, 2006 7:12 AM
To: DBI-Users
Subject: RE: how to invoke .sql file from dbi
-----Original Message-----
From: Ravi.Kongara@xxxxxxx [mailto:Ravi.Kongara@xxxxxxx]
Sent: Tuesday, May 23, 2006 2:40 PM
To: DBI-Users
Subject: how to invoke .sql file from dbi
Hello all,
We have bunch of sql files ( .sql ) that we want to invoke from dbi (
Oracle ). How does it work. Dbi expects actual sql statements to be
given as it's arguments whether in case of do() or prepare() methods.
I wonder is there any option to just point the dbi to a sql file and
dbi taking care of it.
No, there isn't.
I do understand that running sql file is sqlplus feature and may not
be applicable to dbi, as dbi is more generic. We have hundreds of sql
files to be run like this. Each sql file has multiple sql statements
within it.
I know these are my options..
1) Parse sql files and assign each sql statement to string variable
and pass the string as an argument to do(). File handler routines
may come in handy for this.
This is undesirable if your SQL files have anything other than VERY
simple SQL. For example, if your SQL files contain semicolons embedded
in quotes, your parser will have to handle that. Also, if the files are
written specifically for sqlplus, they may contain directives to sqlplus
(as opposed to the database) -- bind variables are one example.
2) Do not use DBI. Instead call 'sqlplus' from perl itself with file
as input parameter.
This is probably both safest and easiest. Rather than spawning a new
sqlplus for every sql file, though, you could just open a pipe to
sqlplus and feed it the files from within perl, e.g.
use File::Copy;
open(my $SQLPLUS, '|sqlplus user/pass@dsn') or die $!;
foreach (@files) {
copy($_, $SQLPLUS);
}
close($SQLPLUS);
hth,
Philip
This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.
This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
.
- Prev by Date: Re: (Fwd) Just a quick question on the DBI perl mod
- Next by Date: slides of "Making DBI/DBD scripts portable"
- Previous by thread: RE: how to invoke .sql file from dbi
- Next by thread: Re: how to invoke .sql file from dbi
- Index(es):
Relevant Pages
|
|
