Return NEWID() from a SQL Query
-
wrote on 19 Dec 2019, 09:40 last edited by
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.
-
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.
wrote on 19 Dec 2019, 09:59 last edited by JonB@Lany
MySQL != MS SQL,NEWID()
does not exist. MySQL hasLAST_INSERT_ID
. I assume you meanTEST_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();
-
wrote on 19 Dec 2019, 10:23 last edited by
Thank you for your answer,!
TEST_ID is not an autoincrement column, this is why I have to manage the creation of IDs. -
Thank you for your answer,!
TEST_ID is not an autoincrement column, this is why I have to manage the creation of IDs. -
wrote on 19 Dec 2019, 11:35 last edited by 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 ""
-
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 ""
wrote on 19 Dec 2019, 12:07 last edited by@Lany
https://doc.qt.io/qt-5/qsqlquery.html#exec-1 also returns abool
value. Check that. When you discover it's returningfalse
, debug out https://doc.qt.io/qt-5/qsqlquery.html#lastError. -
wrote on 19 Dec 2019, 12:39 last edited by 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 "" " "
-
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 "" " "
wrote on 19 Dec 2019, 12:49 last edited by@Lany
IfQSqlQuery::next()
is returningfalse
I would see whatquery.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 returningfalse
. -
I'm unsure if the qt mysql driver can handle multiple queries - I would guess no.
-
wrote on 19 Dec 2019, 14:39 last edited by
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!
-
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!
wrote on 19 Dec 2019, 15:00 last edited by@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() ?
-
wrote on 19 Dec 2019, 15:02 last edited by
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: -
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.
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. -
wrote on 19 Dec 2019, 15:34 last edited by
@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
-
@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
@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). -
wrote on 19 Dec 2019, 15:47 last edited by
@kshegunov
The returned type is :QVariant::double
TEST_ID is a primary key (PK, uniqueidentifier, not null)
If I try thisqDebug() << query.lastInsertId().toDouble();
The returned value is :
0
-
@kshegunov
The returned type is :QVariant::double
TEST_ID is a primary key (PK, uniqueidentifier, not null)
If I try thisqDebug() << query.lastInsertId().toDouble();
The returned value is :
0
What database engine is this? MySQL, PgSQL, MSSQL?
-
wrote on 19 Dec 2019, 15:58 last edited by
Microsoft SQL server 2014
-
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.
1/19