> > east,0,0,2,4,0,0,0,6
> > north,0,0,7,3,0,0,0,10
> > south,3,0,1,3,0,0,0,7
> > west,7,0,0,0,0,0,0,7
>
> If that is really the output from the print statement shown above, then you
> don't have a rows of column data in your @dbdata array elements, you have
> an
> array of scalar string values that are the concatenated values of the
> desired column values joined with commas.
>
Thanks . Your are correct .
>
> Use the Data::Dumper module to see what is really contained within your
> data
> array:
>
> use Data::Dumper;
> ...
> print Dumper(\@dbdata);
>
-----Output ---
$VAR1 = [
'east,0,0,2,4,0,0,0,6',
'north,0,0,7,3,0,0,0,10',
'south,3,0,1,3,0,0,0,7',
'west,7,0,0,0,0,0,0,7'
];
That assumes that the elements of @dbdata are references to arrays.
> Printing
> the array using the Data::Dumper module will tell you if that is so.
>
> If the elements are really 'east,0,0,2,4,0,0,0,6' and the like, then you
> are
> probably trying to store that string into the first column and nothing into
> the subsequent columns. That might explain why you are getting "column
> cannot be null" error messages.
>
> You could split the scalar into columns:
>
> my @row = split(/,/,$dbdata);
>
> but you should probably go back and figure out why @dbdata contains the
> data
> that it does.
>
> If you want more help, please post a short, complete program that
> demonstrates the problem you are having, and one that other people can
> execute on their own systems.
>
> Thanks.
>
>
I have structure below which I want to insert into MySQL Data base.
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 to insert database :
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);
}
It shows below error :
DBD::mysql::st execute failed: Column 'open' cannot be null at /demo.plline
104, <CSV> line 30.
>From Jim Gibson :
print Dumper(\@dbdata);
$VAR1 = [
'east,0,0,2,4,0,0,0,6',
'north,0,0,7,3,0,0,0,10',
'south,3,0,1,3,0,0,0,7',
'west,7,0,0,0,0,0,0,7'
];
It trying to store that string into the first column and nothing into the
subsequent columns. That is why it shows "column cannot be null" error
messages. Thanks Jim Gibson!!!.
Jim Gibson, I understand the problem, but I not sure where in the below code
I am making mistake .
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);
}
Thanks & Rg
Mohan L
