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?