perl variable to module

Question

I'm trying to execute a query that is stored in a module, but the query has some variables that are read from the main file and is not working.

The variable from the main file is not accessible (its blank) inside the module.

Here is the code:

sql.pm

package sql;

use parent 'Exporter'; 

our @EXPORT = qw(%query);

our %query = (
    one => "SELECT isnull(BusinessEntityID, '') as BusinessEntityID,
       isnull(Title, '') as Title,
       isnull(FirstName, '') as FirstName,
       isnull(LastName, '') as LastName,
       isnull(Suffix, '') as Suffix,
       isnull(JobTitle, '') as JobTitle

FROM HumanResources.vEmployee
where BusinessEntityID = ".$id."
order by BusinessEntityID"

);

1;

main.pl

use strict;
use warnings;

use sql;

my $id = 273;
my $query_main = "$query{one}";

print $query_main;

This prints the query with where BusinessEntityID = instead of where BusinessEntityID = 273

Thank you!

Inside sql package create a function get_query { my ($id)=@_; %query=...}. Call it my $query_main=sql::get_query($id); — wolfrevokcats
– wolfrevokcats, Commented Mar 3, 2018 at 15:41
please read bobby-tables.com and use placeholders instead of inserting variables directly into the query. — tinita
– tinita, Commented Mar 3, 2018 at 15:45
@wolfrevokcats Thank you for the quick reply, it helped, but I have many more (50+) that depend on various foreach/ifs etc. Would there be a more easy way without having to declare every variable in the module? Im thinking if there would be possible to export all variables from main.pl and import them into sql.pm — rooger
– rooger, Commented Mar 3, 2018 at 15:53
@rooger global variables are almost always a bad idea. you'll loose control over it. I had to fight a lot with existing programs using global variables like that in several jobs I had. if you have many variables, you should use a hash and pass that to the function(s), at least — tinita
– tinita, Commented Mar 3, 2018 at 15:58
@tinita Thank you for the quick reply, if its possible can you provide me an simple example? — rooger
– rooger, Commented Mar 3, 2018 at 16:07

Borodin · Accepted Answer · 2018-03-03 16:47:15Z

3

There is no lexical $id variable in the scope of your sql package, so it is evaluated as undef which is stringified to the empty string

You will have seen warning messages when you tried to run your program. Please don't ignore them, especially when you're asking for help: they are very important

Please name your modules sensibly. A name like sql denotes a pragma, and affects all of the following code. A module will use Capital::Letters and should be very different from anything in CPAN

I think you should restrict your library to subroutines only, which may include constants, and you should avoid interpolating values into an SQL query string

For instance

package My::Sql;

use Exporter 'import';

our @EXPORT = qw/
    QUERY_ONE
/;

use constant {
    QUERY_ONE => "SELECT isnull(BusinessEntityID, '') as BusinessEntityID,
       isnull(Title, '') as Title,
       isnull(FirstName, '') as FirstName,
       isnull(LastName, '') as LastName,
       isnull(Suffix, '') as Suffix,
       isnull(JobTitle, '') as JobTitle

FROM HumanResources.vEmployee
where BusinessEntityID = ?
order by BusinessEntityID"

);

1;

If you then

use My::Sql;

my $sth = $dbh->prepare(QUERY_ONE);
$sth->execute(42);

then your code should at least function, but the design is still very lacking

answered Mar 3, 2018 at 16:47

Borodin

127k9 gold badges72 silver badges146 bronze badges

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

1 Comment

rooger Over a year ago

Thank you, I will do it like this!

wolfrevokcats · Accepted Answer · 2018-03-03 17:03:28Z

Of course, it is possible in Perl to access global variables of a package from another package.
But that means you would have to turn all your local and my variables into globals.
It would be a mess.
I strongly advise against that.

One of the methods to simplify management of placeholder variables is using Template Toolkit, as shown below:

#!/usr/bin/perl
use strict;
use warnings;
use Template;

my $template = <<'CUT';
SELECT isnull(BusinessEntityID, '') as BusinessEntityID,
       isnull(Title, '') as Title,
       isnull(FirstName, '') as FirstName,
       isnull(LastName, '') as LastName,
       isnull(Suffix, '') as Suffix,
       isnull(JobTitle, '') as JobTitle

FROM HumanResources.vEmployee
where BusinessEntityID = [% id %]
order by BusinessEntityID
CUT
;

my $id=100;
my ($param1,$paramN);
my $vars = {
    'id'=>$id,
    'param1'=>$param1,
     #...
    'paramN'=>$paramN,
};

my $tp = Template->new();
my $out;
$tp->process(\$template, $vars, \$out)
    || die $template->error();
print($out);

output

SELECT isnull(BusinessEntityID, '') as BusinessEntityID,
       isnull(Title, '') as Title,
       isnull(FirstName, '') as FirstName,
       isnull(LastName, '') as LastName,
       isnull(Suffix, '') as Suffix,
       isnull(JobTitle, '') as JobTitle

FROM HumanResources.vEmployee
where BusinessEntityID = 100
order by BusinessEntityID

But if you are going run the same query many times with different parameters you should use parameter binding as shown in Borodin's answer, to avoid performance penalties.

Thank you for the help! I will follow you're advice and use Borodin's answer because I have to run the query many times.

Collectives™ on Stack Overflow

perl variable to module

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related