[QSqlQuery] Problem with not open database

[xkazielx]

Hi folks. I'm trying to write simple database program base on MySQL. I've established connection with database but I'can query. Here is what I'm talking about:

#include "myquery.h"
 
MyQuery::MyQuery(QWidget *parent)
    : QMainWindow(parent)
{
    db=QSqlDatabase::addDatabase("QMYSQL", "lge");
    db.setDatabaseName("WR");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("password");
    using std::cout;
    if(db.open())
    {
        cout<<"Database opend.\n";
    }
 
    cout<<query.exec("SELECT * FROM items");
}

#include "myquery.h"

MyQuery::MyQuery(QWidget *parent)
    : QMainWindow(parent)
{
    db=QSqlDatabase::addDatabase("QMYSQL", "lge");
    db.setDatabaseName("WR");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("password");
    using std::cout;
    if(db.open())
    {
        cout<<"Database opend.\n";
    }

    cout<<query.exec("SELECT * FROM items");
}

To copy to clipboard, switch view to plain text mode 

I'm trying to run this under WinXP. I've added "CONFIG+=console" to see std output and I'm getting:

Database opend.
QSqlQuery::exex:database not open
0

What I'm doing wrong?

[jano_alex_es]

is the QSQLQuery query connected to the database? I'm missing

QSqlQuery query(db);

QSqlQuery query(db);

To copy to clipboard, switch view to plain text mode 

on the other hand, to avoid strange problems, always try to execute "prepare" before executing.

[xkazielx]

Thanks, that helped me with "SELECT". But when I was doing INSERT I've saw:

