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

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.