PDA

View Full Version : Attempting to use Sqlite backup api from driver handle fails



markktoo
18th November 2010, 18:00
I want to be able to either load or save db between memory and file. I have attempted to use the handle to sqlite3 api from the Qsqldatabase object.

I follow the example code to obtain the driver. The function is passed Qsqldatabase pointer for the two databases.

Showing snippet for one db to obtain handle:

v = db1->driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0)
// v.data() returns a pointer to the handle
pInMemory = *static_cast<sqlite3 **>(v.data());

After checking that both handles are not null, I decide which is the src and dest db and load the to/from sqlite3 pointers ( same as the backup example on Sqlite website)

sqlite3_backup_init(pTo, "main", pFrom, "main");

I receive the following error:

First-chance exception at 0x00000000 in xxx.exe: 0xC0000005: Access violation reading location 0x00000000.

Note: I am using the qt sqlite library. To get to the sqlite api I have also included the sqlite3 library in the build (Found couple of online references to doing this).

Can anyone shed light on this error when using the driver ahndle to sqlite api?

Thanks

chemmalion
30th November 2010, 14:28
To make SQLite in-memory load/save I use this steps:

1. Add two rows to .pro file:
INCLUDEPATH = $$[QT_INSTALL_PREFIX]/src/3rdparty/sqlite
SOURCES += $$[QT_INSTALL_PREFIX]/src/3rdparty/sqlite/sqlite3.c

2. Add include to file where I plan to use SQLite API:
#include <sqlite3.h>

3. Use function similar to this:
/*
** This function is used to load the contents of a database file on disk
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database,
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
bool sqliteDBMemFile( QSqlDatabase memdb, QString filename, bool save )
{
bool state = false;
QVariant v = memdb.driver()->handle();
if( v.isValid() && qstrcmp(v.typeName(),"sqlite3*") == 0 )
{
// v.data() returns a pointer to the handle
sqlite3 * handle = *static_cast<sqlite3 **>(v.data());
if( handle != 0 ) // check that it is not NULL
{
sqlite3 * pInMemory = handle;
const char * zFilename = filename.toLocal8Bit().data();
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup; /* Backup object used to copy data */
sqlite3 *pTo; /* Database to copy to (pFile or pInMemory) */
sqlite3 *pFrom; /* Database to copy from (pFile or pInMemory) */

/* Open the database file identified by zFilename. Exit early if this fails
** for any reason. */
rc = sqlite3_open( zFilename, &pFile );
if( rc==SQLITE_OK ){

/* If this is a 'load' operation (isSave==0), then data is copied
** from the database file just opened to database pInMemory.
** Otherwise, if this is a 'save' operation (isSave==1), then data
** is copied from pInMemory to pFile. Set the variables pFrom and
** pTo accordingly. */
pFrom = ( save ? pInMemory : pFile);
pTo = ( save ? pFile : pInMemory);

/* Set up the backup procedure to copy from the "main" database of
** connection pFile to the main database of connection pInMemory.
** If something goes wrong, pBackup will be set to NULL and an error
** code and message left in connection pTo.
**
** If the backup object is successfully created, call backup_step()
** to copy data from pFile to pInMemory. Then call backup_finish()
** to release resources associated with the pBackup object. If an
** error occurred, then an error code and message will be left in
** connection pTo. If no error occurred, then the error code belonging
** to pTo is set to SQLITE_OK.
*/
pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
if( pBackup ){
(void)sqlite3_backup_step(pBackup, -1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
}

/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);

if( rc == SQLITE_OK ) state = true;
}
}
return state;
}

ArKay
26th April 2011, 18:22
This also crashes on me at sqlite3_backup_init. To isolate the problem I have created a small test program. sqlite3.h & .c are taken from the Qt 4.7.1. archive (the version I am developing with), somehow those files were missing in the OpenSUSE 11.4 Qt Source package.

This code tries to do an online backup of an opened database, so it's not from memory.

==12265== Thread 2:
==12265== Invalid write of size 8
==12265== at 0x438492: pthreadMutexEnter (sqlite3.c:15266)
==12265== by 0x4382F0: sqlite3_mutex_enter (sqlite3.c:14537)
==12265== by 0x454E0A: sqlite3_backup_init (sqlite3.c:45601)



