no error after exec() on INSERT QSqlQuery but .LastInsertId() is empty

[polrus]

how is it possible that i get no erros after executing INSERT query but still nothing is inserted?
btw it works if I connect to another server

following simple code


query.prepare("INSERT INTO Ruchy (z,do,twr_id,ilosc,opis,czas,dozor_prc_id) VALUES ('"+ui->LE_ruchy_z_id->text()+"','"+ui->LE_ruchy_do_id->text()+"','"+ui->LE_ruchy_twr_id->text()+"','"+QString::number(ui->SB_ruchy_ilosc->value())+"','"+ui->PTE_ruchy_opis->toPlainText()+"',now(),'"+QString::number(::prc_id)+"')");
query.exec();
qDebug()<<"ruchy querry : "<<query.lastQuery();
qDebug()<<"Ruchy insert : "<<query.lastError();
qDebug()<<"ruchy last id: "<<query.lastInsertId().toString();

gives following debug


ruchy querry : "INSERT INTO Ruchy (z,do,twr_id,ilosc,opis,czas,dozor_prc_id) VALUES ('-2','1','210','1','test2',now(),'1')"
Ruchy insert : QSqlError(-1, "", "")
ruchy last id: ""

[Lesiok]

What database ? Not each and not always have this future.

[ChrisW67]

Your table also has to have a suitably defined primary key that you are not inserting a value into (so the database does it for you). We cannot see the definition of your table.

You absolutely should use bindValue() rather than build queries by pasting strings.

[polrus]

First of all thx for Your help.

The database is MariaDB 5.5 on opensuse 13.1

I tried Your suggestion ChrisW67 and I converted it to bindValue but it does not help


query.prepare("INSERT INTO Ruchy (z,do,twr_id,ilosc,opis,czas,dozor_prc_id) VALUES (:z,:do,:twr_id,:ilosc,:opis,now(),:dozor_prc_id)");
query.bindValue(":z",ui->LE_ruchy_z_id->text());
query.bindValue(":do",ui->LE_ruchy_do_id->text());
query.bindValue(":twr_id",ui->LE_ruchy_twr_id->text());
query.bindValue(":ilosc",QString::number(ui->SB_ruchy_ilosc->value()));
query.bindValue(":opis",ui->PTE_ruchy_opis->toPlainText());
query.bindValue(":dozor_prc_id",QString::number(::prc_id));

query.exec();

the table deffinition is as follows


CREATE TABLE `Ruchy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`z` int(11) DEFAULT NULL,
`do` int(11) DEFAULT NULL,
`twr_id` int(11) DEFAULT NULL,
`ilosc` int(11) DEFAULT NULL,
`opis` varchar(450) COLLATE utf8_unicode_ci DEFAULT NULL,
`czas` datetime DEFAULT NULL,
`dozor_prc_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


the strange think is that that the LastInsertId() works on other table sreturning the ID but not here

If I export qDebug()<< query.lastQuery() as a text to MYSQL workbench then the row is inserted without any problems - this is strange

I will attache the whole function that contains this insert bellow so You'll be able to track it maybe


