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

Maarouf Med Mahdi

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.

Taz742

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

Maarouf Med Mahdi

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

Taz742

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

Maarouf Med Mahdi

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

Vinod Kuntoji

@Maarouf-Med-Mahdi ,

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

Maarouf Med Mahdi

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

JonB

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

Taz742

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

JonB

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

Vinod Kuntoji

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

Maarouf Med Mahdi

@Vinod-Kuntoji yes i m sure

Maarouf Med Mahdi

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

Maarouf Med Mahdi

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

Maarouf Med Mahdi

@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

Stoyan

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

Maarouf Med Mahdi

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

Maarouf Med Mahdi

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

Stoyan

@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';

Maarouf Med Mahdi

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