Passing psql query from perl into string

Question

Currently I have a perl script that accesses our database, performs certain queries and prints output to the terminal. Instead, I would like to output the results into a template latex file before generating a pdf. For most of my queries I pull out numbers and store these as scalar variables (eg how often a particular operator carries out a given task). eg.

foreach $op (@operator) {
    $query = "SELECT count(task_name) FROM table WHERE date <= '$date_stop' and 
              date >= '$date_start' and task=\'$operator[$index]\';";

    #execute query
    $result=$conn->exec($query);
    $conres = $conn->errorMessage;
    if ($result->resultStatus eq PGRES_TUPLES_OK) {
        if($result->ntuples > 0) {
            ($task[$index]) = $result->fetchrow;
        }
        printf("$operator[$index] carried out task: %d\n", $task[$index]);   
    } else {
        die "Failed.\n$conres\n\n";
        exit -1;
    }
    $index++;
} 
printf("**********************************\n\n");

In the final report I will summarise how many times each operator completed each task in a table. In addition to this there will also be some incidents which must be reported. I can print these easily to the terminal using a command such as

$query = "SELECT operator, incident_type from table_name WHERE incident_type = 'Y' 
          and date <= '$date_stop' and date >= '$date_start';";
$result=$conn->exec($query);
$conres = $conn->errorMessage;
if ($result->resultStatus eq PGRES_TUPLES_OK) {
    if($result->ntuples > 0) {
        $result->print(STDOUT, 1, 1, 0, 0, 0, 1, "\t", "", "");
    }
} else {
    die "Failed.\n$conres\n\n";
    exit -1;
}

An example of the output of this command is

operator  |   incident_type
-----------------------------
AB        |  Incomplete due to staff shortages
-------------------------------
CD        |  Closed due to weather
-----------------------------

How can I make my perl script pass the operator names and incidents into a string array rather than just sending the results to the terminal?

Are you using the DBI module? You should perhaps specify that. — TLP
– TLP, Commented Jan 15, 2013 at 13:53
It looks like you're not even using strict, and you're using printf and exit wrong. Then there's the pointless \' and the SQL injection issues. This does not make me want to take a closer look. — melpomene
– melpomene, Commented Jan 15, 2013 at 13:55
The Pg module seems to be 13 years old, perhaps not the most up-to-date, though I suppose its possible it still works. You have multiple errors in your code, so my advice is to scrap it and start over, using the DBI module instead. — TLP
– TLP, Commented Jan 15, 2013 at 14:15
@moadeep You're using printf where print would suffice (print "*****\n\n", print "$operator[$index] carried out task: $task[$index]\n"); you're using printf with a variable format string (printf "$operator[$index] ...", format string injection bug); exit (at least on unixish OSes) only takes values between 0 and 255 (exit -1 is nonsensical). If you're using strict 'vars', then all of your variables have a too wide scope (globals?). You really should start each file with use warnings; use strict;. — melpomene
– melpomene, Commented Jan 15, 2013 at 14:15
@melpomene Don't forget using $op in the for loop, but using $operator[$index] to access the elements. And using die before exit is also rather a pointless exercise. — TLP
– TLP, Commented Jan 15, 2013 at 14:28

score 4 · Accepted Answer · 2013-01-15 15:28:47Z

4

You should consider updating your script to use DBI. This is the standard for database connectivity in Perl.

DBI has a built in facility for inserting parameters into a query string. It is safer and faster than manually creating the string yourself. Before the loop, do this once:

#dbh is a database handle that you have already opened.
my $query = $dbh->prepare(
    "SELECT count(task_name) FROM table WHERE date<=? and date>=? and task=?"
);

Then within the loop, you only have to do this each time:

$query->execute($date_stop,$date_start,$op);

Note that the parameters you pass to execute automatically get inserted in place of the ?'s in your statement. It handles the quoting for you.

Also in the loop, after you execute the statement, you can get the results like this:

my $array_ref = $query->fetchall_array_ref;

Now all of the rows are stored in a two-dimensional array structure. $array_ref->[0][0] would get the first column of the first row returned.

See the DBI documentation for more information.

As others have mentioned, there are quite a few other mistakes in your code. Make sure you start with use strict; use warnings;, and ask more questions if you need further help!

edited Jan 15, 2013 at 15:28

answered Jan 15, 2013 at 15:03

user1919238

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

2 Comments

moadeep Over a year ago

Thanks for the reply. As it is a postgreSQL database, should I be using DBD:Pg driver to connect to the database?

user1919238 Over a year ago

@moadeep, I haven't used postgreSQL, but yes, that appears to be the correct driver.

Martin · Accepted Answer · 2013-01-15 16:48:58Z

2

Lots of good feedback to your script, but nothing about your actual question.

How can I make my perl script pass the operator names and incidents into a string array rather than just sending the results to the terminal?

Have your tried creating an array and pushing items to it?

my @array;
push (@array, "foo");

Or using nested arrays:

push (@array, ["operator", "incident"]);

answered Jan 15, 2013 at 16:48

Martin

5,4023 gold badges20 silver badges16 bronze badges

2 Comments

moadeep Over a year ago

I haven't but this looks exactly what I need. I have taken the feedback on board and I am currently rewriting my script from scratch and attempting to do it properly and following good perl convention. Thanks

user1919238 Over a year ago

My answer does show how to get the query results in an array (albeit using a different module).

Collectives™ on Stack Overflow

Passing psql query from perl into string

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related