Assign Query Result to Variable

[winky]

Hey everyone,

Relatively new to Perl and had a quick question. Is it possible to assign a SQL query result to a variable? The query will always return a single interger result. This is what I have thus far:

use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:SLOGAN","","") or die "Cannot connect to database";
$stmt = "SELECT VARIABLE_VALUE from LAWSON.WFVARIABLE where VARIABLE_NAME = 'RQH_REQ_NUMBER' AND WORKUNIT = 1";
$sth = $dbh->prepare($stmt);
$sth->exectue();
#This is where I would like to assign the result to a variable

I connect to the database, write my select statement, prepare it, and then execute. From this point, I want to assign what was returned to a variable. Thanks for all your help!

[d5e5]

An example of a query that returns only one row consisting of one column:

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

use warnings;
use strict;
use DBI;
my $pwd = 'dogfood';
my ($dbh, $sth);

$dbh=DBI->connect('dbi:mysql:daniweb','david',$pwd) || 
   die "Error opening database: $DBI::errstr\n";

#The following query should return only one row containing one integer.
$sth=$dbh->prepare("SELECT 5 as lucky_number;") ||
   die "Prepare failed: $DBI::errstr\n";

$sth->execute() ||
   die "Couldn't execute query: $DBI::errstr\n";

my @record = $sth->fetchrow_array; #Assign result to array variable
print "The result of query is $record[0] \n";

$sth->finish();
$dbh->disconnect || die "Failed to disconnect\n";

[winky]

So, if I wanted it to ultimately be stored into a string, I could do the following?

my @record = $sth->ftechrow_array;
$result = "@record"

This is using your example.

[d5e5]

That would work, I guess. Since there is only one element in the array I would do it as follows because to me it looks clearer to understand at a glance that you are assigning one element of an array to one scalar variable:

my @record = $sth->fetchrow_array; #Assign result to array variable
my $result = $record[0];
print "The result of query is $result \n";

[erezschatz]

print "@array" will print all the elements of the array, delimited by the value of $" (default is space).

If there are more than 1 element, you can format an array into a string by assigning $":

my @array = qw(123 234 345 456);
local $" = ', ';
print "@array"

or by using join:

join ', ' @array

Both concatenates all the elements using ', ' as the delimiter

[d5e5]

Here is an example of a slightly different way of assigning the first (and only) element retrieved by fetchrow_array to a scalar variable without having to declare an array variable as an intermediary:

#The following query should return only one row containing one integer.
$sth=$dbh->prepare("SELECT COUNT(*) FROM tasks;") ||
   die "Prepare failed: $DBI::errstr\n";

$sth->execute() ||
   die "Couldn't execute query: $DBI::errstr\n";

my ($nbr) = $sth->fetchrow_array; #Assign result to scalar variable (note parentheses)
print "The number of tasks is $nbr \n";