SQLite semi Embedded database functionality in AutoIt

[ptrex]

@webmedic

I can uderstand that you don' t want to have it changed.

But this is always the risk, if you start writing scripts based upon BETA UDF's.

What I am aiming for is for a higher goal, despite of some casualties here and there, sorry

ptrex

[JSThePatriot]

@webmedic

I can uderstand that you don' t want to have it changed.

But this is always the risk, if you start writing scripts based upon BETA UDF's.

What I am aiming for is for a higher goal, despite of some casualties here and there, sorry

ptrex

@ptrex and piccaso

Code breaking changes for the long run are a casualty of creating functions and programs. I think that a standard naming would be good, but would that be following the SQLite Website? I am just curious as that is where I am going to get my naming from. I would like that all of the different methods of acessing SQLite be at the minimum similar so that users arent confused between the versions.

Thanks,

JS

[ptrex]

@JSThePatriot

I would like that all of the different methods of acessing SQLite be at the minimum similar so that users arent confused between the versions

This is a reason the more for standardization !!

And be honest I am not asking for code breaking changes !!

Onces the names have been set and standardized, I can set any script back to work in no time.

Just use FIND and REPLACE and SciTe will do it in NO TIME (it is just a rename of a UDF, nothing more)

Thanks for you input.

Regards

ptrex

[piccaso]

i found a bug

_SQLite_Exec() is leaking

... ill fix that

@ptrex

sorry but wrapping all this api's you quoted makes no sence.

autoit can call them all but...

... sqlite cant call back

... there are 3 (or more?) ways to execute a query but autoit cant use them all

... some of them are deprecated

... you cant use bindings with autoit variables

...

i could spend hours on explaining why which apicall is needed or not, possible or not, ... but i'll just stop here

please tell me if you i shuld (try to) explain something specific.

and furthermore everything would get very complicated.

for people who want to use the library directly there is a way to turn savemode off and use dllcall.

you may be right that function nameing is bad, this happened because there was no plan in the beginning

i'm not sure if its not to late to change this...

_SQLite_Startup([$sDll_Filename]) Loads SQLite3.dll
_SQLite_Shutdown() Unloads SQLite3.dll
_SQLite_Open([$sDatabase_Filename]) Opens Database, Sets Standard Handle, Returns Handle
_SQLite_Close([$hDB]) Closes Database
_SQLite_GetTable($hDB | -1 , $sSQL , ByRef $aResult , ByRef $iRows , ByRef $iColumns , [$iCharSize = 64]) Executes $sSQL Query to $aResult, Returns Error Code
_SQLite_Exec($hDB | -1 , $sSQL) Executes $sSQL (No Result), Returns Error Code
_SQLite_LibVersion() Returns Dll's Version No.
_SQLite_LastInsertRowID($hDB) Returns Last INSERT ROWID
_SQLite_GetTable2d($hDB | -1 , $sSQL , ByRef $aResult , ByRef $iRows , ByRef $iColumns , [$iCharSize = 64], [$fSwichDimensions = False]) Executes $sSQL Query to $aResult, Returns Error Code
_SQLite_Changes([$hDB]) Returns Number of Changes (Excluding Triggers) of The last Transaction
_SQLite_TotalChanges([$hDB]) Returns Number of All Changes (Including Triggers) of all Transactions
_SQLite_ErrCode([$hDB]) Returns Last Error Code (Numeric)
_SQLite_ErrMsg([$hDB]) Returns Last Error Message
_SQLite_Display2DResult($aResult , [$iCellWidth = 0], [$fReturn = False]) Returns or Prints a 2d Array to console
_SQLite_FetchData($hQuery, ByRef $aRow, [$fBinary = False] ) Fetches Results From First/Next Row of $hQuery Query into $aRow, Returns Error Code
_SQLite_Query($hDB | -1 , $sSQL , ByRef $hQuery) Prepares $sSql, Returns Error Code
_SQLite_SetTimeout([$hDB = -1] , [$iTimeout = 1000]) Sets Timeout for busy handler
_SQLite_SaveMode($fSaveModeState) Turn Savemode On or Off (boolean)
_SQLite_QueryFinalize($hQuery) Finalizes a Query
_SQLite_QueryReset($hQuery) Resets a Query
_SQLite_FetchNames($hQuery, ByRef $aNames) Read out the Tablenames of a _SQLite_Query() based query
_SQLite_QuerySingleRow($hDB | -1 , $sSQL , ByRef $aRow) Read out the first Row of the Result from the Specified query
_SQLite_SQLiteExe( $sDatabaseFile , $sInput , ByRef $sOutput , $sSQLiteExeFilename = "SQLite3.exe" ) Executes commands in SQLite.exe
_SQLite_Encode($vData) Returns Encoded String

