SQLITE partially working in Autoit

[Guest notchent]

Hi,

I've gotten Autoit to communicate directly with the Sqlite dll by using the sqlite C api function prototypes given at http://sqlite.org/c_interface.html , but I need some help getting info out of the database. I'm able to update the database using an Autoit DllCall (my little test database has a single table called "toons"), and I can add to that table using an sqlite_exec function and executing an "insert into" query - that works just fine. It also works using the "sqlite_get_table" command. To return data to Autoit (my goal is to display data from the sqlite database in a listview), sqlite_exec requires the use of a callback function in Autoit, and I don't know if that's possible. The "sqlite_get_table" function in the sqlite api is supposed to allow retrieval of the data from an sql command without using a callback function, but the return value is supposed to be "int" - and it returns an array - I don't get that completely, but it happens. I've tried looking through all the elements of the array, but don't see any of the data from the database (index 2 returns the sql command, and index 1 returns a pointer to the data).

Here's what I've got so far. Can anyone help?

;OPEN DATABASE - this works fine

$result = DllCall("C:\test\sqlite.dll", "ptr", "sqlite_open", "str", "C:\test\myDB2.db")

msgbox(0, "sqlite_open", $result[0])

;INSERT DATA into database using "sqlite_exec" command - this works fine too

$exec = DllCall("C:\test\sqlite.dll", "int", "sqlite_exec", "ptr", $result[0], "str", "INSERT INTO toons (firstname,lastname) VALUES ('ugh','ugh')", "ptr", "", "ptr", "", "ptr", "")

msgbox(0, "sqlite_exec", $exec[0])

;READ DATA using "sqlite_exec" - don't know how to get this to work!

$execqqqq = DllCall("C:\test\sqlite.dll", "int", "sqlite_exec", "ptr", $result[0], "str", "SELECT * FROM toons", "ptr", "", "ptr", "", "ptr", "")

msgbox(0, "2nd sqlite_exec", $execqqqq[2])

;INSERT DATA into database using "sqlite_get_table" command - this one works

$execqq = DllCall("C:\test\sqlite.dll", "ptr", "sqlite_get_table", "ptr", $result[0], "str", "INSERT INTO toons (firstname,lastname) VALUES ('blah','blah')", "str", "", "int", "", "int", "", "str", "")

msgbox(0, "sqlite_get_table", $execqq[0])

;READ DATA using "sqlite_get_table" - don't know how to get this to work!

$execqqq = DllCall("C:\test\sqlite.dll", "ptr", "sqlite_get_table", "ptr", $result[0], "str", "SELECT * FROM toons", "str", "", "int", "", "int", "", "str", "")

msgbox(0, "sqlite_get_table_2", $execqqq[1])

;GET VERSION - this one also doesn't return the correct value

$version = DllCall("C:\test\sqlite.dll", "str", "sqlite_version")

msgbox(0, "Version", $version)

;CLOSE DATABASE - this works fine

$close = DllCall("C:\test\sqlite.dll", "none", "sqlite_close", "ptr", $result[0])

msgbox(0, "sqlite_close", $close)

sqlite.dll

[Lazycat]

Unfortunately, DllCall can't return arrays. Exec works, because SQL request is just simple string (methods of SQL request are very nice!). But getting table data required pointer to array, that actually not supported...

Sure, possible to make some wrapper (dll from dll... ), but this is lame way and not sure it worth dealing... Better use simple CSV and standard Autoit functions

In version case you just using wrong function call, it should be:

;GET VERSION - this one also doesn't return the correct value
$version = DllCall("sqlite.dll", "str", "sqlite_libversion")
msgbox(0, "Version", $version[0])

Note, that if call was sucsessfull DllCall always return array.

[tazdev]

I'm confused. You mean AutoIt can access SQL databases or is this a third party program that can be used with AutoIt to access one.

[Insolence]

A third party program that AutoIT can use to manipulate a database.

THANK YOU so much for your work on this, I've wanted this for a while

[trids]

@ LazyCat + Larry

SQLite looks like quite a nifty piece of work .. any chance of providing a wrapper that sends results of queries to a nominated output file? An example of this approach is isql.exe for SYBASE databases, which works like a charm: you provide the SQL query/script in an input file, and nominate the output file, passing both file references as commandline parameters to isql.exe

When the wrapper terminates, the remainder of the au3 script can then interrogate the output file

Just a note regarding the link above: isql.exe can be used interactively as well, but this is not what I'm suggesting. Just thought you might find it useful to see how it operates as a wrapper, for ideas on how to provide such a wrapper for au3 to access SQLite.

HTH

[Lazycat]

SQLite looks like quite a nifty piece of work .. any chance of providing a wrapper that sends results of queries to a nominated output file? An example of this approach is isql.exe for SYBASE databases, which works like a charm: you provide the SQL query/script in an input file, and nominate the output file, passing both file references as commandline parameters to isql.exe

Seems SQLite already have very similar commandline tool: http://www.sqlite.org/sqlite.html.

[trids]

Mmm .. it's not quite the same though: it looks like you can only specify the input and output files from within an interactive session

.. but i have an idea for some tricks (no promises yet!)

[trids]

Ok .. here we go: DOS to the rescue

Here's a UDF and some sample calls to it. Sorry, but I didn't have much time to polish it up, but it serves to illustrate a working technique.

