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!

Answer 1

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