[SOLVED] Cannot create table with postgresql driver

[ashaihullin]

Hi.
I have an issue. So, when I try to execute table creation by sql query i have an error:
@Syntax error at end of input
Line 1: EXECUTE 1
^@

The query is:
@CREATE TABLE service_groups (ident text PRIMARY KEY,name text NOT NULL,active boolean);@

Here is little program reproduction:
@
#include <QCoreApplication>
#include <QSqlDatabase>
#include <Qt/qsqlquery.h>
#include <QSqlError>
#include <QDebug>

int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);

QSqlDatabase db = QSqlDatabase::addDatabase( "QPSQL" );
db.setHostName( "127.0.0.1" );
db.setPort( 5432 );
db.setDatabaseName( "selvic" );
db.setUserName( "selvic" );
db.setPassword( "selvic" );
bool okOpen = db.open();

if ( !okOpen )
{
    qDebug() << "not open" << db.lastError();
    exit(1);
}

QString m_schema = QString( "CREATE TABLE service_groups (ident text PRIMARY KEY,name text NOT NULL,active boolean);" );

// m_schema.append( "ident text PRIMARY KEY," );
// m_schema.append( "name text NOT NULL," );
// m_schema.append( "active boolean);" );

QSqlQuery tableQuery( m_schema, db );
bool ok = tableQuery.exec();
if ( !ok )
{
    qDebug() << tableQuery.lastError();
}

return a.exec&#40;&#41;;

}

@

Strange, but when I execute this code, the error exists, but table creates.
When I use such code in complex application error exists and table doesn't create.

My env:
@psql --version
psql (PostgreSQL) 9.2.4@

@/usr/lib/qt5/bin/qmake-qt5 --version
QMake version 3.0
Using Qt version 5.1.0 in /usr/lib@

Any suggestions?

[panosk]

Hi,

Just remove the semicolon from the end of your query string. QSqlQuery handles this for you.

[ashaihullin]

[quote author="panosk" date="1377884893"]Hi,

Just remove the semicolon from the end of your query string. QSqlQuery handles this for you.[/quote]

Unfortunately it doesn't help in my case. Still:
@2013-08-31T00:12:58.624370+06:00 ashaihullin postgres[19043]: [6-1] 2013-08-31 00:12:58 KGT selvic selvic ОТМЕТКА: оператор: CREATE TABLE service_groups (ident text PRIMARY KEY,name text NOT NULL,active boolean)
2013-08-31T00:12:58.708086+06:00 ashaihullin postgres[19043]: [7-1] 2013-08-31 00:12:58 KGT selvic selvic ОШИБКА: ошибка синтаксиса в конце (символ 9)
2013-08-31T00:12:58.708198+06:00 ashaihullin postgres[19043]: [7-2] 2013-08-31 00:12:58 KGT selvic selvic ОПЕРАТОР: EXECUTE@

PS Sorry for Russian locale in logs...

[panosk]

It shouldn't be a matter of a different QSqlQuery constructor, but anyway here's how I have all my queries with QPSQL in my own code and they work fine:
@
QSqlQuery tableQuery( db );
bool ok = tableQuery.exec(m_schema);
@

[ashaihullin]

[quote author="panosk" date="1377889281"]It shouldn't be a matter of a different QSqlQuery constructor, but anyway here's how I have all my queries with QPSQL in my own code and they work fine:
@
QSqlQuery tableQuery( db );
bool ok = tableQuery.exec(m_schema);
@

[/quote]
I'm not really sure why, but it works. Very strange that it doesn't work in my way. I guess this is due to some prepare state, may be cache.
Anyway, thank you panosk, for help!

[panosk]

According to the docs, it seems that the constructor you used "QSqlQuery":http://qt-project.org/doc/qt-5.0/qtsql/qsqlquery.html#QSqlQuery-2 actually executes the query (?). At least that's what the doc says but I've never used it so I have no clues how it actually behaves. Also, "QSqlQuery::exec()":http://qt-project.org/doc/qt-5.0/qtsql/qsqlquery.html#exec-2 needs a prepared statement in order to execute.

I don't know if your code can run in other dbs, but Postgre is very strict with such things. For example, Postgre won't permit a (meaningless) prepared statement with no value binding, but Sqlite has no problem executing it.

Anyway, glad you sorted it out, so don't forget to mark your post as [SOLVED].