SQLite semi Embedded database functionality in AutoIt

[ptrex]

@blink314

- Excel Jumping is not solved using your solution. So I will put mine (back in and test it.

GUISetState(@SW_SHOW, $Main)

$tmp = WinGetPos($GUIText)

MouseClick("left", $tmp[0] + 275, $tmp[1] + 30,1,1)

This is the one the helps (at least at my side.)

- Importing existing : tested and OK

- Open "" selection does not create a .db file : tested OK

Thanks Kevin

[ptrex]

@blink314

- Status line is not showing correct #. This is 1 short. To solve this the line 642 should +1,

guictrlsetdata($StatusLabel,"Record " & $SSRow+1)

- When een TABLE is clicked from a tree with 0 items, the status is still showing the previous #.

So a RESET $StatusLabel = 0 should be placed at the beginning of the Function.

I like the statusbar a lot. I had this also in mind. If I was you I would put in the more info.

Like the PATH and DB name, could be moved in there. Remove the Editable field.

So you will have more space on top of the TREEVIEW. More ideas come as we go.

Nice Job so far.

What are your next plans nearly everything is working now. Are you planning to tackle the TO DO list ?

[ptrex]

@Kevin

- Import NEW give a COM error in line 1656.

Maybe I did not use the correct procedure. But I have tried different ways. No succes.

This is what the LOG says ;

CREATE TABLE Atable(Don't forget the :: delimiter!! , Don't forget the :: delimiter!! , Don't forget the :: delimiter!! , Don't forget the :: delimiter!!)

But I' ve tried with and without delimiters.

Edited November 17, 2005 by ptrex

[ptrex]

@blink314

- Tested SQL Entry more than 1 line. OK (perfect !!)

- Small correction, when executing 2 queries in a row the GRID is not cleaned in between.

Still records of the 1st Qry are showing.

I have updated the FIXES and TO DO with the test results I did so far.

Kevin Maybe you can add the SQLite version in the statusbar as well. To retrieve the version see my Example in the post #2

Edited November 17, 2005 by ptrex

[blink314]

- Excel jumping: THe only thing I added was a mouse move after the click so the user didnt have to wonder where his mouse went. I'm having no problems with it.

- Statusbar will be updated as I figure out where I want to see information. THe text in it will be as short as possible to allow loops to spend as little time in it as possible. As far as replacing the input control, I am keeping that there because in the frontend there will probably be a combobox there. I still have to think about the frontend so all that could change.

- Import New: your 1st row in excel has fields that look like this?

Field1::1

Field2::1,9

etc

- Tackling the todo list? I am almost ready to call a brief halt to this part of the program. I want to start work on the frontend and see what features I will need to add to make that work correctly. This doesnt mean I'm not going to add things from the todo list, but before I go too much further I want to start seeing how the front end design will influence what I need. Most of the things on the todo list can be done through the SQL tab. The GUI is for rapid creation of common commands.

- The whole MISC thing in the treeview has it's place. If you create a unique column it stores it's data in the table as well. These will show up in misc. The problem is I have not cleaned up the algorithm so the misc also catches parts of the CREATE INDEXES.

- SQLite version will not be added in the statusbar since the statusbar will occasionally be entirely overwritten. I may make a multi part statusbar for some of this stuff.

3 quick questions:

- cREATE VIEWS are not stored in the sqlite_master?

- Why does number("A") return true??

- load database into memory? How do you do this?

Kevin

[ptrex]

@blink314

- Excel jumping:

This depends on the screen resolution. I may work on yours but not on mine.

- statusbar

You can add more labels in a row, with different data elements in it. And updating saprarately. So that is possilbel to add more info in the statusbar, updating indipendently

- Import New

OK, I will try it. I was missing the syntax requirement should be in the Excel 1st row.

- The whole MISC

Not important like you say, forget it.

- make a multi part statusbar

See above

- cREATE VIEWS are not stored in the sqlite_master?

Some of the statements are not saved in the master. Like SELECT and CREATE VIEWS, ...

That' s why I said to go the SYSTEMS table to store all the TEMPLATE qry statements for the manual qry entry (remember ?). With a function to call them at a later stage.

- Why does number("A") return true??

Is this a SQLite or AutoIT question ?

- load database into memory? How do you do this?

I have to check this out how this is done exactly, but it is possible according to some post on the internet.

- I am almost ready to call a brief halt to this part of the program.

Does this mean that the version you last uploaded is the final one for now ?

I can start Adding/Changing thing as from now correct ?

[blink314]

This version corrects the whole MISC issue.

Adds statusbars.

Inserts numbers as numbers not strings.

Column names are generated for SELECT in the SQL tab.

Almost ready if you want to take over. What I''m thinking as far as SELECTS and VIEWS is to make two more system tables. One for each. THis way you can easily find them without hunting through gobs of other junk (granted, using a select statement would get around this, but I like being able to see it that way). Let me implement this first.

The above tables are implemented. Let me know if they work correctly. If there are no major bugs, I'll get ready to release my 'final' version.

Kevin

Database_1.au3

Edited November 17, 2005 by blink314

[piccaso]

so here is the proof...

expandcollapse popup

$dll = DllOpen("sqlite3.dll")
$r = DllCall($dll, "int", "sqlite3_open", "str", "my.db" _; Database filename
        , "long_ptr", 0); OUT: SQLite db handle
$hSql = $r[2]
$r = DllCall($dll, "int", "sqlite3_get_table", _
        "ptr", $hSql, _; An open database
        "str", "select upper('uPpEr') as x, lower('lOwEr') as y, sqlite_version(*) as Version", _; 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 $r[0] > 0 Then
    $t1 = DllStructCreate ("char[64]", $r[6])
    ConsoleWrite("sqlite Error   : " & $r[0] & @CR)
    ConsoleWrite("sqlite Errormsg. : " & DllStructGetData ($t1, 1) & @CR)
EndIf
$ptr = $r[3]
$nrow = $r[4]
$ncolumn = $r[5]
$results = (($nrow) + 1) * ($ncolumn)
Dim $struct
For $i = 1 To $results - 1
    $struct &= "ptr;"
Next
$struct &= "ptr"
$s = DllStructCreate ($struct, $ptr)
Dim $i2 = 1
For $i = 1 To $results
    $rval = DllStructGetData ($s, $i)
    $s1 = DllStructCreate ("char[64]", $rval)
    ConsoleWrite(DllStructGetData ($s1, 1))
    If $i2 >= $ncolumn Then
        ConsoleWrite(@CR); Next Row
        $i2 = 0
    Else
        ConsoleWrite(@TAB); Next Column
    EndIf
    $i2 = $i2 + 1
Next
$r = DllCall($dll, "none", "sqlite3_free_table", "ptr", $ptr); pointer to 'resultp' from sqlite3_get_table
$r = DllCall($dll, "int", "sqlite3_close", "ptr", $hSql); An open database
DllClose($dll)

the query was

select upper('uPpEr') as x, lower('lOwEr') as y, sqlite_version(*) as Version

and the result looks like

x       y       Version
UPPER   lower   3.2.7

(if you use Scite, Sqlite.dll v 3.2.7 and the latest AutoIt Beta)

and a empty db is created called my.db in the @WorkingDir of the script

a word about '...DllStructCreate ("char[64]"... ' (line 30)

i'm not sure if 64 is the right value, higher values (like 256) work to but arent needet for the example...

@ptrex,blink314

could you try some BIG querys with this...

Edited November 18, 2005 by piccaso

[blink314]

Whoa.... try this select against a slightly bigger database (such as the Nwind example, I used another database I have laying around):

SELECT * FROM sqlite_master

I get this as the result:

type name tbl_name rootpage sql

table test test 2 CREATE TABLE test( a INTEGER PRIMARY KEY, b TEXT COLLATE unaccented, c INTEGER, d FLOAT )

index b test 3 CREATE INDEX b ON test(b ASC)

table TrialTable TrialTable 9 CREATE TABLE TrialTable (Beta TEXT, Google NUMERIC, Trial TEXT)

table DefaultTable DefaultTable 11 CREATE TABLE DefaultTable (Date TEXT DEFAULT CURRENT_DATE, Time TEXT DEFAULT CURRENT_TIME, Text TEXT)

table UniqueTable UniqueTable 10 CREATE TABLE UniqueTable (Trial2 TEXT, Unique1 TEXT UNIQUE)

index sqlite_autoindex_UniqueTable_1 UniqueTable 12 0

table AnotherTable AnotherTable 13 CREATE TABLE AnotherTable (Text TEXT, DateTime TEXT DEFAULT CURRENT_TIMESTAMP)

Looks like you figured something out!! That's awesome work! Have to work on getting this into my script. I have no idea about dll's so it will take some learning. But, then again I didn't know how to use objects when I started this either. Thanks a lot picasso!

Kevin

[piccaso]

...Thanks a lot picasso!

your Welcome

i changed the example a bit so it spits out the sql error if one happens.

and addes some comments to the DllCall's (from sqlite doc)

but the real pain is getting the result out of the strage struct (or whatever it is)...

this should be wrapped up into func's bevore you implement it into your script, and more testing is needed.

(i'm not sure if sqlite3_free_table frees any memory, but DllClose sure does...)

[piccaso]

@picasso

Thanks a lot !! I will test it today. I will also look at the links you provided.

Did you have a change to look at the available C code examples on the SQLite hompage (I have put a link in the post in the developers are) ? I there something that a C programmer could pick up easily to create a dll to be used as plug in .

sorry i overlooked that one...

so your welcome to

writing a plugin shouldnt be that hard if you have c skills, but would you still want it ?

btw: where did you post the link (cant find it) i'm curios about the feedback...

Edited November 18, 2005 by piccaso

[blink314]

If you could write a plugin that wouldn't require recompiling with every new SQLite release that would be awesome. It would make access much easier. I, for one, would be interested.

Kevin

[piccaso]

the example shown above uses the official version.

btw: i'm only writing dll's in freebasic/powerbasic

so if i would write a wrapper it has to be used with DllCall.

[blink314]

Ahhhh....

On the subject of DLL call:

I am looking at the SQLite API. Why do you set $hsql to what appears to be a random number and then use it in the next dllcall? What is $hsql referring to?

Kevin

[piccaso]

Ahhhh....

On the subject of DLL call:

I am looking at the SQLite API. Why do you set $hsql to what appears to be a random number and then use it in the next dllcall? What is $hsql referring to?

Kevin

its a handle like a filehandle from FileOpen...

[ptrex]

Hallo All.

I see that last night was a busy night, lot's of activity. Very nice !!

I appreciate a lot that picasso did not leave us. He has got a nice break through in DLL calling.

It's getting more and more, interesting day by day.

I think we have a nice thing going. Keep it up.

@blink314

What I'm thinking as far as SELECTS and VIEWS is to make two more system tables.

I would not go for a separate table for SELECT, VIEWS, TRIGGERS, FUNCTIONS, .... for obvious reasons.

The better appraoch is to make 1 SYSTEM TABLE with a COLUMN = TYPE (which is the filter for SELECT, VIEWS, ...). I have show you an example before.

Than you can select what is needed by the STATEMENT select * from SYSTEM where TYPE = 'VIEW';

(or any other Type).

Rule 2 in database management is : CREATE as minimum TALES as needed, is you can solve it other wise.

More tables slow down performance of the DB when doing Queries.

- Regarding the creation of a Application front end.

If you plan to create a front end to a DB. Did you consider using Excel as a front end tool ?

Or did you consider using IE as a front end Tool. Or MS Access or ...

Having the LiteX registered anyway. You can do the same (semi embedded thing) in the MS Office apps, as you did in AutoIT. And have much more functionality, available.

If choosing for Excel. You can download (free) from MS site, Excel Addin called CUBE Analysis. And build reports from there. etc. Also using ODBC is an option.

I have lot' s of idea's in that direction.

What I wanted to say is, there are a lot of existing possibility available, with enhanced features.

Why put the effort in creating something new.

Let me know you opinion.

Edited November 18, 2005 by ptrex

[ptrex]

@picasso.

Thanks a lot for the DLL call example. I thought you left us alone.

This is very nice !!!

I wonder if you got that far, why not take a look at a previous attemp of DLL call.

Look here and bundle this in your example. (see the link to the thread/post in the CODE).

;
; http://www.autoitscript.com/forum/index.php?showtopic=8684&st=0&p=61614&#entry61614
; DLLCall using AutoIT
;

#include <GUIConstants.au3>

;GET VERSION - this one also doesn't return the correct value
$version = DllCall("C:\_\Apps\AutoIT3\_Development\SQLIte\SQLiteAU3.dll", "str", "sqlite_libversion")
msgbox(0, "Version", $version)

;OPEN DATABASE - this works fine
$result = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "ptr", "sqlite3_open", "str", "C:\_\Apps\AutoIT3\myDB.db")
msgbox(0, "sqlite3_open", $result[0])

;INSERT DATA into database using "sqlite_exec" command - this works fine too
$exec = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "int", "sqlite3_exec", "ptr", $result[0], "str", "INSERT INTO toons (firstname,lastname) VALUES ('ugh','ugh')", "ptr", "", "ptr", "", "ptr", "")
msgbox(0, "sqlite3_exec", $exec[0])

;READ DATA using "sqlite_exec" - don't know how to get this to work!
$execqqqq = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "int", "sqlite3_exec", "ptr", $result[0], "str", "SELECT * FROM toons", "ptr", "", "ptr", "", "ptr", "")
msgbox(0, "2nd sqlite3_exec", $execqqqq[2])

