Solved QSqlQuery "ALTER TABLE" PostgreSQL
-
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
-
@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?
-
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 commandfrom 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(); }
-
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 toprepare()
. 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 cannotALTER TABLE
in a "normal" prepared statement, like yours. You should indeed try executing directly, not viaprepare()
. -
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 :)