How to insert below data structure in data base?
#!/usr/bin/perl
>
>> use strict;
>> use warnings;
>>
>> my %data = (
>>
>> south => {
>> status => {
>> open => { count => 3 },
>> pws => { count => 3 },
>> wip => { count => 0 },
>> hold => { count => 1 },
>> 're-open' => { count => 0 },
>> pwu => { count => 0 },
>> openesc => { count => 0 },
>> },
>> total_count => 7,
>> },
>>
>> north => {
>> status => {
>> open => { count => 3 },
>> pws => { count => 0 },
>> wip => { count => 0 },
>> hold => { count => 7 },
>> 're-open' => { count => 0 },
>> pwu => { count => 0 },
>> openesc => { count => 0 },
>> },
>> total_count => 10,
>> },
>> );
>>
>
I am using below code , but I think some thing going wrong here.
my @dbdata=();
for my $region ( sort keys %region_data )
{
my @mydata;
my $d = $region_data{ $region };
@mydata= join( ",", $region,map( $d->{ status }{ $_ }{ count },
@statuses_string ),$d->{ total_count },);
push(@dbdata,@mydata);
}
my $connect = DBI->connect($dsn, $username, $password);
my $query = "INSERT INTO location_wise(region,open,pws,wip
,hold,reopen,pwu,openesc,total) VALUES (?,?,?,?,?,?,?,?,?)";
my $query_handle = $connect->prepare($query);
for my $datum (@dbdata) {
$query_handle->execute(@$datum);
}
DBD::mysql::st execute failed: Column 'open' cannot be null at
./demo.plline 104, <CSV> line 30.
DBD::mysql::st execute failed: Column 'open' cannot be null at
./demo.plline 104, <CSV> line 30.
DBD::mysql::st execute failed: Column 'open' cannot be null at
./demo.plline 104, <CSV> line 30.
DBD::mysql::st execute failed: Column 'open' cannot be null at
./demo.plline 104, <CSV> line 30.
I am hanging on this issue. Any help will be really appreciated.
> @dbdata=(
> ["north" ,'3', '0' ,'0', '7' ,'0' ,'0',' 0',' 10'],
> ["south", '3',' 3' ,'0' ,'1', '0',' 0' ,'0', '7' ],
> )
> my $connect = DBI->connect($dsn, $username, $password);
> my $query = "INSERT INTO by_region (region,open,pws,wip
> ,hold,reopen,pwu,openesc,total) VALUES (?,?,?,?,?,?,?,?,?)";
>
> my $query_handle = $connect->prepare($query);
> for my $datum (@dbdata)
> {
> $query_handle->execute(@$datum);
> }
>
>
