Why is the perl script erroring out on a 'use of uninitialized value in concatentation or string ' with bind params

I am using Perl to connect to Oracle database to retrieve rows.

The code fails with the error when attempting to assign bind parameters to 3 variables derivdate, group and row_number. The code works CORRECTLY and outputs data ONLY when the 3 values are hard coded in the query without the bind param assignments.


my $gen_text = $dbh->prepare(qq(SELECT col1, col2 FROM region WHERE STATUS = 'N' AND PARTITION_KEY IN (SELECT col_1 AS part_key FROM Sched  WHERE derivdate = to_date(?,'YYYY-MM-DD') and group = ? and row_number = ?)))    
               						or die("Could not get data from table '$_'".DBI->errstr); 
               						 
$gen_text->bind_param(1,$derivdate); 
$gen_text->bind_param(2,$group); 
$gen_text->bind_param(3,$row_number); 
 
$gen_text->execute($derivdate, $group, $row_number); 
 
 
my @firstrow; 
my @columns; 
 
 
if (exists($$cols[0])) { 
	   
        foreach my $col (@$cols) { 
            my ($name) = $$col[0]; 
            $name =~ s/\s+//g; 
            push @columns, $name; 
        } 
    } else { 
    	   
        my $rc  = $gen_text->fetchrow_hashref() or $nodata = 1; 
        if ($nodata) { 
        	print("  - No data found in table '$_' ".$gen_text->errstr());   -- Line 86
        	exit(0); 
        } 
        my @row = @{$gen_text->{NAME}}; 
        @$cols  = @row; 
     
        foreach my $col (@$cols) { 
            my $name = $col; 
            $name =~ s/\s+//g; 
            my $data = $rc->{$name}; 
            push @firstrow, $data; 
            push @columns, $name; 
        } 
    } 
    print "Creating export file for table '$_'\n";

Open in new window



Error message(s) :
Use of uninitialized value in concatenation (.) or string at filename.pl line 86.
Use of uninitialized value in concatenation (.) or string at filename.pl line 86.
  - No data found in table ''

Open in new window

steve2312Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
The source you provided is truncated prior to line 86, so no way to really answer your question.

I'd suggest you add this to the top of your code, to assist with your debugging...

use strict;
use warnings;

Open in new window


Likely this will help some.
0
steve2312Author Commented:
I have the select statement to the right of the scrollbar showing the parameters in question and Am using the warnings and strict clauses in my code. This may be due to the way/order of bind variables and the execute statements for 3 variables are used??

use strict;
use warnings;
use DBI;
use File::Copy;
use File::Path;
use Date::Manip;
use Getopt::Std;


Date_Init('TZ=US/Central','Internal=1');

# Enable Autoflush
#
#$| = 1;


my %opts;
getopts('d:l:h', \%opts);

my $date    = UnixDate(($opts{'d'} || 'today'), '%q');
my $dumpdir = $opts{'l'} || '.';
my $nodata = 0;
my ($db, $user, $pw, @tables, $derivedate, $group, $row_number)  = @ARGV;

#unless($db && $user && $pw && @tables && $batchdate && $calcgroup && $runnumber) 
#{
#	print("args = $date, $outdir, $db, $user, $pw, @tables, $batchdate, $calcgroup, $runnumber\n\n");
#    die("\n\n  - ERROR:  The Required number of arguments were not received.  Verify that the AppWorx prompts are correct and reset the module..\n\n");
#} 

if (scalar(@tables) == 0) {
    print ("  - No tables specified. Exiting.\n"); 
    exit(1);
}

my $flcnt   = 0;

# Connection check to the database

my $dbh = DBI->connect("dbi:Oracle:$db", $user, $pw) or die("Cannot connect to ... '$db' " . DBI->errstr());
	
 my $cols = $dbh->selectall_arrayref(qq( 
        SELECT columnname 
          FROM columnmetadata c, 
               tablemetadata t 
         WHERE t.uidtable = c.uidtable 
           AND t.tablename = ? 
      ORDER BY columnnumber), undef, uc($_)) or die("  - Could not get column names for table '$_' ".$dbh->errstr());


my $gen_text = $dbh->prepare(qq(SELECT col1, col2, col3, col4,FROM region WHERE STATUS = 'N' AND PARTITION_KEY IN (SELECT col1 AS part_key	
FROM schedule where derivdate = to_date(?,'YYYY-MM-DD') and group = ? and row_number = ?)))   
or die("Could not get data from table '$_'".DBI->errstr);
               						
$gen_text->bind_param(1,$derivedate);
$gen_text->bind_param(2,$group);
$gen_text->bind_param(3,$row_number);

$gen_text->execute($batchdate, $calcgroup, $runnumber);
....

Open in new window

0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
The error you mention relates to line #86, which isn't shown, so difficult to tell what's occurring.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

steve2312Author Commented:
It actually the line 26 on the code snippet (i took out the comments from the original code)

if ($nodata) { 
        	print("  - No data found in table '$_' ".$gen_text->errstr());   -- Line 26
        	exit(0);

Open in new window

0
FishMongerCommented:
It appears that $_ is undefined.

I also see a problem with this statement:
my ($db, $user, $pw, @tables, $derivedate, $group, $row_number)  = @ARGV;

Open in new window

@tables will slurp all the remaining args leaving $derivedate, $group, $row_number all undefined.  Is that what you intended?
0
FishMongerCommented:
Your code also shows design inconsistencies.  Such as using @ARGV when assigning your vars as well as using Getopt::Std?  It would be more consistent  to use one or the other.  IMO, it would also be better to use Getopt::Long (and Pod::Usage) instead of Getopt::Std.
0
steve2312Author Commented:
@tables was included as a parameter to select rows from the oracle db and output.  The intent is that this table will stay the same and will not change. So probably this need to be in the parameter list?
0
FishMongerCommented:
Are you passing a list of table names as command line args to the script?  If not, then it should not be in that assignment statement.  If you are passing them on the command line, then they need the be the last items passed to the script and @tables should be the last item in that assignment statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
Yes, I would be passing the table name and database parameters (dbname, user/pwd et) as a command line args to the script. The script would connect to the database and retrieve data from table and created a out file.
0
steve2312Author Commented:
Noted the order of arguments!  

As for the bind parameter execution order, is the below order the right way to go about?   Or should the execute() be after each bind_param assignments?

$gen_text ->  bind_param(1, $derivdate);
$gen_text --> bind_param(2, $group);
$gen_text --> bind_param(3, $row_number);

$gen_text --> execute();

Open in new window

0
FishMongerCommented:
Bind statements first, then the execute statement.
0
steve2312Author Commented:
adding the tables as a last parameters worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.