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.
Error message(s) :
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";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 ''ASKER
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);
....
The error you mention relates to line #86, which isn't shown, so difficult to tell what's occurring.
ASKER
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);
It appears that $_ is undefined.
I also see a problem with this statement:
I also see a problem with this statement:
my ($db, $user, $pw, @tables, $derivedate, $group, $row_number) = @ARGV;
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.
ASKER
@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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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?
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();
Bind statements first, then the execute statement.
ASKER
adding the tables as a last parameters worked
I'd suggest you add this to the top of your code, to assist with your debugging...
Open in new window
Likely this will help some.