Do an UPSERT

[Koch]

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

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

I Tried this:

consulta2.append("UPDATE OR INSERT INTO cpais("
"Pais)"
"VALUES("
"'"+Pais+"'"
");");
(+Pais+ is a QString, cpais is the table and Pais is the only field)

And this


consulta2.append("INSERT INTO cpais("
"Pais)"
"VALUES("
"'"+Pais+"'"
") "
"ON DUPLICATE KEY UPDATE Pais="
"'"+Pais+"'"
");");

and this


consulta2.append("INSERT OR REPLACE INTO cpais("
"Pais)"
"VALUES("
"'"+Pais+"'"
");");

And no one of these works ...

In the first and the second throws this error
ERROR! QSqlError("", "", "")
And in the third, just works like an INSERT, don't do REPLACE

Thx for help :)

[jefftee]

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.

[wysota]

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).

[Koch]

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

listc.cpp

#include "listc.h"
#include "ui_listc.h"
#include <QDebug>
#include <QModelIndexList>
#include <nuevoc.h>
#include <QModelIndex>

LISTC::LISTC(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::LISTC)
{
ui->setupUi(this);
QString NDB;
NDB.append("DBSERG.sqlite");
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("DBSERG.sqlite");
if (db.open()){
qDebug()<< "Se ha conectado con EXITO la Base de Datos.";
}
else{
qDebug()<< "No se ha conectado con EXITO la Base de Datos.";
}
CREARTABLA();
MOSTRARTABLA();


}

LISTC::~LISTC()
{
delete ui;
}

void LISTC::CREARTABLA()
{
QString consulta;
consulta.append("CREATE TABLE IF NOT EXISTS clientes("
"ID INTEGER PRIMARY KEY AUTOINCREMENT,"
"Nombre VARCHAR(100),"
"Apellido VARCHAR(100),"
"Telefono INTEGER NOT NULL,"
"Localidad VARCHAR(100),"
"Pais VARCHAR(100),"
"Provincia VARCHAR(100)"
");");
QSqlQuery crear;
crear.prepare(consulta);
if(crear.exec()){
qDebug()<< "La tabla USUARIOS existe o se ha creado correctamente.";
}
else{
qDebug()<< "La tabla USUARIOS NO se ha creado correctamente o no existe.";
qDebug()<< "ERROR!"<< crear.lastError();
}
QString consulta2;
consulta2.append("CREATE TABLE IF NOT EXISTS cpais("
"Pais VARCHAR(100)"
");");
QSqlQuery crear2;
crear2.prepare(consulta2);
if(crear2.exec()){
qDebug()<< "La tabla USUARIOS existe o se ha creado correctamente.";
}
else{
qDebug()<< "La tabla USUARIOS NO se ha creado correctamente o no existe.";
qDebug()<< "ERROR!"<< crear.lastError();
}
FC1 = new QSqlQueryModel();
QString hola;
hola.append("SELECT * FROM cpais ORDER BY Pais ASC;");
QSqlQuery holar;
holar.prepare(hola);
holar.exec();
FC1->setQuery(holar);
ui->comboBoxFPAIS->setModel(FC1);
}

void LISTC::MOSTRARTABLA()
{
modDB = new QSqlRelationalTableModel (this, db);
modDB->setTable("clientes");
modDB->select();
proxyDB = new QSortFilterProxyModel(this);
proxyDB->setSourceModel(modDB);
proxyDB->setFilterCaseSensitivity(Qt::CaseInsensitive);
proxyDB->setFilterKeyColumn(-1);
ui->tableViewDB->setModel(proxyDB);
ui->tableViewDB->setSelectionBehavior(QAbstractItemView::SelectRows );
ui->tableViewDB->setSortingEnabled(true);


}

void LISTC::on_pushButtonNUEVO_clicked()
{
/*QModelIndex insertIndex = ui->tableViewDB->currentIndex();
int row = insertIndex.row() == -1 ? 0 : insertIndex.row();*/
//Lo de arriba es para el editar y el borrar
NUEVOC * nc = new NUEVOC();
nc->setModal(true);
connect(nc,SIGNAL(my_signal(QString,QString,QStrin g,QString,QString,QString)),this,SLOT(insert(QStri ng,QString,QString,QString,QString,QString)));
nc->exec();
modDB->setTable("clientes");
modDB->select();
proxyDB->setSourceModel(modDB);
proxyDB->setFilterCaseSensitivity(Qt::CaseInsensitive);
proxyDB->setFilterKeyColumn(-1);
ui->tableViewDB->setModel(proxyDB);
ui->tableViewDB->setSelectionBehavior(QAbstractItemView::SelectRows );
ui->tableViewDB->setSortingEnabled(true);
QString hola;
hola.append("SELECT * FROM cpais ORDER BY Pais ASC;");
QSqlQuery holar;
holar.prepare(hola);
holar.exec();
FC1->setQuery(holar);
ui->comboBoxFPAIS->setModel(FC1);
}

