SQLite semi Embedded database functionality in AutoIt

[ptrex]

@blink314

I don' t have a problem wiht any of your statements made. Just want to share some good experience.

(Since 23 is the age the build up a lot of experience is it ?) Preventing you from getting frustated now and than.

Anyhow as you said is has to be fun. And it sure was (and still is).

- using the dllcall functions.

I included some partialy working example in my previous posts. Grab it from elsewhere. I don' t understand it myself. Never dug into DLL call. Read somewhere else that it is hard and frustrating sometimes.

By anyway if you got around it, share your results and we' ll see if this is the good approach.

I found an other way (less complicated I think than DLL call) to use SQLite, using the Sqlite3.dll directly.

This implies the command line tool in the middle.

I will share my results as I go along.

But first I want to add functionalities to the SQL Gui when you release it.

PS : I updated the Bug list after testing.

Edited November 18, 2005 by ptrex

[blink314]

Yeah what I was missing was the type field in the log table. I'll change that.

dllcall is the brute force way to do it. I think it's a fine approach though a bit more complicated than a plugin. It will allow you to use the latest version of SQLite (until they change the API anyway!). So I'm going to look into it.

I'll clean up the storing of views and such, correct a few minor things I noticed and call it ready. I think.

Kevin

[ptrex]

@blink314

OK. I will hold my horses.

Neverheless I am eager to start with adding things. I will not do it at the same pace you did.

One step at the time.

I'll keep informing you all about the status ( and upload for evaluations).

So I' ll know what to do whith my little free time left

[blink314]

Ok here is my final release type thing. I will be continuing to update things and read what's going on here. I also will probably continue to upload major changes. But, from here on feel free to modify and post your modifications. ptrex is going to continue and do things he wants to do with the code. I'm going to continue and do things I want to do!

Dont worry, I cant keep up the current pace much longer! It's hard though when other people are relying on your code... puts more pressure to get things done! That's the main reason I'm passing this off: I put myself under too much pressure! But, I will continue to update this, "steal" ideas I like from what ptrex does, and upload my major changes.

Major changes I'm looking at next:

- Code rewrite to move all database calls to their own functions so I can more easily do #2

- Move to DLLCall so I dont have to depend on an external wrapper and can use the latest version of SQLite

- Add (as a first step toward my frontend) a section in the SQL tab to present a list of views and selects so you dont have to keep typing them in.

So have at it! Any questions about the code... I'll try and answer! But, who knows if I can even read it!

Kevin

Database_1.au3

[ptrex]

@blink314

Thanks for the nice cooperation so far !!

I hope you did learn something from this project. I did for sure from reading your AutoIt code.

We'll be in touch.

Edited November 18, 2005 by ptrex

[piccaso]

i wrapped up some functions...

go here > http://www.autoitscript.com/forum/index.ph...ndpost&p=125951

a bit easyer to use

expandcollapse popup

#include "sqlite.au3"
Local $aResult, $iColumns, $sErrorMsg, $i, $iRows, $tmp
_SQLite_Startup ()
ConsoleWrite("Sqlite Ver: " & _SQLite_LibVersion () & @CR)
_SQLite_Open (@ScriptDir & "\test.db")
ConsoleWrite("Creating Table :")
_SQLite_Exec (-1, "create table test(a,b,c)", $sErrorMsg)
ConsoleWrite($sErrorMsg & @CR)
ConsoleWrite("Inserting Data: " & @CR)
For $i = 1 To 5
    $tmp = "insert into test(a,b,c) values('a" & $i & "','b" & $i & "','c" & $i & "')"
    _SQLite_Exec (-1, $tmp, $sErrorMsg)
    ConsoleWrite(@TAB & $tmp & " -> " & $sErrorMsg & @CR)
Next
ConsoleWrite("Last ROWID: " & _SQLite_LastInsertRowID (-1) & @CR)
ConsoleWrite("Sqlite Ver: " & _SQLite_LibVersion () & @CR)
$tmp = "select ROWID,* from test"
ConsoleWrite($tmp)
_SQLite_GetTable (-1, $tmp, $aResult, $iRows, $iColumns, $sErrorMsg)
ConsoleWrite(" -> " & $sErrorMsg & @CR)
For $i = 1 To $aResult[0]
    ConsoleWrite(StringFormat("%-8s |   ", $aResult[$i]))
    if ($i / $iColumns) = Round($i / $iColumns, 0) Then; Next Row
        ConsoleWrite(@CR)
    EndIf
    if ($i = $iColumns) Then; Next Row = First Data Row
        ConsoleWrite("---------+-------------+-------------+-------------+" & @CR)
    EndIf
Next
ConsoleWrite("Dropping Table :")
_SQLite_Exec (-1, "drop table test", $sErrorMsg)
ConsoleWrite($sErrorMsg & @CR)
ConsoleWrite("Changes: " & _SQLite_Changes (-1) & @CR)
ConsoleWrite("TotalChanges: " & _SQLite_TotalChanges (-1) & @CR)
_SQLite_Exec (-1, "vacuum", $sErrorMsg)
While _SQLite_Exec (-1, "commit", $sErrorMsg) = $SQLITE_BUSY
    Sleep(100)
