DBI selectall_hashref

[Jeff]

I'm having issues with selectall_hashref. Here's my code:

===================

#!/usr/bin/perl

use DBI;
use Data:umper;

$ENV{'TDSPORT'}="1433";

# Set Login Information
$Server="xxxx.xxx.xxx";
$User="xxxxx";
$Pass="xxxxxx";

# Connect to database
$dbh=DBI->connect("dbi:Sybase:server=$Server",$User,$Pass) || die
"$DBI::errstr\n";

# Read from database
$SQL="
SELECT
CallID,PlannedDate,PlannedTime,Solution,Status,DevApprov,qaApprvl,whoApprvdDev,whoApprvdQA
FROM heat.Detail WHERE PlannedDate = '2004-10-06'
";
$Results = $dbh->selectall_hashref("$SQL","CallID");

# Disconnect from database
$dbh->disconnect;

__END__


Here's what Data:umper shows . . .

$VAR1 = {
'00492613' => {
'CallID' => '00492613',
'PlannedTime' => ' ',
'Status' => '1 - Pending',
'PlannedDate' => '2004-10-06',
'DevApprov' => 'F',
'Solution' => 'test lead notification',
'whoApprvdQA' => ' ',
'qaApprvl' => 'F',
'whoApprvdDev' => ' '
},
};


=====================

Question one: Where is the $VAR1 variable coming from?
Question two: What happened to $Results?
Question three: How do I reference the data (ie, in a print
statement)?

Thanks.

 

[Paul Lalli]

Subject: DBI selectall_hashref

You do not seem to have a question about selectall_hashref. You seem to
have questions about Data:umper. Please try to put the subject of
your post in the Subject of your post.

Here's what Data:umper shows . . .

$VAR1 = {
'00492613' => {
'CallID' => '00492613',
'PlannedTime' => ' ',
'Status' => '1 - Pending',
'PlannedDate' => '2004-10-06',
'DevApprov' => 'F',
'Solution' => 'test lead notification',
'whoApprvdQA' => ' ',
'qaApprvl' => 'F',
'whoApprvdDev' => ' '
},
};

=====================

Question one: Where is the $VAR1 variable coming from?

Did you read perldoc Data:umper ?

Question two: What happened to $Results?

Did you read perldoc Data:umper ?

Question three: How do I reference the data (ie, in a print
statement)?

$Results is a hash reference. The hash it references contains one key,
the string '00492613'. This key's value is a hash reference.

$Results is a hash reference
%$Results is the hash it refers to.
$$Results{'00492613'} is a reference to the 'inner' hash.
$Results->{'00492613'}is another way to write that
$Results->{'00492613'}->{'PlannedDate'} is the value '2004-10-06'.

I suggest you read one or more of the following documentations:
perldoc perlreftut
perldoc perllol
perldoc perldsc

Paul Lalli

 

[Brian McCauley]

I'm having issues with selectall_hashref.

No you are not. Or maybe you are but all the questions you have posted
here have nothing to do with selectall_hashref and are purely questions
about references and Data:umper. Partitioning your problem correctly
is a very important skill to develeop.

$Results = $dbh->selectall_hashref("$SQL","CallID");

# Disconnect from database
$dbh->disconnect;

Here's what Data:umper shows . . .

$VAR1 = {
'00492613' => {
'CallID' => '00492613',
'PlannedTime' => ' ',
'Status' => '1 - Pending',
'PlannedDate' => '2004-10-06',
'DevApprov' => 'F',
'Solution' => 'test lead notification',
'whoApprvdQA' => ' ',
'qaApprvl' => 'F',
'whoApprvdDev' => ' '
},
};


=====================

Question one: Where is the $VAR1 variable coming from?

It is the default name Data:umper:umper will use for the first
variable you pass it unless you tell it otherwise.

Question two: What happened to $Results?

At a guess you passed it as a argument to Data:umper:umper but for
some reason you deleted the line in your code where you did this before
posting.

Question three: How do I reference the data (ie, in a print
statement)?

The use of references is covered in

perldoc perlreftut
perldoc perlref

Typically you'd do something like:

print $Results->{00492613}{Status};

Note:

You should always declare all variables as lexically scoped in the
smallest applicable lexical scope unless you have a positive reason to
do otherwise. BTW: this is not perculliar to Perl, it applies in all
programming languges - allowing that a language not having lexical
variables is a positive reason .

