Qt4 and Postgres quick example

Just a quick example of some very fundamental Postgres database access with Qt4. I was tracking down a problem all afternoon, so thought I would post this test. The only important thing to learn is that bindValue() really does only stuff the current value into a temporary string. This is NOT like an SQL pre-compiler which binds the variable for life, saving you a lot of work.

tax_2138=# select * from expenses;
tran_dt | category | tax_ded | payee | amount
———+———-+———+——-+——–
(0 rows)

#include
#include
#include

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(”"); // yes I deleted my username
db.setPassword(”" ); // yes I deleted my password

//
// 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 from text only insert: " << query.numRowsAffected();
db.commit();
qApp->processEvents();
QSqlQuery preparedQuery(db);
preparedQuery.prepare( “INSERT INTO expenses( category, tax_ded, payee, amount, tran_dt) ”
“VALUES (:category, :tax_ded, :payee, :amount, :tran_dt)”);

db.transaction();
localTranDt = QDate( 2138, 10, 11);
localCategory = “Hardware”;
localPayee = “3Com”;
localAmount = 876.54;
localTax_ded = true;

preparedQuery.bindValue( “:tran_dt”, localTranDt);
preparedQuery.bindValue( “:category”, localCategory);
preparedQuery.bindValue( “:tax_ded”, localTax_ded);
preparedQuery.bindValue( “:payee”, localPayee);
preparedQuery.bindValue( “:amount”, localAmount);

qApp->processEvents();

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

db.transaction();

//
// Simply changing the variable values isn't enough.
// Qt doesn't work like SQL compilers which bind variables for
// the life of the program. There is no real bind here,
// simply things being forced into a command string. You have
// to both change the data and bind the value. I guess that is why
// it is "bindValue" not "bindVariable"
//
localTranDt = QDate( 2138, 6, 11);
localCategory = "Books";
localPayee = "Borders";
localAmount = 88.99;
localTax_ded = true;

preparedQuery.bindValue( ":tran_dt", localTranDt);
preparedQuery.bindValue( ":category", localCategory);
preparedQuery.bindValue( ":tax_ded", localTax_ded);
preparedQuery.bindValue( ":payee", localPayee);
preparedQuery.bindValue( ":amount", localAmount);

preparedQuery.exec();
qDebug() << "just wrote duplicate row" << preparedQuery.numRowsAffected();
qApp->processEvents();
db.commit();

}

return 0;

}

roland@roland-desktop:~/qt_stuff/db$ ./db
Rows affected from text only insert: 1
Rows affected: 1 Error text: ” ”
just wrote duplicate row 1
roland@roland-desktop:~/qt_stuff/db$

tax_2138=# select * from expenses;
tran_dt | category | tax_ded | payee | amount
————+—————————+———+—————————————————-+———
2138-05-06 | Software | t | CDW | 3456.72
2138-10-11 | Hardware | t | 3Com | 876.54
2138-06-11 | Books | t | Borders | 88.99
(3 rows)

This entry was posted on November 12, 2008 at 10:34 pm and is filed under Information Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print