WEnd
_SQLite_Close (-1)
_SQLite_Shutdown ()

output:

Sqlite Ver: 3.2.7
Creating Table :Successful result
Inserting Data: 
    insert into test(a,b,c) values('a1','b1','c1') -> Successful result
    insert into test(a,b,c) values('a2','b2','c2') -> Successful result
    insert into test(a,b,c) values('a3','b3','c3') -> Successful result
    insert into test(a,b,c) values('a4','b4','c4') -> Successful result
    insert into test(a,b,c) values('a5','b5','c5') -> Successful result
Last ROWID: 5
Sqlite Ver: 3.2.7
select ROWID,* from test -> Successful result
rowid   |   a       |   b       |   c       |   
---------+-------------+-------------+-------------+
1       |   a1     |    b1     |    c1     |    
2       |   a2     |    b2     |    c2     |    
3       |   a3     |    b3     |    c3     |    
4       |   a4     |    b4     |    c4     |    
5       |   a5     |    b5     |    c5     |    
Dropping Table :Successful result
Changes: 1
TotalChanges: 5

Never dug into DLL call. Read somewhere else that it is hard and frustrating sometimes.

come on take the challenge ...

Look here and bundle this in your example

there are more udf's planned

sqlite3_exec -> no results just for INSERT/UPDATE/CREATE... query's (will only return error code)

sqlite3_libversion (@ptrex: your example uses wrong function name or dll)

And Later if there is time...

sqlite3_last_insert_rowid

sqlite3_errcode

sqlite3_errmsg

sqlite3_changes

sqlite3_total_changes

Edit:

Added _SQLite_Exec()

Added Result-Codes Constants

Fixed Silly bug in _SQlite_Open()

Added _SQLite_LibVersion()

Added _SQLite_TotalChanges()

Added _SQLite_Changes()

Changed _SQLite_Get_Tables() -> _SQLite_GetTables()

Added _SQLite_ErrMsg()

Added _SQLite_ErrCode()

Added Ability to use -1 or "" instead of dbhandle (use Last opened)

Edited November 21, 2005 by piccaso

[blink314]

Yeah, I've been doing the same... different format but same ideas. I got these functions done:

Extract Version

Open DB

Close DB

Query using GetTable (reworked your example)

Just started work on Execute. Man there are a lot functions you can implement! Just trying to figure out which ones are most useful.

Kevin

Just got an excute to work... made a new database and inserted a table.

Edited November 19, 2005 by blink314

[piccaso]

Man there are a lot functions you can implement! Just trying to figure out which ones are most useful.

if you cross out all funcs that need callback and those who are associated with them only a hand full are left over.

IMHO only the funcs named above make sence.

but correct me if i'm wrong

[blink314]

How do you know which ones need callback?

Kevin

Also, how do you get the number of things in a dll structure if the size isn't given in returned array? I know a pointer and I want to try to find out what is in it. Sorry, I'm probably confusing the issue... my familiarity with dll's and such is limited!

Edited November 19, 2005 by blink314

[blink314]

Whoa... if you get an error with an execute you can pull data out about where the error occured. I figured you could do this (the error handler does it in the current script) but I managed to pull some of the data out.

Kevin

Func SQLite_Execute($s_DBhandle,$s_SQL)
    $s_Execute = DllCall($s_DLL, _
            "int", "sqlite3_exec", _
            "ptr", $s_DBHandle, _
            "str", $s_SQL, _
            "long_ptr", 0, _
            "long_ptr", 0, _
            "long_ptr", 0)
    if $s_execute[5] > 0 Then

        $s_Rtn = DllStructCreate("char[256]", $s_execute[5])
        $s_RtnValue = DllStructGetData($s_Rtn, 1)
    ;msgbox(0,"",$s_RtnValue)
        return $s_RtnValue
    Else
        return "No Error"
    EndIf
EndFunc

Same thing can be done on the sqlite3_get_table function... it stores it's error info in the 6th element of the array instead of the 5th.

Edited November 19, 2005 by blink314

[piccaso]

How do you know which ones need callback?

documentation -> http://www.sqlite.org/capi3ref.html

Also, how do you get the number of things in a dll structure if the size isn't given in returned array? I know a pointer and I want to try to find out what is in it. Sorry, I'm probably confusing the issue... my familiarity with dll's and such is limited!

if you mean the value inside char[64] i dont know but its seems like to large is no problem...

Whoa... if you get an error with an execute you can pull data out about where the error occured. I figured you could do this (the error handler does it in the current script) but I managed to pull some of the data out.

Same thing can be done on the sqlite3_get_table function... it stores it's error info in the 6th element of the array instead of the 5th.

i Know

but sqlite3_err* would come in handy too...

[blink314]