For Perl this means that most of the time the declaration of scalars
should be combined with the first assignment. BTW: this to is not
perculliar to Perl, it also applies in other programming languges
where assignment and declaration can be combined.

By following this convention you will be able to get maximum beniefit
out of putting "use strict" at the top of all your scripts.

Try to get into this habit now, do not wait for your failure to do so
to cause you the unecessary distress of wasting your own time and that
of other people. The longer you leave it the harder you will find it
to adjust. Worse still, if you leave it too long you may never adjust
and may mutate into a bitter and twisted troll.

 

[Tad McClellan]

$Results = $dbh->selectall_hashref("$SQL","CallID");

^ ^
^ ^
$Results = $dbh->selectall_hashref($SQL,"CallID");


See:

perldoc -q vars

What's wrong with always quoting "$vars"?

 

[Glenn Jackman]

I'm having issues with selectall_hashref. Here's my code: [...]
$Results = $dbh->selectall_hashref("$SQL","CallID"); [...]
Here's what Data:umper shows . . .

$VAR1 = {
'00492613' => {
'CallID' => '00492613',
'PlannedTime' => ' ',
'Status' => '1 - Pending',
'PlannedDate' => '2004-10-06',
'DevApprov' => 'F',
'Solution' => 'test lead notification',
'whoApprvdQA' => ' ',
'qaApprvl' => 'F',
'whoApprvdDev' => ' '
},
};


=====================

Question one: Where is the $VAR1 variable coming from?
Question two: What happened to $Results?

Presumably, somewhere you issued "print Dumper($Results)", although you
don't show that in your code. This is how Dumper() works.
Read: perldoc Data:umper

Question three: How do I reference the data (ie, in a print
statement)?

Read: perldoc perlref

foreach my $callID (keys %$Results) {
print $callID, "\t", $Results->{$callID}{Status}, "\n";
}

 

[Glenn Jackman]

I'm having issues with selectall_hashref. Here's my code: [...]
$Results = $dbh->selectall_hashref("$SQL","CallID"); [...]
Here's what Data:umper shows . . .

$VAR1 = {
'00492613' => {
'CallID' => '00492613',
'PlannedTime' => ' ', [...]
'whoApprvdDev' => ' '
},
};
=====================
Question one: Where is the $VAR1 variable coming from?
Question two: What happened to $Results?

Presumably, somewhere you issued "print Dumper($Results)", although you
don't show that in your code. This is how Dumper() works.
Read: perldoc Data:umper

Question three: How do I reference the data (ie, in a print
statement)?

Read: perldoc perlref

foreach my $callID (keys %$Results) {
print $callID, "\t", $Results->{$callID}{Status}, "\n";
}

 

[Ben Morrow]

I'm having issues with selectall_hashref. Here's my code:

Others have dealt with your problems... these are just a few comments.

#!/usr/bin/perl

use warnings;
use strict;

use DBI;
use Data:umper;

I prefer Data:ump, as I find its interface less confusing .

# Connect to database
$dbh=DBI->connect("dbi:Sybase:server=$Server",$User,$Pass) || die
"$DBI::errstr\n";

In general use 'or' not '||' for flow control: it has low precedence so
statements like

open my $H, '<', $file or die $!;

don't need parenthesising.

Also, if you put "\n" on the end of a die message it won't tell you
where the error was, so don't do that (at least, don't for messages
meant for 'the developer'. Do for messages meant for 'the user': in
which case they need to be a little more comprehensible than just a bare
$DBI::errstr).

# Read from database
$SQL="
SELECT
CallID,PlannedDate,PlannedTime,Solution,Status,DevApprov,qaApprvl,whoApprvdDev,whoApprvdQA
FROM heat.Detail WHERE PlannedDate = '2004-10-06'
";
$Results = $dbh->selectall_hashref("$SQL","callid");

I would suggest using a here-doc here; I would also suggest using
prepared statements and bind values (although in this particular case
there are no issues with quoting, it is a good habit to get into).
Also SQL lower-cases all identifiers, so there's no need for all those
silly caps.

So we have

my $Results =
$dbh->selectall_hashref(<<SQL, 'callid', undef, '2004-10-06');

SELECT callid, planneddate, plannedtime, solution, status,
devapprov, qaapprvl, whoapprvddev, whoapprvdqa
FROM heat.detail
WHERE planneddate = ?

SQL

# Disconnect from database
$dbh->disconnect();

Assuming you have called your database handle something sensible (like
$dbh), this comment adds precisely nothing to the line of code below it.
Save comments for when you *do* need to explain something.

Ben