void LISTC::insert(QString Nombre, QString Apellido, QString Telefono, QString Localidad, QString Pais, QString Provincia)
{
QString consulta;
consulta.append("INSERT INTO clientes("
"Nombre,"
"Apellido,"
"Telefono,"
"Localidad,"
"Pais,"
"Provincia)"
"VALUES("
"'"+Nombre+"',"
"'"+Apellido+"',"
""+Telefono+","
"'"+Localidad+"',"
"'"+Pais+"',"
"'"+Provincia+"'"
");");
QSqlQuery insertar;
insertar.prepare(consulta);
if(insertar.exec()){
qDebug()<< "El USUARIO se ha insertado correctamente.";
}
else{
qDebug()<< "El USUARIO NO se ha insertado correctamente.";
qDebug()<< "ERROR!"<< insertar.lastError();
}
QString consulta2;
/*consulta2.append("INSERT INTO cpais("
"Pais)"
"VALUES("
"'"+Pais+"'"
");");*/
consulta2.append("UPDATE OR INSERT INTO cpais("
"Pais)"
"VALUES("
"'"+Pais+"'"
// ") "
// "ON DUPLICATE KEY UPDATE Pais="
// "'"+Pais+"'"
");");
QSqlQuery insertar2;
insertar2.prepare(consulta2);
if(insertar2.exec()){
qDebug()<< "El Pais se ha insertado correctamente.";
}
else{
qDebug()<< "El Pais NO se ha insertado correctamente.";
qDebug()<< "ERROR!"<< insertar.lastError();
}
}

void LISTC::on_pushButtonID_clicked()
{
proxyDB->setFilterKeyColumn(0);
proxyDB->setFilterFixedString(ui->lineEditID->text());
// proxyDB->setFilterKeyColumn(-1);
}


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

#ifndef LISTC_H
#define LISTC_H

#include <QMainWindow>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QSqlQueryModel>
#include <QSqlRelationalTableModel>
#include <QSortFilterProxyModel>
#include "nuevoc.h"

namespace Ui {
class LISTC;
}

class LISTC : public QMainWindow
{
Q_OBJECT

public:
explicit LISTC(QWidget *parent = 0);
~LISTC();
QSqlQueryModel * FC1;
QSqlQueryModel * FC2;
QSqlQueryModel * FC3;
void CREARTABLA();
void MOSTRARTABLA();
QSqlRelationalTableModel * modDB;
QSortFilterProxyModel * proxyDB;


private slots:
void on_pushButtonNUEVO_clicked();
void insert(QString Nombre, QString Apellido, QString Telefono, QString Localidad, QString Pais, QString Provincia);

void on_pushButtonID_clicked();

private:
Ui::LISTC *ui;
QSqlDatabase db;
};

#endif // LISTC_H

and the .pro file

#-------------------------------------------------
#
# Project created by QtCreator 2014-12-16T08:57:11
#
#-------------------------------------------------

QT += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = Proyecto2
TEMPLATE = app


SOURCES += main.cpp\
login.cpp \
panel.cpp \
listc.cpp \
listp.cpp \
listi.cpp \
nuevoc.cpp

HEADERS += login.h \
panel.h \
listc.h \
listp.h \
listi.h \
nuevoc.h

FORMS += login.ui \
panel.ui \
listc.ui \
listp.ui \
listi.ui \
nuevoc.ui


thx for help :)

[ChrisW67]

"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

[Koch]

"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).

[wysota]

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


QSqlQuery insertar;
insertar.prepare("INSERT INTO clientes(Nombre, Apellido, Telefono, Localidad, Pais, Provincia) VALUES( :nombre, :apelido, :telefono, :localidad, :pais, :provincia)";
insertar.bindValue(":nombre", Nombre);
insertar.bindValue(":apelido", Apelido);
insertar.bindValue(":telefono", Telefono);
insertar.bindValue(":localidad", Localidad);
insertar.bindValue(":pais", Pais);
insertar.bindValue(":provincia", Provincia);
insertar.exec();