void MainWindow::on_PB_ruchy_zapisz_clicked()
{
if (ui->LE_ruchy_do_id->text().isEmpty() or ui->LE_ruchy_z_id->text().isEmpty() or ui->LE_ruchy_twr_id->text().isEmpty())
{
QMessageBox msgBox;
msgBox.setText("Musisz wybrac magazyn zrodlowy, docelowy, oraz towar");
msgBox.exec();
return;
}

QSqlQuery query;

query.prepare("LOCK TABLE inicio.Stany WRITE");query.exec();



query.prepare("SELECT id,stan from Stany where twr_id='"+ui->LE_ruchy_twr_id->text()+"' and magazyn_id='"+ui->LE_ruchy_z_id->text()+"'" );
query.exec();
if (query.next())
{
int stan=query.value(1).toInt();
if (stan>=ui->SB_ruchy_ilosc->value())
{
query.prepare("UPDATE Stany set stan=stan-"+QString::number(ui->SB_ruchy_ilosc->value())+" where id='"+query.value(0).toString()+"' ");
query.exec();
}
else
{
if (ui->LE_ruchy_z_id->text()!="-2")
{
QMessageBox msgBox;
msgBox.setText("Nie mozna wydac wiecej niz jest na stanie \n \n Na stanie: "+QString::number(stan)+" szt.");
msgBox.exec();
return;
}
}


}
else
{
if (ui->LE_ruchy_z_id->text()=="-2")
{ //mozna wydac z zerowego stanu tylko przy zakupach - zakupy maja prc_id=-2
query.prepare("INSERT INTO Stany (magazyn_id,twr_id,stan) VALUES ('"+ui->LE_ruchy_z_id->text()+"','"+ui->LE_ruchy_twr_id->text()+"','"+QString::number(0-ui->SB_ruchy_ilosc->value())+"')");
query.exec();
}
else
{//jesli ktos nie ma to nie moze wydac
QMessageBox msgBox;
msgBox.setText("Nie ma tego towaru w magazynie zrodlowym wiec nie moze on go wydac");
msgBox.exec();
return;
}

}

query.prepare("INSERT INTO Ruchy (z,do,twr_id,ilosc,opis,czas,dozor_prc_id) VALUES (:z,:do,:twr_id,:ilosc,:opis,now(),:dozor_prc_id)");
query.bindValue(":z",ui->LE_ruchy_z_id->text());
query.bindValue(":do",ui->LE_ruchy_do_id->text());
query.bindValue(":twr_id",ui->LE_ruchy_twr_id->text());
query.bindValue(":ilosc",QString::number(ui->SB_ruchy_ilosc->value()));
query.bindValue(":opis",ui->PTE_ruchy_opis->toPlainText());
query.bindValue(":dozor_prc_id",QString::number(::prc_id));

query.exec();

qDebug()<<"ruchy querry : "<<query.lastQuery();
qDebug()<<"Ruchy insert : "<<query.lastError();
qDebug()<<"ruchy last id: "<<query.lastInsertId().toString();

query.prepare("SELECT id from Stany where twr_id='"+ui->LE_ruchy_twr_id->text()+"' and magazyn_id='"+ui->LE_ruchy_do_id->text()+"'" );
query.exec();
if (query.next())
{
query.prepare("UPDATE Stany set stan=stan+"+QString::number(ui->SB_ruchy_ilosc->value())+" where id='"+query.value(0).toString()+"' ");
query.exec();
qDebug()<<"aktualizuj stan bo byl :"+query.lastQuery();

}
else
{
query.prepare("INSERT INTO Stany (magazyn_id,twr_id,stan) VALUES ('"+ui->LE_ruchy_do_id->text()+"','"+ui->LE_ruchy_twr_id->text()+"','"+QString::number(ui->SB_ruchy_ilosc->value())+"')");
query.exec();
qDebug()<<"dodaj stan bo nie bylo "+query.lastQuery();
qDebug()<<"stany insert : "<<query.lastError();
qDebug()<<"stany last id: "<<query.lastInsertId().toString();



}
query.prepare("UNLOCK TABLE inicio.Stany ");query.exec();
qDebug()<<"unlock table : "<<query.lastError();

query.prepare("UNLOCK TABLES");query.exec();
qDebug()<<"unlock tables : "<<query.lastError();


qDebug()<<"commit : "<<query.lastError();

[polrus]

this was a strange issue with table locks -
really strange because it does not happen on another sever beeing almost a mirror of that one - the same DB the same OS.

however very interesting think that may help someone -
the query variable didn't return any errors after line #65 Exec but when I introduced a new variable
QSqlQuery insert_query;

and replaced query with insert_query in lines from 57 to 69 i finally started getting non-empty insert_query.lastError() - and this helped me to track the lock issues

why is that?

I used the same qsqlquery many many times with different sql statement one after another and never had any problems up to now.