compared to php's interface to mysql the function nameing is very clean

... dont know much about sqlite interface.

what is it what litex can do that you miss?

[JSThePatriot]

sorry but wrapping all this api's you quoted makes no sence.

autoit can call them all but...

... sqlite cant call back

... there are 3 (or more?) ways to execute a query but autoit cant use them all

... some of them are deprecated

... you cant use bindings with autoit variables

...

i could spend hours on explaining why which apicall is needed or not, possible or not, ... but i'll just stop here

please tell me if you i shuld (try to) explain something specific.

Piccaso, with the use of a plugin, do you believe that those callbacks could be turned into an extra function return some how?

You have done more research of the API than I have had time to do yet.

JS

[piccaso]

Fixed _SQLite_Exec() Memory Leak on SQL error

Added SQL Error Reporting (only in interpreted mode)

SQLite.zip

and this is how it could look like:

#include <sqlite.au3>
#include <sqlite.dll.au3>
Local $aRow
_SQLite_Startup()
_SQLite_Open()
_SQLite_Exec(-1,"CREATE TABLE tblTemp (a,b,c) Me Error")
_SQLite_QuerySingleRow(-1,"SELECT * FROM tblTemp LIMIT 1",$aRow)
_SQLite_Close()
_SQLite_Shutdown()

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: CREATE TABLE tblTemp (a,b,c) Me Error

--> Error: near "Me": syntax error

! SQLite.au3 Error

--> Function: _SQLite_GetTable2d or _SQLite_QuerySingleRow

--> Query: SELECT * FROM tblTemp LIMIT 1

--> Error: no such table: tblTemp

wow color

@Ptrex

i was thinking about your idea again...

please take a look on _SQLite_FetchData()

even if that dllcalls would turn into functions, do you realy want to do that everytime you want the next row?

@JS

sorry im not sure what plugins can do.

if its possible to call a autoit function from the plugin (like com events) then it would be possible to have

aggregats, functions and coalition sequences and all that stuff that requires callback

if this stuff works in a single threaded environment (i think hooks wont)

... last time i checked it was just planed

edit: Linked alternative download location, the repository is student driven so i guess they wont fix it over the weekend

Edited September 23, 2006 by piccaso

[JSThePatriot]

@JS

sorry im not sure what plugins can do.

if its possible to call a autoit function from the plugin (like com events) then it would be possible to have

aggregats, functions and coalition sequences and all that stuff that requires callback

if this stuff works in a single threaded environment (i think hooks wont)

... last time i checked it was just planed

My understandings of "Callbacks" is from this definition...

A scheme used in {event-driven} programs where the program registers a {subroutine} (a "callback handler") to handle a certain {event}. The program does not call the handler directly but when the event occurs, the {run-time system} calls the handler, usually passing it arguments to describe the event.

The above definition of callbacks, gives me the idea that a DLL file could certainly handle the call backs for AutoIt, and then return the value needed to AutoIt back in a form that AutoIt can handle.

I am going to have a good bit of study ahead of me to really get this going well.