#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlDriver>
#include <QString>
#include <QVariant>
#include "sqlite3/sqlite3.h"

void backup(QString src, QString dst) {
QSqlDatabase sqlDb = QSqlDatabase::addDatabase("QSQLITE");
sqlDb.setDatabaseName(src);
sqlDb.open();

QVariant v = sqlDb.driver()->handle();
sqlite3* pSource = *static_cast<sqlite3 **>(v.data());

int rc;
sqlite3 *pDest;
sqlite3_backup *pBackup;
rc = sqlite3_open(dst.toLocal8Bit().data(), &pDest);
if(rc == SQLITE_OK) {
pBackup = sqlite3_backup_init(pDest, "main", pSource, "main");
if (pBackup) {
do {
rc = sqlite3_backup_step(pBackup, 5);
if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
sqlite3_sleep(250);
}
} while(rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED );

/* Release resources allocated by backup_init(). */
sqlite3_backup_finish(pBackup);
}
sqlite3_close(pDest);
}

sqlDb.close();
}


int main(int argc, char *argv[]) {
backup("/home/arkay/Projects/QtWebApp/database/Current/architektur.db",
"/home/arkay/Projects/QtWebApp/database/Backup/architektur.db");
}

ArKay
26th April 2011, 21:42
Problem solved :o

SUSE links the Qt sqlite plugin against a current sqlite release so I only had to use the source they supplied instead of the ancient one which comes with the Qt source distribution.

chemmalion
10th November 2011, 22:00
There is some corrected code from my previous post:
/*
** This function is used to load the contents of a database file on disk
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database,
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
bool sqliteDBMemFile( QSqlDatabase memdb, QString filename, bool save )
{
bool state = false;
QVariant v = memdb.driver()->handle();
if( v.isValid() && qstrcmp(v.typeName(),"sqlite3*") == 0 )
{
// v.data() returns a pointer to the handle
sqlite3 * handle = *static_cast<sqlite3 **>(v.data());
if( handle != 0 ) // check that it is not NULL
{
sqlite3 * pInMemory = handle;
QByteArray array = filename.toLocal8Bit();
const char * zFilename = array.data();
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup; /* Backup object used to copy data */
sqlite3 *pTo; /* Database to copy to (pFile or pInMemory) */
sqlite3 *pFrom; /* Database to copy from (pFile or pInMemory) */

/* Open the database file identified by zFilename. Exit early if this fails
** for any reason. */
rc = sqlite3_open( zFilename, &pFile );
if( rc==SQLITE_OK ){

/* If this is a 'load' operation (isSave==0), then data is copied
** from the database file just opened to database pInMemory.
** Otherwise, if this is a 'save' operation (isSave==1), then data
** is copied from pInMemory to pFile. Set the variables pFrom and
** pTo accordingly. */
pFrom = ( save ? pInMemory : pFile);
pTo = ( save ? pFile : pInMemory);

/* Set up the backup procedure to copy from the "main" database of
** connection pFile to the main database of connection pInMemory.
** If something goes wrong, pBackup will be set to NULL and an error
** code and message left in connection pTo.
**
** If the backup object is successfully created, call backup_step()
** to copy data from pFile to pInMemory. Then call backup_finish()
** to release resources associated with the pBackup object. If an
** error occurred, then an error code and message will be left in
** connection pTo. If no error occurred, then the error code belonging
** to pTo is set to SQLITE_OK.
*/
pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
if( pBackup ){
(void)sqlite3_backup_step(pBackup, -1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
}

/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);

if( rc == SQLITE_OK ) state = true;
}
}
return state;
}
The bug was located in this old line:
const char * zFilename = filename.toLocal8Bit().data();It is example of wrong using of QByteArray temporary object generated by toLocal8Bit() method. The data() method returns point to temporary object, that can be destroyed at any next moment. So in corrected code of this post I use copy of temporary QByteArray object to solve some potential problems.

Both code examples (in this post and in my previous post in this forum's thread) are published under public domain license. Main part of code was used from SQLite documentation (http://www.sqlite.org/backup.html).

paulanon
5th February 2013, 09:13
This is a fairly old thread, but hoping someone's still reading this.
I've followed the code by chemmalion (10th November 2011), which works well. However, if I try to use sqlite3_exec(), I get an exception: "Unhandled exception at 0x778f15de in app.exe: 0xC0000005: Access violation." Oddly, if I call the sqliteDBMemFile() before hand, I do not get the exception. Here's the code:



QSqlDatabase db;
db = QSqlDatabase::addDatabase("QSQLITE", "testsqliteptr");
db.setDatabaseName("blabla.sqlite");
if (!db.open()) {
return -1;
}
//Uncommenting line below prevents error at <tag_error>. What's this function doing that prevents the error?
//sqliteDBMemFile(db, "newfile.sqlite", true);
v = db.driver()->handle();
sqlite3* sql3_db = *static_cast<sqlite3 **>(v.data());
//<tag_error>
sqlite3_exec(sql3_db, "CREATE TABLE if not exists ABC(foo,bar)", 0, 0, 0);

Any idea as to what's going on? How stable is it to use both handles at the same time? I prefer to use QSql but require sqlite3* handle for a library I'm using.

jimmytaker
12th June 2016, 05:39
The answer is already here, but not written explicitly. THE call that makes the difference between crash and no crash is sqlite3_open. Apparently having the sqlite3.dll plugin on one side and the sqlite3.c compiled in on the other (to be able to call any of the sqlite3 API directly messes up or misses some init. So basically what is needed is:
1. Include sqlite code (.c and .h files) in your project
2. Use this snippet after m_Database.open()::


QVariant v = m_Database.driver()->handle();
if (v.isValid() && strcmp(v.typeName(), "sqlite3*") == 0) {
// v.data() returns a pointer to the handle
sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
if (handle != 0) { // check that it is not NULL
sqlite3 *p; //without this there is a crash.
int result = sqlite3_open( ":memory:", &p );
if (result == SQLITE_OK) {
sqlite3_close(p);
//call any API you need on handle.
} else
qDebug() << "Could not sqlite3_open p" << result;
} else {
qDebug() << "Could not get sqlite handle";
}
} else {
qDebug() << "handle variant returned typename " << v.typeName();
}

mohannad
11th May 2017, 01:09
hello
I still having this problem
with throw exception at this section:

SQLITE_PRIVATE void sqlite3WalEndReadTransaction(Wal *pWal){
sqlite3WalEndWriteTransaction(pWal);
if( pWal->readLock>=0 ){
walUnlockShared(pWal, WAL_READ_LOCK(pWal->readLock));
pWal->readLock = -1;
}
}


and I attempts all your answers

here my code

QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE");

m_db.setDatabaseName(":memory:");
m_db.open();
qDebug() << "create: " << m_db.driverName();

// create a table in the memory DB
QSqlQuery q_create = m_db.exec("CREATE TABLE qdn (id int, name varchar(50))");
qDebug() << "create: " << q_create.lastError();

// populate with some data
QSqlQuery q_insert(m_db);
q_insert.prepare("INSERT INTO qdn (id, name) VALUES (:id, :name)");
q_insert.bindValue(":id", QVariant::fromValue(1));
q_insert.bindValue(":name", "Volker");
qDebug() << "insert volker: " << q_insert.exec();

q_insert.bindValue(":id", QVariant::fromValue(2));
q_insert.bindValue(":name", "Root");
qDebug() << "insert root: " << q_insert.exec();

// get the inner sqlite3 handle(as QVariant)
QVariant v = m_db.driver()->handle();
//// check its validity
if (v.isValid() && strcmp(v.typeName(), "sqlite3*") == 0)
{

// v.data() returns a pointer to the handle
// it is valid, so cast to sqlite3 handle
sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
if (handle != 0)
{

loadOrSaveDb(handle, "d:/backupfrommemory.sqlite", 1);
}
else
{
qDebug() << "Could not get sqlite handle";
}
}

else
{
qDebug() << "handle variant returned typename " << v.typeName();
}
}

also I tried with sqliteDBMemFile as answer but also same error ... so please if I can find help
with thanks