SQLite semi Embedded database functionality in AutoIt

[JSThePatriot]

Okay, I have successfully created a plugin DLL that can Open and Close a database. I am going to try to add more functionality shortly.

This will probably be a slow process, as I am already seeing some issues that I am going to run into. I can only return certain variable types, and those dont include array's. I may have to create a work-around until we can get the AutoIt Plugin's to return Arrays.

I did however want to report on my success!

JS

[ptrex]

@JSThePatriot

The _SQLite_Exec() should be able to accept numerous SQL statements

Not correct. I can only accept Resultless QRY statements !!

Though it can handle one just fine, but you mentioned the need for 100 SQL queries

What about 100 qry. By the looks of your reply you have never written a DB application.

@piccaso

the PRAGMA stuff is not an UDF

This is not relevant. I am only trying to execute the command.

and whats keeping you from reusing variables?

Show me how ? I am only used to LiteX, where you dont have to deal with all of this.

It execute anything regardless of which kind of QRY or CMD. Maybe something to learn from.

but if you want to prepare 100 query's and store them for later

I don't waznt to prepare a 100 and then release them. 1 at the ime is enough.

Give me some examples and show us your tricks.

thanks

trex

[piccaso]

expandcollapse popup

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $hQuery,$aRow,$iRows,$iCols,$aResult
_SQLite_Startup()
_SQLite_Open()
_SQLite_Exec(-1,"Create table tblTest (a int,b,c single not null);" & _
                "Insert into tblTest values (1,2,3);" & _
                "Insert into tblTest values (4,5,6);")

