udvk009 has asked for the wisdom of the Perl Monks concerning the following question:

dear monks, is there a way to parse perl variable defined in config file which in turn is the sql statement that is read in a file-handle and assigned to the variable which is further executed via a db-handle. To elaborate further.... 1) suppose here is the config file say test_query.sql test_query.sql contains below. The variable $schema & $name are further defined in the master config file that is read separately. 

select * from $schema.app_table where col1 = $name

[download]
2) my main perl script will read this file into a filehandler and assign to a variable that will be further passed to db-handler 
open SQLFILE, $sqlFile or die "Cannot open file $sqlFile : $!\n";
$sqlRef = <SQLFILE>;
$sth =$dbh ->prepare($sqlRef);
$sth -> execute();

[download]
3) The issue here is i see below error at the prepare call as the perl-variable is not evaluated 
:: DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD E
+RROR: error possibly near <*> indicator at char (<*>$schema.app_table
+)

[download]
4) Kindly help me point to right direction on how to go about evaluating the variable from config file. Let me know if you need more information ? Thanks in advance!!

Replies are listed 'Best First'.
Re: How to parse perl variable defined in config file
by Corion (Patriarch) on May 29, 2015 at 07:34 UTC

    Maybe you want to use a proper templating system to replace variable names?

    If you need to do this yourself, follow these steps:

    1. Read the variable values
    2. Read the statement
    3. Replace the variable values in the statement
    4. Execute the statement

    For step three, I suggest the following data structure:

    my %variables= (
    schema => 'myschema',
    name => 'my_name',
);

my $statement= 'select * from $schema.app_table where col1 = $name';

$statement =~ s/\$(\w+)/$variables{ $1 } || '$'.$1/ge;

    [download]

    Note that you will either need to add proper quotes around $name in the SQL statement or add additional logic to recognize when a variable is used as a column value and then transform the logic to use SQL placeholders.

[reply]
[d/l]
[select]

      Thanks for the quick suggestion!

[reply]

Back to Seekers of Perl Wisdom