Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Return NEWID() from a SQL Query

Return NEWID() from a SQL Query

Scheduled Pinned Locked Moved Unsolved General and Desktop
19 Posts 6 Posters 1.9k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • L Offline
    L Offline
    Lany
    wrote on last edited by
    #1

    Hello,

    I need the new id "NEWID()" created in this SQL Query, how can I get it?

    QSqlQuery query;
    query.exec("INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (NEWID(), '0');");
    

    I tried something like this without success

    QSqlQuery query;
    query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
               "SET @myid = NEWID();"
               "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
               "OUTPUT @myid;");
    query.next();
    qDebug() << query.value(0).toString();
    

    Thanks.

    JonBJ kshegunovK 2 Replies Last reply
    0
    • L Lany

      Hello,

      I need the new id "NEWID()" created in this SQL Query, how can I get it?

      QSqlQuery query;
      query.exec("INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (NEWID(), '0');");
      

      I tried something like this without success

      QSqlQuery query;
      query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                 "SET @myid = NEWID();"
                 "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                 "OUTPUT @myid;");
      query.next();
      qDebug() << query.value(0).toString();
      

      Thanks.

      JonBJ Offline
      JonBJ Offline
      JonB
      wrote on last edited by JonB
      #2

      @Lany
      MySQL != MS SQL, NEWID() does not exist. MySQL has LAST_INSERT_ID. I assume you mean TEST_ID is an autoincrement column:

      QSqlQuery query;
      query.exec("INSERT INTO TBL_TEST (TEST_01) VALUES ('0');"
                 "SELECT LAST_INSERT_ID();");
      query.next();
      qDebug() << query.value(0).toString();
      
      1 Reply Last reply
      0
      • L Offline
        L Offline
        Lany
        wrote on last edited by
        #3

        Thank you for your answer,!
        TEST_ID is not an autoincrement column, this is why I have to manage the creation of IDs.

        JonBJ 1 Reply Last reply
        0
        • L Lany

          Thank you for your answer,!
          TEST_ID is not an autoincrement column, this is why I have to manage the creation of IDs.

          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by
          #4

          @Lany
          Then say what your NEWID() is?

          If your original code was broadly correct in that respect, then try the last line as SELECT @myid; instead of OUTPUT @myid;.

          1 Reply Last reply
          0
          • L Offline
            L Offline
            Lany
            wrote on last edited by Lany
            #5

            I'm not a expert in SQL, I started to work in this project (and SQL) at the start of this week.
            Here's the full code.

            QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
            db.setConnectOptions();
            db.setDatabaseName("Driver={SQL Server};Server=" + m_str_adresseBaseSQL
                               + ";Database=" + m_str_nomBaseQSL + ";Uid=" + m_str_nomUtilisateur 
                               + ";Pwd=" + m_str_passUtilisateur + ";");
            
            if (db.open()){
                QSqlQuery query;
                query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
            	       "SET @myid = NEWID();"
            	       "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
            	       "SELECT @myid;");
                qDebug() << query.next();
                qDebug() << query.value(0).toString();
                db.close();
            }
            

            And the debug output:

            false
            QSqlQuery::value: not positioned on a valid record
            ""
            
            JonBJ 1 Reply Last reply
            0
            • L Lany

              I'm not a expert in SQL, I started to work in this project (and SQL) at the start of this week.
              Here's the full code.

              QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
              db.setConnectOptions();
              db.setDatabaseName("Driver={SQL Server};Server=" + m_str_adresseBaseSQL
                                 + ";Database=" + m_str_nomBaseQSL + ";Uid=" + m_str_nomUtilisateur 
                                 + ";Pwd=" + m_str_passUtilisateur + ";");
              
              if (db.open()){
                  QSqlQuery query;
                  query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
              	       "SET @myid = NEWID();"
              	       "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
              	       "SELECT @myid;");
                  qDebug() << query.next();
                  qDebug() << query.value(0).toString();
                  db.close();
              }
              

              And the debug output:

              false
              QSqlQuery::value: not positioned on a valid record
              ""
              
              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by
              #6

              @Lany
              https://doc.qt.io/qt-5/qsqlquery.html#exec-1 also returns a bool value. Check that. When you discover it's returning false, debug out https://doc.qt.io/qt-5/qsqlquery.html#lastError.

              1 Reply Last reply
              2
              • L Offline
                L Offline
                Lany
                wrote on last edited by Lany
                #7

                Here's the result of

                qDebug() << query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                                       "SET @myid = NEWID();"
                                       "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                                       "SELECT @myid;");
                qDebug() << query.next();
                qDebug() << query.value(0).toString();
                qDebug() << query.lastError().text();
                

                Debug output:

                true
                false
                QSqlQuery::value: not positioned on a valid record
                ""
                " "
                
                JonBJ 1 Reply Last reply
                0
                • L Lany

                  Here's the result of

                  qDebug() << query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                                         "SET @myid = NEWID();"
                                         "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                                         "SELECT @myid;");
                  qDebug() << query.next();
                  qDebug() << query.value(0).toString();
                  qDebug() << query.lastError().text();
                  

                  Debug output:

                  true
                  false
                  QSqlQuery::value: not positioned on a valid record
                  ""
                  " "
                  
                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on last edited by
                  #8

                  @Lany
                  If QSqlQuery::next() is returning false I would see what query.lastError() on the immediately following line returns (I don't know whether that gets set, try it). I would read https://doc.qt.io/qt-5/qsqlquery.html#next, where restrictions are mentioned, e.g. for all I know/I suspect that for your statement https://doc.qt.io/qt-5/qsqlquery.html#isSelect is returning false.

                  1 Reply Last reply
                  0
                  • Christian EhrlicherC Offline
                    Christian EhrlicherC Offline
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    I'm unsure if the qt mysql driver can handle multiple queries - I would guess no.

                    Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    1 Reply Last reply
                    1
                    • L Offline
                      L Offline
                      Lany
                      wrote on last edited by
                      #10

                      I tried this

                      qDebug() << query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                                             "SET @myid = NEWID();"
                                             "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                                             "SELECT @myid;");
                      qDebug() << query.lastError().text();
                      qDebug() << query.isActive();
                      qDebug() << query.isSelect();
                      qDebug() << query.next();
                      qDebug() << query.value(0).toString();
                      

                      the result:

                      true
                      " "
                      true
                      false
                      false
                      QSqlQuery::value: not positioned on a valid record
                      ""
                      

                      The query is not in a SELECT state, as mentioned by Christian, I think this driver can't handle multiple query.
                      I'm going to look for another solution.

                      Thanks!

                      KroMignonK 1 Reply Last reply
                      0
                      • L Lany

                        I tried this

                        qDebug() << query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                                               "SET @myid = NEWID();"
                                               "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                                               "SELECT @myid;");
                        qDebug() << query.lastError().text();
                        qDebug() << query.isActive();
                        qDebug() << query.isSelect();
                        qDebug() << query.next();
                        qDebug() << query.value(0).toString();
                        

                        the result:

                        true
                        " "
                        true
                        false
                        false
                        QSqlQuery::value: not positioned on a valid record
                        ""
                        

                        The query is not in a SELECT state, as mentioned by Christian, I think this driver can't handle multiple query.
                        I'm going to look for another solution.

                        Thanks!

                        KroMignonK Offline
                        KroMignonK Offline
                        KroMignon
                        wrote on last edited by
                        #11

                        @Lany said in Return NEWID() from a SQL Query:

                        I'm going to look for another solution.

                        Perhaps you can try to send the SQL statement through QSqlDatabase::exec() ?

                        It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

                        1 Reply Last reply
                        0
                        • MucipM Offline
                          MucipM Offline
                          Mucip
                          wrote on last edited by
                          #12

                          Hi,
                          I think you want to insert a record and get the record serila number from DB.
                          I guess it depends of what you use as database but in my case I use PostgreSQL and in this situation you have good SQL command like below:

                          //Yeni kaydet yapılıyor...
                              QSqlQuery kayitDb(baglanti::mdb());
                              kayitDb.prepare("INSERT INTO teklif(teklif_no, teklif_tarih, teklif_tip, aciklama, cari_kod, teslim_sure, odeme_tip, odeme_vade, teklif_kur, proje_kod) "
                                              "VALUES(:teklif_no, :teklif_tarih, :teklif_tip, :aciklama, :cari_kod, :teslim_sure, :odeme_tip, :odeme_vade, :teklif_kur, :proje_kod) RETURNING sira_no;");
                          
                              kayitDb.bindValue(":teklif_no", ui->lETeklifNo->text());
                              kayitDb.bindValue(":teklif_tarih", ui->dateTeklifTarih->date());
                              kayitDb.bindValue(":teklif_tip", ui->lETeklifTip->text() );
                              kayitDb.bindValue(":aciklama", ui->plainAciklama->toPlainText());
                              kayitDb.bindValue(":cari_kod", ui->lECariKod->text().toInt());
                              kayitDb.bindValue(":teslim_sure", ui->lETeslim->text());
                          
                              kayitDb.bindValue(":odeme_tip", ui->cBOdeme->currentText() );
                              kayitDb.bindValue(":odeme_vade", ui->lEVade->text() );
                              kayitDb.bindValue(":teklif_kur", ui->comboKur->currentText() );
                          
                          
                          
                               //Sending and returning serial ID...
                              if(kayitDb.exec() && kayitDb.first())
                              {
                                  int belgeSiraNo = kayitDb.value(0).toInt();
                                   ui->lESiraNo->setText(kayitDb.value(0).toString());
                          
                          
                                      if(detayKaydet(belgeSiraNo))
                                      {
                                          //İşlem sonu
                                          QMessageBox::information(this, "Tamamdır :)", "Fiş: " + ui->lESiraNo->text() + " sıra numarası ile kaydedildi.");
                                          ekranTemizle();
                                          iptalKonum();
                                      }
                          
                              } else
                              {
                                  QMessageBox::critical(this, "Hata", "Veritabanına ulaşılamadı!\n"+kayitDb.lastError().text() );
                                  return;
                              }
                          

                          You're sending INSERT sql and reading returning serila value.

                          Regards,
                          Mucip:

                          1 Reply Last reply
                          0
                          • L Lany

                            Hello,

                            I need the new id "NEWID()" created in this SQL Query, how can I get it?

                            QSqlQuery query;
                            query.exec("INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (NEWID(), '0');");
                            

                            I tried something like this without success

                            QSqlQuery query;
                            query.exec("DECLARE @myid UNIQUEIDENTIFIER;"
                                       "SET @myid = NEWID();"
                                       "INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (@myid, '0');"
                                       "OUTPUT @myid;");
                            query.next();
                            qDebug() << query.value(0).toString();
                            

                            Thanks.

                            kshegunovK Offline
                            kshegunovK Offline
                            kshegunov
                            Moderators
                            wrote on last edited by
                            #13

                            Just use the api, here you go: https://doc.qt.io/qt-5/qsqlquery.html#lastInsertId
                            There's little sense in trying to emulate something that's not standard and is already implemented in the driver.

                            Read and abide by the Qt Code of Conduct

                            1 Reply Last reply
                            0
                            • L Offline
                              L Offline
                              Lany
                              wrote on last edited by
                              #14

                              @kshegunov
                              The ID is generated by the db not by "me"

                              query.exec("INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (NEWID(), '0');");
                              qDebug() << query.lastInsertId().toString();
                              

                              result:

                              "0"
                              

                              In the db I have something like this :

                              315FD979-F300-4493-A0B9-4B5FE64A8F45
                              
                              kshegunovK 1 Reply Last reply
                              0
                              • L Lany

                                @kshegunov
                                The ID is generated by the db not by "me"

                                query.exec("INSERT INTO TBL_TEST (TEST_ID, TEST_01) VALUES (NEWID(), '0');");
                                qDebug() << query.lastInsertId().toString();
                                

                                result:

                                "0"
                                

                                In the db I have something like this :

                                315FD979-F300-4493-A0B9-4B5FE64A8F45
                                
                                kshegunovK Offline
                                kshegunovK Offline
                                kshegunov
                                Moderators
                                wrote on last edited by kshegunov
                                #15

                                @Lany said in Return NEWID() from a SQL Query:

                                The ID is generated by the db not by "me"

                                Yes, that's why the client library and the driver on top of it knows about it and can give it back to you. It's not necessary to do a query to emulate the behavior.

                                PS. You need to handle the variant you got properly, not just blindly convert it to a string.

                                qDebug() << query.lastInsertId().type();
                                

                                should help. Also, please make sure that TEST_ID is a primary key in the database (i.e. is properly indexed).

                                Read and abide by the Qt Code of Conduct

                                1 Reply Last reply
                                0
                                • L Offline
                                  L Offline
                                  Lany
                                  wrote on last edited by
                                  #16

                                  @kshegunov
                                  The returned type is :

                                  QVariant::double
                                  

                                  TEST_ID is a primary key (PK, uniqueidentifier, not null)
                                  If I try this

                                  qDebug() << query.lastInsertId().toDouble();
                                  

                                  The returned value is :

                                  0
                                  
                                  kshegunovK 1 Reply Last reply
                                  0
                                  • L Lany

                                    @kshegunov
                                    The returned type is :

                                    QVariant::double
                                    

                                    TEST_ID is a primary key (PK, uniqueidentifier, not null)
                                    If I try this

                                    qDebug() << query.lastInsertId().toDouble();
                                    

                                    The returned value is :

                                    0
                                    
                                    kshegunovK Offline
                                    kshegunovK Offline
                                    kshegunov
                                    Moderators
                                    wrote on last edited by kshegunov
                                    #17

                                    What database engine is this? MySQL, PgSQL, MSSQL?

                                    Read and abide by the Qt Code of Conduct

                                    1 Reply Last reply
                                    0
                                    • L Offline
                                      L Offline
                                      Lany
                                      wrote on last edited by
                                      #18

                                      Microsoft SQL server 2014

                                      kshegunovK 1 Reply Last reply
                                      0
                                      • L Lany

                                        Microsoft SQL server 2014

                                        kshegunovK Offline
                                        kshegunovK Offline
                                        kshegunov
                                        Moderators
                                        wrote on last edited by kshegunov
                                        #19

                                        Right, sorry I was misled about the talk about MySql. Anyways, you really should auto-generate the id, not insert it manually (probably not the problem here, just saying). I haven't worked with MS for ages. Check if the driver supports lastInstertId at all.

                                        qDebug() << db.driver()->hasFeature(QSqlDriver::LastInsertId);
                                        

                                        If it doesn't then you're stuck to using non-standard queries. That means once you run the insert, you run a second query to fetch the inserted id.

                                        Read and abide by the Qt Code of Conduct

                                        1 Reply Last reply
                                        2

                                        • Login

                                        • Login or register to search.
                                        • First post
                                          Last post
                                        0
                                        • Categories
                                        • Recent
                                        • Tags
                                        • Popular
                                        • Users
                                        • Groups
                                        • Search
                                        • Get Qt Extensions
                                        • Unsolved