Important: Please read the Qt Code of Conduct -

Qt and SQLite can’t handle ‘NULL’ values or i’m doning something wrong?

  • 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" );;

    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 = QSqlQuery( "SELECT * FROM a", db );;
    // 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();

  • SQLite is generally untyped, does it even support "NOT NULL" columns?

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

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

  • As far as I know SQLite supports NULL entries.

    Have u tried the QVariant.isNull() method to check if the value is NULL?

Log in to reply