Sign in to follow this  
Followers 0
Djordhan

Retrieving SQLite memory database handle

14 posts in this topic

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!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Djordhan

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thank you that's all I needed to know ;)

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

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

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 :).

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 :)....

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Main Script:

$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 by Djordhan

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0