Create sqlite table is not work.

[MhM93]

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.

[mrjj]

hi
if you
qDebug() <<"prepare: " << q.prepare("create table xxxxx

You will see it return false which means it did not like you statement.
So that is good place to start debugging your function.

[MhM93]

@mrjj : it shows me this => prepare: false.
what does it mean? when I copy the line in terminal sqlite3 it works.

[mrjj]

@MhM93
docs says
Prepares the SQL query query for execution. Returns true if the query is prepared successfully; otherwise returns false.

So for some reason it dont like it.

[MhM93]

@mrjj
thanks. How can I find the problem in prepare statement ?

[mrjj]

@MhM93
hi
you seems to be using this code
http://stackoverflow.com/questions/27844759/how-to-create-a-sqlite-database-in-qt

Have you tried a the sample and see if it works as is?

[mrjj]

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

[MhM93]

thanks. It resolved :
q.prepare just uses for SQL query contains** bindvalue()**, like select() or **insert() **.
for create table just use query.exec()

[mrjj]

@MhM93
super. I was not sure if illigal to use prepare without bindings/select but
it was.
Thank for the update