The SQLite exception "no such table: <table> Unable to execute statement" with QSqlDatabase



  • welcome,
    I opened my database sqlite in Qt without error and I checked that all the tables are well created in the base and well the path of the base. but when I try to execute a query I get an error (no such table: <table> Unable to execute statement).
    I am sure that I connected to a real base and that it contains all the table .
    Someone you can help me please.



  • @Maarouf-Med-Mahdi
    Can you post your query?



  • @Taz742

    // --connecting data base code :
    
       mydb=QSqlDatabase::addDatabase("QSQLITE");
    
        mydb.setDatabaseName("D:\Applications\App Gestion Data CenterV2\GestionDataCenterV2\GestionDataCenter_BD.db");
        if(mydb.open())
            qDebug() << "connected";
        else
            qDebug() << "deconnected";
    
    // --executing query code : 
     QSqlQuery requette;
     QString text_req = "insert into serveurs values('123.112.113','Phisique',1,2,3,3,'01/01/1997','12/01/1997');";
    
     if(requette.exec(text_req))
         qDebug()<<"requette valide";
     else
         qDebug()<<"invalide requette"<<" --> "<<requette.lastError().text();
    


  • @Maarouf-Med-Mahdi said in How insert data into sqlite database with Qt:

    insert into serveurs values('123.112.113','Phisique',1,2,3,3,'01/01/1997','12/01/1997');"

    It is a valid query?
    Does this query work in sql without Qt?



  • Yes , i tested the same query before execute with Qt .



  • @Maarouf-Med-Mahdi ,

    Path of DB file contains some spaces. Remove those spaces and try.



  • @Vinod-Kuntoji
    qt connected well to my database and i have run other query like
    select * from sqlite_master where tbl_name = '<table>' "; it works to show you that the path of bd is valid,



  • @Maarouf-Med-Mahdi
    You said:

    (no such table: <table> Unable to execute statement).

    So it's telling you there is no table named whatever it reports for <table> (really wish you'd give the actual text of the error message, then we'd know....)

    Your statement is:
    insert into serveurs ...
    so, assuming it reports serveurs as the table name, there is no such table as serveurs in whatever is your current database....

    P.S.
    Have you tried SELECT * FROM serveurs in your Qt code for yourself, to decide whether it's really an INSERT problem?



  • @Maarouf-Med-Mahdi
    Try:

    QSqlQuery query;
    query.prepare("insert into serveurs (field1, field2, field3) values (:value1, :value2, :value3)");
    query.bindValue(":value1," MyValue1);
    query.bindValue(":value2", MyValue2);
    query.bindValue(":value3", MyValue3);
    
    if (query.exec()) {
        //
    } else {
        qDebug() << query.lastError().text();
    }
    


  • @Taz742
    https://sqlite.org/lang_insert.html is telling you that the list of column/field names is optional in INSERT INTO, if that's what you're trying to tell the OP to correct to.



  • @Maarouf-Med-Mahdi ,
    Are you sure, your table name is correct?



  • @Vinod-Kuntoji yes i m sure



  • @JNBarchan that is my dataBase file

    -- Fichier généré par SQLiteStudio v3.1.1 sur mer. déc. 13 21:27:34 2017

    -- Encodage texte utilisé : System

    PRAGMA foreign_keys = off;
    BEGIN TRANSACTION;

    -- Table : historiques
    CREATE TABLE historiques(
    IP varchar(20) ,
    date_modification date,
    anc_type_serveur varchar(10),
    anc_croire int (10),
    anc_rack int(10),
    anc_chassis int (4),
    anc_position int (10),
    constraint FK_historiques foreign key (IP) references serveurs(IP)
    );

    -- Table : serveurs
    CREATE TABLE serveurs (
    IP varchar(20) ,
    type_serveur varchar(10),
    croire int (10),
    rack int(10),
    chassis int (4),
    position int (10),
    date_creation date,
    date_der_accee date,
    constraint PK_serveurs primary key (IP)
    );

    COMMIT TRANSACTION;
    PRAGMA foreign_keys = on;



  • @JNBarchan I give you the result of this query
    select * from serveurs;
    ***the code:
    mydb.setDatabaseName("D:\Applications\App Gestion Data CenterV2\GestionDataCenterV2\GestionDataCenter_BD.db");
    if(mydb.open())
    qDebug() << "connected";
    else
    qDebug() << "deconnected";

    QSqlQuery requette;
    QString text_req = "select * from serveurs;";
    if(requette.exec(text_req))
    qDebug()<<"requette valide";
    else
    qDebug()<<"invalide requette"<<" --> "<<requette.lastError().text();
    ***the debug
    connected
    invalide requette --> "no such table: serveurs Unable to execute statement"



  • @Taz742 this query shows me the same error
    QString text_req = "insert into serveurs('IP','type_serveur','croire','rack','chassis','position','date_creation','date_der_accee') values('123.112.113','Physique',1,2,3,3,'01/01/1997','12/01/1997');";

    // I wish you saw my database I posted it



  • @Maarouf-Med-Mahdi
    This message also prove that there is no such table in your database.
    You can check which tables are available with this code:

    if(mydb.open())
            qDebug() << "connected";
        else
            qDebug() << "deconnected";
    qDebug() << "Tables: " << mydb.tables();
    

    Maybe there is a difference in some letter in other code page (UTF-8 vs ANSI).



  • Hi @Stoyan that show me (), i think you saw my db i posted it ,
    all tables has been created but Qt can not find it



  • @Stoyan previously I used the encoding "system", now I've changed to UTF-8 but the same error still see



  • @Maarouf-Med-Mahdi
    It seems that you are not connected to the database.
    Try to change your code for open database.
    Instead:

    if(mydb.open())
            qDebug() << "connected";
        else
            qDebug() << "deconnected";
    

    use this:

        if(!mydb.open())
        {
            qDebug() << mydb.lastError().text();
        }
    

    BTW, what is the result from query that you use to test database connection:

    select * from sqlite_master where tbl_name = 'serveurs';
    


  • @Stoyan connected and that query works
    select * from sqlite_master where tbl_name = 'serveurs';



  • @Maarouf-Med-Mahdi
    OK, but what is the result? Empty set or 2-3 rows?



  • In SQLite a database is a single file. And I think that, if database file don't exists it is created and practically the connection is made to new empty database.
    You can search on your computer for file with the name "GestionDataCenter_BD.db". I guess that there will be another such file in other folder than original database.



  • @Stoyan i understood what you said, but i m sure i connected to the real database from a real path
    mydb.setDatabaseName("D:\Applications\App Gestion Data CenterV2\GestionDataCenterV2\GestionDataCenter_BD.db");0_1513280011928_QSL2.PNG



  • I think the answer is quite simple:
    use double backslash!

    instead of

     mydb.setDatabaseName("D:\Applications\App Gestion Data CenterV2\GestionDataCenterV2\GestionDataCenter_BD.db");
    
    use
    
     mydb.setDatabaseName("D:\\Applications\\App Gestion Data CenterV2\\GestionDataCenterV2\\GestionDataCenter_BD.db");
    

    regards
    Karl-Heinz



  • Hi @karlheinzreichel , i got that message
    connected
    invalide requette --> "file is encrypted or is not a database Unable to execute statement"



  • @Maarouf-Med-Mahdi said in How insert data into sqlite database with Qt:

    (no such table: <table> Unable to execute statement).

    But before you get the error:
    (no such table: <table> Unable to execute statement).

    now you get
    file is encrypted or is not a database Unable to execute statement

    it's somehow different.

    Have you tried to test the database with sqlite3 database application?



  • @karlheinzreichel
    all queries it works well with SQLiteStudio and has the same database



  • @Maarouf-Med-Mahdi
    I'm sure there is such database in correct path. But did you found another file with the same name in other directory?
    Also, you should use '/' as directory separator:

    Qt uses "/" as a universal directory separator in the same way that "/" is used as a path separator in URLs. If you always use "/" as a directory separator, Qt will translate your paths to conform to the underlying operating system.
    


  • @Maarouf-Med-Mahdi
    With SQLiteStudio you are connecting to real database and everything works.
    With Qt you are trying to connect to wrong database and new database file is created and connected. This database is empty and that's why there is no tables in it.
    Execute this query with SQLiteStudio and with Qt and compare the results.

    select * from sqlite_master where tbl_name = 'serveurs';
    

    And how much files with name "GestionDataCenter_BD.db" did you found in your computer?



  • I copied the same database (exported) and put it in the directory of my project. and I checked with notepade that all the tables are created, in the end I gave it to QT by the way of this last database



  • @Stoyan that query
    select * from sqlite_master where tbl_name = 'serveurs';
    works in the both



  • @Maarouf-Med-Mahdi
    It always works. The question is how many rows returns in each case.



  • @Stoyan -1
    QString text_req = "select * from sqlite_master where tbl_name='serveurs'";
    if(requette.exec(text_req))
    qDebug()<<"requette valide"<<requette.size();
    else
    qDebug()<<"invalide requette"<<" --> "<<requette.lastError().text();
    connected
    requette valide -1



  • @Maarouf-Med-Mahdi
    Unfortunately, SQLite doesn't support QSqlQuery::size() function.
    Instead you can use this:

    if(requette.exec(text_req))
    {
        requette.last();
        qDebug() << requette.at() + 1;
    }


  • @Stoyan that give me -1 again



  • @Maarouf-Med-Mahdi
    You should just change directory separator in your code from "\" to "/".
    I make some tests with this code:

        QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
        mydb.setDatabaseName("C:\Projects\Test\BD.db");
        if(!mydb.open())
        {
            qDebug() << mydb.lastError().text();
        }
        qDebug() << "Tables: " << mydb.tables();
        quint16 cnt = 0;
         QSqlQuery q;
         QString text_req = "select * from sqlite_master where tbl_name='serveurs'";
         if(q.exec(text_req))
         {
             while(q.next())
             {
                 cnt++;
             }
             qDebug() << "Records count: " << cnt;
         }
         else
             qDebug()<<"Invalide query"<<" --> " << q.lastError().text();
    

    On compile it give warnings:

    warning: unknown escape sequence: '\P'
    warning: unknown escape sequence: '\T'
    warning: unknown escape sequence: '\B'
    

    The result was this (just like yours):

    Tables:  ()
    Records count:  0
    

    When I change this row:

        mydb.setDatabaseName("C:\Projects\Test\BD.db");
    

    with this:

        mydb.setDatabaseName("C:/Projects/Test/BD.db");
    

    there are no warnings, everything works and the result is:

    Tables:  ("serveurs", "historiques")
    Records count:  2
    


  • Just I want to give my experience result or a advice about this and to don't repeate to other members.
    Sometimes, Qt does not accept an absolute path of the database file if it is not in the
    same output program file (Debug or Release).
    If Qt can not recover your database file, it will create a new empty database in the same output program folder. this can make the not such table sql error .
    I want to thank all the members they help me for finding my proposed solution.


  • Lifetime Qt Champion

    Hi,

    That's unrelated to Qt. It's how SQLite behaves. If there's no file matching the parameter you give it, it will create it. What you can do is check the file existence with QFile.



  • Hi @SGaist
    Thanks for that SQLite information . I checked that .
    all what i did to find that solution, i have moved the DataBase file to the output folder .


Log in to reply
 

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