QSqlQuery prepare() bindValue() broken badly

[RolandHughes]

psql 8.3.4
Ubuntu 8.10 64-bit AMD

After discovering the last bug, I decided to write a small test program to just write three records three different ways into an empty postgresql table. Looks like some pretty broken code.

output
========================
roland@roland-desktop:~/qt_stuff/db$ ./db
Rows affected: 1
"INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount) VALUES ( :tran_dt, :category, :tax_ded, :payee, :amount)"
"INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount) VALUES ( :tran_dt, :category, :tax_ded, :payee, :amount)"
Rows affected: -1 Error text: "ERROR: syntax error at or near "("
LINE 1: EXECUTE ('2138-10-11', 'Hardware', 'Y', '3Com', 876.54)
^
QPSQL: Unable to create query"
just wrote duplicate row
roland@roland-desktop:~/qt_stuff/db$

===================================

Source
===============
#include <QtGui>
#include <QtSql>
#include <QtDebug>



int main(int argc, char *argv[])
{
//int i_x;
QString dbName, localCategory, localPayee;
double localAmount;
QDate localTranDt;
bool localTax_ded;


QApplication app(argc, argv);

{
QString driverName = "QPSQL";

//
// Set up information for driver and see if driver is available
//
QSqlDatabase db = QSqlDatabase::addDatabase(driverName, "xpns");
db.setHostName("localhost");
QString dbName = "tax_2138";
db.setDatabaseName( dbName);
db.setUserName( );
db.setPassword( );

//
// Have they been good little children and created our database already?
//
if (!db.open()) {
qDebug() << "database wasn't opened";
return 0;
}

QSqlQuery query(db);
db.transaction();
query.exec("INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount)"
"VALUES ( '21380506', 'Software', 'Y', 'CDW', 3456.72)");
qDebug() << "Rows affected: " << query.numRowsAffected();
db.commit();
qApp->processEvents();
QSqlQuery preparedQuery(db);
preparedQuery.prepare( "INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount) VALUES ( :tran_dt, :category, :tax_ded, :payee, :amount)");

db.transaction();
qDebug() << preparedQuery.lastQuery();
localTranDt = QDate( 2138, 10, 11);
localCategory = "Hardware";
localPayee = "3Com";
localAmount = 876.54;
localTax_ded = true;

query.bindValue( ":tran_dt", localTranDt);
query.bindValue( ":category", "Hardware");
query.bindValue( ":tax_ded", "Y");
query.bindValue( ":payee", "3Com");
query.bindValue( ":amount", 876.54);
qDebug() << preparedQuery.lastQuery();

qApp->processEvents();

query.exec();
qApp->processEvents();
qDebug() << "Rows affected: " << query.numRowsAffected()
<< " Error text: " << query.lastError().text();
db.commit();


localTranDt = QDate( 2138, 6, 11);
localCategory = "Books";
localPayee = "Borders";
localAmount = 88.99;
localTax_ded = true;

query.exec();
qDebug() << "just wrote duplicate row";
qApp->processEvents();

}

return 0;

}

===============


Yes, I deleted my username and password from that.


If you need the database SQL, here it is.


START TRANSACTION;

DROP TABLE IF EXISTS expenses;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS payees;


COMMIT;

START TRANSACTION;

CREATE TABLE categories (
category char(25) CONSTRAINT category_constraint NOT NULL PRIMARY KEY,
description char(50),
tax_ded boolean);

CREATE TABLE payees (
payee char(50) CONSTRAINT payee_constraint NOT NULL PRIMARY KEY,
contact char(50));

COMMIT;

START TRANSACTION;
--
-- No index or key on transaction table.
-- If this were a real-time system rather than a system used
-- at the end of the year in a mad rush we would use timestamp instead of date
-- data type and make this column the primary key.
--
CREATE TABLE expenses (
--tran_id serial,
tran_dt date,
category char(25) CONSTRAINT valid_cat REFERENCES categories (category) MATCH FULL ON DELETE RESTRICT,
tax_ded boolean,
payee char(50) CONSTRAINT valid_payee REFERENCES payees (payee) MATCH FULL ON DELETE RESTRICT,
amount numeric(10,2) CONSTRAINT amt_constraint NOT NULL);

COMMIT;
roland@roland-desktop:~/xpnsqt$

[RolandHughes]

Never mind, I was an idiot. Guess I really shouldn't code without sufficeint Mt. Dew on hand. Crossed up query and preparedQuery.

All afternoon lost on a stupid.

[RolandHughes]

No, this is still broken badly.

query.exec("INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount)"

query.bindValue( ":tran_dt", localTranDt);
query.bindValue( 1, "8888");

Both of these bindValue calls should at BEST, do nothing; at WORST throw an exception. What currently happens when there aren't any targets in the string is it creates a new string. Granted, stupidity and a lack of caffiene lead to this, but you can go one past on the numerics and end up with a similar problem.

[Lesiok]

You have in code 2 QSqlQuery objects.
First named query and second named preparedQuery. You call method prepare() on object preparedQuery and then You try bind values to object query. So why are You wondering that all is going bad ?

[RolandHughes]

Please read the other posts.