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, ayee, :amount)"
"INSERT INTO expenses( tran_dt, category, tax_ded, payee, amount) VALUES ( :tran_dt, :category, :tax_ded, ayee, :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, ayee, :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( "ayee", "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$
Bookmarks