SQLite semi Embedded database functionality in AutoIt

[piccaso]

Fine job

Some more comments.

Thanks

1. why to keep those to similar functions. _SQLite_Exec and _SQLite_Execute.

Personnally I don't like too much those ErrorMsg/Charsize.

I will suggest we remove them in _SQLite_GetTable2d,_SQLite_GetTable too and remove _SQLite_Exec.

Your right _SQLite_Exec is just more complicated, when i did that one i wanted it to be like the sqlite api...

I dont know about Charsize & GetTable, dont realy know if there is a perfect value.

do you have any sugesstion?

2. why don't have a way to create permanently the SQLite3.dll with the Inline stuff.

3. I don't know who is compiling Sqlite3.dll and sqlite.exe but tell him to have a version property.

I'm not sure what you mean ... ?

4. it could be good to have the same programming functions as in Php_sqlite. I don't know if it is really possible.

Cheers

Do you mean Syntax or Functionality?

If there is some functionality missing please point me to it and i'll see what i can do...

As for Syntax, There have been so many downloads it wont be very friendly to make code breaking changes now...

@JSThePatriot

I use that script ...

expandcollapse popup

$sInfile = "bin\sqlite3.dll"
$sOutfile = "bin\include\SQLite.dll.au3"
;------------------------------
#include <date.au3>
#include <File.au3>
FileDelete($sOutfile)
$sSqliteVersion =  DllCall($sInfile, "str:cdecl", "sqlite3_libversion")
$sSqliteVersion = $sSqliteVersion[0]
$iDataSize = FileGetSize($sInfile)
$iFileTimeModified = FileGetTime($sInfile,0,1)
$aFileTimeModified = FileGetTime($sInfile,0,0)
$iFileTimeCreated = FileGetTime($sInfile,1,1)
$aFileTimeCreated = FileGetTime($sInfile,1,0)
$vData = FileRead($sInfile,FileGetSize($sInfile))
$sData = "0x" & Hex($vData);
$sDataSize = StringLen($sData)
$fh = FileOpen($sOutfile,2)
FileWriteLine($fh,';Inline SQLite3.dll, Creation Time: ' & _NowCalc())
FileWriteLine($fh,'#include-once')
FileWriteLine($fh,'Func __SQLite_Inline_Modified()')
FileWriteLine($fh,' Return "' & $iFileTimeModified & '" ; ' & $aFileTimeModified[0] & '/' & $aFileTimeModified[1] & '/' & $aFileTimeModified[2] & ' ' & $aFileTimeModified[3] & ':' & $aFileTimeModified[4] & ':' & $aFileTimeModified[5] )
FileWriteLine($fh,'EndFunc')
FileWriteLine($fh,'Func __SQLite_Inline_Created()')
FileWriteLine($fh,' Return "' & $iFileTimeCreated & '" ; ' & $aFileTimeCreated[0] & '/' & $aFileTimeCreated[1] & '/' & $aFileTimeCreated[2] & ' ' & $aFileTimeCreated[3] & ':' & $aFileTimeCreated[4] & ':' & $aFileTimeCreated[5] )
FileWriteLine($fh,'EndFunc')
FileWriteLine($fh,'Func __SQLite_Inline_Version()')
FileWriteLine($fh,' Return "' & $sSqliteVersion & '"')
FileWriteLine($fh,'EndFunc')
FileWriteLine($fh,'Func __SQLite_Inline_SQLite3Dll() ; Dont Tidy me!')
FileWriteLine($fh,'Local $sData')
$cnt = 0
$out = ""
$iStepSize = 2 * 2041
ProgressOn("",$sInfile)
$iTimer = TimerInit()
For $i = 1 To $sDataSize Step $iStepSize
    $cnt += $iStepSize
    $out &= StringMid($sData,$i,$iStepSize)
    If $cnt > 4066 Then
        $cnt = 0
        If $i = 1 Then
            FileWriteLine($fh,'$sData  = "' & $out & '"')
        Else
            FileWriteLine($fh,'$sData &= "' & $out & '"')
        EndIf
        ProgressSet(Round($i * 100 / $sDataSize,0),"ETA: " & _DateAdd('s',Round((TimerDiff($iTimer) / 1000) / ($i * 100 / $sDataSize) * 100,0),_NowCalc()))
        $out = ""
    EndIf
