SQLite semi Embedded database functionality in AutoIt

[blink314]

hey ptrex, I saw your excel object example... is there any way to turn that into more of a listview? I mean for some things I like to be able to not let people change data. Also, is there a way to get rid of the toolbars, sheet change buttons, etc. These are all very powerful, but for certain applications I would love to be able to get rid of them.

Kevin

[blink314]

Let's take another stab at it! Here is the new latest version. The main new features are improved handling of PRIMARY KEYS... you dont need to create them now! After some reading I learned about the SQLite ROWID. So many things needed changing. The other big change comes when you create a new field for a new table... many more options!

I may not have updated all the Beginner Mode features so don't totally rely on them. Also I am indebted to SQLite Browser for much help. It does a lot of what I am trying to do... I just don't like the interface at all! But, I've been able to test my ideas on it and see what SQL commands it is calling.

My next item on the todo list is mow the grass... but, my next todo as far as this program is concerned is to go back and clean up the code. It is rather messy again. Enjoy,

Kevin

Bug Update - immediately after posting this I think I discovered why ptrex saw that crash... and I think I fixed it. ptrex could you try that database again?

Database.au3

Edited November 12, 2005 by blink314

[piccaso]

did someone try this?

how about modifying the sqlite source and change the calling convention to _stdcall ?

there is a wrapper around sqlite3_exec named sqlite3_get_table that doesent need callback...

or using fbsl.dll

(FBSL Supports _declspec dll calls)

[blink314]

Sorry... I dont quite understand most of what you posted. I'm not familiar with dll's or anything more advanced than VBA, AutoIt, or the stuff I studied for electrical engineering in college. A question:

