ORDER BY Sqlite in QT

[Koch]

Hi again (unfortunately for me XD)
First of all, thx for help with my proxyProblem

Well, In this time, I created a sqlite table and insert things, and works, but, i have a problem with ORDER BY, this doesn't sort, and i don't know the reason ...

here is the code

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H
 
#include <QMainWindow>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
 
namespace Ui {
class MainWindow;
}
 
class MainWindow : public QMainWindow
{
    Q_OBJECT
 
public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();
 
    void crearTablaUsuarios();
    void insertarUsuario();
    void mostrarDatos();
 
private slots:
    void on_pushButtonAgregar_clicked();
 
    void on_pushButton_clicked();
 
private:
    Ui::MainWindow *ui;
    QSqlDatabase db;
};
 
#endif // MAINWINDOW_H

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

    void crearTablaUsuarios();
    void insertarUsuario();
    void mostrarDatos();

private slots:
    void on_pushButtonAgregar_clicked();

    void on_pushButton_clicked();

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

#endif // MAINWINDOW_H

To copy to clipboard, switch view to plain text mode 

and my mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDebug>
 
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    qDebug()<< "Aplicacion iniciada...";
    QString nombre;
    nombre.append("BaseDeDatos1.sqlite");
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(nombre);
    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.";
    }
    crearTablaUsuarios();
    mostrarDatos();
}
 
MainWindow::~MainWindow()
{
    delete ui;
}
 
void MainWindow::crearTablaUsuarios()
{
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS usuarios("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    "nombre VARCHAR(100),"
                    "apellido VARCHAR(100),"
                    "edad INTEGER NOT NULL,"
                    "clase INTEGER NOT NULL"
                    ");");
    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();
    }
}
 
void MainWindow::insertarUsuario()
{
    QString consulta;
    consulta.append("INSERT INTO usuarios("
                    "nombre,"
                    "apellido,"
                    "edad,"
                    "clase)"
                    "VALUES("
                    "'"+ui->lineEditNombre->text()+"',"
                    "'"+ui->lineEditApellido->text()+"',"
                    ""+ui->lineEditEdad->text()+","
                    ""+ui->lineEditClase->text()+""
                    ");");
    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();
    }
}
 
void MainWindow::mostrarDatos()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios");
    QSqlQuery consultar;
    consultar.prepare(consulta);
    if(consultar.exec()){
        qDebug()<< "Se ha consultado correctamente.";
    }
    else{
        qDebug()<< "NO se ha consultado correctamente.";
        qDebug()<< "ERROR!"<< consultar.lastError();
    }
 
    int fila = 0;
 
    ui->tableWidgetDatos->setRowCount(fila);
 
    while (consultar.next()){
        ui->tableWidgetDatos->insertRow(fila);
        ui->tableWidgetDatos->setItem(fila, 0, new QTableWidgetItem(consultar.value(1).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 1, new QTableWidgetItem(consultar.value(2).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 2, new QTableWidgetItem(consultar.value(3).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 3, new QTableWidgetItem(consultar.value(4).toByteArray().constData()));
        fila++;
    }
}
 
void MainWindow::on_pushButtonAgregar_clicked()
{
    insertarUsuario();
    mostrarDatos();
}
 
void MainWindow::on_Sort_clicked()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios ORDER BY clase ASC;");
    QSqlQuery ordenar;
    ordenar.prepare(consulta);
    ordenar.exec();
    if (ordenar.exec()){
        qDebug()<< "Se ha ordenado";
    }
    else{
        qDebug()<< "La Cagaste";
        qDebug()<< "ERROR!"<< ordenar.lastError();
    }
    mostrarDatos();
}

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDebug>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    qDebug()<< "Aplicacion iniciada...";
    QString nombre;
    nombre.append("BaseDeDatos1.sqlite");
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(nombre);
    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.";
    }
    crearTablaUsuarios();
    mostrarDatos();
}

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

void MainWindow::crearTablaUsuarios()
{
    QString consulta;
    consulta.append("CREATE TABLE IF NOT EXISTS usuarios("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                    "nombre VARCHAR(100),"
                    "apellido VARCHAR(100),"
                    "edad INTEGER NOT NULL,"
                    "clase INTEGER NOT NULL"
                    ");");
    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();
    }
}

