CODEWITHSUNDEEP
mysqlsqldatabaseperl
JB670
JB670

Reputation: 7

Inserting into a MySQL table with an auto-increment column

I'm trying to insert data into my database with a Perl script, but there's something wrong with my code.

I previously created a table annotations with eight columns, and the first one is an auto incremented primary key. I also need the two last columns to be filled with 1 and DB on each row.

The errors I get when I launch my program in the prompt are :

DBD::mysql::st execute failed: Incorrect integer value: 'NULL' for column 'id_anntion' at row 1 at C:\Users\Jean Baptiste\cours\Base de donnÚes\Dossier\programmes\remplissage_table_projet.pl line 25, line 1.
Erreur insertion : Incorrect integer value: 'NULL' for column 'id_anntion' at row 1 at C:\Users\Jean Baptiste\cours\Base de donnÚes\Dossier\programmes\remplissage_table_projet.pl line 25, line 1.

And this is the code :

use DBI;
use strict;
use utf8;

use warnings;

my ($ligne, @tab);

# Connexion à la base de données
my $dbh = DBI->connect( "DBI:mysql:database=projet;host=localhost", "root", "" )
  or die "Erreur de connexion : " . DBI->errstr();


# Gestion de l'encodage UTF-8
$dbh->{'mysql_enable_utf8'} = 1;
$dbh->do('set names utf8');

# Préparation d'une requête pour l'insertion de valeurs dans la BDD
my $ins = $dbh->prepare("INSERT INTO annotations VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
  or die "Probleme preparation : " . $dbh->errstr();

open( FILEIN, '<:encoding(utf8)', 'alsace_DB.csv' );
while ( $ligne = <FILEIN> ) {
    chomp($ligne);
    @tab = split( /;/, $ligne );
    $ins->execute("NULL", $tab[0], $tab[1], $tab[2], $tab[3], $tab[4], "1", "DB")
      or die "Erreur insertion : " . $ins->errstr();
}
close(FILEIN);

# Déconnexion de la base de données
$dbh->disconnect();

I know there's something to do with the "NULL" part but I can't tell what. And I think it's not the only problem.

Upvotes: 0

Views: 1561

Answers (2)

Kamal Nayan
Kamal Nayan

Reputation: 1940

Use undef in perl for inserting default values in MySQL table. So, here you need to modify your line as:
$ins->execute(undef, $tab[0], $tab[1], $tab[2], $tab[3], $tab[4], "1", "DB") or die "Erreur insertion : " . $ins->errstr();

In this case, it will auto-increment the column.

Upvotes: 1

Borodin
Borodin

Reputation: 126722

You are trying to insert the string "NULL" into an integer column

If you want to override the autoincrement behaviour then use a number such as 100 here

If you want MySQL to generate the numbers, then use zero 0 or, if the column is NOT NULL then you can use undef

Upvotes: 3

Related Questions