In fact, (@ notchent) .. I'm sure a spruced up version along with your reference to SQLite3 would be very welcome in the Scripts and Scraps forum and the wiki - if you're up to it

Anyway - here you go ..

expandcollapse popup

;------------------------------------------------------------------------
;SQLite automation = Au3SQL.exe
;------------------------------------------------------------------------

#cs
;First lets create a table.. this is a once-off
    _DoSQL("CREATE TABLE tbTrids (id integer, descr varchar(20));")
    
;Now insert some records into it..  
    _DoSQL("INSERT INTO tbTrids SELECT 5, 'aaaaaa';")
    _DoSQL("INSERT INTO tbTrids SELECT 6, 'bbbbbbbb';")
    _DoSQL("INSERT INTO tbTrids SELECT 7, '123456789012345678901234567890';")
#ce 

;Retrieve some records, and display them
    $sRecs = _DoSQL("SELECT * FROM tbTrids;")
    MsgBox(4096, @SCRIPTNAME, "Recordset.." & @LF & $sRecs)
    
   ;put the records into an array
    $asRec = StringSplit(StringStripWs($sRecs, 2), @LF)
    MsgBox(4096, @SCRIPTNAME, "Records returned.." & @LF & $asRec[0])
    MsgBox(4096, @SCRIPTNAME, "First record is.." & @LF & $asRec[1])
    MsgBox(4096, @SCRIPTNAME, "Last record is.." & @LF & $asRec[$asRec[0]])
    
EXIT

Func _DoSQL($psSQL)
;pass the SQL statement to SQLite3.exe
;assumptions:
;------------
;   + SQLite3.exe is placed in the system path (eg C:\WINNT\SYSTEM32)
;   + you have already created a database in the script path called "mydb.db"
Local $sX, $sSQLout, $sSQLin

;determine  filenames
    $sSQLin = @SCRIPTDIR & "\_sql4au3.sql"
    $sSQLout = "_sql4au3.out"
    
;Set preferences and udate $sSQLin
   ;To parse records, just leave the defualt of pipe-delimited fields,
   ;then you can StringSplit each record on "|"..
    $sX = ".header off" & @LF
    $sX = $sX & ".mode column" & @LF
   ;nominate the file to receive any results (the recordset)..
    $sX = $sX & ".output " & $sSQLout & @LF
    $sX = $sX & $psSQL 
    
    FileDelete($sSQLout)
    FileDelete($sSQLin)
    FileWrite($sSQLin, $sX)
    
;Send it all to SQLite3
    RunWait(@COMSPEC & " /c SQLite3.exe mydb.db<" & FileGetShortName($sSQLin), @SCRIPTDIR, @SW_HIDE)
    
;Get the output to send back    
    If FileExists($sSQLout) then
        Return FileRead($sSQLout, FileGetSize($sSQLout))
    Endif
    
EndFunc

[Lazycat]

I can't get to work you script...

But I tried to compile SQLite sources and make wrapper, adapted for Autoit. First (very early) version here. I stuck with dynamic memory allocation for result... Will try to solve this prob, but not today...

SQLite_test.zip

[trids]

I can't get to work you script... 

[..]

<{POST_SNAPBACK}>

You do need to do the following once-offs before just running the example:

• download sqlite3 and place the SQLite3.exe in a system folder (eg c:\winnt\system32)
• in your script directory, at the DOS prompt, create mydb.db by running: SQLite3.exe mydb.db
• run the script with the #cs..#ce block uncommented, in order to create table tbTrids. After that, you should replace the comments, so that you don't try and create the table over and over again).

Anyway .. it's all just a working example to show how the DOS redirector "<" comes to the rescue.

HTH

[Lazycat]

Ups, missed 3 condition... It's works now.

[supergg02]

You do need to do the following once-offs before just running the example:

• download sqlite3 and place the SQLite3.exe in a system folder (eg c:\winnt\system32)

• in your script directory, at the DOS prompt, create mydb.db by running: SQLite3.exe mydb.db

• run the script with the #cs..#ce block uncommented, in order to create table tbTrids. After that, you should replace the comments, so that you don't try and create the table over and over again).

Anyway .. it's all just a working example to show how the DOS redirector "<" comes to the rescue.

HTH

<{POST_SNAPBACK}>

Please, did you try your script ? I did but no succed! i the function return always 1 i.e problem with FileRead(...)

[phillip123adams]

Please, did you try your script ? I did but no succed! i the function return always 1 i.e problem with FileRead(...)

<{POST_SNAPBACK}>

The exact code posted by trids (post 9) works for me provided the instructions are followed (post 11). However, it is not necessary to "create mydb.db by running: SQLite3.exe mydb.db" beforehand as the script will create it if it does not exist. Also, if you do not want to put SQLite3.exe in the system32 folder, put it anywhere you like and specify the path in the RunWait statement.

[supergg02]

The exact code posted by trids (post 9) works for me provided the instructions are followed (post 11).  However, it is not necessary to "create mydb.db by running: SQLite3.exe mydb.db" beforehand as the script will create it if it does not exist.  Also, if you do not want to put SQLite3.exe in the system32 folder, put it anywhere you like and specify the path in the RunWait statement.

<{POST_SNAPBACK}>

Other think, you should use FileGetShortName before sending commands to sqlite.exe (because of spaces in Paths in windows )