MariaDB problem inserting NULL date

[Stefanoxjx]

Hi, I've a problem inserting NULL date in a table.
This is the query generated from Query.prepare and Query.BindValue:

INSERT INTO Assunzioni (Dipendenti_id, Livelli_id, Scadenze_id, data_assunzione, data_dimissione) VALUES(2, 1, 1, 20201114, NULL)

it give me error:

QSqlError("1292", "QMYSQL3: Unable to execute statement", "Incorrect date value: 'NULL' for column `db`.`assunzioni`.`data_dimissione` at row 1")

But same query, executed in HeidiSQL, works fine.
If in Qt I replace NULL with a date I haven't problems.

This is the definition of table:

CREATE TABLE `Assunzioni` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Dipendenti_id` INT NOT NULL,
  `Livelli_id` INT NOT NULL,
  `Scadenze_id` INT NOT NULL,
  `data_assunzione` DATE NOT NULL,
  `data_dimissione` DATE NULL,
  PRIMARY KEY (`id`),
  KEY(`Dipendenti_id`))
ENGINE = InnoDB;

In many case, I need to leave empty data_dimissione.
How I can do it?

Thanks.

Stefano

[SGaist]

Hi,

Did you try to just not mention that filed at all in your insert query ?

[Stefanoxjx]

Hi SGaist, if I comment this line: Query.bindValue(":data_dimissione", Dimissione); I haven't errors.
I this the correct way?

[SGaist]

I would have tested with the equivalent of:

INSERT INTO Assunzioni (Dipendenti_id, Livelli_id, Scadenze_id, data_assunzione) VALUES(2, 1, 1, 20201114)

So no mention of data_dimissione in the query at all.

[Stefanoxjx]

Ah ok!
Without data_dimissione works fine.

[Christian Ehrlicher]

@Stefanoxjx said in MariaDB problem inserting NULL date:

Query.bindValue(":data_dimissione", Dimissione);

What's the value of Dimissione in this case?

[Stefanoxjx]

@Christian-Ehrlicher said in MariaDB problem inserting NULL date:

@Stefanoxjx said in MariaDB problem inserting NULL date:

Query.bindValue(":data_dimissione", Dimissione);

What's the value of Dimissione in this case?

If Dimissione is (for example) 20201115 all works fine.
If Dimissione is NULL I've an error.

[Christian Ehrlicher]

@Stefanoxjx said in MariaDB problem inserting NULL date:

If Dimissione is NULL I've an error.

Since it's a QVariant, it can't be 'NULL' - only QVariant::isNull() or isValid() can return true. Do you mean than QVariant::isNull() returned true?

[JonB]

@Christian-Ehrlicher
Doubtless you are right, and this will prove the nub of the issue, but the OP said originally:

INSERT INTO Assunzioni (Dipendenti_id, Livelli_id, Scadenze_id, data_assunzione, data_dimissione) VALUES(2, 1, 1, 20201114, NULL)

and

Incorrect date value: 'NULL' for column db.assunzioni.data_dimissione at row 1

but you'd think that NULL at the end implied that the statement had been generated as desired?

[Christian Ehrlicher]

I'm not sure if the prepared statement quotes strings so it can still be NULL as string, therefore my question. And if it's really QVariant::isNull() then a simple reproducable example would be nice.

[Stefanoxjx]

Reading your reply, I tought that my error was to have defined Dimissione as QString.
Then, I've tried this solution:

if(...condition...) QVariant Dimissione = QVariant();
qDebug() << "QVariant::IsNull " << Dimissione.isNull();
...
Query.bindValue(":data_dimissione", Dimissione);
...

So, QVarinat::IsNull return true, and I haven't errors in Query.exec()

I learned a new thing :)

Thanks for your invaluable help.

[Christian Ehrlicher]

@Stefanoxjx Nice to hear, can you please mark this topic as solved then?