Next
If $cnt Then FileWriteLine($fh,'$sData &= "' & $out & '" ; ' & $i)
FileWriteLine($fh,'Return BinaryString($sData)')
FileWriteLine($fh,'EndFunc')
FileClose($fh)
ConsoleWrite(_PathFull($sOutfile) & "(1,1)" & @CR)

... i wont recomend using this with a larger file (>1mb)

dont forget about MAX_LINESIZE (=4095 for now) if you do stuff like that

edit:

Update LiteX to SQLite version 3.3.5...

The same typo in this posts Title and LiteX website

You guys must be friends...

Edited June 5, 2006 by piccaso

[jpm]

@Picasso

1. for gettable I suggest you just suppress them leaving to the user the necessity to call _SQLite_ErrMsg if he want the string of the message. on the return of the function gettable.

2. something that will after startup will not be destroyed so can be use by other script without recreation.

BTW I thing the modified date is the only info which really need to be set. The creation date is not so important.

3. when you right click on autoIt3.exe you gety and tab version where you find the autoit version. that' what I suggest to be added during the building of the .exe or the .dll.

4.I am not an expert of php but I have read in French the API that can be used in a PHP script and that is what I am refering to. Perhaps ptrex can help to improve or not this wonderful UDF. I just Google

[ptrex]

The same typo in this posts Title and LiteX website

Did you steal the "o" from the title ?

[ptrex]

@Jpm

Perhaps ptrex can help to improve or not this wonderful UDF

I went to see the PHP UDF's and they look good, definitely something we should aim for when creating our UDF.

I would like to help where I can, but I am not into DLLCall stuff. That is my shortcomming here

Only piccaso knows how to translate the SQLite API to DLLCall functions.

I would like to know how it's done to, if someone could explain me how it's done I could help.

But for now, I can only assist by reviewing the work of piccaso and creating examples and documentation.

And expand the COM examples I started with.

[jpm]

@Jpm

I went to see the PHP UDF's and they look good, definitely something we should aim for when creating our UDF.

I would like to help where I can, but I am not into DLLCall stuff. That is my shortcomming here

Only piccaso knows how to translate the SQLite API to DLLCall functions.

I would like to know how it's done to, if someone could explain me how it's done I could help.

But for now, I can only assist by reviewing the work of piccaso and creating examples and documentation.

And expand the COM examples I started with.

I think the main difficulty come from the extension that can be embedded in the query request. Some callback mechanism perhaps not possible to implement. You can address the problem to Larry ar any other developper than me.

Continue the big improvement of this very helpfull UDF.

[Proze]

Greetings All, Ive come out of lurkdom as I have Encountered an Odd (possible) bug in THe SQLite Code.

Briefly:

The Following Code is Suffiecent to Cause the Error

#include <SQLite.au3>

Local $myQuery, $queryStorage

 _SQLite_Startup ()
 _SQLite_Open ("my.db")
 
_SQLite_Execute(-1, "CREATE TABLE test (a,b,c);")
_SQLite_Execute(-1, "INSERT INTO test (a,b,c) VALUES ('testing1', 'testing2', 'testing3');")

_SQlite_Query(-1, "SELECT a FROM test WHERE a = 'testing1';", $myQuery)
_SQLite_FetchData($myQuery, $queryStorage)

MsgBox(0,"testing1",$queryStorage[0])

$test = _SQLite_Execute(-1, "UPDATE test SET a='changeme' WHERE a ='testing1';")
MsgBox(0,"Testing Update",$test)

_SQLite_Execute (-1, "DROP TABLE test")

_SQLite_Close()
_SQLite_Shutdown()

What Occurs is that the UPDATE statement Returns an error Code of 6 stating that a Table in the DB is Locked. If you perform the "UPDATE" snippet first then you can run any number of Querys afterwards with no problems whatsoever.

