[SOLVED] QPSQL: Unable to create query - What is wrong?

[RolBri]

Hello,

I am trying to insert some data into a PostgreeSQL table.
Sadly I am always getting the error: ^\n(42601) QPSQL: Unable to create query" and I have no idea why.

This is how my table looks:

CREATE TABLE "measuredData"
(
  doornumber smallint NOT NULL,
  angle double precision NOT NULL,
  year smallint NOT NULL,
  month smallint NOT NULL,
  day smallint NOT NULL,
  hour smallint NOT NULL,
  minute smallint NOT NULL,
  second smallint NOT NULL,
  millisecond smallint NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "measuredData"
  OWNER TO postgres;

And this is my code:

void test(int DoorNumber, double Angle, int Year, int Month, int Day, int Hour, int Minute, int Second, int Millisecond)
{
    hostName="localhost";
    databaseName="cCo";
    userName="postgres";
    password="12345";
    tableName="measuredData";

    db = QSqlDatabase::addDatabase("QPSQL");
    db.setHostName(hostName);
    db.setPort(5432);
    db.setDatabaseName(databaseName);
    db.setUserName(userName);
    db.setPassword(password);

    if(db.open())
    {
        QSqlQuery insert;
                insert.prepare("INSERT INTO "+tableName+" (:doornumber, :angle, :year, :month, :day, :hour, :minute, :second, :millisecond)" "VALUES (doornumber, angle, year, month, day, hour, minute, second, millisecond)");
                insert.bindValue(":doornumber",QString::number(DoorNumber) );
                insert.bindValue(":angle", QString::number(Angle,'f', 3) );
                insert.bindValue(":year", QString::number(Year) );
                insert.bindValue(":month", QString::number(Month) );
                insert.bindValue(":day", QString::number(Day) );
                insert.bindValue(":hour", QString::number(Hour) );
                insert.bindValue(":minute", QString::number(Minute) );
                insert.bindValue(":second", QString::number(Second) );
                insert.bindValue(":millisecond", QString::number(Millisecond) );
                if (!insert.exec()) qDebug() << "Error!" << insert.lastError().text() ;

 //Second try:
                        insert.prepare("INSERT INTO "+tableName+" (:doornumber, :angle, :year, :month, :day, :hour, :minute, :second, :millisecond)" "VALUES (doornumber, angle, year, month, day, hour, minute, second, millisecond)");
                        insert.bindValue(":doornumber",DoorNumber );
                        insert.bindValue(":angle", Angle );
                        insert.bindValue(":year", Year );
                        insert.bindValue(":month", Month );
                        insert.bindValue(":day", Day );
                        insert.bindValue(":hour", Hour );
                        insert.bindValue(":minute", Minute );
                        insert.bindValue(":second", Second );
                        insert.bindValue(":millisecond", Millisecond );
                        if (!insert.exec()) qDebug() << "Error!" << insert.lastError().text() ;

         db.close();
    }
    else qDebug() << "Error!" << db.lastError().text() ;
}

These are the error messages of the two input tries.

Error! "FEHLER:  Syntaxfehler bei \u201E(\u201C\nLINE 1: EXECUTE  ('1', '24.000', '2015', '8', '17', '10', '6', '59',...\n                 ^\n(42601) QPSQL: Unable to create query"
Error! "FEHLER:  Syntaxfehler bei \u201E(\u201C\nLINE 1: EXECUTE  (1, 24, 2015, 8, 17, 10, 6, 59, 999)\n                 ^\n(42601) QPSQL: Unable to create query"

What is wrong with my code?
I do not get it :-/

Thank you very much :-)

[p3c0]

@RolBri You have swapped placeholders with columns.

[RolBri]

@p3c0
Thank you very much :-)
You are right, this was one mistake.

Sadly even after changing this I still get the same errors even after a complete rebuild.

Do you have another idea?

Thanks :-)

[RolBri]

I found the second error :-)

The table name has to be a name with only lower case letters.
Then everything works fine :-)