how can i pass perl variable in sql file in perl script

Question

here i have 3 variable in perl and i m passing those variable in one db.sql file in sql query line then executing that db.sql file in same perl file but it's not fetching those variable.

$patch_name="CS2.001_PROD_TEST_37987_spyy";
$svn_url="$/S/B";
$ftp_loc="/Releases/pkumar";
open(SQL, "$sqlfile") or die("Can't open file $sqlFile for reading");
while ($sqlStatement = <SQL>) 
{
   $sth = $dbh->prepare($sqlStatement) or die qq("Can't prepare $sqlStatement");

   $sth->execute() or die ("Can't execute $sqlStatement");
}
close SQL;
$dbh->disconnect;

following is the db.sql file query

insert into branch_build_info(patch_name,branch_name,ftp_path)
values('$patch_name','$svn_url','$ftp_loc/$patch_name.tar.gz');

pls help me how can i pass the variable so that i can run more the one insert query at a time.

What error messages or other info about what goes wrong do you have? Note that you are using two different variables in $sqlfile and $sqlFile, and "$/S/B" contains the dollar sign meta character, which will be expanded to $/, which is the input record separator. — TLP
– TLP, Commented Sep 27, 2011 at 12:18
You can't prepare the statement 'insert into branch_build_info(patch_name,branch_name,ftp_path)' which is what you're trying to do by going one line at a time--given that your db.sql file is valid. — Axeman
– Axeman, Commented Sep 27, 2011 at 12:43

Egga Hartung · Accepted Answer · 2011-09-27 11:57:31Z

4

If you can influence the sql-file, you could use placeholders

insert into ... values(?, ?, ?);

and then supply the parameters at execution:

my $ftp_path = "$ftp_loc/$patch_name.tar.gz";
$sth->execute( $patch_name, $svn_url, $ftp_path) or die ...

That way you would only need to prepare the statement once and can execute it as often as you want. But I'm not sure, where that sql-file comes from. It would be helpful, if you could clarify the complete workflow.

answered Sep 27, 2011 at 11:57

Egga Hartung

1,09111 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

glibdud · Accepted Answer · 2011-09-27 15:14:52Z

You can use the String::Interpolate module to interpolate a string that's already been generated, like the string you're pulling out of a file:

while ($sqlStatement = <SQL>) 
{
   my $interpolated = new String::Interpolate { patch_name => \$patch_name, svn_url => \$svn_url, ftp_loc => \$ftp_loc };
   $interpolated->($sqlStatement);

   $sth = $dbh->prepare($interpolated) or die qq("Can't prepare $interpolated");

   $sth->execute() or die ("Can't execute $interpolated");
}

Alternately, you could manually perform replacements on the string to be interpolated:

while ($sqlStatement = <SQL>) 
{
   $sqlStatement =~ s/\$patch_name/$patch_name/g;
   $sqlStatement =~ s/\$svn_url/$svn_url/g;
   $sqlStatement =~ s/\$ftp_loc/$ftp_loc/g;

   $sth = $dbh->prepare($sqlStatement) or die qq("Can't prepare $sqlStatement");

   $sth->execute() or die ("Can't execute $sqlStatement");
}

plusplus · Accepted Answer · 2011-09-27 15:49:34Z

there's a few problems there - looks like you need to read in all of the SQL file to make the query, but you're just reading it a line at a time and trying to run each line as a separate query. The <SQL> just takes the next line from the file.

Assuming there is just one query in the file, something like the following may help:

open( my $SQL_FH, '<', $sqlfile)
   or die "Can't open file '$sqlFile' for reading: $!"; 

my @sql_statement = <$SQL_FH>;

close $SQL_FH;   

my $sth = $dbh->prepare( join(' ',@sql_statement) )
   or die "Can't prepare @sql_statement: $!";

$sth->execute();

However, you still need to expand the variables in your SQL file. If you can't replace them with placeholders as already suggested, then you'll need to either eval the file or parse it in some way...

Collectives™ on Stack Overflow

how can i pass perl variable in sql file in perl script

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related