After Some Reading through the SQLite Documentation on the Locking Mechanism's It seems that the SELECT statement is Not completing and Releasing its Lock on the Table. However if you insert a second SELECT Immediatly After the UPDATE then the second SELECT works fine.

Proze

Edited June 7, 2006 by Proze

[ptrex]

@Proze

I' ve run your example as well as others from the last disctribution of piccaso.

And indead there seems to be a problem. Probably last version update has changed the behaviour of the functions.

@piccaso

Can you run some tests to see of you can confirm this.

Thanks for you input.

[piccaso]

1. for gettable I suggest you just suppress them leaving to the user the necessity to call _SQLite_ErrMsg if he want the string of the message. on the return of the function gettable.

I am about to remove them...

i was talking about $iCharSize of _SQLite_GetTable*()

Is there a better way to 'Read from this point in memory until you hit a \00' ?

What would happen if the size of the DllStruct("char") is too large?

Is it possible to make it so large that is would be part of another process or other things in memory and cause problems there or will DllStruct throw an @error?

I could think of (not sure if i can write it) a sulution in an external dll but would it be worth it?

:">

2. something that will after startup will not be destroyed so can be use by other script without recreation.

BTW I thing the modified date is the only info which really need to be set. The creation date is not so important.

I dint plan to destroy something, what did i miss ?

If the creation date is not set its after the modification date, looks wierd

3. when you right click on autoIt3.exe you gety and tab version where you find the autoit version. that' what I suggest to be added during the building of the .exe or the .dll.

Its not a costume build but i'll see what reshacker can do...

4.I am not an expert of php but I have read in French the API that can be used in a PHP script and that is what I am refering to. Perhaps ptrex can help to improve or not this wonderful UDF. I justGoogle

User functions/collation sequences/hooks are not possible whithout callback, but they would be terribly slow if they where.

miss something else?

@ptrex

can you tell me more about these problems?

if you want to see sqlite3.dll's functions in a vb like declaration you might want to take a look at sqlite3.bi from freebasic

edit:

i just ran across a nice function i didnt wrap yet

sqlite3_table_column_metadata

do you want to try/learn on this one, i'll guide you to it... ?

@Proze

The table is locked until the transaction is done... (see comments)

#include <SQLite.au3>
Local $myQuery, $queryStorage
 _SQLite_Startup ()
 _SQLite_Open () ; i like :memory: databases for playing around :)
 _SQLite_Execute(-1, "CREATE TABLE test (a,b,c);")
_SQLite_Execute(-1, "INSERT INTO test (a,b,c) VALUES ('testing1', 'testing2', 'testing3');")

; The query wasnt finalized
; either process the whole query like this
; or use _SQLite_QueryFinalize()
_SQlite_Query(-1, "SELECT a FROM test WHERE a = 'testing1';", $myQuery)
While _SQLite_FetchData($myQuery, $queryStorage) = $SQLITE_OK
    MsgBox(0,"testing1",$queryStorage[0])
WEnd
; -----------------------------------------

; or if you know that its just one line use _SQLite_QuerySingleRow()
; You dont have to care about finalizing here
_SQLite_QuerySingleRow(-1,"SELECT b FROM test WHERE a = 'testing1' LIMIT 1;", $queryStorage)
MsgBox(0,"testing2",$queryStorage[0])
; ------------------------------------------------------------------

$test = _SQLite_Execute(-1, "UPDATE test SET a='changeme' WHERE a ='testing1';")
MsgBox(0,"Testing Update",$test)
_SQLite_Execute (-1, "DROP TABLE test")
_SQLite_Close()
_SQLite_Shutdown()

Edited June 7, 2006 by piccaso

[ptrex]

@piccaso

User functions/collation sequences/hooks are not possible whithout callback

Can you explain why the LiteX COM version does support COLLATE in AutoIT, and DLLCall not ?

Where is the callback handled then with the LiteX version ?

can you tell me more about these problems?

Most of the examples I ran from the last distribution had errors relating the "ARRAY's BAD subscript"

Include version 1.68.

Can you confirm this.

