How to store the data fetched from the database in perl

rajsai28 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I want to fecth list of values from the database as shown below in perl script

ABC
JKL
HGJ
KJI
GHJ
LKJ
[download]

sub Emp_Name 
{
my $sth = $dbh->prepare(<<"SQL") or die "Prepare failed: " . $dbh->err
+str();
SELECT DISTINCT EMP_NAME FROM EMPLOYEE
WHERE EMP_ID = ?
SQL
 
$sth->execute () or die "Cannot execute: " . $sth->errstr();
 while ( %Result = $sth->fetchrow() )
 {
 print Dumper(%Result);
}
  $sth->finish;    
}# end
[download]

I want to store the list in hash or array and use the same in perl to check if an element exist in hash/array

I am reading field from the input record and checking whether that field exist in hash/array

Can i store the list in Hash, please let me know

thanks & regards,

Comment on How to store the data fetched from the database in perl Select or Download Code

Replies are listed 'Best First'.
Re: How to store the data fetched from the database in perl by frozenwithjoy (Priest) on Jun 11, 2013 at 06:18 UTC
`#!/usr/bin/env perl use strict; use warnings; use feature 'say'; use Data::Printer; # populating your hash my %data = map { chomp; $_ => 1 } <DATA>; # for your example, might look something like this: # my %data = map { $_ => 1 } $sth->fetchrow(); # do you mean fetchrow_ +array? # two ways to check whether key exists in hash say "ABC exists" if exists $data{'ABC'}; say "JKL exists" if $data{'JKL'} == 1; p %data; __DATA__ ABC JKL HGJ KJI GHJ LKJ` [download] Output: `{ ABC 1, GHJ 1, HGJ 1, JKL 1, KJI 1, LKJ 1 } ABC exists JKL exists` [download]	[reply] [d/l] [select]
Re^2: How to store the data fetched from the database in perl by rajsai28 (Novice) on Jun 11, 2013 at 06:38 UTC
I have used below code while fetching data from the sql but i got the output as below `$sth->execute ($lBOA_BLC_MASTER_ID) or die "Cannot execute: " . $sth-> +errstr(); %data = map { $_ => 1 } $sth->fetchrow();` [download] Output `$VAR1 = 'ABC'; $VAR2 = 1;` [download] thanks and regards,	[reply] [d/l] [select]
Re^3: How to store the data fetched from the database in perl by choroba (Cardinal) on Jun 11, 2013 at 06:51 UTC
`fetchrow` fetches one row at a time. If you want to fetch all the rows, use `fetchall_arrayref`. See DBI. لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ	[reply] [d/l] [select]
Re^3: How to store the data fetched from the database in perl by Neighbour (Friar) on Jun 11, 2013 at 06:53 UTC
Try using `my $hr_data = $dbh->selectall_hashref('SELECT DISTINCT EMP_NAME FROM EMPLOYEE', [ 'EMP_NAME' ]);` This fetches all data in one go. You can then use `say "ABC exists" if exists $hr_data->{ABC};` to check if an element is there.	[reply] [d/l] [select]
Re^3: How to store the data fetched from the database in perl by frozenwithjoy (Priest) on Jun 11, 2013 at 06:51 UTC
I haven't used this module before, so am not totally familiar with this method, but does this work? `my %data; while ( my $result = $sth->fetchrow() ) { $data{$result} = 1; }` [download]	[reply] [d/l]
Re: How to store the data fetched from the database in perl by rpnoble419 (Pilgrim) on Jun 12, 2013 at 01:05 UTC
I'd use YAML for this. Look at the dumpfile and read file functions.	[reply]