[Solved] SQLite and BLOB, problem with INSERT OR REPLACE
-
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_OBJECTConfiguracion* 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 dbchrisw@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 dbchrisw@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() ) 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(cons) ) //<- 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.