SQL database call into Multidimensional Array using Perl Script

#!/usr/local/bin/perl
use DBI;
use File::Copy;
use Time::Local;
use Data::Dumper;

-Comments Describing what I'm doing--------------
-I'm pulling information from a database that has an ID and Name. They are separated by a space and I'm trying to load them into a multidimensional array so as to manipulate Name of each element while keeping it's relationship to the ID constant.--------------------------

When I print #1, the information displays exactly how it should, but somewhere between the push and split function it goes wrong.

 $stm3 = $dbh->prepare($profile);
        $stm3->execute();
#       while (@row = $stm3->fetchrow_array)
        while (@row = $stm3->fetchrow()) {
#1               print "This is the Array: $row[0] $row[1]\n";
                @tmp = ([split / /, @row]);
                print "tmp is 0: $tmp[0] \n ";
#               push (@final, @tmp);
        #       print "E1: $final[0] E2: $final[1]\n";
        }

-I'm using the following statement to print out the results of the multi-dimensional array--------------------

for($i=0; $i<=$#final; $i++)
{
  print "i=$i\n";
  for($j=0; $j<2; $j++)
  {
    print " j = $j, Array: $final[$i][$j]";
  }
  print "\n";
}

Any thing will help.

You could do something like this -

C:\>
C:\>type fetchdata.pl
#!perl -w
use strict;
use DBI;
 
my @all_data;
my $sql = "SELECT empno, ename FROM emp";
my $dbh = DBI->connect ("dbi:Oracle:db_name", "user", "password");
my $sth = $dbh->prepare ($sql);
$sth->execute();
while (my @row = $sth->fetchrow()) {
 push @all_data, [@row];
}
$sth->finish();
$dbh->disconnect();
 
# ##############################################################################
# The array @all_data looks like this now.
# Each array element is a reference to an anonymous array that has the "Empno"
# and "Ename" values.
# ##############################################################################
#   $all_data[1]  = [ "7369", "SMITH"  ];
#   $all_data[2]  = [ "7499", "ALLEN"  ];
#   $all_data[3]  = [ "7521", "WARD"   ];
#   $all_data[4]  = [ "7566", "JONES"  ];
#   $all_data[5]  = [ "7654", "MARTIN" ];
#   $all_data[6]  = [ "7698", "BLAKE"  ];
#   $all_data[7]  = [ "7782", "CLARK"  ];
#   $all_data[8]  = [ "7788", "SCOTT"  ];
#   $all_data[9]  = [ "7839", "KING"   ];
#   $all_data[10] = [ "7844", "TURNER" ];
#   $all_data[11] = [ "7876", "ADAMS"  ];
#   $all_data[12] = [ "7900", "JAMES"  ];
#   $all_data[13] = [ "7902", "FORD"   ];
#   $all_data[14] = [ "7934", "MILLER" ];
# ##############################################################################
 
# loop and print
foreach my $item (@all_data) {
 printf ("Empno = %4d  Ename = %-s\n", @$item );
}
 
C:\>
C:\>perl fetchdata.pl
Empno = 7369  Ename = SMITH
Empno = 7499  Ename = ALLEN
Empno = 7521  Ename = WARD
Empno = 7566  Ename = JONES
Empno = 7654  Ename = MARTIN
Empno = 7698  Ename = BLAKE
Empno = 7782  Ename = CLARK
Empno = 7788  Ename = SCOTT
Empno = 7839  Ename = KING
Empno = 7844  Ename = TURNER
Empno = 7876  Ename = ADAMS
Empno = 7900  Ename = JAMES
Empno = 7902  Ename = FORD
Empno = 7934  Ename = MILLER
 
C:\>
C:\>

Or you could also use selectall_arrayref() to fetch all data at once, store it in a multidimensional array and return the reference of that array.

C:\>
C:\>type fetchdata_1.pl
#!perl -w
use strict;
use DBI;
 
my $sql = "SELECT empno, ename FROM emp";
my $dbh = DBI->connect ("dbi:Oracle:db_name", "user", "password");
my $all_data = $dbh->selectall_arrayref($sql);
$dbh->disconnect();
 
# ##############################################################################
# The scalar variable $all_data is a reference to an anonymous array. Let's say
# that array is @x. Then:
#   $all_data = [ @x ];
# And the elements of array @x are as follows:
# ##############################################################################
#   $x[1]  = [ "7369", "SMITH"  ];
#   $x[2]  = [ "7499", "ALLEN"  ];
#   $x[3]  = [ "7521", "WARD"   ];
#   $x[4]  = [ "7566", "JONES"  ];
#   $x[5]  = [ "7654", "MARTIN" ];
#   $x[6]  = [ "7698", "BLAKE"  ];
#   $x[7]  = [ "7782", "CLARK"  ];
#   $x[8]  = [ "7788", "SCOTT"  ];
#   $x[9]  = [ "7839", "KING"   ];
#   $x[10] = [ "7844", "TURNER" ];
#   $x[11] = [ "7876", "ADAMS"  ];
#   $x[12] = [ "7900", "JAMES"  ];
#   $x[13] = [ "7902", "FORD"   ];
#   $x[14] = [ "7934", "MILLER" ];
# ##############################################################################
# i.e. each array element is a reference to an anonymous array that has the
# "Empno" and "Ename" values.
 
# loop and print the data
foreach my $item (@$all_data) {
 printf ("Empno = %4d  Ename = %-s\n", @$item );
}
 
C:\>
C:\>perl fetchdata_1.pl
Empno = 7369  Ename = SMITH
Empno = 7499  Ename = ALLEN
Empno = 7521  Ename = WARD
Empno = 7566  Ename = JONES
Empno = 7654  Ename = MARTIN
Empno = 7698  Ename = BLAKE
Empno = 7782  Ename = CLARK
Empno = 7788  Ename = SCOTT
Empno = 7839  Ename = KING
Empno = 7844  Ename = TURNER
Empno = 7876  Ename = ADAMS
Empno = 7900  Ename = JAMES
Empno = 7902  Ename = FORD
Empno = 7934  Ename = MILLER
 
C:\>
C:\>

Hope that helps,
tyler_durden

Thanks you've been a big help so far.

Does the print statement only work assuming that the ID no. is 4 digits?

Also how would I traverse and operate on only the second element of each array reference.

x[0] = "1234", "Wow",
x[1] = "2222", "Yep",

I want to traverse through this and manipulate "Wow" and "Yep" and then either insert the newly manipulated back into the original array 'x' replacing the old or create an entire new array with the newly manipulated data along with the original ID no.
???

It's actually the plain old "printf" function borrowed from the C programming language. If you specify the format "%nd" where "n" is an integer, then:

(a) if the number to be printed has "n" or less number of digits, then it is printed using "n" columns, left-padded with blank spaces if necessary.
(b) if the number to be printed has "n+1" or more number of digits, then it uses as many columns as there are digits, to print the number. That is, it does not chop off the number.

An example is given below:

$
$
$ perl -e 'foreach $i (0..10) { $x += 10**$i+1; printf ("Formatted Number =>|%6d|<=\n", $x) }'
Formatted Number =>|     2|<=
Formatted Number =>|    13|<=
Formatted Number =>|   114|<=
Formatted Number =>|  1115|<=
Formatted Number =>| 11116|<=
Formatted Number =>|111117|<=
Formatted Number =>|1111118|<=
Formatted Number =>|11111119|<=
Formatted Number =>|111111120|<=
Formatted Number =>|1111111121|<=
Formatted Number =>|11111111122|<=
$
$

$x is just a number whose magnitude increases rapidly per iteration of the "foreach" loop.
As you can see in the first six cases, the printf function uses 6 columns to display the number ($x) as long as it could - right-justifying it wherever necessary. If the number of digits is more than 6, printf displays the number entirely, using as many columns as required. It does not chop off the number, thereby preserving all information.

Since I am unable to figure out the type of "manipulation" you want to do, I'll assume you want to change the case of "Wow" and "Yep" to all uppercase.
Here's a short script that does that -

$
$
$ perl -le 'push @x, [1234, Wow];   # add the first element
            push @x, [2222, Yep];   # add the second element
            print "Array before manipulation:";
            foreach $i (@x) {
              printf ("Element => [ %5d, %s ]\n", $i->[0], $i->[1]);
            }
            # Now manipulate the array
            foreach $i (@x) {       # loop through the array
              $i->[1] = uc $i->[1]; # change the 2nd element to upper case
            }
            print "=" x 40;
            print "Array after  manipulation:";
            foreach $i (@x) {
              printf ("Element => [ %5d, %s ]\n", $i->[0], $i->[1]);
            }
           '
Array before manipulation:
Element => [  1234, Wow ]
Element => [  2222, Yep ]
========================================
Array after  manipulation:
Element => [  1234, WOW ]
Element => [  2222, YEP ]
$
$
$

tyler_durden