how to use delimiter in fetchrow_array

[vivekgalera]

All,

I have a script where I query DB to get two column delimited by a single white-space. When I use fetchrow_array to fetch each column value it is not able to identify 2nd column at all and fetches both column value together as first column.

In below script I am trying to fetch two variables $jb_schema, $MSG_CNT and if you see script output it shows both column for $jb_schema and $MSG_CNT is NULL.

Can someone help me to use proper delimiter setting with fetchrow_array if possible. Else any idea how do identify two columns separately.

Thanks
Vivek

Script (truncated):
====================================
foreach (@jb_schemas)
{
if ($_ eq "@jb_schemas[$jb_schemas_len]") {
$sql1 = $sql1."\nSELECT '$_ ' || COUNT(*) \"SCHEMA TOTAL_MSG\" FROM $_.JMS_MESSAGE";
} else {
$sql1 = $sql1."\nSELECT '$_ ' || COUNT(*) \"SCHEMA TOTAL_MSG\" FROM $_.JMS_MESSAGE
UNION";
}
}
#print "SQL STATEMENT : $sql1 \n\n";
#
$cursor = $dbh1->prepare($sql1);
$cursor->execute;
while (($jb_schema, $MSG_CNT) = $cursor->fetchrow_array()) {
if ($MSG_CNT >= 1) {
unless ($mycritical) {
$mymsg = $mymsg . "critical: ";
$mycritical="yep";
}
$mymsg = $mymsg . "$jb_schema(Count=$MSG_CNT)";
$state=$ERRORS{"CRITICAL"};
} elsif ($MSG_CNT >= 0) {
unless ($mywarn) {
$mymsg = $mymsg . "warning: ";
$mywarn="yep";
}
$mymsg = $mymsg . "$jb_schema(Count=$MSG_CNT) ";
$state=$ERRORS{"WARNING"} if $state < $ERRORS{"WARNING"};
}
}
#$message = $message . $mymsg . " " ;
print $mymsg;
=====================================

Query Output:
=====================================
SQL> SELECT 'JBOSS1 ' || COUNT(*) "TOTAL_MSG" FROM JBOSS1.JMS_MESSAGE
2 UNION
SELECT 'JBOSS3 ' || COUNT(*) "TOTAL_MSG" FROM JBOSS3.JMS_MESSAGE 3 ;
TOTAL_MSG
-----------------------------------------------
JBOSS1 1
JBOSS3 0
SQL>
=====================================

Script Output:
=====================================
[root@gwmon1 libexec]# ./bg_test.pl -u jboss_read -p jboss_read -c sdbcayod -i 10.11.1.43

warning: JBOSS1 1(Count=) JBOSS2 0(Count=) JBOSS3 1(Count=) JBOSS4 0(Count=) JBOSS5 0(Count=)
=======================================

 

[stevexff]

I'm a bit confused by your SQL statement(s). Try

use strict;
use warnings;
[red]use Data::Dumper;[/red]

my @sql;

foreach my $schema (@jb_schemas) {
   push @sql, "SELECT '$schema', COUNT(*) FROM $schema.JMS_MESSAGE";
}

my $sqh = $dbh->prepare(join(" UNION ", @sql));

$sqh->execute();

my $data = $sqh->fetchall_arrayref();

print Dumper($data);

and see what you get...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]