Results 1 to 5 of 5

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

  1. 30th December 2013, 13:09 #1
    polrus
    polrus is offline Beginner
    Join Date
    Jul 2008
    Location
    EU , Poland, Zabrze
    Posts
    13
    Thanks
    1
    Qt products
    Qt4
    Platforms
    Unix/X11

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

    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

    Qt Code: 
    1. 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)+"')");
    2.     query.exec();
    3.     qDebug()<<"ruchy querry : "<<query.lastQuery();
    4.     qDebug()<<"Ruchy insert  : "<<query.lastError();
    5.     qDebug()<<"ruchy last id: "<<query.lastInsertId().toString();
    To copy to clipboard, switch view to plain text mode 

    gives following debug

    Qt Code: 
    1. ruchy querry :  "INSERT INTO Ruchy (z,do,twr_id,ilosc,opis,czas,dozor_prc_id) VALUES ('-2','1','210','1','test2',now(),'1')" 
    2. Ruchy insert  :  QSqlError(-1, "", "") 
    3. ruchy last id:  ""
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  2. 30th December 2013, 16:43 #2
    Lesiok
    Lesiok is offline Expert
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,540
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    What database ? Not each and not always have this future.
    Reply With Quote Reply With Quote
  3. 30th December 2013, 20:27 #3
    ChrisW67
    ChrisW67 is offline Guru
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

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

    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.
    Reply With Quote Reply With Quote
  4. 2nd January 2014, 10:41 #4
    polrus
    polrus is offline Beginner
    Join Date
    Jul 2008
    Location
    EU , Poland, Zabrze
    Posts
    13
    Thanks
    1
    Qt products
    Qt4
    Platforms
    Unix/X11

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

    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

    Qt Code: 
    1. 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)");
    2.     query.bindValue(":z",ui->LE_ruchy_z_id->text());
    3.     query.bindValue(":do",ui->LE_ruchy_do_id->text());
    4.     query.bindValue(":twr_id",ui->LE_ruchy_twr_id->text());
    5.     query.bindValue(":ilosc",QString::number(ui->SB_ruchy_ilosc->value()));
    6.     query.bindValue(":opis",ui->PTE_ruchy_opis->toPlainText());
    7.     query.bindValue(":dozor_prc_id",QString::number(::prc_id));
    8.  
    9.     query.exec();
    To copy to clipboard, switch view to plain text mode 

    the table deffinition is as follows

    Qt Code: 
    1. CREATE TABLE `Ruchy` (
    2.   `id` int(11) NOT NULL AUTO_INCREMENT,
    3.   `z` int(11) DEFAULT NULL,
    4.   `do` int(11) DEFAULT NULL,
    5.   `twr_id` int(11) DEFAULT NULL,
    6.   `ilosc` int(11) DEFAULT NULL,
    7.   `opis` varchar(450) COLLATE utf8_unicode_ci DEFAULT NULL,
    8.   `czas` datetime DEFAULT NULL,
    9.   `dozor_prc_id` int(11) DEFAULT NULL,
    10.   PRIMARY KEY (`id`)
    11. ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    To copy to clipboard, switch view to plain text mode 


    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

    Qt Code: 
    1. void MainWindow::on_PB_ruchy_zapisz_clicked()
    2. {
    3.     if (ui->LE_ruchy_do_id->text().isEmpty() or ui->LE_ruchy_z_id->text().isEmpty() or ui->LE_ruchy_twr_id->text().isEmpty())
    4.     {
    5.         QMessageBox msgBox;
    6.         msgBox.setText("Musisz wybrac magazyn zrodlowy, docelowy, oraz towar");
    7.         msgBox.exec();
    8.         return;
    9.     }
    10.  
    11.     QSqlQuery query;
    12.  
    13.     query.prepare("LOCK TABLE inicio.Stany WRITE");query.exec();
    14.  
    15.  
    16.  
    17.     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()+"'" );
    18.     query.exec();
    19.     if (query.next())
    20.     {
    21.         int stan=query.value(1).toInt();
    22.         if (stan>=ui->SB_ruchy_ilosc->value())
    23.         {
    24.           query.prepare("UPDATE Stany set stan=stan-"+QString::number(ui->SB_ruchy_ilosc->value())+" where id='"+query.value(0).toString()+"' ");
    25.           query.exec();
    26.         }
    27.         else
    28.         {
    29.             if (ui->LE_ruchy_z_id->text()!="-2")
    30.             {
    31.                 QMessageBox msgBox;
    32.                 msgBox.setText("Nie mozna wydac wiecej niz jest na stanie \n \n Na stanie: "+QString::number(stan)+" szt.");
    33.                 msgBox.exec();
    34.                 return;
    35.             }
    36.         }
    37.  
    38.  
    39.     }
    40.     else
    41.     {
    42.         if (ui->LE_ruchy_z_id->text()=="-2")
    43.         { //mozna wydac z zerowego stanu tylko przy zakupach - zakupy maja prc_id=-2
    44.             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())+"')");
    45.             query.exec();
    46.         }
    47.         else
    48.         {//jesli ktos nie ma to nie moze wydac
    49.             QMessageBox msgBox;
    50.             msgBox.setText("Nie ma tego towaru w magazynie zrodlowym wiec nie moze on go wydac");
    51.             msgBox.exec();
    52.             return;
    53.         }
    54.  
    55.     }
    56.  
    57.     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)");
    58.     query.bindValue(":z",ui->LE_ruchy_z_id->text());
    59.     query.bindValue(":do",ui->LE_ruchy_do_id->text());
    60.     query.bindValue(":twr_id",ui->LE_ruchy_twr_id->text());
    61.     query.bindValue(":ilosc",QString::number(ui->SB_ruchy_ilosc->value()));
    62.     query.bindValue(":opis",ui->PTE_ruchy_opis->toPlainText());
    63.     query.bindValue(":dozor_prc_id",QString::number(::prc_id));
    64.  
    65.     query.exec();
    66.  
    67.     qDebug()<<"ruchy querry : "<<query.lastQuery();
    68.     qDebug()<<"Ruchy insert  : "<<query.lastError();
    69.     qDebug()<<"ruchy last id: "<<query.lastInsertId().toString();
    70.  
    71.     query.prepare("SELECT id from Stany where twr_id='"+ui->LE_ruchy_twr_id->text()+"' and magazyn_id='"+ui->LE_ruchy_do_id->text()+"'" );
    72.     query.exec();
    73.     if (query.next())
    74.     {
    75.         query.prepare("UPDATE Stany set stan=stan+"+QString::number(ui->SB_ruchy_ilosc->value())+" where id='"+query.value(0).toString()+"' ");
    76.         query.exec();
    77.         qDebug()<<"aktualizuj stan bo byl :"+query.lastQuery();
    78.  
    79.     }
    80.     else
    81.     {
    82.         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())+"')");
    83.         query.exec();
    84.         qDebug()<<"dodaj stan bo nie bylo "+query.lastQuery();
    85.         qDebug()<<"stany insert  : "<<query.lastError();
    86.         qDebug()<<"stany last id: "<<query.lastInsertId().toString();
    87.  
    88.  
    89.  
    90.     }
    91.     query.prepare("UNLOCK TABLE inicio.Stany ");query.exec();
    92.     qDebug()<<"unlock table : "<<query.lastError();
    93.  
    94.     query.prepare("UNLOCK TABLES");query.exec();
    95.     qDebug()<<"unlock tables : "<<query.lastError();
    96.  
    97.  
    98.     qDebug()<<"commit : "<<query.lastError();
    To copy to clipboard, switch view to plain text mode 
    Reply With Quote Reply With Quote
  5. 2nd January 2014, 13:09 #5
    polrus
    polrus is offline Beginner
    Join Date
    Jul 2008
    Location
    EU , Poland, Zabrze
    Posts
    13
    Thanks
    1
    Qt products
    Qt4
    Platforms
    Unix/X11

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

    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.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. QSqlQuery Memory issues. QSqlQuery::exec() and QSqlDatabase::open()/close();
    By johnnyturbo3 in forum Qt Programming
    Replies: 1
    Last Post: 18th July 2011, 12:12
  2. qsqlquery insert error
    By fantom in forum Qt Programming
    Replies: 4
    Last Post: 23rd February 2011, 17:15
  3. Qt4 QPSQL QSqlQuery::lastInsertId oid problem
    By feniksa in forum Qt Programming
    Replies: 2
    Last Post: 11th August 2010, 11:23
  4. QSqlQuery.exec() weird error
    By MarkoSan in forum Qt Programming
    Replies: 3
    Last Post: 25th May 2010, 13:02
  5. QSqlQuery and no response while exec()
    By jacek_ in forum Qt Programming
    Replies: 5
    Last Post: 5th November 2009, 08:47

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Qt is a trademark of The Qt Company.