C:/Documents and Settings/user/My Documents/queryTest/MyQuery/main.cpp:28: error: no matching function for call to `QSqlQuery::addBindValue(int)'
c:/Qt/4.5.2/include/QtSql/../../src/sql/kernel/qsqlquery.h:113: note: candidates are: void QSqlQuery::addBindValue(const QVariant&, QSql::ParamType)

When I've included QVariant it started working as a charm.
Thanks 4 your help.

[xkazielx]

Hi friends!!! I'm still fighting with SQL app. Now I've got a derived class with a slot that is connected to a signal emited by MainWindow class (top class in my pro). In a class I have two variables QSqlQuery *query, and QSqlDatabase baza. I'm allocating memory for QSqlQuery with:

query = new QSqlQuery(baza);

I'm able to open database. But when I call a slot that id doing query in consol i still see

database not open

Here is code of my slot:

if(!query->prepare("INSERT into urzadzenia(nazwa, opis) VALUES (:nazwa, :opis))")) std::cerr<<"\nError in prepering query\n";
query->bindValue(":nazwa", naz);
query->bindValue(":opis", opis);
if(bazaDanych.isOpen()){
    std::cout<<"DB open\t";
    std::cout<<"query.exec()"<<query->exec()<<'\n';
}
else{
    std::cout<<"DB not open";
}

if(!query->prepare("INSERT into urzadzenia(nazwa, opis) VALUES (:nazwa, :opis))")) std::cerr<<"\nError in prepering query\n";
query->bindValue(":nazwa", naz);
query->bindValue(":opis", opis);
if(bazaDanych.isOpen()){
    std::cout<<"DB open\t";
    std::cout<<"query.exec()"<<query->exec()<<'\n';
}
else{
    std::cout<<"DB not open";
}

To copy to clipboard, switch view to plain text mode 

And i console I can see:

QSqlQuery::prepare: database not open
Error in prepering query
DB open query.exec() 0

Any ideas what is wrong?

[jano_alex_es]

Maybe the "into" should be capitalized and you need to call the QString constructor.

if(!query->prepare(QString("INSERT INTO urzadzenia(nazwa, opis) VALUES (:nazwa, :opis))"))) std::cerr<<"\nError in prepering query\n";

if(!query->prepare(QString("INSERT INTO urzadzenia(nazwa, opis) VALUES (:nazwa, :opis))"))) std::cerr<<"\nError in prepering query\n";

To copy to clipboard, switch view to plain text mode 

[spirit]

coud you provide a minimal compilaple example which reproduces a problem?

[spiderman3000p]

i have the same problem.
im using a SQLITE3 database. i didnt create the table contained from my app, i've crate it outside with another program (sqlite browser).


here is the first fragment of my code, below:

#include "mainwindow.h"
#include "ui_mainwindow.h"
 #include <QSqlDriver>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlTableModel>
#include <QMessageBox>
#include <QSqlError>
#include <QDate>
QString movistar[6]={"10 Bs","12 Bs","20 Bs","25 Bs","40 Bs","60 Bs"};
QString movilnet[4]={"15 Bs","25 Bs","40 Bs","60 Bs"};
QSqlQuery sql;
int mov=6,movi=4;
 
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    //deshabilitar boton maximizar
   // this->setWindowFlags(Qt::WindowMinimizeButtonHint);
   //create the database
    QSqlDatabase bd=QSqlDatabase::addDatabase("QSQLITE");
    //set database name
    bd.setDatabaseName("C:/registro de ventas.db3");
  bd.setPassword("120590");
   bd.setHostName("localhost");
 
    if(!bd.open())
    {
       MessageBox::critical(this,"error","error al abrir base de datos");
 
 
    }
   else
   { MessageBox::about(this,"error","base de datos abierta");
 
   }
   //inicializando montos....
    for(int i=0;i<6;i++)
   ui->monto->addItem(movistar[i]);
 
   ui->monto->setCurrentIndex(0);
 
   calcular();
   //-------------------
}

#include "mainwindow.h"
#include "ui_mainwindow.h"
 #include <QSqlDriver>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlTableModel>
#include <QMessageBox>
#include <QSqlError>
#include <QDate>
QString movistar[6]={"10 Bs","12 Bs","20 Bs","25 Bs","40 Bs","60 Bs"};
QString movilnet[4]={"15 Bs","25 Bs","40 Bs","60 Bs"};
QSqlQuery sql;
int mov=6,movi=4;

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    //deshabilitar boton maximizar
   // this->setWindowFlags(Qt::WindowMinimizeButtonHint);
   //create the database
    QSqlDatabase bd=QSqlDatabase::addDatabase("QSQLITE");
    //set database name
    bd.setDatabaseName("C:/registro de ventas.db3");
  bd.setPassword("120590");
   bd.setHostName("localhost");

    if(!bd.open())
    {
       MessageBox::critical(this,"error","error al abrir base de datos");


    }
   else
   { MessageBox::about(this,"error","base de datos abierta");
    
   }
   //inicializando montos....
    for(int i=0;i<6;i++)
   ui->monto->addItem(movistar[i]);

   ui->monto->setCurrentIndex(0);

   calcular();
   //-------------------
}

To copy to clipboard, switch view to plain text mode 

in this first segment of the code, i can realize that the conection to the database its succesfuly done.
the problem come when i try to execute any query...insert, for example.

void MainWindow::vender()
{QMessageBox m;
 venta objeto;
 
   int indice=ui->monto->currentIndex();
   int tarjeta=0;
   int cant=ui->cantidad->value();
   float recibido=ui->efectivo->text().toFloat();
   objeto.cantidad=cant;
   if(ui->tipo->currentText()=="Movistar")
   { switch(indice)
     {
     case 0: tarjeta=10;break;
     case 1: tarjeta=12;break;
     case 2: tarjeta=20;break;
     case 3: tarjeta=25;break;
     case 4: tarjeta=40;break;
     case 5: tarjeta=60;break;
     }
   }
   if(ui->tipo->currentText()=="Movilnet")
   { switch(indice)
     {
     case 0: tarjeta=15;break;
     case 1: tarjeta=25;break;
     case 2: tarjeta=40;break;
     case 3: tarjeta=60;break;
     }
   }
  //calculando monto a cobrar, vuelto
  float acobrar=cant*tarjeta;
 
  float vuelto2=recibido-(acobrar);
 
  objeto.tipo=ui->tipo->currentText();
   //hora
  QDate d;
  QTime t;
  QString message;
  objeto.hora=t.currentTime();
  objeto.fecha=d.currentDate();
   //fecha
   objeto.acobrar=acobrar;
   objeto.recibido=recibido;
   objeto.vuelto=vuelto2;
 
 if(registrar_BD(objeto))      //<------------------------------------------ here i call the function that will execute the query
 {m.setText("Registro exitoso");}
 else
 {message=sql.lastError().Text(); //< ---------------------- this will show me if any error happend.
     m.setText(message);
 
 }
 
 m.exec();
 
}
 
bool MainWindow::registrar_BD(venta objeto)//<---- this its the object that contain the data that i want to save
{  // and her i asingn constants values just for try
   return (sql.exec("INSERT INTO venta(tarjeta,monto,cantidad,efectivo_recibido,efectivo_devuelto) VALUES('digitel',20,1,40,20)"));
 
 //return (sql.exec("insert into ventas (fecha,hora,tarjeta,monto,cantidad,efectivo_recibido,efectivo_devuelto)
//"values(objeto.fecha,objeto.hora,objeto.tipo,objeto.monto,objeto.cantidad,objeto.recibido,objeto.vuelto)"));
 
 
}

void MainWindow::vender()
{QMessageBox m;
 venta objeto;

   int indice=ui->monto->currentIndex();
   int tarjeta=0;
   int cant=ui->cantidad->value();
   float recibido=ui->efectivo->text().toFloat();
   objeto.cantidad=cant;
   if(ui->tipo->currentText()=="Movistar")
   { switch(indice)
     {
     case 0: tarjeta=10;break;
     case 1: tarjeta=12;break;
     case 2: tarjeta=20;break;
     case 3: tarjeta=25;break;
     case 4: tarjeta=40;break;
     case 5: tarjeta=60;break;
     }
   }
   if(ui->tipo->currentText()=="Movilnet")
   { switch(indice)
     {
     case 0: tarjeta=15;break;
     case 1: tarjeta=25;break;
     case 2: tarjeta=40;break;
     case 3: tarjeta=60;break;
     }
   }
  //calculando monto a cobrar, vuelto
  float acobrar=cant*tarjeta;

  float vuelto2=recibido-(acobrar);

  objeto.tipo=ui->tipo->currentText();
   //hora
  QDate d;
  QTime t;
  QString message;
  objeto.hora=t.currentTime();
  objeto.fecha=d.currentDate();
   //fecha
   objeto.acobrar=acobrar;
   objeto.recibido=recibido;
   objeto.vuelto=vuelto2;

 if(registrar_BD(objeto))      //<------------------------------------------ here i call the function that will execute the query
 {m.setText("Registro exitoso");}
 else
 {message=sql.lastError().Text(); //< ---------------------- this will show me if any error happend.
     m.setText(message);

 }

 m.exec();

}

bool MainWindow::registrar_BD(venta objeto)//<---- this its the object that contain the data that i want to save
{  // and her i asingn constants values just for try
   return (sql.exec("INSERT INTO venta(tarjeta,monto,cantidad,efectivo_recibido,efectivo_devuelto) VALUES('digitel',20,1,40,20)"));
   
 //return (sql.exec("insert into ventas (fecha,hora,tarjeta,monto,cantidad,efectivo_recibido,efectivo_devuelto)
//"values(objeto.fecha,objeto.hora,objeto.tipo,objeto.monto,objeto.cantidad,objeto.recibido,objeto.vuelto)"));


}

To copy to clipboard, switch view to plain text mode 

and this is the code of thel mainwindow.h:

#ifndef MAINWINDOW_H
#define MAINWINDOW_H
 
#include <QMainWindow>
#include<mis_datos.h>
#include <QSqlQuery>
namespace Ui {
    class MainWindow;
}
 
class MainWindow : public QMainWindow
{
    Q_OBJECT
 
public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();
 
private:
    Ui::MainWindow *ui;
    QImage Imagen;
private slots:
void calcular();
void comprobarTipo();
void mostrarimg();
void cargarimg(int,int);
void vender();
bool registrar_BD(venta);
};
 
#endif // MAINWINDOW_H

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include<mis_datos.h>
#include <QSqlQuery>
namespace Ui {
    class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

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

private:
    Ui::MainWindow *ui;
    QImage Imagen;
private slots:
void calcular();
void comprobarTipo();
void mostrarimg();
void cargarimg(int,int);
void vender();
bool registrar_BD(venta);
};

#endif // MAINWINDOW_H

To copy to clipboard, switch view to plain text mode 

when i execute my app, just when i push the "vender" button, this is what i got:

i have to say that i've tried to compile the libraries of sqlite with the commands below:
configure -qt-sql-sqlite para habilitar las librerias sqlite
and configure -qmake32 ...or something like that...

i've tried:
put my app in the directory: Qt/2010.04/qt/bin
copy and pste the dll's of sqlite from Qt/2010.04/qt/bin and sqldrivers to my app directory
here is a rar whith the proyect (i know that it have so many sintaxis errors and logicals errors, but it doesnt matter in the end):
http://www.fileden.com/files/2007/6/..._tarjetas2.rar

some details

* the file "registro de ventas.db3" that its inside the rar, is the database that must be in C partition. well, in my code i put it there, you can move it for use it. but that is the file that have to be used.

* my qt version is 4.6.3. con qt creator 2.0

i hope some one of u give me the answer!!!!! the succesfull answer!

thanks so much friends!

[norobro]

From the docs:

Warning: You must load the SQL driver and open the connection before a QSqlQuery is created.

Works fine when the query is created in your "registrar_BD" method. You'll have to handle error messaging some other way if you do that though.

HTH

[spiderman3000p]

i has solved that problem...thanks for your reply norobro.

[avpro]

i has solved that problem...thanks for your reply norobro.

great, if you share your solution you might help others.
thanks.

[ChrisW67]

You woke up a three year old thread to add that?

Accessing databases has been done repeatedly in this forum and there are plenty of examples in the docs. If you have a specific question of your own then feel free to open a new thread, show what you have done, explain what you expected, what you got, what you have done to try to resolve the problem etc.

Spiderman3000p simply had to follow norobro's hint that the routine works if the query object is created in the routine that tries to use it. Actually, it just needs to be created after the database is added.

[avpro]

You woke up a three year old thread to add that?

Hi Chris,
sorry for that, I saw the date of the post after I hit the reply button. Another lesson learned. I will be careful next time.