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?

Answer 1

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;

Answer 2

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.