Even the example of you for @@Proze does only show a blank message box for "testing2" and one with "21". Which is the return of the error code 21 for the "Testing Update".

Must be something wrong on my system ??

Edited June 8, 2006 by ptrex

[piccaso]

@piccaso

Can you explain why the LiteX COM version does support COLLATE in AutoIT, and DLLCall not ?

Where is the callback handled then with the LiteX version ?

It isnt possible with litex too.

Litex registers 2 Collation sequences and 1 Function but it doesent enable autoit to add your own.

'COLLATE' and Functions are still supported by both versions but the callback function must be in an external dll or compiled into sqlite3.dll

In theory i could think of an proxy dll that would catch the callback and start another instance of autoit to handle the situation with a snip of code provided by the main instance but this would be

many hours of work for something thats very slow.

It would be faster and would require less memory if you sort your data in autoit.

Most of the examples I ran from the last distribution had errors relating the "ARRAY's BAD subscript"

Include version 1.68.

Can you confirm this.

Even the example of you for @@Proze does only show a blank message box for "testing2" and one with "21". Which is the return of the error code 21 for the "Testing Update".

Must be something wrong on my system ??

What the ...

Maybe litex and sqlite arent as compatible as i thaught they would be ...

i'll do some testing

edit:

works fine with litex too.

can you tell me the return values of the other functions ?

Edited June 8, 2006 by piccaso

[ptrex]

@piccaso

'COLLATE' and Functions are still supported by both versions

Sorry it was not clear to me which Collate you were refering to. I was confused

Now it is clear.

can you tell me the return values of the other functions

Can you tell me which functions you mean. the functions in the little example you posted or the functions in General ?

[piccaso]

@ptrex

Please run this and post the output...

#include <SQLite.au3>
Local $myQuery, $queryStorage, $dbg
$dbg = _SQLite_Startup ()
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQLite_Open ()
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQLite_Execute (-1, "CREATE TABLE test (a,b,c);")
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQLite_Execute (-1, "INSERT INTO test (a,b,c) VALUES ('testing1', 'testing2', 'testing3');")
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQlite_Query (-1, "SELECT a FROM test WHERE a = 'testing1';", $myQuery)
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
While _SQLite_FetchData ($myQuery, $queryStorage) = $SQLITE_OK
    MsgBox(0, "testing1", $queryStorage[0])
WEnd
_SQLite_QuerySingleRow (-1, "SELECT b FROM test WHERE a = 'testing1' LIMIT 1;", $queryStorage)
MsgBox(0, "testing2", $queryStorage[0])
$test = _SQLite_Execute (-1, "UPDATE test SET a='changeme' WHERE a ='testing1';")
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $test & @TAB & "@error = " & @error & @CR)
MsgBox(0, "Testing Update", $test)
$dbg = _SQLite_Execute (-1, "DROP TABLE test")
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQLite_Close ()
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)
$dbg = _SQLite_Shutdown ()
ConsoleWrite("@@debug(" & @ScriptLineNumber - 1 & ") : $dbg = " & $dbg & @TAB & "@error = " & @error & @CR)

[piccaso]

Changed _SQLite_Exec(), _SQLite_GetTable2d(), _SQLite_GetTable() Removed '$sErrorMsg' parameter

Removed _SQLite_Execute() because _SQLite_Exec() was the same

SQLite.zip

Sorry for the codebreaking changes...

Its realy better that way

Examples are updated to the new syntax but doc's are still miles away form beeing done...

[ptrex]

@picasso

Thanks for the update. I'll run some tests tomorrow.

Examples are updated to the new syntax but doc's are still miles away form beeing done

I still offer my help. But I run a little out of time as well. As soon as I see some time available I will continue.

I did not forget it

CU later.

[jpm]

rereading your _SQLite_Startup() code lead me to do some simplification in the code for readibility perhaps bug correction on return error

I understand that this function update the sqlite3.dll in systemdir if it can. I update the doc on this subject.

I removed the updating of the creation time which is not relevant.

If you are the one which has linked the .dll just PM me the file needed to recompile so I can add the version in the dll.

I still want to undertsand if we can have the same way to use those semi embedded functions as PHP.

