[Solved] SQLite and BLOB, problem with INSERT OR REPLACE



  • Hello, good afternoon,

    I'm trying to insert a QByteArray in a SQLite BLOB and returns me "Parameter count mismatch", the definition of the table is as follows:

    @
    CREATE TABLE geometria (clave VARCHAR(256) UNIQUE NOT NULL PRIMARY KEY, valor BLOB NULL)
    @

    and this is the code for storage:

    @
    bool Configuracion::almacenarGeometria(QString clave, const QByteArray valor)
    {
    if ( _conectada )
    {
    QSqlQuery qry(db);
    QString cons;

        cons  = "Insert Or Replace Into geometria (clave, valor) Values ";
        cons += "(\"" + clave + "\", :valQByteArray)";
    
        qry.prepare( cons );
        qry.bindValue(":valQByteArray", valor);
    
        if ( qry.exec(cons) )
            return true;
        else
            _error = qry.lastError().text();
    }
    else
        _error = "SQLite no está conectada";
    
    return false;
    

    }
    @

    This procedure works perfect for MySQL but not for SQLite.

    Can anyone help me?

    Thanks a lot

    Dario.



  • Can you try this?

    @bool Configuracion::almacenarGeometria(QString clave, const QByteArray valor)
    {
    if ( _conectada )
    {
    QSqlQuery qry(db);
    QString cons;

        cons  = "Insert Or Replace Into geometria (clave, valor) Values (:clave, :valQByteArray)";
    
        qry.prepare( cons );
        qry.bindValue(":clave", clave);
        qry.bindValue(":valQByteArray", valor);
    
        if ( qry.exec(cons) )
            return true;
        else
            _error = qry.lastError().text();
    }
    else
        _error = "SQLite no está conectada";
    
    return false;
    

    }
    @



  • Yes, got me the same message: "Parameter count mismatch"
    and with this also:

    @
    cons = "Insert Or Replace Into geometria (clave, valor) Values ";
    cons += "(?, ?)";

        qry.prepare( cons );
        qry.addBindValue(clave);
        qry.addBindValue(valor);
    

    @

    This is the header:

    @
    #ifndef CONFIGURACION_H
    #define CONFIGURACION_H

    #include <QObject>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QDir>
    #include <QByteArray>
    #include <QVariant>
    #include "configuracion.h"

    //---------------------------------------------------------
    class Configuracion : public QObject
    {
    Q_OBJECT

    QSqlDatabase        db;
    
    QString             dirAp;
    QString             _error;
    bool                _conectada;
    
    bool conectarSQLite(void);
    bool inicializarSQLite();
    

    public: //-------------------------------------------------

    explicit Configuracion(QString appdir,
                           QObject *parent = 0);
    
    const QByteArray obtenerGeometria(QString clave);
    bool almacenarGeometria(QString clave, const QByteArray valor);
    
    bool conectada(void)
    {
        return _conectada;
    }
    QString error(void)
    {
        return _error;
    }
    

    signals: //------------------------------------------------

    public slots: //-------------------------------------------

    };
    //---------------------------------------------------------
    #endif // CONFIGURACION_H

    @

    and this implementation:

    @
    #include "configuracion.h"

    //-----------------------------------------------------------------------------

    Configuracion::Configuracion(QString appdir, QObject *parent) :
    QObject(parent)
    {
    dirAp = appdir;

    _conectada = conectarSQLite();
    

    }

    //-----------------------------------------------------------------------------

    bool Configuracion::conectarSQLite()
    {
    bool inicializar = false;

    dirAp.append(QDir::separator()).append("config.dat");
    dirAp = QDir::toNativeSeparators(dirAp);
    
    QFile archbd(dirAp);
    inicializar = !archbd.exists();
    
    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName(dirAp);
    
    if ( db.open() )
    {
        if ( inicializar )
            return inicializarSQLite();
    
        return true;
    }
    else
        return false;
    

    }

    //-----------------------------------------------------------------------------

    bool Configuracion::inicializarSQLite()
    {
    QSqlQuery qry(db);
    QString cons;

    cons  = "CREATE TABLE IF NOT EXISTS geometria ";
    cons += "(clave VARCHAR(256) UNIQUE NOT NULL PRIMARY KEY,";
    cons += " valor BLOB NULL)";
    
    return qry.exec&#40;cons&#41;;
    

    }

    //-----------------------------------------------------------------------------
    //############################ P U B L I C O S ############################
    //-----------------------------------------------------------------------------

    const QByteArray Configuracion::obtenerGeometria(QString clave)
    {
    if ( _conectada )
    {
    QSqlQuery qry(db);
    QString cons;

        cons  = "Select valor From geometria Where clave = '" + clave + "'";
    
        if ( qry.exec&#40;cons&#41; )
        {
            qry.next();
    
            QByteArray ba = qry.value( 0 ).toByteArray();
    
            return ba;
        }
    }
    
    _error = db.lastError().text();
    
    return NULL;
    

    }

    //-----------------------------------------------------------------------------

    bool Configuracion::almacenarGeometria(QString clave, const QByteArray valor)
    {
    if ( db.open() )
    {
    QSqlQuery qry(db);
    QString cons;

        cons  = "Insert Or Replace Into geometria (clave, valor) Values ";
        cons += "(?, ?)";
    
        qry.prepare( cons );
        qry.addBindValue(clave);
        qry.addBindValue(valor);
    
        if ( qry.exec&#40;cons&#41; )
            return true;
        else
            _error = qry.lastError().text();
    }
    else
        _error = "SQLite no está conectada";
    
    return false;
    

    }

    //-----------------------------------------------------------------------------

    @

    Thanks for the help.

    Dario.



  • Which version of Qt do you using? On Linux or Windows?

    I've made a similar test.... and it works. I'm using Linux (Fedora 17 x86_64), and tested with
    Qt 5.0.0 (64bits) and 4.8.3 (64 bits)

    @#include <QtCore>
    #include <QCoreApplication>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QSqlResult>
    #include <QSqlRecord>

    #include <QDebug>

    int main(int argc, char *argv[])
    {
    //QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase ("QSQLITE");
    
    db.setDatabaseName(":memory:");
    
    db.open();
    
    QSqlQuery qry(db);
    QString cons;
    
    cons  = "CREATE TABLE IF NOT EXISTS geometria ";
    cons += "(clave VARCHAR(256) UNIQUE NOT NULL PRIMARY KEY,";
    cons += " valor BLOB NULL)";
    
    if (!qry.exec&#40;cons&#41;)
    {
        qDebug() << qry.lastError().text();
    }
    
    qry.clear();
    
    cons  = "Insert Or Replace Into geometria (clave, valor) Values (?, ?)";
    
    QString clave("Test");
    QByteArray valor("array or bytes");
    
    qry.prepare( cons );
    qry.addBindValue(clave);
    qry.addBindValue(valor);
    
    if (!qry.exec&#40;&#41;)
    {
        qDebug() << qry.lastError().text();
    }
    
    //Check the row inserted
    
    qry.clear();
    cons = "Select * from geometria";
    if (!qry.exec&#40; cons &#41;)
    {
        qDebug() << qry.lastError().text();
    }
    else
    {
        while (qry.next())
        {
            qDebug() << qry.record().value(0);
            qDebug() << qry.record().value(1);
        }
    }
    
    qry.clear();
    
    
    db.close();
    
    //return a.exec&#40;&#41;;
    

    }
    @



  • Hello TioRoy,

    I run your code without any problem, but the difference with my code is yours to create a database in memory and mine on the disk.

    My Version of Qt is the 4.8.4 32-bit on Windows 7 x 64

    Thanks for the reply,

    Best regards

    Dario.



  • In Ubuntu x64 with GNome 3.6 Qt 4.8.3 gcc 4.7.2 I have the same result: “Parameter count mismatch”



  • The QByteArray which I am trying to store is the output of MainWindow::saveState()

    @
    almacenarGeometria ("geometry", MainWindow::saveState ())
    @

    I do not believe that that influence something but I'll try with a picture to be sure.



  • The mainwindow.cpp:

    @
    #include "mainwindow.h"
    #include "ui_mainwindow.h"

    //-----------------------------------------------------------------------------

    MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
    {
    ui->setupUi(this);

    conf = NULL;
    
    connect(
                ui->btnConectarSQLite,
                SIGNAL(clicked()),
                this,
                SLOT(eventoBtnConectarSQLite())
           );
    connect(
                ui->btnObtGeom,
                SIGNAL(clicked()),
                this,
                SLOT(eventoBtnObtGeom())
           );
    connect(
                ui->btnAlmGeom,
                SIGNAL(clicked()),
                this,
                SLOT(eventoBtnAlmGeom())
           );
    

    }

    //-----------------------------------------------------------------------------

    MainWindow::~MainWindow()
    {
    if ( conf ) delete conf;

    delete ui;
    

    }

    //-----------------------------------------------------------------------------

    bool MainWindow::conectarSQLite()
    {
    conf = new Configuracion(QApplication::applicationDirPath());

    if ( conf->conectada() )
        return true;
    else
    {
        delete conf;
        conf = NULL;
        return false;
    }
    

    }

    //-----------------------------------------------------------------------------

    void MainWindow::eventoBtnConectarSQLite()
    {
    if ( conectarSQLite() )
    {
    ui->statusBar->showMessage("SQLite conectada e inicializada");
    ui->btnConectarSQLite->setEnabled(false);
    ui->btnObtGeom->setEnabled(true);
    ui->btnAlmGeom->setEnabled(true);
    }
    else
    {
    ui->statusBar->showMessage("Imposible conectar a SQLite");
    ui->btnConectarSQLite->setEnabled(true);
    ui->btnObtGeom->setEnabled(false);
    ui->btnAlmGeom->setEnabled(false);
    }
    }

    //-----------------------------------------------------------------------------

    void MainWindow::eventoBtnObtGeom()
    {
    if ( conf )
    {
    if ( !MainWindow::restoreState( conf->obtenerGeometria("geometria") ) )
    ui->text->appendPlainText( "No se pudo restaurar la geometría" );
    }
    else
    ui->text->appendPlainText( conf->error() );
    }

    //-----------------------------------------------------------------------------

    void MainWindow::eventoBtnAlmGeom()
    {
    if ( conf )
    {
    if ( !conf->almacenarGeometria("geometria", MainWindow::saveState()) )
    ui->text->appendPlainText( conf->error() );
    }
    else
    ui->text->appendPlainText( conf->error() );
    }

    //-----------------------------------------------------------------------------
    @

    and the mainwindow.h

    @
    #ifndef MAINWINDOW_H
    #define MAINWINDOW_H

    #include <QMainWindow>
    #include "configuracion.h"

    namespace Ui {
    class MainWindow;
    }

    //---------------------------------------------------------

    class MainWindow : public QMainWindow
    {
    Q_OBJECT

    Configuracion*      conf;
    
    bool conectarSQLite(void);
    bool inicializarSQLite(void);
    
    void cargarGeometria(void);
    void guardarGeometria(void);
    

    public: //-------------------------------------------------

    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();
    

    private: //------------------------------------------------

    Ui::MainWindow *ui;
    

    private slots:

    void eventoBtnConectarSQLite(void);
    void eventoBtnObtGeom(void);
    void eventoBtnAlmGeom(void);
    

    };

    //---------------------------------------------------------

    #endif // MAINWINDOW_H
    @



  • [quote author="ChanchoCiego" date="1357586418"]Hello TioRoy,
    I run your code without any problem, but the difference with my code is yours to create a database in memory and mine on the disk.[/quote]
    TioRoy's code works just fine either way.

    Is your actual database location writeable? I can reproduce similar errors by removing directory scan permissions from the folder containing the database file. For example, with the database in a folder "db/test.db":
    @
    chrisw@newton /tmp/test $ ls -ld db
    drwxr-xr-x 2 chrisw users 4096 Jan 8 07:33 db

    chrisw@newton /tmp/test $ ./test
    QVariant(QString, "Test")
    QVariant(QByteArray, "array or bytes")

    chrisw@newton /tmp/test $ chmod u-x db

    chrisw@newton /tmp/test $ ls -ld db
    drw-r-xr-x 2 chrisw users 4096 Jan 8 07:35 db

    chrisw@newton /tmp/test $ ./test
    QSqlQuery::exec: database not open
    " "
    QSqlQuery::prepare: database not open
    " Parameter count mismatch"
    " Parameter count mismatch"
    QSqlQuery::exec: database not open
    " "
    @

    On Windows trying to write the the "Program Files" tree or "Windows" directory will probably trigger a similar issue. To write Sqlite needs to be able to create temporary files in the same place as the database.



  • Hello ChrisW67,

    The permissions of folders both on Windows and on Linux are either assigned, and this I checked it in the procedure conectarSQLite that creates the database file with:

    @
    db = QSqlDatabase::addDatabase ("QSQLITE");
    db.setDatabaseName(dirAp);

    if ( db.open() )
        /* . . . */
    

    @

    Everytime I am by running the application I delete the config.dat file so db.open () re-create it

    Now the application is running in the debug directory, created by QtCreator with sufficient permissions

    Thanks for the reply,

    Best regards

    Dario.



  • Dario,

    The error is here:

    bq. @bool Configuracion::almacenarGeometria(QString clave, const QByteArray valor)
    {
    if ( db.open() ) //<----- HERE
    {
    QSqlQuery qry(db);
    QString cons;
    @

    You are executing open method twice.



  • Hi TioRoy, good afternoon,

    Yes, that is a mistake that I changed it to:

    @
    if ( db.isOpen() )
    @

    However it keeps telling me "Parameter count mismatch", now I'll try storing an image,

    Many thanks for the reply,

    Best regards

    Dario.



  • The storage of images goes very well, this is the code:

    @
    bool Configuracion::almacenarImagen(void)
    {
    if ( db.isOpen() )
    {
    QFile f("E:\Programacion\Pruebas Qt\ConfiguracionConSQLite-build-Desktop_VC_10-Debug\Imagen.jpg");

        if(f.open(QIODevice::ReadOnly))
        {
            QSqlQuery qry(db);
            QByteArray ba;
            ba = f.readAll();
            f.close();
    
            qry.prepare( "Insert Into geometria (clave, valor) Values (\"Imagen.jpg\", :imagen)" );
            qry.bindValue( ":imagen", ba);
    
            if ( qry.exec&#40;&#41; )
                return true;
            else
                return false;
        }
        else
            _error = "No se pudo abrir el archivo";
    }
    else
        _error = "SQLite no está conectada";
    
    return false;
    

    }
    @

    Then, I have a problem with the QByteArray sent him to SQLite, whose origin is the return of MainWindow:saveState () and this is according to the dump in the debugger:

    @
    Locals
    clave "geometria" QString
    cons "Insert Or Replace Into geometria (clave, valor) Values (?, ?)" QString
    qry class QSqlQuery QSqlQuery
    this @0x600f68 Configuracion *
    QObject "" QObject
    _conectada true bool
    _error "" QString
    db class QSqlDatabase QSqlDatabase
    dirAp "E:\Programacion\Pruebas Qt\ConfiguracionConSQLite-build-Desktop_VC_10-Debug\debug\config.dat" QString
    staticMetaObject struct QMetaObject QMetaObject
    staticMetaObjectExtraData struct QMetaObjectExtraData QMetaObjectExtraData
    valor @0x49998e0 "" QByteArray
    [0] 0 '\0' unsigned char
    [1] 0 '\0' unsigned char
    [2] 0 '\0' unsigned char
    [3] 255 'ÿ' unsigned char
    [4] 0 '\0' unsigned char
    [5] 0 '\0' unsigned char
    [6] 0 '\0' unsigned char
    [7] 0 '\0' unsigned char
    [8] 253 'ý' unsigned char
    [9] 0 '\0' unsigned char
    [10] 0 '\0' unsigned char
    [11] 0 '\0' unsigned char
    [12] 0 '\0' unsigned char
    [13] 0 '\0' unsigned char
    [14] 0 '\0' unsigned char
    [15] 2 unsigned char
    [16] 64 '@' unsigned char
    [17] 0 '\0' unsigned char
    [18] 0 '\0' unsigned char
    [19] 1 unsigned char
    [20] 188 '¼' unsigned char
    [21] 0 '\0' unsigned char
    [22] 0 '\0' unsigned char
    [23] 0 '\0' unsigned char
    [24] 4 unsigned char
    [25] 0 '\0' unsigned char
    [26] 0 '\0' unsigned char
    [27] 0 '\0' unsigned char
    [28] 4 unsigned char
    [29] 0 '\0' unsigned char
    [30] 0 '\0' unsigned char
    [31] 0 '\0' unsigned char
    [32] 8 unsigned char
    [33] 0 '\0' unsigned char
    [34] 0 '\0' unsigned char
    [35] 0 '\0' unsigned char
    [36] 8 unsigned char
    [37] 252 'ü' unsigned char
    [38] 0 '\0' unsigned char
    [39] 0 '\0' unsigned char
    [40] 0 '\0' unsigned char
    [41] 0 '\0' unsigned char
    Inspector
    Expressions
    Return Value
    Tooltip
    @

    I believed that "Insert Or Replace" was the problem but I removed "Or Replace" and obtained the same result, I guess that "Insert Or Replace" is supported by SQLite, but then will prove it.

    Apparently addBindValue not parse its parameter, and now arises me the problem of parsing and unparsing (is well said?) the QByteArray so it can be used by MainWindow:restoreState.

    Can you suggest me a way to follow?

    Many thanks for all the help,

    Best regards

    Dario.



  • I think the problem isn't with INSERT OR REPLACE.

    Can you send the prototype of saveState()? How the QByteArray is constructed? Is there some type of concurrency (multithreaded) ?

    I've tested some combinations of QByteArray and cannot reproduce your issue.

    I've checked the source qsql_slite.cpp (sqlite driver):

    @
    bool QSQLiteResult::exec()
    {
    const QVector<QVariant> values = boundValues();

    /* Some code */

    int paramCount = sqlite3_bind_parameter_count(d->stmt);
    if (paramCount == values.count()) {

    /* Some code */

    } else {
           setLastError(QSqlError(QCoreApplication::translate("QSQLiteResult",
                           "Parameter count mismatch"), QString(), QSqlError::StatementError));
           return false;
       }
    

    @

    For some reason:

    • Sqlite is returning wrong number of paramareters (this is not true, because my test and your test with image are working - same query)

    • boundValues() (that returns a map of the bound values) is not returning all values

    This is very strange.

    Put this line before exec:

    @qDebug() << qry.boundValues();
    @



  • Hi Tioroy, good afternoon,

    I send you the project: http://sddsoft.com.ar/descargas/ConfiguracionConSQLite.zip

    the return of qDebug is as follows:

    @
    QMap((":f", QVariant(QString, "geometria") ) ( ":fb" , QVariant(QByteArray, "
    @

    Thanks a lot

    Dario.



  • Dario,

    The problem is here:

    bq. @ QSqlQuery qry(db);
    QString cons;

        cons  = "Insert Or Replace Into geometria (clave, valor) Values ";
        cons += "(?, ?)";
    
        qry.prepare( cons );
        qry.addBindValue(clave);
        qry.addBindValue(valor);
    
        if ( qry.exec&#40;cons&#41; ) //<- here
    

    @

    When you call prepare method, you need to call exec with no parameter. Calling exec with parameterer, internally the QSqlQuery clears the boundValues, because you are passing another query to it.



  • Noooo please!

    That stupid error!

    Please, sorry for posting this!

    Thank you very much TioRoy!

    I regret the time you did lose,

    Best regards

    Dario.


Log in to reply
 

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