Create sqlite table is not work.
-
I have a method that make a connection to SQLITE and work with database by conditions (insert data or create table in my database.)
When I call it in my code, at first open database correctly but can not create tables.
this is my method :
void SQL(string cmnd) { QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//not dbConnection QString path = "DBAbsent"; db.setDatabaseName(path); QSqlQuery q(db); if(!db.open()) { printf("Error to open database"); } else { if (cmnd=="createTable") { q.prepare("create table IF NOT EXISTS Personel (P_id int primary key, " "CardID varchar(50), " "name varchar(50));"); if(!q.exec()) { qDebug()<<strerror(errno); qDebug()<<q.lastError(); // show this error : QSqlError("", "Unable to fetch row", "No query") } q.prepare("create table IF NOT EXISTS Absent " "(P_id int , " "DateTime varchar(50), " "key1 int,key2 int,key3 int,key4 int);"); if(!q.exec()) qDebug()<<strerror(errno); db.close(); return; } else if (cmnd=="Register") { string card=ReadFromCard(); printf("\nCard Accepted : %s\nEnter Your name: ",card.c_str()); string name; int id; //string dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"); cin>>name; printf("\n Name Accepted.\nEnter Your Personel ID: "); cin>>id; q.prepare( "SELECT count(P_id) FROM Personel WHERE P_id =?;" ); q.bindValue(0,id); if( !q.exec() ) { printf("\nThe error occured from P_ID database"); qDebug()<<strerror(errno); //ShowMainMenu(); //return; } // Note: if you don't return in case of an error, put this into the else{} part while( q.next() ) { if (q.value(0).toInt()>0) { printf("\nThis personel ID was repeated\n"); break; } else { q.prepare("SELECT CardID FROM Personel WHERE CardID =?;"); q.bindValue(0,card.c_str()); q.exec(); while(q.next()) { printf("\nThis card is not valid : it was repeated\n"); break; } } } q.prepare("insert into Personel value(?,?,?);"); q.bindValue(0,id); q.bindValue(1,card.c_str()); q.bindValue(2,name.c_str()); q.exec(); printf("\nInsert Personel complete\n"); db.close(); return; } else { string card; card=cmnd; QString dt=QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"); q.prepare("SELECT P_id FROM Personel WHERE CardID =?;"); q.bindValue(0,card.c_str()); if( !q.exec() ) { printf("\nThe error occured from database418\n"); ShowMainMenu(); } string result=("\n CARD NOT VALID =>repeated %s\n"); // Note: if you don't return in case of an error, put this into the else{} part while( q.next() ) { int P_id = q.value(0).toInt(); q.prepare("insert into Absent(P_id,DateTime,key1,key2,key3,key4) value(?,?,1,0,0,0);"); q.bindValue(0,P_id); q.bindValue(1,dt); q.exec(); result=("\n********WELL COME **********%s\n"); } printf("%s",result); db.close(); return; } } }And this is my output :
0 - Press 0 to Back 1 - Press 1 to Register the card 1 //the below lines when I want to select something from both tables , occurred No such file or directory QSqlError("", "Unable to fetch row", "No query") No such file or directory //the below line is when the if(cmnd="Register") {..} block is running , occurred. QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed. -
hi
if you
qDebug() <<"prepare: " << q.prepare("create table xxxxxYou will see it return false which means it did not like you statement.
So that is good place to start debugging your function. -
@mrjj : it shows me this => prepare: false.
what does it mean? when I copy the line in terminal sqlite3 it works. -
@MhM93
hi
you seems to be using this code
http://stackoverflow.com/questions/27844759/how-to-create-a-sqlite-database-in-qtHave you tried a the sample and see if it works as is?
-
Hi
this works for me.
but first when i set path to real path
So maybe that could also be your issue.
Note I use e:/ so if u dont have that drive,sample will not "just " work.void SQL(std::string cmnd) { QString path = "e:/MYDB"; QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//not dbConnection db.setDatabaseName(path); db.open(); QSqlQuery query; int res=query.exec("create table person " "(id integer primary key, " "firstname varchar(20), " "lastname varchar(30), " "age integer)"); qDebug() << "exec :" << query.lastError(); } -
thanks. It resolved :
q.prepare just uses for SQL query contains** bindvalue()**, like select() or **insert() **.
for create table just use query.exec()
