Search code examples
perlmariadb

How to insert new row Mariadb perl

My host moved to MariaDB from mySQL. This is a Perl CGI.pm script. If I use use DBI; and use DBD::MariaDB;, most everything translates well to the new database. But when I try to insert a new row it wants data for each column. I get:

Software error: DBD::MariaDB::st execute failed: Incorrect integer value: '' for column `mydb`.`mytable`.`mycolumn` at row 1 at myscript.cgi line 18302.

my $dbh = DBI->connect("DBI:MariaDB:database=$database;host=localhost", $MYSQLuserid, $MYSQLpassword, { RaiseError => 1, PrintError => 0 });
my $sth = $dbh->prepare("INSERT INTO mytable ($placeholders) values ($questionmarks)");
$sth->execute(@new_row) or die "Failed to execute statement: " . $sth->errstr;
$new_order_number = $sth->{LAST_INSERT_ID};
$sth->finish() or die $DBI::errstr;
$dbh->disconnect()  or die $DBI::errstr;

I've tried modifying NULL to: NOT NULL, NULL/none/as defined and NULL. This was never an issue with mysql and I need to be able to leave columns empty for future use. Tried many variations on the code. Thanks for your ideas.

Solution

  • The issue you're encountering is related to MariaDB and MySQL handless null and empty strings in columns that have not null. I would check that the columns in mytable that are giving you trouble are correctly defined. In MariaDB, if you're trying to insert an empty string into it, you will be thrown an error, especially for integer columns.

    You'll meed to adjust your Perl script to handle passing an empty string. You can either set the value to undef (which in SQL corresponds to NULL) or ensure that the columns can accept empty strings.

    use DBI;
use DBD::MariaDB;

my $dbh = DBI->connect("DBI:MariaDB:database=$database;host=localhost", $MYSQLuserid, $MYSQLpassword, { RaiseError => 1, PrintError => 0 });

# Ensure that any empty strings are converted to undef if the column should allow NULLs
my @new_row = map { $_ eq '' ? undef : $_ } @new_row;

my $sth = $dbh->prepare("INSERT INTO mytable ($placeholders) VALUES ($questionmarks)");
$sth->execute(@new_row) or die "Failed to execute statement: " . $sth->errstr;
$new_order_number = $sth->{mysql_insertid};  # Note: Use mysql_insertid to get the last insert id
$sth->finish() or die $DBI::errstr;
$dbh->disconnect() or die $DBI::errstr;

    If a column should have a default value or accept NULL, make sure it's defined that way in the database schema. If the column is NOT NULL and there is no default, you must provide a value during the insert.