[SOLVED] Cannot create table with postgresql driver



  • 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&#40;&#41;;
    if ( !ok &#41;
    {
        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?



  • Hi,

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



  • [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...



  • 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 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!



  • 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].


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.