Parameterized query not working.

[pnunn]

Hi folks...

I'm trying to get an app working using parameterized queries that I have working using a hand worked query successfully.

The code is below... description of what's not happening follows.

#include "mainwindow.h"
#include "ui_mainwindow.h"
 
MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent), ui(new Ui::MainWindowClass)
{
    ui->setupUi(this);
    connect(ui->comboBox, SIGNAL(itemChanged(QModelIndex)), this, SLOT(showPriceData(QModelIndex)));
 
    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid"
                   "where p.shareid = :share");
 
    populateGroupBox();
}
 
MainWindow::~MainWindow()
{
    delete ui;
}
 
void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    query->bindValue(":share", shareidx.toInt());
    qDebug() << query->lastQuery();
 
    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();
 
    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}
 
void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select sharename, shareid from shareid");
    ui->comboBox->setModel(comboQueryModel);
}

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent), ui(new Ui::MainWindowClass)
{
    ui->setupUi(this);
    connect(ui->comboBox, SIGNAL(itemChanged(QModelIndex)), this, SLOT(showPriceData(QModelIndex)));

    priceQueryModel = new QSqlQueryModel;
    query = new QSqlQuery;
    query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid"
                   "where p.shareid = :share");

    populateGroupBox();
}

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

void MainWindow::updatePriceData(int idx)
{
    QSqlRecord record = comboQueryModel->record(idx);
    if(record.isEmpty())
    {
        qDebug() << "Error! Record is empty";
        return;
    }
    QVariant shareidx = record.value("shareid");
    query->bindValue(":share", shareidx.toInt());
    qDebug() << query->lastQuery();

    priceQueryModel->setQuery(*query);
    if(priceQueryModel->lastError().isValid())
        qDebug() << priceQueryModel->lastError();

    ui->tableView->setModel(priceQueryModel);
    priceQueryModel->removeColumn(0);
    priceQueryModel->setHeaderData(0, Qt::Horizontal, QObject::tr("Closing Date"));
    priceQueryModel->setHeaderData(1, Qt::Horizontal, QObject::tr("Share Price"));
    priceQueryModel->setHeaderData(2, Qt::Horizontal, QObject::tr("Total Value"));
    priceQueryModel->setHeaderData(3, Qt::Horizontal, QObject::tr("Company"));
}

void MainWindow::populateGroupBox(void)
{
    comboQueryModel = new QSqlQueryModel;
    comboQueryModel->setQuery("select sharename, shareid from shareid");
    ui->comboBox->setModel(comboQueryModel);
}

To copy to clipboard, switch view to plain text mode 

The application output (from the query->lastQuery() call still has the variable as a variable, not as the bound value for some reason.

ie.
"select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareidwhere p.shareid = :share"

Why is :share not the integer I've set it to?

Ta

Peter.

[spirit]

I don't see any database connection. did you make it? show us compilable example.

[pnunn]

Sorry... the connection is in connection.h (based on qt examples)

#ifndef CONNECTION_H
#define CONNECTION_H
 
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
 
/*
    This file defines a helper function to open a connection to
    the shares database (hard coded).
*/
 
static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE");
    QSqlError err;
 
    db.setDatabaseName("/home/pnunn/src/database/share/shares2.fdb");
    db.setHostName("localhost");
 
    if (!db.open("SYSDBA","masterkey")) {
        err = db.lastError();
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            err.text(), QMessageBox::Cancel);
        return false;
    }
 
    return true;
}
 
#endif

#ifndef CONNECTION_H
#define CONNECTION_H

#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

/*
    This file defines a helper function to open a connection to
    the shares database (hard coded).
*/

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE");
    QSqlError err;

    db.setDatabaseName("/home/pnunn/src/database/share/shares2.fdb");
    db.setHostName("localhost");

    if (!db.open("SYSDBA","masterkey")) {
        err = db.lastError();
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            err.text(), QMessageBox::Cancel);
        return false;
    }

    return true;
}

#endif

To copy to clipboard, switch view to plain text mode 

the function is called in main

#include <QtGui/QApplication>
#include "mainwindow.h"
#include "connection.h"
 
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection()) // try and open the database
        exit(1);
 
    MainWindow w;
    w.show();
    return a.exec();
}

#include <QtGui/QApplication>
#include "mainwindow.h"
#include "connection.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection()) // try and open the database
        exit(1);

    MainWindow w;
    w.show();
    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

That makes the complete app. other than the UI which consists of a combobox and a table on a form (currently with a button that does nothing) [attached]

Peter.

[pnunn]

Still not winning with this one... I've even tried now using the anonymous parameter '?' as follows

In constructor
 
query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid"
                   "where p.shareid = ?");

In constructor

query->prepare("select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareid"
                   "where p.shareid = ?");

To copy to clipboard, switch view to plain text mode 

In function 
 
query->addBindValue(shareidx.toInt());
query->exec();

In function 

query->addBindValue(shareidx.toInt());
query->exec();

To copy to clipboard, switch view to plain text mode 

but query->lastQuery(); still has no parameter (returns as follows in the output)

select p.shareid, p.pricedate, p.valuecol, p.totalvalue, s.sharename from pricedata p inner join shareid s on p.shareid = s.shareidwhere p.shareid = ?


Peter

[spirit]

did you try to execute this query in Qt's sqlbrowser which is located in QTDIR/demos/sqlbrowser?

[pnunn]

[red face]
I have been trying to figure this out for ages... just installed qtcreator 1 and found a new error message from the running of the system.

Turns out that I split the sql into two strings in the prepare statement and consequently missed a space between two key words...
[/red face]

Now works as advertised.