If Not can you clarify what is missing to do it.

Continue the good work.

[piccaso]

rereading your _SQLite_Startup() code lead me to do some simplification in the code for readibility perhaps bug correction on return error

I understand that this function update the sqlite3.dll in systemdir if it can. I update the doc on this subject.

I removed the updating of the creation time which is not relevant.

Thank you for putting straight logic in here

If you are the one which has linked the .dll just PM me the file needed to recompile so I can add the version in the dll.

Its from the standard distribution, i never managed to build more than a .lib

I still want to undertsand if we can have the same way to use those semi embedded functions as PHP.

If Not can you clarify what is missing to do it.

i'm not sure what your after but i'll try...

aggregate functions -> havent heard this bevore, no idea what it does...

user functions -> would require callback or another workaround.

i didnt read anything about Collation sequences in the php docs but this would require callback to.

seek/rewind -> there is no api for that only reset (which feels like rewind) and finalize

sqlite_escape_string -> i dont know which char's and how, but there is still _SQLite_Encode()

unbuffered query's -> i 'think' its like setting PRAGMA synchronous = OFF; (0)

[jpm]

Thank you for putting straight logic in here :)Its from the standard distribution, i never managed to build more than a .lib i'm not sure what your after but i'll try...

aggregate functions -> havent heard this bevore, no idea what it does...

user functions -> would require callback or another workaround.

i didnt read anything about Collation sequences in the php docs but this would require callback to.

seek/rewind -> there is no api for that only reset (which feels like rewind) and finalize

sqlite_escape_string -> i dont know which char's and how, but there is still _SQLite_Encode()

unbuffered query's -> i 'think' its like setting PRAGMA synchronous = OFF; (0)

Thanks Picasso I don't know how we can have callback but that's seems the main different point.

Seek/rewind looks like a way to select info in data that can be retrieved by fetchdata only sequentially in fact the next only.

Thanks for all I stop my PHP teasing

[piccaso]

Thanks Picasso I don't know how we can have callback but that's seems the main different point.

hmm...

my asembler is even worse than my c/c++ so its far over my head

Seek/rewind looks like a way to select info in data that can be retrieved by fetchdata only sequentially in fact the next only.

Thanks for all I stop my PHP teasing

Seek could be done by resetting the query and stepping to the row...

i dont know if this works with finalized query's but if it would SaveMode wont let it happen

but is this realy usefull?

imo OFFSET (part of SELECT clause) suits this purpose much better

... correct me if i'm wrong

edit:

about version info in the dll:

do you know a place where i can find out more about the .res format?

reshacker can add this information but you have to provide/write a resource file

... i know that jdeb's CompileAU3 does that somehow but i didnt find out how jet

Edited June 9, 2006 by piccaso

[piccaso]

Merged...

Cleaning _SQlite_Startup(). (JPM)

SQLite.zip

Edited June 9, 2006 by piccaso

[Azu]

@flyingboz

Thanks for your feedback.

I understand your approach that integrating it in the distribution would be a little too much.

But did you look at it from all the perspectives ?

How can a less than 200 K large dll, be of any overhead.

It contains +/- 15 additional functions.

By the way did bother to have a look at thoose ? http://republika.pl/roed/litex/

This would expose all the native SQL, features from within the AutoIt.

Reasons to include it are:

When the SQL(ite) is natively included it would not have to do any compromises on SPEED, when running Queries. Which is always the case with COM and ODBC.

Less coding is needed in the script, No COM or ODBC connection settings have to be specified in the script.

This means less CreateObj statements and variables to reach COM objects.

More readable and less complex to write any scripts for SQL database connectivity.

etc ...

From a technical and user friendly point of view, there are a lot of pro's, to go for a native support of SQLite.

But I will leave it to the developers and the database users, to vote for, or against the native SQL(ite).

As you indicated, including this through the addin or plugin, might be a good compromise.

Anyhow I can't estimate what the effects are on speed, and other technical consequenses.

The only thing which is clear is that a lot of users would appreciate having SQL and Database support available.

Let's hear your votes !!

I vote yes!