Retrieving SQLite memory database handle

[Djordhan]

To set up the context, I have a main script in which I create a memory sqlite database. I want to pass the handle as parameter to other scripts (I launch the other scripts using RunWait("blabla..") and pass the handle as a simple string, which is probably why it doesn't work..

How can I use a database created in another script?

Purpose is a automated test manager launching multiple subroutine test script, each subroutine must write its result in the database create in the main test manager.

Thanks a lot!

[Djordhan]

Any ideas?

Currently, it looks like this:

Main script:

_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf

$dHdl = _SQLite_Open ()

If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

If Not _SQLite_Exec (-1, "CREATE TEMP TABLE tests (Test, Status);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

RunWait("AutoIt3.exe SuroutineA.au3 " & $dHdl)

_SQLite_Close(-1)
_SQLite_Shutdown()

In each Subroutine:

If Not _SQLite_Exec ($CmdLine[1], "INSERT INTO TABLE tests (Test, Pass);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

Edited February 11, 2011 by Djordhan

[jchd]

The :memory: DBs created by SQLite are non-shareable and are per connection.

Having your code split in distinct programs precludes the passing of anything (they aren't in the same address space!). Both have their "copy" of the SQLIte dll, which you need to _Startup separately.

So, no your design decision doesn't allow what you intend. At least not this way. Use some IPC to have the sub-programs pass data for the main script to process (but don't use RunWait in this case!) and all will be fine.

Now why don't you use a disk-based DB, maybe with required speedup options/pragmas? It would greatly simplify your code.

[Djordhan]

Thank you that's all I needed to know

You're right I guess I'll go for the disk-based DB solution.

[jchd]

There are a number of ways to speed up bulk inserts (one writer at a time only, of course) if you really need so.

As a simple first step measure embrace your writing loop(s) inside a transaction:

begin immediate;

insert/update loop

commit;

Chime again if that isn't enough.

[KaFu]

Maybe a year or two ago I got stuck with the same problem... but thinking about it now, it is really a tempting thought to create a "Shared SQLite Memory Database UDF" based on the by GreenCan. I utilize his functions in the current SMF beta, successfully transferring BMPs between scripts. There needs to be a backend or main script handling the actual database, and other scripts could pass queries and receive results via shared memory buffers.

[Djordhan]

Oooh that sounds very interesting! I'll take a look at it!! Thx

Honestly I'd rather use a memory database since there's no need to keep the data once the program ends.

[Djordhan]

AH! That's perfect! It's not exactly the idea of a Shared SQLite memory database but it does what I need!

Now my sub-programs directly modify shared variable then the mother application pass the query to the memory database.

Thanks KaFu! and jchd of course

[KaFu]

It would be nice if you would post your results later on. Wrap it into a neat UDF and I'll guarantee you some happy customers .

[Djordhan]

I'll sure do once I'm done with this!

[jchd]

Yep, almost any IPC method will do. Named pipes, shared memory, shared file, you name it.

BTW if your tables are temporary, you can just declare them TEMPORARY so that they get automagically deleted by closing the connection. :memory: DBs get deleted anyway, but temporary disk-based tables vanish as well. Now since an SQLite is only one single file, you can delete it yourself once you don't need it any more. If you do so, _SQLite_Close the db first so that a journal file gets eaten as well.

You see, you have plenty of solutions to make things work the way you want.

Almost forgetting: if ever you decide your :memory: DB is worse keeping for later use, just search the example forum for my SQLite backup UDF.

We seem to have more solutions than problems at hand, how nice!

[KaFu]

Almost forgetting: if ever you decide your :memory: DB is worse keeping for later use, just search the example forum for my SQLite backup UDF.

That one definitely needs to be included in such a solution ....

[Djordhan]

Basically, here's how I (KaFu and jchd) solved my problem using the

Main Script:

expandcollapse popup

$struct = "wchar ins[260]"
$Structure=DllStructCreate($struct)
If @error Then
    MsgBox(0,"","Error in DllStructCreate " & @error);
    Exit
Endif

; Get the pointer
$Pointer1 = DllStructGetPtr($Structure,"ins") ; Returns the pointer

; Initialize default value
Write_Memory(@AutoItPID, $Pointer1, "null", "wchar var4[260]")

; Build a list of the test to be executed
$aTestList = _FileListToArray("C:\TestCases\Execution\")


; Loop through the test list
For $i = 1 To $aTestList[0]

    Run("C:\Program Files\AutoIt3\AutoIt3.exe C:\TestCases\Execution\" & $aTestList[$i] & " " & @AutoItPID & " " & $Pointer1, "C:\Program Files\AutoIt3\")

        ; Script waits while TestCase script modify the shared variable including all details about the testcase.
    While Read_Memory(@AutoItPID, $Pointer1, "wchar var4[260]") = "null"
        Sleep(100)
    WEnd
        
        ; TestCase script return a result and Report_Format prepare the string for SQL INSERT.
    $aValues = Report_Format(Read_Memory(@AutoItPID, $Pointer1, "wchar var4[260]"))

        ; INSERT into Memory Database using value retrieved from the shared string variable.
    If Not _SQLite_Exec (-1, "INSERT INTO Tests VALUES ('" & $aValues[1] & "','" & $aValues[2] & "','" & $aValues[3] & "','" & $aValues[4] & "','" & $aValues[5] & "');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

    ; Set shared variable's value back to "null"
    Write_Memory(@AutoItPID, $Pointer1, "null", "wchar var4[260]")

Next

TestCase call is made really simple using Report_Write():

Report_Write($CmdLine[1], $CmdLine[2], $aTD, "001", "Pass")

Func Report_Write($PID, $Pointer, $aTD, $step, $status)
    Write_Memory($PID, $Pointer, _
                        $aTD[0] & "|" & _   ; Test Category
                        $aTD[1] & "|" & _   ; Test ID
                        $aTD[2] & "|" & _   ; Test Title
                        $step & "|" & _     ; Test Step
                        $status, _          ; Step Status
                        "wchar var4[260]")
EndFunc

Main Script calls Report_Format, converting the shared string to array using StringSplit. (Depending on my future needs, I may change the workflow to use multiple pointer to different data type instead of a single string.)

That's not much of an innovation but it might gives idea for using a "shared" SQLite database.

Edited February 14, 2011 by Djordhan

[jchd]

Note to subsequent readers: any non-memory-based SQLite DB _is_ shareable. Paradoxally, SQLite is also a very good IPC for massive communication between processes!

It's still possible to make a DB in a RAM disk and share it among processes.