ThorTL67 ThorTL67 - 1 month ago 14
MySQL Question

Inserting into MySQL database using Perl column cannot be null error

long time reader & first question!

I'm currently following a Perl5 course in which I am scraping an XML file for band names, albums & their retrospective chart positions. I now need to pop these into a database. I am using MySQL & have used Workbench to set up the database.

I am getting the following when running parse.pl :


Connected to database.

Inserting The Basspluckers into database..

Revenge of the Squirrels, 434

$VAR1 = undef;

DBD::mysql::st execute failed: Column 'band_id' cannot be null at parse.pl line 84.

Unable to execute albums insert. at parse.pl line 85.


Please see the following in parse.pl (Removed credentials for db) :

use strict;
use warnings;
use Data::Dumper;
use Getopt::Std;
use 5.18.2;
use XML::Simple;
use DBI;
# Connect to database
$| = 1;

sub main {
my %opts;

# Get command line options
getopts('d:r', \%opts);

if(!checkusage(\%opts)) {
usage();
exit();
}

my $input_dir = $opts{"d"};

my @files = get_files($input_dir);

my @data = process_files(\@files, $input_dir);

add_to_database(\@data);

}

sub add_to_database {
my $data = shift;

my $dbh = DBI->connect("dbi:mysql:bands", "", "");

unless (defined($dbh)) {
die "Cannot connect to database\n";
}

say "Connected to database.";

my $sth_bands = $dbh->prepare('insert into bands (name) values (?)');
my $sth_albums = $dbh->prepare('insert into albums (name, position, band_id) values (?, ?, ?)');
# sth statement handle dbh database handle
# values (?) ? is a placement holder, provides some protection from SQLi
unless ($sth_bands) {
die "Error preparing band insert SQL\n";
}

unless ($sth_albums) {
die "Error preparing album insert SQL\n";
}


#Clean the database
$dbh->do('delete from bands') or die "Can't clean bands table\n";
$dbh->do('delete from albums') or die "Can't clean albums table\n";

for my $data(@$data) {
my $band_name = $data->{"name"};
my $albums = $data->{"albums"};

say "Inserting $band_name into database..";
unless($sth_bands->execute($band_name)) {
die "Error executing SQL\n";
}

my $band_id = $sth_bands->{'mysql_last_insert_id'};

for my $album(@$albums) {
my $album_name = $album->{"name"};
my $album_position = $album->{"position"};

say "$album_name, $album_position";
print Dumper($band_id);
unless ($sth_albums->execute($album_name, $album_position, $band_id)) {
die "Unable to execute albums insert."
}

}

}

$sth_bands->finish;
$sth_albums->finish;

$dbh->disconnect();
say "Disconnected from database.";
}


I have two tables: Bands and Albums.

Within Bands I have:

id INT - set with PK, NN & AI. (Primary Key, Not Null, Auto Inc)
name VARCHAR(45) - set with NN.


Within Albums I have:

id INT - PK,NN,AI
name VARCHAR(45) - NN
position INT - NN
band_id INT - NN


I have set up a one to many association whereby 1 Band.id can have many Albums. This is shown in the Album table where it says

References: (band_id) TO Bands.(id)

I understand that the error is happening because the band_id is null(undef) & I have set the database to reject null values, but I cannot understand why band_id isn't taking the value from Bands(id).

Thanks for any help in advance.

Answer

The error message seems pretty clear. You're trying to insert a null value into a "not null" column in the database. "Null" in SQL-speak translates to "undef" in Perl-speak, so the problem is that your $band_id is undefined when you try to execute the SQL to insert the album. And you can see that's true as you have inserted the line:

print Dumper($band_id);

which is producing this output:

 $VAR1 = undef;

So why is this value undefined at this point. Here's where you try to give it a value:

my $band_id = $sth_bands->{'mysql_last_insert_id'};

I hadn't seen that 'mysql_last_insert_id' syntax before, so I looked in the documentation for DBD::mysql. But I couldn't find it mentioned anywhere. However, I did find this:

mysql_insertid

If the statement you executed performs an INSERT, and there is an AUTO_INCREMENT column in the table you inserted in, this attribute holds the value stored into the AUTO_INCREMENT column, if that value is automatically generated, by storing NULL or 0 or was specified as an explicit value.

Typically, you'd access the value via $sth->{mysql_insertid}. The value can also be accessed via $dbh->{mysql_insertid} but this can easily produce incorrect results in case one database handle is shared.

So either you've mistyped the attribute name, or the Perl 5 course that you're following has got it wrong. Changing your code to:

my $band_id = $sth_bands->{'mysql_insertid'};

should fix the problem.

It's always worth checking the documentation!

Comments