Guest notchent Posted February 17, 2005 Share Posted February 17, 2005 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 Link to comment Share on other sites More sharing options...
Lazycat Posted February 17, 2005 Share Posted February 17, 2005 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. Koda homepage ([s]Outdated Koda homepage[/s]) (Bug Tracker)My Autoit script page ([s]Outdated mirror[/s]) Link to comment Share on other sites More sharing options...
tazdev Posted February 18, 2005 Share Posted February 18, 2005 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. Link to comment Share on other sites More sharing options...
Insolence Posted February 18, 2005 Share Posted February 18, 2005 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 "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. Link to comment Share on other sites More sharing options...
trids Posted February 18, 2005 Share Posted February 18, 2005 @ LazyCat + LarrySQLite 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.exeWhen 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 Link to comment Share on other sites More sharing options...
Lazycat Posted February 18, 2005 Share Posted February 18, 2005 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.exeSeems SQLite already have very similar commandline tool: http://www.sqlite.org/sqlite.html. Koda homepage ([s]Outdated Koda homepage[/s]) (Bug Tracker)My Autoit script page ([s]Outdated mirror[/s]) Link to comment Share on other sites More sharing options...
trids Posted February 18, 2005 Share Posted February 18, 2005 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!) Link to comment Share on other sites More sharing options...
trids Posted February 18, 2005 Share Posted February 18, 2005 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 Link to comment Share on other sites More sharing options...
Lazycat Posted February 18, 2005 Share Posted February 18, 2005 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 Koda homepage ([s]Outdated Koda homepage[/s]) (Bug Tracker)My Autoit script page ([s]Outdated mirror[/s]) Link to comment Share on other sites More sharing options...
trids Posted February 19, 2005 Share Posted February 19, 2005 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.dbrun 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 Link to comment Share on other sites More sharing options...
Lazycat Posted February 19, 2005 Share Posted February 19, 2005 Ups, missed 3 condition... It's works now. Koda homepage ([s]Outdated Koda homepage[/s]) (Bug Tracker)My Autoit script page ([s]Outdated mirror[/s]) Link to comment Share on other sites More sharing options...
supergg02 Posted March 15, 2005 Share Posted March 15, 2005 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(...) Link to comment Share on other sites More sharing options...
phillip123adams Posted March 16, 2005 Share Posted March 16, 2005 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 Link to comment Share on other sites More sharing options...
supergg02 Posted March 16, 2005 Share Posted March 16, 2005 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 ) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now