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

TioRoy

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&#40;cons&#41; )
        return true;
    else
        _error = qry.lastError().text();
}
else
    _error = "SQLite no está conectada";

return false;

}
@

ChanchoCiego

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.

TioRoy

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;;

}
@

ChanchoCiego

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.

ChanchoCiego

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

ChanchoCiego

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.

ChanchoCiego

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
@

ChrisW67

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

ChanchoCiego

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.

TioRoy

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.

ChanchoCiego

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.

ChanchoCiego

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.

TioRoy

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();
@

ChanchoCiego

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.

TioRoy

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.

ChanchoCiego

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.