void MainWindow::insertarUsuario()
{
    QString consulta;
    consulta.append("INSERT INTO usuarios("
                    "nombre,"
                    "apellido,"
                    "edad,"
                    "clase)"
                    "VALUES("
                    "'"+ui->lineEditNombre->text()+"',"
                    "'"+ui->lineEditApellido->text()+"',"
                    ""+ui->lineEditEdad->text()+","
                    ""+ui->lineEditClase->text()+""
                    ");");
    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();
    }
}

void MainWindow::mostrarDatos()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios");
    QSqlQuery consultar;
    consultar.prepare(consulta);
    if(consultar.exec()){
        qDebug()<< "Se ha consultado correctamente.";
    }
    else{
        qDebug()<< "NO se ha consultado correctamente.";
        qDebug()<< "ERROR!"<< consultar.lastError();
    }

    int fila = 0;

    ui->tableWidgetDatos->setRowCount(fila);

    while (consultar.next()){
        ui->tableWidgetDatos->insertRow(fila);
        ui->tableWidgetDatos->setItem(fila, 0, new QTableWidgetItem(consultar.value(1).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 1, new QTableWidgetItem(consultar.value(2).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 2, new QTableWidgetItem(consultar.value(3).toByteArray().constData()));
        ui->tableWidgetDatos->setItem(fila, 3, new QTableWidgetItem(consultar.value(4).toByteArray().constData()));
        fila++;
    }
}

void MainWindow::on_pushButtonAgregar_clicked()
{
    insertarUsuario();
    mostrarDatos();
}

void MainWindow::on_Sort_clicked()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios ORDER BY clase ASC;");
    QSqlQuery ordenar;
    ordenar.prepare(consulta);
    ordenar.exec();
    if (ordenar.exec()){
        qDebug()<< "Se ha ordenado";
    }
    else{
        qDebug()<< "La Cagaste";
        qDebug()<< "ERROR!"<< ordenar.lastError();
    }
    mostrarDatos();
}

To copy to clipboard, switch view to plain text mode 

My problem is here

void MainWindow::on_Sort_clicked()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios ORDER BY nombre ASC;");
    QSqlQuery ordenar;
    ordenar.prepare(consulta);
    ordenar.exec();
    if (ordenar.exec()){
        qDebug()<< "Se ha ordenado";
    }
    else{
        qDebug()<< "La Cagaste";
        qDebug()<< "ERROR!"<< ordenar.lastError();
    }
    mostrarDatos(); //this function show the table
}

void MainWindow::on_Sort_clicked()
{
    QString consulta;
    consulta.append("SELECT * FROM usuarios ORDER BY nombre ASC;");
    QSqlQuery ordenar;
    ordenar.prepare(consulta);
    ordenar.exec();
    if (ordenar.exec()){
        qDebug()<< "Se ha ordenado";
    }
    else{
        qDebug()<< "La Cagaste";
        qDebug()<< "ERROR!"<< ordenar.lastError();
    }
    mostrarDatos(); //this function show the table
}

To copy to clipboard, switch view to plain text mode 

This doesn't throw error, work perfectly, but don't sort ...

Thx for help me again

[jefftee]

Well, In this time, I created a sqlite table and insert things, and works, but, i have a problem with ORDER BY, this doesn't sort, and i don't know the reason ...

You are executing your SELECT query twice. Once in MainWindow::on_Sort_clicked with an ORDER BY clause, but in your function MainWindow::mostrarDatos(), you are re-executing the query without the ORDER by clause.

On another note, don't use concatenation when building your INSERT SQL statement. This leaves you exposed to SQL injections and you should use either named or positional query parameters, prepare the SQL statement, then QSqlQuery::bindValue to dynamically associate values to the named or positional query parameters.

Hope that helps,

Jeff

[wysota]

The query is executed three times. There are two exec statements in on_Sort_clicked.

[Koch]

Ohh thank you both, i didn't know this. Now this work perfectly, I'm so grateful, thank you so much !