perl DBI recipes column array

Question

Below is my sql query output

Company Col1    Col2    Col3
Comp1   1       2       3
Comp2   4       5       6
Comp3   7       8       9

Perl procedure to connect and retrive results

my $query1= qq(select * from database_table);
my $result1 = $dbh->selectall_arrayref($query1, {Slice => {}}); 

  my %result1 =
        map { shift @$_, [ @$_ ]}
        @{$dbh->selectall_arrayref($query1)};
    my @json_output = map { encode_json( { 'name' => $_, 'data'=> $result1{$_} } )
                  } sort keys %result1 ;
    print Dumper %result1;

    [{"name":"Comp1","data":[1,2,3]}, {"name":"Comp2","data":[4,5,6]}, {"name":"Comp3","data":[7,8,9]}]

I have been reading http://www.perlmonks.org/?node_id=284436#fetching but i could not figure out how to store columns values as array elements. (As shown below)

    [{"name":"Col1","data":[1,4,7]}, {"name":"col2","data":[2,5,8]}, {"name":"col3","data":[3,6,9]}]

Also, the value fields by default come as "string" in json, any recommendation on how to convert them as numbers?

Please correct syntax error in code.`my $query1 qq(select * from database_table);` should be `my $query1=qq(select * from database_table);`. — , May 21 '12 at 06:19
I am trying to convert the mysql returned string as integers, from your suggestion above i dont understand how to access data elements individually to multiply with 1. i tried $result1{shift @col_names}=1*\@res; but the output doesnt make any sense. — Linus, May 22 '12 at 06:11
`\@res` is a reference,if you want to multiply you will have to work on values of `@res`. — , May 22 '12 at 06:35
Edited answer with new code `@res=map {$_ * 1} @res;` , please check. — , May 22 '12 at 06:50

score 1 · Accepted Answer · 2012-05-22T06:49:29.200

Here's how it would be

use strict;
use warnings;

use DBI;
use DBD::mysql;    
use JSON;    
use Data::Dumper;

my $dbh = DBI->connect('DBI:mysql:dbnew:localhost');    
my $query1=qq(select * from database_table);    
my $sth=$dbh->prepare($query1);    
$sth->execute;

my @col_names=@{$sth->{NAME}};

my %result1;    

for(my $i=0;$i<3;$i++)
{
my @res = map { $_->[$i]} @{$dbh->selectall_arrayref($query1)};

@res=map {$_ * 1} @res;

$result1{shift @col_names}=\@res;
}
my @json_output = map {encode_json( { 'name' => $_ , 'data'=> $result1{$_} } )}  sort keys %result1;

print @json_output;

score 1 · Answer 2 · edited Jun 17 '13 at 19:24

Assuming I have the table as you showed but called "fm".

use DBI;
use strict;
use warnings;
use Data::Dumper;
use JSON::XS;

my $h = DBI->connect('dbi:ODBC:xxx', 'xxx', 'xxx');
my $r = $h->selectall_arrayref(q/select company, col1, col2, col3 from fm/);
print Dumper($r);
my @to_encode;
foreach my $row (@$r) {
    my $hash;
    $hash->{name} = shift @$row;
    $hash->{data} = $row;
    push @to_encode, $hash;
}
my $js = encode_json(\@to_encode);
print Dumper($js);

outputs:

$VAR1 = [
          [
            'comp1',
            '1',
            '2',
            '3'
          ],
          [
            'comp2',
            '4',
            '5',
            '6'
          ],
          [
            'comp3',
            '7',
            '8',
            '9'
          ]
        ];
$VAR1 = '[{"name":"comp1","data":["1","2","3"]},{"name":"comp2","data":["4","5","6"]},{"name":"comp3","data":["7","8","9"]}]';

EDIT: re-read your example and I think this is really what you want:

use DBI;
use strict;
use warnings;
use Data::Dumper;
use JSON::XS;

my $h = DBI->connect('dbi:ODBC:baugi', 'sa', 'easysoft');
my $s = $h->prepare(q/select col1, col2, col3 from fm/);
$s->execute;
my $cols = $s->{NAME};

my @data;
for (my $n = 0; $n < scalar(@$cols); $n++)  {
    push @data, {name => $cols->[$n], data => []};
}
while (my @row = $s->fetchrow) {
    for (my $n = 0; $n < scalar(@$cols); $n++) {
        push @{$data[$n]->{data}}, shift @row;
    }
}
my $js = encode_json(\@data);
print Dumper($js);

$VAR1 = '[{"name":"col1","data":["1","4","7"]},{"name":"col2","data":["2","5","8"]},{"name":"col3","data":["3","6","9"]}]';

There are probably much more elegant ways of doing it and simplifying the Perl work with better SQL but it is early and I've not had my first coffee yet.

As you point out the numbers look like strings in the encoded JSON. That is because your JSON module (well JSON::XS anyway) uses something like sv_POK on the scalars to try and guess whether they are numbers or strings and most DBD modules bind all columns as strings and set the returned scalar with sv_setpv. It is annoying but you will need to add 0 to each number before calling encode_json OR:

By sheer chance I just happen to be changing DBD::ODBC so it will bind integers as integers - see Major changes to column binding in Perl DBD::ODBC

With DBD::Oracle you can bind the columns as SQL_INTEGER add the DiscardString attribute e.g.,

$s->prepare(q/select company,col1,col2,col3 from mytable);
$s->execute;
$s->bind_col(2, undef, {TYPE => SQL_INTEGER, DiscardString => 1});
# repeat for col2 and col3
$r = $s->fetchall_arrayref

I believe some other DBDs already bind integers as integers - may be DBD::Pg.

I am using mysql and {TYPE => SQL_INTEGER, DiscardString => 1} did not help the output to be integers. http://search.cpan.org/~makamaka/JSON-2.53/lib/JSON.pm#PERL_-%3E_JSON suggested what you said above. Any suggestions on how to numify @row array into integers? — Linus, May 22 '12 at 02:58
You could just change the shift in my example to + 0 to it "push @{$data[$n]->{data}}, 0+shift @row;". A longer term solution which would avoid this is to persuade the DBD::mysql guys to bind integers as integers and use sv_setiv instead of sv_setpv in the XS or to implement sql_type_cast (although the latter still needs you to specify a type when binding). — bohica, May 22 '12 at 07:16

perl DBI recipes column array

2 Answers2