Qt and SQLite can’t handle ‘NULL’ values or i’m doning something wrong?
-
wrote on 2 Aug 2010, 13:05 last edited by
Hello.
If i create a table using Qt and SQLite that has a nullable columns (columns without "NOT NULL") and add a null value into it (using NULL QVariant) retrieving such value back will return not and NULL QVariant, but QVariant of type String and value "". Am i doing something wrong or Qt with SQLite can't distinguish between NULL value in database and default value (0, "", default date ect)?
Latest Qt for windows is used. My test code:@#include <QtCore/QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>
#include <QSqlRecord>int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
db.setHostName( "MyHost" );
db.setDatabaseName( "c:\tmp\db.sql" );
db.open();QSqlQuery( "CREATE TABLE a ( b INT )", db );
QSqlQuery query( db );
query.prepare( "INSERT INTO a ( b ) VALUES ( :b )" );
// Insert NULL value into table (NULL variant).
query.bindValue( ":b", QVariant( QVariant::Int ) );
query.exec();
query = QSqlQuery( "SELECT * FROM a", db );
query.next();
// Get value from table - it has type string and is "".
QVariant val = query.value( query.record().indexOf( "b" ) );
QVariant::Type type = val.type();
QString str = val.toString();
}
@ -
wrote on 3 Aug 2010, 09:01 last edited by
SQLite is generally untyped, does it even support "NOT NULL" columns?
-
wrote on 3 Aug 2010, 09:32 last edited by
Yes, as far as i know. If i change "b INT" to "b INT NOT NULL", insert with fail with "constraint failed" error message. Also, SQLite database browsers (such as Lita) shows cells with no value (for integer, browser will show 0 if 0 integer was added and will show empty cell if NULL value was added as shown in my example code).
-
wrote on 3 Aug 2010, 09:57 last edited by
I have checked the Qt code - for NULL fields where are no 'type', so Qt initialize them with @QVariant(QVariant::String)@
Correct code for retrieving NULL values will be ignoring value type and checking QVariant with isNull().
-
wrote on 3 Aug 2010, 11:08 last edited by
As far as I know SQLite supports NULL entries.
Have u tried the QVariant.isNull() method to check if the value is NULL?
1/5