JS

[piccaso]

i'll try to explain it an otherway...

AutoITCode >Calls> Plugin
    Plugin >Calls> Sqlite
        Slite <Calls back< Plugin
    Plugin <Calls back< a Function In AutoITCode
AutoItCode [Does something]
AutoItCode >Retunrs to> Plugin
    Plugin >Returns to> SQLite
        SQlite <Retuns to< Plugin
    Plugin <Returns to< AutoItCode

if you can get that don your the king

[JSThePatriot]

Could it not be more like...

>> = Calls

<< = Returns

AutoIt >> Plugin >> SQLite Lib.
SQLite Lib. << Plugin >> SQLite Lib (Callback)
SQLite Lib. << Plugin << AutoIt

Does that seem possible to you? Or does AutoIt need to process information for the "callback" to function properly? It would seem to me that the Plugin could handle the callbacks, and return a valid AutoIt variable.

JS

[piccaso]

depends on what you want to do.

if you just want to wrap sqlite3_exec then its fine.

but there is no need to wrap sqlite3_exec, there is allready sqlite3_gettable...

if you want to implement aggregats, user functions and so then

it must be possible to write them in autoit otherwise it wont make sence (for me)

[JSThePatriot]

depends on what you want to do.

if you just want to wrap sqlite3_exec then its fine.

but there is no need to wrap sqlite3_exec, there is allready sqlite3_gettable...

if you want to implement aggregats, user functions and so then

it must be possible to write them in autoit otherwise it wont make sence (for me)

Yea I didnt think of User Functions in that last model, but I still think it could be done in AutoIt, using some functions in the plugin. I guess this will all be stuff I have to work out as I go along.

JS

[piccaso]

Added _SQLite_Escape()

SQLite.zip

good luck JS :">

[ptrex]

@Piccaso

thanks for the new contributions !!

Comming to the point with what I was referring to before :

There are the changes I am hoping you would inorporate in the UDF's

; _SQLite_Startup = OK

; _SQLite_Shutdown = OK

; _SQLite_Open =OK

; _SQLite_Close =OK

; _SQLite_GetTable =OK

; _SQLite_Exec =OK

; _SQLite_LibVersion =OK

; _SQLite_LastInsertRowID =OK

; _SQLite_GetTable2d =OK

; _SQLite_Changes =OK

; _SQLite_TotalChanges =OK

; _SQLite_ErrCode =OK

; _SQLite_ErrMsg =OK

; _SQLite_Display2DResult =_SQLite_2DResult2Console

; _SQLite_FetchData =_SQLite_Query_SingleRow ?? dubble ??

; _SQLite_Query =OK

; _SQLite_SetTimeout =OK

; _SQLite_SaveMode =OK

; _SQLite_QueryFinalize =OK

; _SQLite_QueryReset =OK

; _SQLite_FetchNames =_SQLite_ColumnNames

; _SQLite_QuerySingleRow =OK what is the difference with _SQLite_FetchData ??

; _SQLite_SQLiteExe =OK

; _SQLite_Encode =OK

; _SQLite_Escape =OK

I am not asking much here, am I ?

But the changes will clarify the UDF's much better.

Once we agree on the naming standards we can add the documention as well.

Piccaso can you review this and give some feedback.

Regards

ptrex

Edited September 24, 2006 by ptrex

[piccaso]

v1.73 is here

Changed _SQLite_Escape() New szString Reading method, Result will no longer be truncated to 256 chars by default

Changed _SQLite_GetTable*()New szString Reading method, Result will no longer be truncated to 64 chars by default

SQLite.zip

@ptrex

_SQLite_Display2DResult does not only write to console...

MsgBox(0,"Result",_SQLite_Display2DResult($aResult,Default,True))

_SQLite_FetchNames =>_SQLite_FetchColumnNames

how about that ? like _FetchData it uses a prepared query to...