Would either of these methods allow the user to just download the newest version of SQLite and use it? (it wouldn't require a third party to update something)

If so that would be great... but it would require someone else to do it!

Kevin

[piccaso]

changeing calling convention requieres c/c++ skills and i dont know if it works for sure (or if some bugs are introduced to sqlite throu the manipulation)

using fbsl.dll is much easyer but creates a overhead ob about 100kb.

and it would be much more work and a bit of performance loss.

but no third party to update would be needed.

[blink314]

Any further info on the fbsl.dll? I would consider looking into it. SQLite seems pretty fast, I unless the overhead is huge it may be worth it. The 100k number is that added file size to the generated AutoIt exe?

Kevin

Went to fbsl.net... looks somewhat interesting. Any ideas on how one would call SQLite from AutoIt through FBSL?? I would need at least one example.

Edited November 12, 2005 by blink314

[piccaso]

i'm sorry i dont have the time (right now) to write an example.

but ask the devs if _declspec dll calling could be made possible throu autoit then fbsl.dll would not be needed.

its a seperate dll and if you fileinstall it appends about 100k to your exe

[blink314]

Well, more bugs fixed and features added. I'm done for the night, but you can now add indexes and delete them. One warning: the Add Index for the Modify Table group does not work. I'm fighting through the SQL at the moment (looks easy but everything I've tried fails.). I may have to go about it the long way, we'll see.

Kevin

Database.au3

[ptrex]

@blink314

object example... is there any way to turn that into more of a listview ?

Yes this is possible.

See my updated exaple code post #2. Here I added the export to this excel object.

But you can use it directly to dump data in as a replacement for the LV.

Advantage is that this will have not the AutoIT limitations of about 4.000 records, it can contain 65.000 records.

If you want I can add the Excel object to your project to export the LV2excel. Phase 2 can be that you use this as a replacement for the LV.

About protecting the data and removing the toolbar I am not sure if this is possible. This might be something for @Randallc to answer he is more advance in excel COM than I am.

ptrex could you try that database again?

I did a test with the latest version on the Nwind.db. These are the errors when opening the database.

I also added the Nwind.db to post #1 to download an test against it.

C:\_\Apps\AutoIT3\_Development\SQLIte\DatabaseNew.au3 (404) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: 
$ColType = StringStripWS(StringReplace($TempArray[$Count + 1] & " ", $Data1, "", 1), 3) 
$ColType = StringStripWS(StringReplace(^ ERROR
>AutoIT3.exe ended.
>Exit code: 0   Time: 15.873

Add Index for the Modify Table group does not work

If I have the time I will have a look at this

Kevin I must say that you already did a great job and you are a fast learner of SQL(ite).

[ptrex]

@piccaso

Hi there, good to have you in the SQLite area !

Regarding changing the SQL source code ? I am not into C programming so I can' t change the code.

If anyone is willing to do that.

We would be vary happy, if we could get the integration between SQLite and AutoIT completed in some way.

What the wrapper concernes I will definitely have a look at that.

I am in favour of the 100K overhead.

But not everyone agrees as you might have read from the previous posts.

So the closest we will ever get is that someone will write a plugin for SQLite, to be used in AutoIT.

Still no volunteers found to do that

but ask the devs if _declspec dll calling could be made possible throu autoit then fbsl.dll

would not be needed

I will post the question in the developers area.

[ptrex]

@blink314

Hi there was a problem with resizing the TABs when Maximizing the GUI.

To solve this I added this to line 56

GUICtrlSetResizing ($Tab,$GUI_DOCKAUTO); added PTRex

This solves the TAB resizing problem in your last version.

I have some other interesting idea's to be implemented.

When you execute an SQL statement manually, it would be interesting to save them.

My suggestion is to save them into the actual DB where they are executed.

To do this I suggest that by default a SYSTEM table is created with every new DB you create.

In this SYSTEM table. I would add the following fields to save the data in.

SYSTEM

-> Number

-> Type (can be QRY, VIEW or FUNCTION)

-> Statement (Will contain the QRY, VIEW or FUNCTION statement)

-> Description (Will describe short what the meaning is of the the QRY, VIEW or FUNCTION)

This way each QRY, VIEW or FUNCTION can be INSERTED and be retrieved to populate the manual QRY field. So you don' t have to retype all the previous statements again.

Also for later development these STATEMENT can be re-used to build End User GUI's around it.

Very powerfull.

Keep up the good work !!

Let me know what you think of this. Also let me know if you want me to add the Excel object to your project.

[randallc]

Hi,

@blink

Research, no script....

it is all possible if you know how..

owc-part-iii-introduction-spreadsheet-control

Best, Randall

[blink314]

As far as export/import and excel are concerned, I'm not sure what I'm going to do just yet. Right now, I'm thinking about using CSV since this way people arent confined to excel. In reading your other post about the excel grid, I am mildly concerned about requiring Outlook to be installed. I may still add the excel object as a listview, not sure just yet. It may also turn into an option. I have an idea for greater than 4000 listview items... it would require "scrolling" but I think it could work.

Regarding bugfixes and the NWind database. I have some work to do this morning, but after that I will look into what you said. I also will be looking into the excel ideas. With the system database you mean a log database? This would be kind of cool... I'll think about how best to implement it.

I'll be back!

Kevin

after reading Randall's post (I was posting at the time!) the excel object does look promising. With that much control it could take over the ListView's place. I will look at the page you posted in greater detail after I get some real work done! (the stuff I'm getting paid for!)

Edited November 14, 2005 by blink314

[ptrex]

@blink314

Excel as on option.

This is a good idea. Let the people decide what to use.

About the system database. It is not a database but a table within each db.

I will attach a thumbnail of how it should look. This tumbnail will be in the post #1.

It includes also the Export2Excel tab.

I hope you don't mind that I use your application to promote SQLite in AutoIT

@randallc

Thanks for the good link to OWC Excel page !!

[ptrex]

@blink314

I have attached the thumbnail picture in post #1.

I'll think about how best to implement it.

The way it should be implemented is very simple.

When clicking the record in the listview "Raw View" you can use populate the "SQL Entry" box with the value of SQL_STATEMENT column of the System table.

Func Get_SQL() ;Read Selected item from ListView
$line = _GUICtrlListViewGetItemTextArray ($listview)
 If $line = $LV_ERR Then 
   MsgBox(0, "Retry Again", "Nothing Selected, click the first column to select an item.",5)
  Else
 $SQL_record = $Line[3]; reads the 3 element of the LV into Variable

 ;Assign this to a button to read the variable $SQL_record into the SQL input box.
EndFunc

I hope this helps to speed up the development.

[randallc]

Thanks;

so before you make your grid;

$owcWbook = $oExcel.ActiveWorkbook

$owcWSheet=$owcWbook.ActiveSheet

$oExcel.DisplayTitleBar = 0

$oExcel.DisplayToolbar = 0

With $oExcel.ActiveWindow()

.DisplayWorkbookTabs = 0

EndWith

And After, to lock it, if you want;

With $owcWSheet.Protection()

.AllowDeletingColumns = 0

.AllowDeletingRows = 0

.AllowInsertingColumns = 0

.AllowInsertingRows = 0

.AllowSorting = 0

.Enabled = 1

EndWith

Works in your original example @ptrex; i got it! after all

Best. Randall

Edited November 14, 2005 by randallc

[blink314]

Ahhhh... here is what I was looking for. Forget Outlook! Download the web components from http://www.microsoft.com/downloads/details...&displaylang=en

I think I shall include this. I'll work on replacing the listview control with the web control first. After that I will work on incorporating the other changes suggested.

One thing ptrex... I dont know about including a system table within each database. Not saying I wont do it, but something just doesnt sit well. We'll see. But for now, I work on incorporating the spreadsheet OWC.

Kevin

[ptrex]

@blink314

OK nice that you found a separate download for OWC.

But it comes standard with the installation of MS Office as well.

Have a look at my function to export2xls OWC

Func Export2Xls();Read from ListView
GUISetState(@SW_LOCK)
    Local $a_list, $x
            For $x = 0 To _GUICtrlListViewGetItemCount($listview) - 1
                If IsArray($a_list) Then
                    ReDim $a_list[UBound($a_list) + 1]
                Else
                    Dim $a_list[1]
                EndIf
                $a_list[UBound($a_list) - 1] = _GUICtrlListViewGetItemText ($listview, $x)
            Next
    For $x = 0 To UBound($a_list) -1
    $string = StringSplit($a_list[$x], "|", 0)
               ;MsgBox(0,"a_list[" & $x+1 & "]",$a_list[$x])
      For $a=0 To _GUICtrlListViewGetSubItemsCount($listview)-1                 
      If IsObj($oExcel) Then
      With $oExcel
      .cells($x+1,$a+1).value = $string[$a]
      EndWith
       Else
        MsgBox(0,"Reply","Not an Object",4)
      EndIf
        Next
    Next
GUISetState(@SW_UNLOCK)
EndFunc

What the replacement of the LV concerns with Excel OWC, I am not sure if this a good choise.

To have to option might be better. People who don' t have office or OWC can still use the front end with the native ListView against it. To is less overhead of course.

Regarding the System table. Maybe you fail to see the benifits for now, but you will see that definitely later on when you start using SQL to its full extend.

I have been on that road before, believe me.

Till later.

[blink314]

Any way to catch "errors" from the OWC? If I make a sheet read only, every time Attempt to change something it brings up a messagebox. Is there any way to trap this so the message box wont show up?

Kevin

[randallc]

Hi, Don't know;

but fix protection thus:

With $owcWSheet.Protection()

.Enabled = 0

EndWith

Func Export2Xls()

$owcWbook = $oExcel.ActiveWorkbook

$owcWSheet=$owcWbook.ActiveSheet

$oExcel.DisplayTitleBar = 0

$oExcel.DisplayToolbar = 0

With $oExcel.ActiveWindow()

.DisplayWorkbookTabs = 0

EndWith

With $owcWSheet.Protection()

.Enabled = 0

EndWith

;Read from ListView

GUISetState(@SW_LOCK)

Local $a_list, $x

For $x = 0 To _GUICtrlListViewGetItemCount($listview) - 1

If IsArray($a_list) Then

ReDim $a_list[uBound($a_list) + 1]

Else

Dim $a_list[1]

EndIf

$a_list[uBound($a_list) - 1] = _GUICtrlListViewGetItemText ($listview, $x)

;MsgBox(0,"a_list[" & $x+1 & "]",$a_list[$x])

Next

For $x = 0 To UBound($a_list) -1

$string = StringSplit($a_list[$x], "|", 0)

;MsgBox(0,"a_list[" & $x+1 & "]",$a_list[$x])

For $a=0 To _GUICtrlListViewGetSubItemsCount($listview)-1

If IsObj($oExcel) Then

With $oExcel

.cells($x+1,$a+1).value = $string[$a]

EndWith

Else

MsgBox(0,"Reply","Not an Object",4)

EndIf

Next

Next

GUISetState(@SW_UNLOCK)

With $owcWSheet.Protection()

.AllowDeletingColumns = 0

.AllowDeletingRows = 0

.AllowInsertingColumns = 0

.AllowInsertingRows = 0

.AllowSorting = 0

.Enabled = 1

EndWith

EndFunc ;==>Export2Xls

Randall