Results 1 to 5 of 5

Thread: QSqlQuery prepare() bindValue() broken badly

  1. #1
    Join Date
    Nov 2008
    Posts
    183
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default QSqlQuery prepare() bindValue() broken badly

    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$

  2. #2
    Join Date
    Nov 2008
    Posts
    183
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlQuery prepare() bindValue() broken badly

    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.

  3. #3
    Join Date
    Nov 2008
    Posts
    183
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlQuery prepare() bindValue() broken badly

    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.

  4. #4
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,531
    Thanked 281 Times in 276 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery prepare() bindValue() broken badly

    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 ?

  5. #5
    Join Date
    Nov 2008
    Posts
    183
    Thanks
    13
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QSqlQuery prepare() bindValue() broken badly

    Please read the other posts.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.