_SQLite_QuerySingleRow is a wrapper for _SQLite_GetTable, it returns only the first row and doesent need as much parameters as _GetTable

_SQLite_FetchData fetches the next row from a _SQLite_Query prepared query and is the only way (in this udf lib) to read out binary data

i have asked bevor but i still want to know:

is there something that litex can do that this wrapper isnt able to?

[ptrex]

@piccaso

Comparison LiteX to SQLite DllCall functions :

.version = _SQLite_LibVersion

.close = _SQLite_Close

.Open = _SQLite_Open

.Prepare ($SQL) = _SQLite_Query

.Changes = _SQLite_Changes

.LastInsertRowid = _SQLite_LastInsertRowID

.OpenInMemory() = _SQLite_Open

.ActiveConnection = ?? N/A

.ColumnCount = ??

.ColumnName(idx) = _SQLite_FetchNames ??

.ColumnType( idx ) = ??

.ColumnValue( idx, [type] ) =

. Row = _SQLite_QuerySingleRow

.ParameterCount = ??

.BatchExecute( sql ) = ??

.ParameterName( idx ) = ??

.execute("BEGIN TRANSACTION") = _SQLite_Exec(-1,"Begin;") ??

.execute("COMMIT TRANSACTION") = _SQLite_Exec(-1,"Commit;") ??

.Done = ??

.RowCount = ??

.Step( [nSteps] ) = ??

Most of the ?? functions are incorporated in a SQLite DllCall function.

But it is more practical to have these available as separate functions like in LiteX.

Also having the possibility to get data out of the DB by refering to there IDX is interesting, like ColumnName(idx). Because you don't need to know the column names for it.

But I think first we need to agree on the naming of the existing functions before adding new functions :

_SQLite_FetchNames =>_SQLite_FetchColumnNames

I agree with this one. Nevertheless "FETCH" doen't add something meaningfull.

Because a query by default fetches data, that is what query is about.

If it was up to me I would remove the word "FETCH" out of all the UDF names. It make the name shorter as well , but this is pure personal.

_SQLite_QuerySingleRow

= OK this is a good UDF

_SQLite_FetchData sould also be split up in more sub functions see below.

Have these functions seperate available as well, rather than have everything integrated in 1 UDF with paremaeters :

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_type", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "str:cdecl", "sqlite3_column_text", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_bytes", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_column_blob", "ptr", $hQuery, "int", $i)

.....

picasso I hope this is not too much at ones ?!

PS : I have updated the CHM helpfile can I sent it to you ? Because I don' t have enough capacity to upload it here.

You can download the update here http://www.autoitscript.com/fileman/index.php?act=list&dir=ptrex

regards

ptrex

Edited September 25, 2006 by ptrex

[gcriaco]

The following script causes a runtime error (report is attached) with the statement _SQLite_QuerySingleRow if no records exist into the DB:

#include "Include\SQLite.au3"
#include "Include\SQLite.dll.au3"

Local $aRow
_SQLite_Startup ()
_SQLite_Open (@ScriptDir & "\Apps.db")
_SQLite_Exec(-1,"CREATE TABLE tabCat(Cat TEXT, CatId NUMERIC)")
;~ _SQLite_Exec(-1,"INSERT INTO tabCat (Cat,CatId) VALUES ('Category 1','1');")

_SQLite_QuerySingleRow(-1,"SELECT max(CatID) FROM tabCat;",$aRow)
MsgBox(0,"Max Row: ",$aRow[0])

_SQLite_Close ()
_SQLite_Shutdown ()

I used the last AutoIt Beta version + SQLite.au3 v. 1.73 and SQLite.dll v. 3.3.7.

With AutoIt v. 3.2.0.1 this error occurs:

C:\AutoIt\AppsCat\Include\SQLite.au3 (1113) : ==> AutoIt has encountered a fatal crash as a result of:

Unable to execute DLLCall.:

