QSqlQuery "ALTER TABLE" PostgreSQL

addebito

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!

mrjj

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

addebito

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

mrjj

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

artwaw

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?

Christian Ehrlicher

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

addebito

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();
}

Christian Ehrlicher

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

JonB

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

addebito

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.

JonB

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