Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlQuery "ALTER TABLE" PostgreSQL
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery "ALTER TABLE" PostgreSQL

Scheduled Pinned Locked Moved Solved General and Desktop
11 Posts 5 Posters 1.2k Views 3 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A Offline
    A Offline
    addebito
    wrote on 17 Mar 2021, 17:11 last edited by
    #1

    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!

    1 Reply Last reply
    0
    • M Offline
      M Offline
      mrjj
      Lifetime Qt Champion
      wrote on 17 Mar 2021, 17:40 last edited by
      #2

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

      1 Reply Last reply
      0
      • A Offline
        A Offline
        addebito
        wrote on 17 Mar 2021, 17:44 last edited by
        #3

        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
        
        M 1 Reply Last reply 17 Mar 2021, 18:34
        0
        • A addebito
          17 Mar 2021, 17:44

          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
          
          M Offline
          M Offline
          mrjj
          Lifetime Qt Champion
          wrote on 17 Mar 2021, 18:34 last edited by
          #4

          @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 ?

          A 1 Reply Last reply 18 Mar 2021, 17:49
          2
          • A Offline
            A Offline
            artwaw
            wrote on 17 Mar 2021, 19:20 last edited by
            #5

            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?

            For more information please re-read.

            Kind Regards,
            Artur

            1 Reply Last reply
            2
            • C Offline
              C Offline
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on 17 Mar 2021, 20:04 last edited by
              #6

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

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              1 Reply Last reply
              4
              • M mrjj
                17 Mar 2021, 18:34

                @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 ?

                A Offline
                A Offline
                addebito
                wrote on 18 Mar 2021, 17:49 last edited by
                #7

                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();
                }
                
                JonBJ 1 Reply Last reply 18 Mar 2021, 18:35
                0
                • C Offline
                  C Offline
                  Christian Ehrlicher
                  Lifetime Qt Champion
                  wrote on 18 Mar 2021, 18:35 last edited by
                  #8

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

                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                  Visit the Qt Academy at https://academy.qt.io/catalog

                  1 Reply Last reply
                  2
                  • A addebito
                    18 Mar 2021, 17:49

                    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();
                    }
                    
                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on 18 Mar 2021, 18:35 last edited by JonB
                    #9

                    @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().

                    1 Reply Last reply
                    5
                    • A Offline
                      A Offline
                      addebito
                      wrote on 18 Mar 2021, 20:08 last edited by
                      #10

                      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.

                      JonBJ 1 Reply Last reply 18 Mar 2021, 20:09
                      2
                      • A addebito
                        18 Mar 2021, 20:08

                        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.

                        JonBJ Offline
                        JonBJ Offline
                        JonB
                        wrote on 18 Mar 2021, 20:09 last edited by
                        #11

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

                        1 Reply Last reply
                        2

                        1/11

                        17 Mar 2021, 17:11

                        • Login

                        • Login or register to search.
                        1 out of 11
                        • First post
                          1/11
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • Users
                        • Groups
                        • Search
                        • Get Qt Extensions
                        • Unsolved