$aStrLen = DllCall("msvcrt.dll", "int", "strlen", "ptr", $iszPtr)

Please help

Peppe

[gcriaco]

The following script causes a runtime error (report is attached) with the statement _SQLite_QuerySingleRow if no records exist into the DB:

#include "Include\SQLite.au3"
#include "Include\SQLite.dll.au3"

Local $aRow
_SQLite_Startup ()
_SQLite_Open (@ScriptDir & "\Apps.db")
_SQLite_Exec(-1,"CREATE TABLE tabCat(Cat TEXT, CatId NUMERIC)")
;~ _SQLite_Exec(-1,"INSERT INTO tabCat (Cat,CatId) VALUES ('Category 1','1');")

_SQLite_QuerySingleRow(-1,"SELECT max(CatID) FROM tabCat;",$aRow)
MsgBox(0,"Max Row: ",$aRow[0])

_SQLite_Close ()
_SQLite_Shutdown ()

I used the latest AutoIt Beta version + SQLite.au3 v. 1.73 and SQLite.dll v. 3.3.7.

With AutoIt v. 3.2.0.1 this error occurs:

C:\AutoIt\AppsCat\Include\SQLite.au3 (1113) : ==> AutoIt has encountered a fatal crash as a result of:

Unable to execute DLLCall.:

$aStrLen = DllCall("msvcrt.dll", "int", "strlen", "ptr", $iszPtr)

Please help

Peppe

fc64_appcompat.txt

[piccaso]

Fixed that bug with null pointers peppe reported.

@Ptrex

i have to do more research on slqite to answer your post, tay tuned

[ptrex]

@piccaso

OK I will forward to it.

Don't forget to add my updated "Syntax Help CHM" in your zip for distribution.

See post #555

regards,

ptrex

Edited September 25, 2006 by ptrex

[piccaso]

@ptrex

_SQLite_FetchData sould also be split up in more sub functions see below.

Have these functions seperate available as well, rather than have everything integrated in 1 UDF with paremaeters :

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_type", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "str:cdecl", "sqlite3_column_text", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_column_bytes", "ptr", $hQuery, "int", $i)

DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_column_blob", "ptr", $hQuery, "int", $i)

does this make sence in a typeless language like autoit?

i ment i have to do more research on litex

but it can tell you

This is probably true...

.execute("BEGIN TRANSACTION") = _SQLite_Exec(-1,"BEGIN TRANSACTION")

.execute("COMMIT TRANSACTION") = _SQLite_Exec(-1,"COMMIT TRANSACTION")

.execute("Begin;") = _SQLite_Exec(-1,"Begin;")

.execute("Commit;") = _SQLite_Exec(-1,"Commit;")

.ColumnName(idx) = _SQLite_FetchNames

.ColumnCount = _SQLite_FetchData -> The size of the returned array

.ColumnType( idx ) = we have no types but you can query for that in the master table

.ColumnValue( idx, [type] ) = _SQLite_FetchData -> $aResult[idx]

.Step( [nSteps] ) = _SQLite_FetchData could be called repeatly to do more than one step, this can be done in sql too

.Row = _SQLite_FetchData -> $aResult

there is no '_SQLite_QuerySingleRow' in litex

.Done = just tells you if the last row was fetched

i miss 'finalize' ...

.BatchExecute( sql ) = _SQLite_Exec

.ParameterCount & .ParameterName( idx ) = for bindings

.ActiveConnection = returns the 'Connection object' wich is like $hDB

.RowCount = this is possible by counting rows yourself, but it would be a big slowdown if done every query

Thanks for the Syntax helpfile, ill add it in the next release

edit: updated 'the List'

if you ask me, i dont miss a thing

but i've never uesed litex

of course autoit creates no objects so the autoit version will allways be compleatly differen.

i should rather ask you: 'what functionality do you miss?'

imo the number of methods/functions isnt that what counts.

Edited September 25, 2006 by piccaso