; Query 1
_SQLite_Query(-1,"Select RowID,a From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " a: " & $aRow[1] & @LF)
WEnd

; Query 2
_SQLite_Query(-1,"Select RowID,b From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " b: " & $aRow[1] & @LF)
WEnd

; Query 3
_SQLite_Query(-1,"Select RowID,c From tblTest",$hQuery)
While _SQLite_FetchData($hQuery,$aRow) = $SQLITE_OK
    ConsoleWrite("RowID: " & $aRow[0] & " c: " & $aRow[1] & @LF)
WEnd

; using SQLite's Pragma
_SQLite_QuerySingleRow(-1,"Pragma page_size;",$aRow)
ConsoleWrite("Page Size: " & $aRow[0] & @LF)

_SQLite_GetTable2d(-1,"PRAGMA table_info(tblTest);",$aResult,$iRows,$iCols)
_SQLite_Display2DResult($aResult)

_SQLite_Close()
_SQLite_Shutdown()

'PRAGMA page_size;' is as accessible as any other SQL Command...

i Dont want to write a Function, Documentation & Example fore each and every SQL Command...

some of them are commonly used and might get wrapped but not all

[piccaso]

Doc's and Samples are done and would be ready to submit...

I have been working on this since Nov 05 and i am somhow tired

[JSThePatriot]

@JSThePatriot

Not correct. I can only accept Resultless QRY statements !!

What about 100 qry. By the looks of your reply you have never written a DB application.

@piccaso

This is not relevant. I am only trying to execute the command.

Show me how ? I am only used to LiteX, where you dont have to deal with all of this.

It execute anything regardless of which kind of QRY or CMD. Maybe something to learn from.

I don't waznt to prepare a 100 and then release them. 1 at the ime is enough.

Give me some examples and show us your tricks.

thanks

trex

@ptrex

This is coming directly from the SQLite.org website.

int sqlite3_exec(

sqlite3*, /* An open database */

const char *sql, /* SQL to be executed */

sqlite_callback, /* Callback function */

void *, /* 1st argument to callback function */

char **errmsg /* Error msg written here */

);

A function to executes one or more statements of SQL.

If one or more of the SQL statements are queries, then the callback function specified by the 3rd argument is invoked once for each row of the query result. This callback should normally return 0. If the callback returns a non-zero value then the query is aborted, all subsequent SQL statements are skipped and the sqlite3_exec() function returns the SQLITE_ABORT.

The 4th argument is an arbitrary pointer that is passed to the callback function as its first argument.

The 2nd argument to the callback function is the number of columns in the query result. The 3rd argument to the callback is an array of strings holding the values for each column. The 4th argument to the callback is an array of strings holding the names of each column.

The callback function may be NULL, even for queries. A NULL callback is not an error. It just means that no callback will be invoked.

If an error occurs while parsing or evaluating the SQL (but not while executing the callback) then an appropriate error message is written into memory obtained from malloc() and *errmsg is made to point to that message. The calling function is responsible for freeing the memory that holds the error message. Use sqlite3_free() for this. If errmsg==NULL, then no error message is ever written.

The return value is is SQLITE_OK if there are no errors and some other return code if there is an error. The particular return value depends on the type of error.

If the query could not be executed because a database file is locked or busy, then this function returns SQLITE_BUSY. (This behavior can be modified somewhat using the sqlite3_busy_handler() and sqlite3_busy_timeout() functions.)

I would appreciate before you think I have never created a DB application that you do a bit more research. Just because you cannot get it to do something doesnt mean that it wasnt made for that.

Just so you know I have made a few database applications ranging from small businesses to enterprise applications that processed a double the amount of sales that they were used to in half the time.

I hope you read some more documentation before accusing me of no experience.

JS

[piccaso]

_SQLite_Exec is using sqlite3_exec api but the callback parameter is Null because autoit doesent support callback.

So for AutoIt sqlite3_exec allways is a resultless query...

thats probably caused the confusion

btw: will this ever be possible with a plugin ?

SQLite_Exec($hDb,"Select * From Whatever","_Query1")
Func _Query1($aRow)
    ...
EndFunc

do you have an 'in thory' solution for that or user functions?

Edited September 29, 2006 by piccaso

[JSThePatriot]

_SQLite_Exec is using sqlite3_exec api but the callback parameter is Null because autoit doesent support callback.

So for AutoIt sqlite3_exec allways is a resultless query...

thats probably caused the confusion

I see, I didnt know that you set that param to null.

I am trying to figure out at the moment how I can have it callback, and work with AutoIt nicely. I think it can be done, but having a plugin be able to return arrays would be really nice.

I will put SQLite_Exec() into my plugin without the callback feature (set to NULL as the API says) and see how that goes. Opening and closing... even returning handles with my work around is working great. Only issue is there can only be 64 consecutive databases opened at the same time with my plugin... do you think that limit should be higher?

Thanks,

JS

[ptrex]

@JSThePatriot

I hope you read some more documentation before accusing me of no experience.

I can understand your aggitation, but it's not because your read something somewhere that you know how it works.

I have been using SQLite in reallife. And this is what counts, all the rest is just talk.

I see, I didnt know that you set that param to null.

I see that piccaso agrees what what I am saying.

Nevertheless if you are working on an addin. I hope you have ears for the design problems,

that I see in the UDF for piccasso.

@piccaso.

i Dont want to write a Function, Documentation & Example fore each and every SQL Command...

I will.

I can understand that you are tired. I get tired sometimes myself.

I see that you can use the PRAGMA cmd in in a SQL statement as you and I showed.

But why do the functions have to return ARRAY vars (like $aRow[0] ) for only 1 value that gets returned.

Working with all these Arrays in the result set, for these simple and single return values is overkill.

Again I will bring in the LiteX examples to compare with. I know that you don't like it, but is what I have used up till today. And it works as simple as can be.

I will put it in the new post.

BTW thanks for the example.

[ptrex]

@piccaso

Example 1 :

This is an example of how simple it is to write it in LiteX

$Integrity = $odb.execute ("PRAGMA Integrity_check")oÝ÷ ÛYbæ§vØ^­ë.ضƬ²('yÛhjËZÖ«vö«¦åz)ÀºÚNz®¶²²)ï¢[Þu8b²+!£­r©j·¬¶.+^P1ZºÚ"µÍÔÔS]WÔ]YTÚ[ÛTÝÊLK  ][ÝÔYÛXHYÙWÜÚ^NÉ][ÝË   ÌÍØTÝÊBÛÛÛÛUÜ]J   ][ÝÔYÙHÚ^N  ][ÝÈ  [È ÌÍØTÝÖÌH  [ÈoÝ÷ ØLZ^ظ­yv®¶­s`¢b33cµ&V6÷&BÒb33c¶öF"ç&W&RgV÷Cµ4TÄT5B¢e$ôÒgV÷C²fײb33c´7W%F&ÆRfײgV÷C²tU$R&÷vCÒgV÷C²fײb33c´7W$Æ7DæFW§vÆRb33cµ&V6÷&Bç7FWÒ¦f÷"b33c¶46÷VçBÒFòb33cµ&V6÷&Bæ6öÇVÖæ6÷VçBÓ¢b33c´7W%&V6÷&E²b33c¶46÷VçB³ÒÒb33cµ&V6÷&Bæ6öÇVÖçfÇVRb33c¶46÷VçB¤æW@oÝ÷ Ù$×V®¶­seõ5ÆFUõVW'ÓÂgV÷Cµ4TÄT5B$õtB¢e$ôÒFW7Bõ$DU"%²gV÷C²Âb33c¶VW'¥vÆRõ5ÆFUôfWF6FFb33c¶VW'Âb33c¶&÷rÒb33cµ5ÄDUôô°¤×6t&÷ÂgV÷Cµ5ÆFRgV÷C²ÂgV÷C´vWBFFW6ærfWF6FF¢gV÷C²fײ7G&ætf÷&ÖBgV÷C²RÓ2RÓ2RÓ2RÓ2gV÷C²Âb33c¶&÷u³ÒÂb33c¶&÷u³ÒÂb33c¶&÷u³%ÒÂb33c¶&÷u³5Òfײ5"¥tVæ

You can see the basic difference between the 2 as well.

Anyhowe I will leave it up to you whatever you decide to do with it further.

I least you created a breakthrough for SQLite in AutoIT and that means something. Even if I don't like how the UDF is working

For me the UDF will pass, if I can write the SQLite_GUI application, with as simple instructions as it is done now. See the firts post (writting using LiteX)

regards

ptrex

[piccaso]

i know what your after ptrex

but this library is based off the sqlite3.dll exports.

Thats why results are allways passed out by reference...

_SQLite_QuerySingleRow() passes out an array because its a Row

and if there is only 1 columns this is an array with a single entry.

of course you can have multiple columns.

you are free to add functions to the library, like i sayd before

everybody is welcome to participate

i'll help you but i wont spend to much time on something that will be replaced by a plugin someday.

even if the plugin is out of sight for now.

Only _SQLite_GetTable*() will have compareable speed to the plugin, everything else will be much slower

if your familiar with SVN pn me your email and i'll create an account for you

[ptrex]

@piccaso

OK I understand your position.

I will see if I can simplify in any way your Functions.

But than I probably I won't stick to the UDF standards as you did. Because it'll take too much time to progress.

I will let you all know the status as I go along.

But I will wait till the official release of the UDF and pick it up from there.

thanks again for all the efforts you have put in here

And I'm sure some people who are building very simple DB applications in AutoIT will surely benifit from what you did.

regards

ptrex

[piccaso]

some final tweaking...

Faster szString Reading, Function Header corrections

Changed _SQLite_Exec() Callback

SQLite.zip

i allmost forgot the callback thing

#include <sqlite.au3>
#include <sqlite.dll.au3>

Local $hQuery,$aRow
_SQLite_Startup()
_SQLite_Open()
; Whithout $sCallback its an Resultless query
_SQLite_Exec(-1,"Create table tblTest (a,b int,c single not null);" & _
                "Insert into tblTest values ('1',2,3);" & _
                "Insert into tblTest values (Null,5,6);")

_SQLite_Exec(-1,"Select oid,* From tblTest","_cb") ; _cb Will be called for each row
Func _cb($aRow)
    For $s In $aRow
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@LF)
    ; Return $SQLITE_ABORT ; Would Abort the process and trigger an @error in _SQLite_Exec()
EndFunc
_SQLite_Close()
_SQLite_Shutdown()

@ptrex

Why just simple ?

everything thats possible throu sqlite's api is accessible...

[ptrex]

@piccaso

Why just simple ?

I see you updated the SQL_Exec function. This is very good.

That is what I am talking about when I speak about "simple".

In Simple I mean not in terms of missing functionality. But in terms of Function Syntax.

If the syntax is to complex than it is a "no go" for using it in bigger projects. It should be easy to use and the syntax should be kept as simple as possible.

This is one of the major benifits of AutoIT itself. It has a low level, entrence level with huge power underneath.

The same goes for SQLite. But once the syntax gets in the way, it finished for most of the AutoIT users.

And that would be a shame

The thing you did with the EXE function is the correct approach.

The next one should be the SQL_Query to attack.

Then do a renaming of 2 or 3 functions, like discussed earlier.

And I think it will be finished for now !!!

EDIT

PS: I think that once this is accomplished you will have the best of both worlds in the UDF.

The good naming structure of PHP functions and an easy syntax of LiteX.

And people will allways remember your for this !!!

ptrex

Edited September 29, 2006 by ptrex

[JSThePatriot]

@JSThePatriot

I can understand your aggitation, but it's not because your read something somewhere that you know how it works.

I have been using SQLite in reallife. And this is what counts, all the rest is just talk.

I see that piccaso agrees what what I am saying.

Nevertheless if you are working on an addin. I hope you have ears for the design problems,

that I see in the UDF for piccasso.

@ptrex

Sorry, I did get a bit too agitated. I know that just because I have read something somewhere doesnt mean I know how it works. It was a failure of communication due to my lack of research on the AutoIt side of that one.

I have use SQLite in RL as well. If you dont recall I have built an application with it, hence how I found this entire thread in the first place, I just never tried to use multiple statements, nor cared to, but that doesnt really matter. The plugin will eventually take care of that in reguards to a callback function, to allow full SQLite abilities.

I am listening to everything both of you are talking about. I agree that everything should be kept as simple as possible for the end user.

@piccasso

I dont believe the plugin is as far out as I have originally thought. I keep running into minor hiccups, but then as I sleep I dream up the answer (I am not joking, my subconscious tells me in a dream). When I get SQLite_Exec() in there, I will put up a plugin DLL for download and testing of the first 3 functions. I want to make sure each step of the way is OK before proceeding as that will help keep the debugging time down when the plugin is completed.

@everyone

The plugin DLL is already 323KB, is this going to be a problem for anyone? There will be a small set of UDF's that will need to go with the plugin until the plugin architecture can handle the type of returns that would be needed for this to work flawlessly.

Thanks for everyones interest, and again ptrex sorry for jumping the gun and getting a bit agitated.

JS

[ptrex]

@JSThePatriot

The plugin will eventually take care of that in reguards to a callback function, to allow full SQLite abilities.

Never thought that someone would write a plugIn for it !!

If you get it to run, youre the

I want to make sure each step of the way is OK before proceeding

This seems to be a good approach.

regarding the size of the DLL it's not a problem for me since the original DLL is about the same size.

And we don't need that anymre, so this will compensate eventually.

ptrex sorry for jumping the gun and getting a bit agitated.

Not a problem it happens to me too sometimes

Thanks for the efforts so far.

PS : Did i get it write that you got in touch with the author of the SQLite developer ?

ptrex

[JSThePatriot]

@JSThePatriot

Never thought that someone would write a plugIn for it !!

If you get it to run, youre the

This seems to be a good approach.

regarding the size of the DLL it's not a problem for me since the original DLL is about the same size.

And we don't need that anymre, so this will compensate eventually.

Not a problem it happens to me too sometimes

Thanks for the efforts so far.

PS : Did i get it write that you got in touch with the author of the SQLite developer ?

ptrex

Yes, he has responded to one of my emails about a week ago, he was in meetings all that day, and was going to contact me back, and told me that if he didnt to recontact him. I have, and am awaiting a response. If I dont hear from him by Monday, I will drop him another polite email informing him of the progress I have already made, and see how he can help me as there are some issues I am having.

For the sake of testing what do you think of Exec only "Executing" SQL statements, but not if its a query. Then I could use sqlite3_get_table for the return of query information. sqlite3_get_table is actually a wrapper for exec... I just got an idea... hehe I'll get back to you on this one.

Also sqlite3_exec is also a wrapper for another long set of functions. I will try to stick with exec if I can, but I may have to break into using all the other functions due to AutoIts limitations.

JS

[JSThePatriot]

I wont be on for the rest of the day. I have to help my dad move.

I have gotten sqlite3_exec working (only inserting data, and creating tables so far)!!

This is great progress. I will have to work on it a bit though, I had an error in my SQL, and it didnt error the way I thought it would.

This will be a process, but hopefully a fun one for you guys as much as it is for me!

JS

[piccaso]

@ptrex

you got me wrong.

im done here.

i will support it (fix bugs, etc...)

and if a new 'life changeing' export is added to sqlite3.dll i may wrap it

but why should i break compatibility and add some silly functions i dont need ?

i recieved 'a lot' of support requests and no one ever complained about function nameing or missing functions.

90% of this requests would not have been if documentation would have existed...

JS is motivated and will probably add everything everyone wants to his plugin

which will probably be faster than my wrapper...

@JS

For comparison, the wrapper i wrote adds 224kb to the autoit exe

but the dll is compressed (194kb)

if you upx your plugin you will probably get about the same result...

[piccaso]

JS, is your projects soruce open?

did you put it on a public repository?

... if not and you want to i would recomend https://opensvn.csie.org/

i would realy like to take a peek on what your up to

i cant realy write c/c++ just a little but i can read it.

and since there is a need for a autoit part maybe i could maintain it...

if you want, and i have enaugh time...

[ptrex]

@JS

sqlite3_exec

I would keep the functionality as it is, for resultless Qry statements

sqlite3_get_table

I would prefer sqlite3_Query, but this is a matter of tasted of course.

Better would be that "sqlite3_exec" could handle both the resultless QRY and the normal QRY.

Like LikeX can do. This is one less function to memorize. The easier the better.

@piccaso

im done here.

I am sorry to hear that.

no one ever complained about function nameing or missing functions.

If you don't know anything better, than it doesn't surprise me that no one is complaining.

You can only comment if you know that there other who did things different.

And complaining is not the correct word. I am only advising for IMPROVEMENTS.

Like AutoIT keeps up high standards, I think we should too.

And since I started the SQLite topic, I should watch over this that we meet high standards.

Again don't get me wrong.

The UDF is a great job. But it still needs that little extra change here and there.

Even if the others don't complain (out of respect to you), or out of ignorance.

I feel it's my duty to comment for improvements if I see them.

Sorry for that.

EDIT:

@Piccaso/JS

Did either of you take the effort to read through the script if SQLit_GUI (see first post).

Please do and take the time to study what SQLite (=LiteX) has to offer.

regards

ptrex

Edited September 29, 2006 by ptrex