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


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


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


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 function

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



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.