Results 1 to 7 of 7

Thread: Do an UPSERT

  1. #1
    Join Date
    Dec 2014
    Posts
    11
    Thanks
    5
    Qt products
    Qt5
    Platforms
    Windows

    Default Do an UPSERT

    I want to do an UPSERT, this is something like :

    if (ITEM exists in table)
    {
    update ITEM
    }
    else
    {
    insert ITEM
    }

    I Tried this:
    Qt Code:
    1. consulta2.append("UPDATE OR INSERT INTO cpais("
    2. "Pais)"
    3. "VALUES("
    4. "'"+Pais+"'"
    5. ");");
    To copy to clipboard, switch view to plain text mode 
    (+Pais+ is a QString, cpais is the table and Pais is the only field)

    And this

    Qt Code:
    1. consulta2.append("INSERT INTO cpais("
    2. "Pais)"
    3. "VALUES("
    4. "'"+Pais+"'"
    5. ") "
    6. "ON DUPLICATE KEY UPDATE Pais="
    7. "'"+Pais+"'"
    8. ");");
    To copy to clipboard, switch view to plain text mode 

    and this

    Qt Code:
    1. consulta2.append("INSERT OR REPLACE INTO cpais("
    2. "Pais)"
    3. "VALUES("
    4. "'"+Pais+"'"
    5. ");");
    To copy to clipboard, switch view to plain text mode 

    And no one of these works ...

    In the first and the second throws this error
    Qt Code:
    1. ERROR! QSqlError("", "", "")
    To copy to clipboard, switch view to plain text mode 
    And in the third, just works like an INSERT, don't do REPLACE

    Thx for help

  2. #2
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: Do an UPSERT

    Which database driver are you using?

    Also, don't use concatenation to build your queries. Use positional or named parameter queries and then QSqlQuery::bindValue to bind your variables to the query parameters, etc.

  3. #3
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Do an UPSERT

    Show us the full query cycle, not just the append to a string. And I agree, better bind values, these are properly escaped for you (contrary to appending to strings).
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  4. #4
    Join Date
    Dec 2014
    Posts
    11
    Thanks
    5
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Do an UPSERT

    I want to edit the post but i cant, so, here is the code

    listc.cpp
    Qt Code:
    1. #include "listc.h"
    2. #include "ui_listc.h"
    3. #include <QDebug>
    4. #include <QModelIndexList>
    5. #include <nuevoc.h>
    6. #include <QModelIndex>
    7.  
    8. LISTC::LISTC(QWidget *parent) :
    9. QMainWindow(parent),
    10. ui(new Ui::LISTC)
    11. {
    12. ui->setupUi(this);
    13. QString NDB;
    14. NDB.append("DBSERG.sqlite");
    15. db = QSqlDatabase::addDatabase("QSQLITE");
    16. db.setDatabaseName("DBSERG.sqlite");
    17. if (db.open()){
    18. qDebug()<< "Se ha conectado con EXITO la Base de Datos.";
    19. }
    20. else{
    21. qDebug()<< "No se ha conectado con EXITO la Base de Datos.";
    22. }
    23. CREARTABLA();
    24. MOSTRARTABLA();
    25.  
    26.  
    27. }
    28.  
    29. LISTC::~LISTC()
    30. {
    31. delete ui;
    32. }
    33.  
    34. void LISTC::CREARTABLA()
    35. {
    36. QString consulta;
    37. consulta.append("CREATE TABLE IF NOT EXISTS clientes("
    38. "ID INTEGER PRIMARY KEY AUTOINCREMENT,"
    39. "Nombre VARCHAR(100),"
    40. "Apellido VARCHAR(100),"
    41. "Telefono INTEGER NOT NULL,"
    42. "Localidad VARCHAR(100),"
    43. "Pais VARCHAR(100),"
    44. "Provincia VARCHAR(100)"
    45. ");");
    46. QSqlQuery crear;
    47. crear.prepare(consulta);
    48. if(crear.exec()){
    49. qDebug()<< "La tabla USUARIOS existe o se ha creado correctamente.";
    50. }
    51. else{
    52. qDebug()<< "La tabla USUARIOS NO se ha creado correctamente o no existe.";
    53. qDebug()<< "ERROR!"<< crear.lastError();
    54. }
    55. QString consulta2;
    56. consulta2.append("CREATE TABLE IF NOT EXISTS cpais("
    57. "Pais VARCHAR(100)"
    58. ");");
    59. QSqlQuery crear2;
    60. crear2.prepare(consulta2);
    61. if(crear2.exec()){
    62. qDebug()<< "La tabla USUARIOS existe o se ha creado correctamente.";
    63. }
    64. else{
    65. qDebug()<< "La tabla USUARIOS NO se ha creado correctamente o no existe.";
    66. qDebug()<< "ERROR!"<< crear.lastError();
    67. }
    68. FC1 = new QSqlQueryModel();
    69. QString hola;
    70. hola.append("SELECT * FROM cpais ORDER BY Pais ASC;");
    71. QSqlQuery holar;
    72. holar.prepare(hola);
    73. holar.exec();
    74. FC1->setQuery(holar);
    75. ui->comboBoxFPAIS->setModel(FC1);
    76. }
    77.  
    78. void LISTC::MOSTRARTABLA()
    79. {
    80. modDB = new QSqlRelationalTableModel (this, db);
    81. modDB->setTable("clientes");
    82. modDB->select();
    83. proxyDB = new QSortFilterProxyModel(this);
    84. proxyDB->setSourceModel(modDB);
    85. proxyDB->setFilterCaseSensitivity(Qt::CaseInsensitive);
    86. proxyDB->setFilterKeyColumn(-1);
    87. ui->tableViewDB->setModel(proxyDB);
    88. ui->tableViewDB->setSelectionBehavior(QAbstractItemView::SelectRows);
    89. ui->tableViewDB->setSortingEnabled(true);
    90.  
    91.  
    92. }
    93.  
    94. void LISTC::on_pushButtonNUEVO_clicked()
    95. {
    96. /*QModelIndex insertIndex = ui->tableViewDB->currentIndex();
    97.   int row = insertIndex.row() == -1 ? 0 : insertIndex.row();*/
    98. //Lo de arriba es para el editar y el borrar
    99. NUEVOC * nc = new NUEVOC();
    100. nc->setModal(true);
    101. connect(nc,SIGNAL(my_signal(QString,QString,QString,QString,QString,QString)),this,SLOT(insert(QString,QString,QString,QString,QString,QString)));
    102. nc->exec();
    103. modDB->setTable("clientes");
    104. modDB->select();
    105. proxyDB->setSourceModel(modDB);
    106. proxyDB->setFilterCaseSensitivity(Qt::CaseInsensitive);
    107. proxyDB->setFilterKeyColumn(-1);
    108. ui->tableViewDB->setModel(proxyDB);
    109. ui->tableViewDB->setSelectionBehavior(QAbstractItemView::SelectRows);
    110. ui->tableViewDB->setSortingEnabled(true);
    111. QString hola;
    112. hola.append("SELECT * FROM cpais ORDER BY Pais ASC;");
    113. QSqlQuery holar;
    114. holar.prepare(hola);
    115. holar.exec();
    116. FC1->setQuery(holar);
    117. ui->comboBoxFPAIS->setModel(FC1);
    118. }
    119.  
    120. void LISTC::insert(QString Nombre, QString Apellido, QString Telefono, QString Localidad, QString Pais, QString Provincia)
    121. {
    122. QString consulta;
    123. consulta.append("INSERT INTO clientes("
    124. "Nombre,"
    125. "Apellido,"
    126. "Telefono,"
    127. "Localidad,"
    128. "Pais,"
    129. "Provincia)"
    130. "VALUES("
    131. "'"+Nombre+"',"
    132. "'"+Apellido+"',"
    133. ""+Telefono+","
    134. "'"+Localidad+"',"
    135. "'"+Pais+"',"
    136. "'"+Provincia+"'"
    137. ");");
    138. QSqlQuery insertar;
    139. insertar.prepare(consulta);
    140. if(insertar.exec()){
    141. qDebug()<< "El USUARIO se ha insertado correctamente.";
    142. }
    143. else{
    144. qDebug()<< "El USUARIO NO se ha insertado correctamente.";
    145. qDebug()<< "ERROR!"<< insertar.lastError();
    146. }
    147. QString consulta2;
    148. /*consulta2.append("INSERT INTO cpais("
    149.   "Pais)"
    150.   "VALUES("
    151.   "'"+Pais+"'"
    152.   ");");*/
    153. consulta2.append("UPDATE OR INSERT INTO cpais("
    154. "Pais)"
    155. "VALUES("
    156. "'"+Pais+"'"
    157. // ") "
    158. // "ON DUPLICATE KEY UPDATE Pais="
    159. // "'"+Pais+"'"
    160. ");");
    161. QSqlQuery insertar2;
    162. insertar2.prepare(consulta2);
    163. if(insertar2.exec()){
    164. qDebug()<< "El Pais se ha insertado correctamente.";
    165. }
    166. else{
    167. qDebug()<< "El Pais NO se ha insertado correctamente.";
    168. qDebug()<< "ERROR!"<< insertar.lastError();
    169. }
    170. }
    171.  
    172. void LISTC::on_pushButtonID_clicked()
    173. {
    174. proxyDB->setFilterKeyColumn(0);
    175. proxyDB->setFilterFixedString(ui->lineEditID->text());
    176. // proxyDB->setFilterKeyColumn(-1);
    177. }
    To copy to clipboard, switch view to plain text mode 
    And the driver that i used is SQLITE

    My code is very dirty because i don't clean up yet, sorry for that ...

    listc.h
    Qt Code:
    1. #ifndef LISTC_H
    2. #define LISTC_H
    3.  
    4. #include <QMainWindow>
    5. #include <QtSql/QSqlDatabase>
    6. #include <QtSql/QSqlQuery>
    7. #include <QtSql/QSqlError>
    8. #include <QSqlQueryModel>
    9. #include <QSqlRelationalTableModel>
    10. #include <QSortFilterProxyModel>
    11. #include "nuevoc.h"
    12.  
    13. namespace Ui {
    14. class LISTC;
    15. }
    16.  
    17. class LISTC : public QMainWindow
    18. {
    19. Q_OBJECT
    20.  
    21. public:
    22. explicit LISTC(QWidget *parent = 0);
    23. ~LISTC();
    24. void CREARTABLA();
    25. void MOSTRARTABLA();
    26.  
    27.  
    28. private slots:
    29. void on_pushButtonNUEVO_clicked();
    30. void insert(QString Nombre, QString Apellido, QString Telefono, QString Localidad, QString Pais, QString Provincia);
    31.  
    32. void on_pushButtonID_clicked();
    33.  
    34. private:
    35. Ui::LISTC *ui;
    36. };
    37.  
    38. #endif // LISTC_H
    To copy to clipboard, switch view to plain text mode 

    and the .pro file
    Qt Code:
    1. #-------------------------------------------------
    2. #
    3. # Project created by QtCreator 2014-12-16T08:57:11
    4. #
    5. #-------------------------------------------------
    6.  
    7. QT += core gui sql
    8.  
    9. greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
    10.  
    11. TARGET = Proyecto2
    12. TEMPLATE = app
    13.  
    14.  
    15. SOURCES += main.cpp\
    16. login.cpp \
    17. panel.cpp \
    18. listc.cpp \
    19. listp.cpp \
    20. listi.cpp \
    21. nuevoc.cpp
    22.  
    23. HEADERS += login.h \
    24. panel.h \
    25. listc.h \
    26. listp.h \
    27. listi.h \
    28. nuevoc.h
    29.  
    30. FORMS += login.ui \
    31. panel.ui \
    32. listc.ui \
    33. listp.ui \
    34. listi.ui \
    35. nuevoc.ui
    To copy to clipboard, switch view to plain text mode 

    thx for help

  5. #5
    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: Do an UPSERT

    "UPDATE OR INSERT INTO ..." is not valid Sqlite Sql syntax.
    "INSERT INTO ... ON DUPLICATE KEY ..." Is not valid Sqlite Sql syntax. In any case, the table does not have a unique constraint that would highlight a duplicate..
    "INSERT OR REPLACE INTO ..." Is valid syntax however it only ever inserts because your table does not have any constraint that would trigger the conflict handling "REPLACE". Put a primary or unique key on the column.
    https://www.sqlite.org/lang_update.html
    https://www.sqlite.org/lang_insert.html
    https://www.sqlite.org/lang_conflict.html

  6. #6
    Join Date
    Dec 2014
    Posts
    11
    Thanks
    5
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Do an UPSERT

    Quote Originally Posted by ChrisW67 View Post
    "UPDATE OR INSERT INTO ..." is not valid Sqlite Sql syntax.
    "INSERT INTO ... ON DUPLICATE KEY ..." Is not valid Sqlite Sql syntax. In any case, the table does not have a unique constraint that would highlight a duplicate..
    "INSERT OR REPLACE INTO ..." Is valid syntax however it only ever inserts because your table does not have any constraint that would trigger the conflict handling "REPLACE". Put a primary or unique key on the column.
    https://www.sqlite.org/lang_update.html
    https://www.sqlite.org/lang_insert.html
    https://www.sqlite.org/lang_conflict.html
    Ohhh i didn't know this, now it works, I use this
    "append("CREATE UNIQUE INDEX cpaisPais ON cpais (Pais);");"
    after create cpais table and work perfectly, Thank you so much, i love you XD
    (Im still learning SQL for you guys, thanks to all).

  7. #7
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Do an UPSERT

    I really suggest you get familiar with QSqlQuery::bindValue(), your code would become much cleaner.

    Qt Code:
    1. QSqlQuery insertar;
    2. insertar.prepare("INSERT INTO clientes(Nombre, Apellido, Telefono, Localidad, Pais, Provincia) VALUES( :nombre, :apelido, :telefono, :localidad, :pais, :provincia)";
    3. insertar.bindValue(":nombre", Nombre);
    4. insertar.bindValue(":apelido", Apelido);
    5. insertar.bindValue(":telefono", Telefono);
    6. insertar.bindValue(":localidad", Localidad);
    7. insertar.bindValue(":pais", Pais);
    8. insertar.bindValue(":provincia", Provincia);
    9. insertar.exec();
    To copy to clipboard, switch view to plain text mode 
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


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
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.