CODEWITHSUNDEEP
mysqlajaxperlselect
dcparham
dcparham

Reputation: 291

Perl - mysql select * returns only first field from db

using $.ajax type=post, i call a .pl file that does a mysql select which should return a whole row of data; instead only the first field is output via the $.ajax post. code for index.html:

window.onload = function() {
$.ajax({
url: 'http://server.name:1234/DASH/cgi-bin/getapplicationinfo.pl',
data:  ({ ajaxAction: 'getDBInfo'}),
dataType: 'html',
type: 'POST',
//async: false,
success: function(DB_INFO) {
    a = DB_INFO;
    obj = JSON.parse(a);
    pr_url = obj.DB_INFO;
    alert(pr_url); //i want this to contain all elements separated by comma.

    document.getElementById('divMessage').innerHTML=pr_url[0];
    document.getElementById('pNewOrders').innerHTML=pr_url[1];
    document.getElementById('pBounceRate').innerHTML=pr_url[2];     

},
    error: function(request, status, err) {
        document.getElementById("divMessage").innerHTML="FAILED!";
            }
    });
}

ok, that ajax post calls getapplicationinfo.pl:

    my $q = CGI::new();
    #
#print "!--->".$q->param('ajaxAction')."<br />";
if ( $q->param('ajaxAction') eq "getDBInfo" ){
        #print('ajaxAction = getDBInfo<br />');
    my $return = {};

    #grab info from db=tool_environments.
    my $dbConn = DBI->connect("DBI:mysql:database=tool_db;host=chrdbud01.gcsc.att.com;port=3306",'id','pw',{'RaiseError' => 1}) || return(0);
    my $getDBInfo = $dbConn->prepare("select pr_url from db_connections where application = 'BERZ';");

    #grab info from db=BERZ.
    my $dbConn2 = DBI->connect("DBI:mysql:database=BERZ;host=server.name;port=1234",'id','pw',{'RaiseError' => 1}) || return(0);
    my $getDBInfo2 = $dbConn2->prepare("select companyName from bmt_tickets where ticketNumber = '346404427';");

    #grab info from db=teams.
    my $dbConn3 = DBI->connect("DBI:mysql:database=teams;host=chrdbud01.gcsc.att.com;port=3306",'id','pw',{'RaiseError' => 1}) || return(0);
    my $getDBInfo3 = $dbConn3->prepare("SELECT uid, manageruid FROM employees WHERE firstname='David' AND lastname='Parkam';");

    my $msgInfo;    
    $getDBInfo->execute or die "SQL Error: $DBI::errstr\n";     #info from tool_environments
    $getDBInfo2->execute or die "SQL Error: $DBI::errstr\n";    #info from BERT
    $getDBInfo3->execute or die "SQL Error: $DBI::errstr\n";    #info from C3
    if ($@) { warn "ERROR: $@";}

    my @infoArr;
        while (my ($msgInfo) = $getDBInfo->fetchrow_array()) {
        push(@infoArr, $msgInfo);
        }

        while (my ($msgInfo) = $getDBInfo2->fetchrow_array()) {
        push(@infoArr, $msgInfo);
        print "@row\n";
        }

        while (my ($msgInfo) = $getDBInfo3->fetchrow_array()) {
        push(@infoArr, $msgInfo);
        print "@row\n";
        }

    $return->{DB_INFO} = \@infoArr;
    my $json = to_json($return);
    #print $q->header('text/html');
    print $json;

    exit(0);
 }

as you can see, we loop through each fetchrow_array created by the "$getDBInfo->execute or die". from index.html. note also that in index.html, i alert the array, then access each element one at a time. but, why isn't the array pushed with multiple fields from the select statement? [namely the my $dbConn3 query, which definitely should return 2 fields].

any help would be greatly appreciated!

Upvotes: 0

Views: 177

Answers (2)

sshaw
sshaw

Reputation: 1014

This line:

  while (my ($msgInfo) = $getDBInfo->fetchrow_array()) {

is returning an array but you're only capturing the first element. Here are some examples:

~/ruby/rails41-test >perl
use Data::Dump 'dd';

my $x = (1,2,3);
dd $x;

my ($y) = (1,2,3);
dd $y;

my @z = (1,2,3);
dd @z;
^D
3
1
(1, 2, 3)

See perldata for more info.

Upvotes: 0

Randal Schwartz
Randal Schwartz

Reputation: 44091

my ($msgInfo) = $getDBInfo3->fetchrow_array();

will take the first element of the returned list, and put it into $msginfo. If you want a list of values, use something more like:

my @msg = $getDBInfo3->fetchrow_array();

Upvotes: 1

Related Questions