How to insert numbers in sqlite database?

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

}

[sierdzio]

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.

[JonB]

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

[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

[JonB]

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

[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. :)

[mrjj]

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

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

[JonB]

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

[sierdzio]

@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