Yeah but so far it seems sqlite3_err* is included in the more detailed error... I get "syntax error" after it tells me where the error occured.

Have you figured out last_row_id yet? i keep getting either a large number (> 5 million... assuming it's a pointer) but if I look at that pointer I get 0.

Kevin

[blink314]

Can I ask why you give the result of get_table in a one dimensional array?

Kevin

[piccaso]

_SQLite_LastInsertRowID works fine here...

i dont like multi dimensional arrays (and sqlite returns it in the same format)

but its on my todo list

[blink314]

Ahhh... I forgot to include the database handle. Inserting that also made sqlite3_changes work as well.

Multidimensional arrays can be a pain. I made a function today to dump the contents into a listview... sort of like _arraydisplay for a one dimensional array. Nicer to be able to visualize what's going on!

Off to bed...

Kevin

[ptrex]

@blink314/picasso.

Nice to see that you both get the swing of it !!

Ones there the latest examples are posted. I will start to digg into SQLite DLL call.

As picasso said, it' s a good challange. A a good learning school to see have DLL call and AutoIT work.

Might come on handy, later on with other DLL calls.

Just a tip. Did anyone look at this page ?

http://www.sqlite.org/vdbe.html#trace

It might be interesting to look at, and understand how SQLite handles each instruction.

Might be complementary knowledge to the DLL call, I don' t know ?

Edited November 20, 2005 by ptrex

[piccaso]

Updated again

now only sqlite3_err* func's are missing...

Edited November 20, 2005 by piccaso

[ptrex]

@picasso.

Great job !!

Having DLL call working we don' t need a plugin, do we ?

And we just can use the latest SQLite DLL, if changes are released.

This is even beter

Picasso, did my previous link give you some usefull information ?

[blink314]

Here is a version of picasso's _SQLite_GetTable that returns a two-dimensional array. The first dimension is columns and the second is rows. The first row contains the column names. There is no cell containing the upper bounds.... that is taken care of with the byref $irows and $icolumns.

NOTE:

- the array is always two dimensional... it may have a first dimension of 1 (column 0) but it has two dimensions!

expandcollapse popup

Func _SQLite_GetTable($hDB, $sSQL, ByRef $aResult, ByRef $iRows, ByRef $iColumns, ByRef $sErrorMsg, $iCharSize = 256)
    Global $SQLiteWrapperGlobalVar_hDll
    Global $SQLiteWrapperGlobalVar_hDB
    Local $r, $iResultSize, $i, $struct1, $pResult
    If $hDB = -1 Or $hDB = "" Then
        $hDB = $SQLiteWrapperGlobalVar_hDB
    EndIf
    $r = DllCall($SQLiteWrapperGlobalVar_hDll, "int", "sqlite3_get_table", _
            "ptr", $hDB, _; An open database
            "str", $sSQL, _; SQL to be executed
            "long_ptr", 0, _; Result written to a char *[]  that this points to
            "long_ptr", 0, _; Number of result rows written here
            "long_ptr", 0, _; Number of result columns written here
            "long_ptr", 0); Error msg written here
    If @error > 0 Then
        SetError(1); Dll Calling Error
        Return
    EndIf
    $pResult = $r[3]
    $iRows = $r[4]+1
    $iColumns = $r[5]
    $iResultSize = ($iRows) * ($iColumns)
    
    For $i = 1 To $iResultSize - 1
        $struct1 &= "ptr;"
    Next
    
    $struct1 &= "ptr"
    $struct2 = DllStructCreate($struct1, $pResult)
    
    if $irows > 0 and $icolumns > 0 then    
        Dim $aResult [$icolumns][$irows]
        
        $iCurRow = 0
        $iCurCol = 0
        For $i = 1 To $iResultSize
            if $iCurCol+1 > $icolumns Then
                $iCurRow = $iCurRow+1
                $iCurCol = 0
            EndIf
            
            $aResult[$iCurCol][$iCurRow] = DllStructGetData(DllStructCreate("char[" & $iCharSize & "]", DllStructGetData($struct2, $i)), 1)
            
            $iCurCol = $iCurCol+1
        Next
    EndIf
    If $r[0] = $SQLITE_OK Then
        $sErrorMsg = "Successful result"
    Else
        $sErrorMsg = DllStructGetData(DllStructCreate("char[" & $iCharSize & "]", $r[6]), 1)
        SetError(2); Sql Error
    EndIf
    DllCall($SQLiteWrapperGlobalVar_hDll, "none", "sqlite3_free_table", "ptr", $pResult); pointer to 'resultp' from sqlite3_get_table
    Return $r[0]
EndFunc  ;==>_SQLite_GetTable

Kevin

Edited November 20, 2005 by blink314

[randallc]

Thanks.

is this at the stage you are going to substitute it in your routine for "database"? - opr example of usage in the meantime?

Can you look at my Array View udf and let me know if your display 2D routine would have something to offer here?

Thanks, Randall

Array2D.au3