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.
    

  • Qt Champions 2016

    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.



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


  • Qt Champions 2016

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



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


  • Qt Champions 2016

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


  • Qt Champions 2016

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


  • Qt Champions 2016

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


Log in to reply
 

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