Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlQuery "ALTER TABLE" PostgreSQL



  • Hi all,
    I'm trying to add a column on existing database but I still get this error...

    Error! "ERROR:  syntax error at the end of the input\nLINE 1: EXECUTE \n                ^\n(42601) QPSQL: Unable to create query"
    

    this is the code

                QSqlDatabase::database().transaction();
                QSqlQuery q;
                q.clear();
                q.prepare("ALTER TABLE public.mytable ADD newcol integer;");
                if (q.exec())
                    QSqlDatabase::database().commit();
                else
                {
                    qDebug() << "Error!" << q.lastError().text();
                    QSqlDatabase::database().rollback();
                }
    
    

    I don't understand where is the mistake...
    If I run the same query inside PgAdmin the query works fine!


  • Lifetime Qt Champion

    Hi
    did you try without the ;
    It complains about end so i did wonder but have no
    PostgreSQL to test with.



  • Yes @mrjj
    I've try these...

    q.prepare("ALTER TABLE public.mytable ADD newcol integer"); // ;
    q.prepare("ALTER TABLE mytable ADD newcol integer;"); // public
    q.prepare("ALTER TABLE public.mytable ADD COLUMN newcol integer;"); // COLUMN
    

  • Lifetime Qt Champion

    @addebito
    Hi
    Ok it was worth a shot.
    I assume they all gave same error.

    Apparently the error (42601) QPSQL: Unable to create query
    can be due to many reasons.

    Other operations works weel, like UPDATE and INSERT ?



  • I don't have much experience with pSql however class 42 can be invoked also when user lacks privileges. When you test those queries in pgadmin you use same user?


  • Lifetime Qt Champion

    Also check the return value of the prepare() statement.



  • Thank you @mrjj , @artwaw , @Christian-Ehrlicher for your reply.

    @mrjj the INSERT command works without any problem.
    @artwaw yes I use the same user in my Qt code and PgAdmin
    @Christian-Ehrlicher the "prepare" return FALSE in the ALTER TABLE command and TRUE in the INSERT command

    from the help...
    Prepares the SQL query query for execution. Returns true if the query is prepared successfully; otherwise returns false

    // THIS CODE DOESN'T WORKS
    QSqlDatabase::database().transaction();
    QSqlQuery q;
    q.clear();
    bool b = q.prepare("ALTER TABLE public.mytable ADD newcol integer;");
    qDebug() << "prepare query:" << b;
    if (q.exec())
        QSqlDatabase::database().commit();
    else
    {
        qDebug() << "Error!" << q.lastError().text();
        QSqlDatabase::database().rollback();
    }
    
    // THIS CODE WORKS !!
    QSqlDatabase::database().transaction();
    q.clear();
    b = q.prepare("INSERT INTO public.mytable (name, a, b, c) VALUES ('ppppp', 0, 0, 0);");
    qDebug() << "prepare query:" << b;
    if (q.exec())
        QSqlDatabase::database().commit();
    else
    {
        qDebug() << "Error!" << q.lastError().text();
        QSqlDatabase::database().rollback();
    }
    

  • Lifetime Qt Champion

    Maybe preparing an ALTER TABLE is not allowed in postgresql? It also make no sense to prepare such a statement. Use a simple exec()



  • @addebito
    In the case of the troublesome query at least, there is no need to prepare(). I doubt it will resolve, but at least try :

    QSqlQuery q("ALTER TABLE public.mytable ADD newcol integer;")
    QSqlError error = q.lastError();
    qDebug() << error.text();
    

    No better?

    You have made 100,000% sure that newcol does not already exist in the table, haven't you?

    What other ALTER TABLE statements have you tried? Do any work?

    [ @Christian-Ehrlicher Your post has just crossed while I was composing this! Damn! :) Same thought. ]

    EDIT
    According to https://stackoverflow.com/questions/17648861/can-we-use-ddl-commands-in-a-prepared-statement-postgresql you cannot ALTER TABLE in a "normal" prepared statement, like yours. You should indeed try executing directly, not via prepare().



  • Yes @Christian-Ehrlicher @JonB .
    You are right !!
    Without prepare works !!

    This is the code...

                QSqlDatabase::database().transaction();
                QSqlQuery q;
                if (q.exec("ALTER TABLE public.mytable ADD newcol integer;"))
                    QSqlDatabase::database().commit();
                else
                {
                    qDebug() << "Error!" << q.lastError().text();
                    QSqlDatabase::database().rollback();
                }
    

    Thank you all so much for all your help.



  • @addebito
    I have never used, or even seen, PostgreSQL :)


Log in to reply