;INSERT DATA into database using "sqlite_get_table" command - this one works
$execqq = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "ptr", "sqlite3_get_table", "ptr", $result[0], "str", "INSERT INTO toons (firstname,lastname) VALUES ('blah','blah')", "str", "", "int", "", "int", "", "str", "")
msgbox(0, "sqlite3_get_table", $execqq[0])

;READ DATA using "sqlite_get_table" - don't know how to get this to work!
$execqqq = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "ptr", "sqlite3_get_table", "ptr", $result[0], "str", "SELECT * FROM toons", "str", "", "int", "", "int", "", "str", "")
msgbox(0, "sqlite3_get_table_2", $execqqq[1])

;CLOSE DATABASE - this works fine
$close = DllCall("C:\_\Apps\SQLLite\3.2.7\sqlite3.dll", "none", "sqlite3_close", "ptr", $result[0])
msgbox(0, "sqlite3_close", $close)

The advantage of a plugin versus DLL call is that you don't need to ship the DLL, if you want to distribute you application. The plugin gets incorporated when compiling (if I understood well), so you end up with 1 "exe" file.

btw: where did you post the link (cant find it) i'm curios about the feedback...

[jpm]

@ptrex

The advantage of a plugin versus DLL call is that you don't need to ship the DLL, if you want to distribute you application. The plugin gets incorporated when compiling (if I understood well), so you end up with 1 "exe" file.

Slight comment not true in the present beta but that the final goal as I understand

The main avantage of plugin is you call the function as a UDF or AutoIt Functions

[ptrex]

@jpm

Thanks for explaining it correct to the audience !!

[blink314]

@ptrex: well, obviously the obvious reasons are not so obvious. But this is something you can change soon if you want. I will probably just keep the VIEWs table for myself but whatever... I will probably change my format a bunch of times.

As far as a front end: yeah I could use excel, but I dont have an interest in creating graphs or charts, and I'm actually trying to move away from excel... partially just for fun! Why invent something new? Why invent something old? This is a fun project. I'm starting to wish I could sit down with Legos and just have fun... but, I'm too old (23) and too tainted by reality to be able to just sit down and come up with something out of my head unless it serves a purpose. I am trying to make a small application that will store data for home and work. Parts, pictures, quotes, project data, etc. All of these have been done before... why do anything?! But, it's fun, I'm learning new things, and I can make the program work like I want it to work (and have the bugs I want it to have!).

But, one thing I am going to work on is using the dllcall functions. I really want to try to get away from wrappers! I will also be going through and trying to make my program more function oriented so I do things in only one place. Too much time spent updating little bits of code all over the place!

Kevin