PDA

View Full Version : ORDER BY Sqlite in QT



Koch
24th December 2014, 07:52
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


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();
}


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
}


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

Thx for help me again :D

jefftee
24th December 2014, 08:41
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
24th December 2014, 10:07
The query is executed three times. There are two exec statements in on_Sort_clicked.

Koch
24th December 2014, 18:26
Ohh thank you both, i didn't know this. Now this work perfectly, I'm so grateful, thank you so much :D!