Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to insert numbers in sqlite database?
Forum Updated to NodeBB v4.3 + New Features

How to insert numbers in sqlite database?

Scheduled Pinned Locked Moved Solved General and Desktop
10 Posts 5 Posters 1.6k Views 4 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A Offline
    A Offline
    Aromakc
    wrote on last edited by
    #1

    When I insert alphabets in integer column it sets to zero. How can I make it to show error when I insert non-numeric characters in integer column such as id, price, etc.

    {
    Login conn;
    QString type,brand,item;
    qint64 iid,price,quantity;
    iid=ui->lineEdit_iid->text().toInt();
    type=ui->lineEdit_type->text();
    brand=ui->lineEdit_brand->text();
    item=ui->lineEdit_item->text();
    price=ui->lineEdit_price->text().toInt();
    quantity=ui->lineEdit_qty->text().toInt();

    if (!conn.connOpen())
    {
        qDebug()<<("Failed to open database");
        return;
    }
    conn.connOpen();
    QSqlQuery qry;
    
    qry.prepare("INSERT INTO Inventory (ID,Type,Brand,Items,Price,Quantity) VALUES(?, ?, ?, ?, ?, ?)");
    
    qry.addBindValue(iid);
    qry.addBindValue(type);
    qry.addBindValue(brand);
    qry.addBindValue(item);
    qry.addBindValue(price);
    qry.addBindValue(quantity);
    
    if(qry.exec()){
        QMessageBox::information(this,tr("Save"),tr("Saved"));
    }
    else
    {
        QMessageBox::critical(this,tr("Error."),qry.lastError().text());     
    }
    

    }

    JonBJ 2 Replies Last reply
    0
    • sierdzioS Offline
      sierdzioS Offline
      sierdzio
      Moderators
      wrote on last edited by
      #2

      I don't think there is a way to warn/ crash when there is data type mismatch. You just need to be careful, test the app a lot, have unit testing etc. to make sure your queries run correctly.

      (Z(:^

      1 Reply Last reply
      2
      • A Aromakc

        When I insert alphabets in integer column it sets to zero. How can I make it to show error when I insert non-numeric characters in integer column such as id, price, etc.

        {
        Login conn;
        QString type,brand,item;
        qint64 iid,price,quantity;
        iid=ui->lineEdit_iid->text().toInt();
        type=ui->lineEdit_type->text();
        brand=ui->lineEdit_brand->text();
        item=ui->lineEdit_item->text();
        price=ui->lineEdit_price->text().toInt();
        quantity=ui->lineEdit_qty->text().toInt();

        if (!conn.connOpen())
        {
            qDebug()<<("Failed to open database");
            return;
        }
        conn.connOpen();
        QSqlQuery qry;
        
        qry.prepare("INSERT INTO Inventory (ID,Type,Brand,Items,Price,Quantity) VALUES(?, ?, ?, ?, ?, ?)");
        
        qry.addBindValue(iid);
        qry.addBindValue(type);
        qry.addBindValue(brand);
        qry.addBindValue(item);
        qry.addBindValue(price);
        qry.addBindValue(quantity);
        
        if(qry.exec()){
            QMessageBox::information(this,tr("Save"),tr("Saved"));
        }
        else
        {
            QMessageBox::critical(this,tr("Error."),qry.lastError().text());     
        }
        

        }

        JonBJ Online
        JonBJ Online
        JonB
        wrote on last edited by JonB
        #3

        @Aromakc
        If you think about, you will realize your Qt QSqlQuery code cannot possibly know that where you are passing an alphanumeric as a column value to INSERT in fact the underlying database is storing integers or whatever. Only the SQLite can recognise this, when it goes to perform the INSERT.

        When I insert alphabets in integer column it sets to zero. How can I make it to show error ...

        I am surprised SQLite does not error in this situation. Try it out in whatever SQLite offers as a "workbench" to do this from a GUI, or from the command-line, quite outside of Qt. Does it perhaps issue a warning message? Does it even do the INSERT, or does it ignore it?

        1 Reply Last reply
        1
        • mrjjM Offline
          mrjjM Offline
          mrjj
          Lifetime Qt Champion
          wrote on last edited by
          #4

          Hi
          You could start with using the Ok feature of toInt()
          bool ok;
          iid=ui->lineEdit_iid->text().toInt(&ok);
          Then you know that idd will be zero if ok is not true
          and avoid saving it to the database.

          Use a small helper function to make it more smooth

          A 1 Reply Last reply
          1
          • A Aromakc

            When I insert alphabets in integer column it sets to zero. How can I make it to show error when I insert non-numeric characters in integer column such as id, price, etc.

            {
            Login conn;
            QString type,brand,item;
            qint64 iid,price,quantity;
            iid=ui->lineEdit_iid->text().toInt();
            type=ui->lineEdit_type->text();
            brand=ui->lineEdit_brand->text();
            item=ui->lineEdit_item->text();
            price=ui->lineEdit_price->text().toInt();
            quantity=ui->lineEdit_qty->text().toInt();

            if (!conn.connOpen())
            {
                qDebug()<<("Failed to open database");
                return;
            }
            conn.connOpen();
            QSqlQuery qry;
            
            qry.prepare("INSERT INTO Inventory (ID,Type,Brand,Items,Price,Quantity) VALUES(?, ?, ?, ?, ?, ?)");
            
            qry.addBindValue(iid);
            qry.addBindValue(type);
            qry.addBindValue(brand);
            qry.addBindValue(item);
            qry.addBindValue(price);
            qry.addBindValue(quantity);
            
            if(qry.exec()){
                QMessageBox::information(this,tr("Save"),tr("Saved"));
            }
            else
            {
                QMessageBox::critical(this,tr("Error."),qry.lastError().text());     
            }
            

            }

            JonBJ Online
            JonBJ Online
            JonB
            wrote on last edited by JonB
            #5

            @Aromakc
            Further to @mrjj.
            Since you want columns qint64 iid,price,quantity to be integers, QLineEdit is probably not the right widget type to use anyway. Use a QSpinBox to ensure an integer is entered, or if you really want to stick with a QLineEdit then attach a Q[Int]Validator to ensure it only accepts a proper number.

            1 Reply Last reply
            2
            • mrjjM mrjj

              Hi
              You could start with using the Ok feature of toInt()
              bool ok;
              iid=ui->lineEdit_iid->text().toInt(&ok);
              Then you know that idd will be zero if ok is not true
              and avoid saving it to the database.

              Use a small helper function to make it more smooth

              A Offline
              A Offline
              Aromakc
              wrote on last edited by
              #6

              @mrjj I tried this option and used ok1 and ok2 in price and quantity and coded:
              if(ok1 && ok2) {....code...}
              else
              QmessageBox::information(this,invalid) and worked. I may learn a better way but thanks for now. :)

              mrjjM 1 Reply Last reply
              1
              • A Aromakc

                @mrjj I tried this option and used ok1 and ok2 in price and quantity and coded:
                if(ok1 && ok2) {....code...}
                else
                QmessageBox::information(this,invalid) and worked. I may learn a better way but thanks for now. :)

                mrjjM Offline
                mrjjM Offline
                mrjj
                Lifetime Qt Champion
                wrote on last edited by
                #7

                @Aromakc
                Hi
                Nothing wrong with that way as long you dont have many EditLines to verify as
                else you end up a lot of ok1,ok2,ok3 :)

                1 Reply Last reply
                0
                • KH-219DesignK Offline
                  KH-219DesignK Offline
                  KH-219Design
                  wrote on last edited by
                  #8

                  Caveat about SQLite and DB column types: SQLite itself does not enforce the column datatype you declare in your DDL statements ("CREATE TABLE" and friends)

                  See their FAQ, specifically "SQLite lets me insert a string into a database column of type integer!"
                  https://www.sqlite.org/faq.html#q3

                  See also: https://www.sqlite.org/quirks.html

                  So while you can certainly enforce in C++/Qt code whether some variable is numeric versus string, just be aware that once it is passed to the SQLite engine, the SQLite engine doesn't pay much attention to your typing.

                  (Sorry if I am stating what is already known and obvious to the crowd here. I have found that many people are surprised to learn this fact, so it seems prudent to mention in discussions of SQLite that involve column types.)

                  www.219design.com
                  Software | Electrical | Mechanical | Product Design

                  JonBJ sierdzioS 2 Replies Last reply
                  4
                  • KH-219DesignK KH-219Design

                    Caveat about SQLite and DB column types: SQLite itself does not enforce the column datatype you declare in your DDL statements ("CREATE TABLE" and friends)

                    See their FAQ, specifically "SQLite lets me insert a string into a database column of type integer!"
                    https://www.sqlite.org/faq.html#q3

                    See also: https://www.sqlite.org/quirks.html

                    So while you can certainly enforce in C++/Qt code whether some variable is numeric versus string, just be aware that once it is passed to the SQLite engine, the SQLite engine doesn't pay much attention to your typing.

                    (Sorry if I am stating what is already known and obvious to the crowd here. I have found that many people are surprised to learn this fact, so it seems prudent to mention in discussions of SQLite that involve column types.)

                    JonBJ Online
                    JonBJ Online
                    JonB
                    wrote on last edited by
                    #9

                    @KH-219Design
                    Oh, wow! Happy I have never used SQLite then. Sounds awful. That link says

                    This is a feature, not a bug.

                    I don't care what they say there. If you cannot at least change the behaviour to enforce by some setting, there must millions of SQLite databases out there with accidental strings sitting in numeric columns....

                    1 Reply Last reply
                    0
                    • KH-219DesignK KH-219Design

                      Caveat about SQLite and DB column types: SQLite itself does not enforce the column datatype you declare in your DDL statements ("CREATE TABLE" and friends)

                      See their FAQ, specifically "SQLite lets me insert a string into a database column of type integer!"
                      https://www.sqlite.org/faq.html#q3

                      See also: https://www.sqlite.org/quirks.html

                      So while you can certainly enforce in C++/Qt code whether some variable is numeric versus string, just be aware that once it is passed to the SQLite engine, the SQLite engine doesn't pay much attention to your typing.

                      (Sorry if I am stating what is already known and obvious to the crowd here. I have found that many people are surprised to learn this fact, so it seems prudent to mention in discussions of SQLite that involve column types.)

                      sierdzioS Offline
                      sierdzioS Offline
                      sierdzio
                      Moderators
                      wrote on last edited by
                      #10

                      @KH-219Design said in How to insert numbers in sqlite database?:

                      ee their FAQ, specifically "SQLite lets me insert a string into a database column of type integer!"

                      OMG o_O

                      (Z(:^

                      1 Reply Last reply
                      1

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved