Help With SQLite_Backup

[leegold]

Hi,

I to access a DB every few seconds so I thought to take the file DB "mirror"/copy it to a memory DB and check the memory DB every few seconds. sqlitebackup.au3 seems an easy way to do this - to make the memory copy. But I must be missing something when I try a simple test because I didn't get anything from a select statement on the memory DB. But when I substitute the disk DB with the same code it works. I tried:

...

...

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <sqlitebackup.au3>

$sSQliteDll = _SQLite_Startup()
$sDbName = "C:\sqlite\relays.db"
$hDskDb = _SQLite_Open($sDbName)
Local $aRow

Local $hmemDb = _SQLite_Backup($hDskDb, ':memory:', Default, Default, Default, Default, Default)

Local $d = _SQLite_Exec($hmemDb, "Select * From sched", "_cb")    ; this does not work
; Local $d = _SQLite_Exec($hDskDb, "Select * From sched", "_cb")  ; this works

Func _cb($aRow)
    For $s In $aRow
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@CRLF)
EndFunc   ;==>_cb
; ...
; ...
_SQLite_Close()
_SQLite_Shutdown()

I guess my questions is - What am I doing wrong? Thanks.

[kylomas]

leegold,

Are you sure it is even running.  When I run it (under SCiTE) I get

! Au3check doesn't support input files encoded as UTF8 with BOM:

followed by

"C:UsersADMIN010Desktopsqlite backup.au3"(11,96) : error: _SQLite_Backup(): undefined function.

 

The version of _SQLite_Backup that I am using is dated 2010-03-08 (author - jchd)

Also,

I don't see anything in the UDF that suggests that ':memory:' is valid as the 2ND parm (destination database filename). 

Once you get the script to run you can add error checking after the function call.  I do not know what the UTF8/BOM message means. 

kylomas

edit: running 3.3.10

Edited December 27, 2013 by kylomas

[jchd]

kylomas,

":memory:" indeed is a valid pseudo-filename for a memory-based DB, as mentionned in _SQLite_Open help.

Those whishing to use Au3Check may rewrite the include as ASCII (Au3Check doesn't support UTF-8).

leegold,

I strongly doubt that your _SQLite_Exec on the disk-based DB would work: read help file again and you'll see that _SQLite_Exec doesn't produce any output. Use _SQLite_GetTable2D instead.

[leegold]

snip...

 

leegold,

I strongly doubt that your _SQLite_Exec on the disk-based DB would work: read help file again and you'll see that _SQLite_Exec doesn't produce any output. Use _SQLite_GetTable2D instead.

 

Hi,

Did you mean to say the _SQLite_Exec will not work on the memory DB? Because it does work OK with the disk DB:)  You mean it won't work witht memory DB(?), and it certainly seems not too...so I should try _SQLite_GetTable2D for the memory DB instead was  what you were saying(?) I don't want to put works in you mouth but _SQLite_Exec works on the disk DB fine. Or did I not understand?

You mentioned a help file above. if you could cite a link I'd be grateful. Thank you jchd!

 

[leegold]

I tried this which works fine when using a disk DB but will not work on the memory DB I tried to mirror from the disk db.

I get Sqlite error 21: Library Used Incorrectly. 

I just want to run SQL on the memory DB - is my memory DB valid that I'm trying to make? I must not be seeing this in the documentation - I will try to read more...thanks

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <File.au3>
#include <sqlitebackup.au3>

Local $aResult, $iRows, $iColumns, $iRval

$sSQliteDll = _SQLite_Startup()
$sDbName = "C:\sqlite\relays.db"
$hDskDb = _SQLite_Open($sDbName) ; Open a permanent disk database

Local $hmemDb = _SQLite_Backup($hDskDb, ':memory:', Default, Default, Default, Default, Default)

$iRval = _SQLite_GetTable2d($hmemDb, "SELECT * FROM sched;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()




_SQLite_Close()
_SQLite_Shutdown()

[leegold]

I'm just going to read the disk db into an array and use that - much simpler. I would still like to know like to know what I was doing wrong and I'm grateful for the help provided.

[jchd]

Instead try this:

#include <MsgBoxConstants.au3>

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <File.au3>
#include "sqlitebackup.au3"

Local $aResult, $iRows, $iColumns, $iRval

$sSQliteDll = _SQLite_Startup()
$sDbName = "relaystest.db"
$hDskDb = _SQLite_Open($sDbName) ; Open a permanent disk database

_SQLite_Exec($hDskDb, "create table if not exists test (colA text);")
_SQLite_Exec($hDskDb, "insert into test values ('at least one row here');")

Local $hmemDb = _SQLite_Backup($hDskDb, ':memory:')


$iRval = _SQLite_GetTable2d($hmemDb, "SELECT * FROM test;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()

BTW and once again, _SQLite_Exec on any DB can't return any (SQL) result, just a error code.