Sign in to follow this  
Followers 0
Guest notchent

SQLITE partially working in Autoit

14 posts in this topic

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

Share this post


Link to post
Share on other sites



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... :lmao:), but this is lame way and not sure it worth dealing... Better use simple CSV and standard Autoit functions o:)

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


"I thoroughly disapprove of duels. If a man should challenge me, I would take him kindly and forgivingly by the hand and lead him to a quiet place and kill him." - Mark TwainPatient: "It hurts when I do $var_"Doctor: "Don't do $var_" - Lar.

Share this post


Link to post
Share on other sites

@ 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 :lmao:

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

Ok .. here we go: DOS to the rescue :lmao:

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

Anyway - here you go ..

;------------------------------------------------------------------------
;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

Share this post


Link to post
Share on other sites

I can't get to work you script...  o:)

[..]

<{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

:lmao:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Phillip

Share this post


Link to post
Share on other sites

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 )

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