SQLite semi Embedded database functionality in AutoIt

[ptrex]

@randallc

Your the expert in Excel !! I ran the example and it works fine for me.

(No errors like in the ExcelCOM examples)

Good job.

Randallc do you think you can modify this one to do the ListView selection export.

You know the one the only brings over the selected records from the LV.

Never got that to work

[blink314]

Found out how to make the message boxes go away... there is a View Only Mode property.

Download the documentation at:

http://www.microsoft.com/downloads/details...&DisplayLang=en

[blink314]

Ok, ONLY try this version if you want things not to work as expected!!!

This version basically is a test of the excel object as a listview. There are various GUI problems elsewhere that still need to be worked out. To test, open a database and click on the tables. Anything else is questionable! The guictrlcreateobj does not appear to like tabs so I had to redo the interface slightly. The SQL tab is not ready yet.

I will continue to clean this up and hopefully have everything working and somewhat clean before I get out of work today (assuming I dont get anything thrust on me!).

Kevin

Database.au3

[jpm]

does this version open Nwind.db?

[blink314]

The version in the last post does NOT fix Nwind.db. I have just fixed it and the new version will be uploaded once I get some of the GUI things cleaned up. Nwind is a nice test database I will say that! Lots of entries.

Kevin

Turns out I was parsing the CREATE statements wrong. However, in trying to fix it I found that StringSplit wasnt splitting on the delimiter. I had to work around that and am now using a different function. It's all good now though. I can load NWind and have each table come up in the Excel Listview.

Edited November 14, 2005 by blink314

[ptrex]

The last version of post #103 has 6 errors when running ?

Maybe a later version would be better.

The guictrlcreateobj does not appear to like tabs

If you can upload the latest version I can have a look and help where needed.

Edited November 14, 2005 by ptrex

[jpm]

The version in the last post does NOT fix Nwind.db. I have just fixed it and the new version will be uploaded once I get some of the GUI things cleaned up. Nwind is a nice test database I will say that! Lots of entries.

Kevin

Turns out I was parsing the CREATE statements wrong. However, in trying to fix it I found that StringSplit wasnt splitting on the delimiter. I had to work around that and am now using a different function. It's all good now though. I can load NWind and have each table come up in the Excel Listview.

I am curious to see where StringSplit was not splitting!!!

[blink314]

Wheeew... well it took awhile but I think we're almost back to where we started this morning with the addition of the Excel Control and the Nwind fix. Still a few GUI oddities (Excel Control does not resize when window is maximized; some of the controls arent drawn quite right... add a new field to the create table part and when you add the field everything gets messed up.) but, things appear to be working. The SQL tab should be working and I've tested the actions that I thought were affected by the control change.

I still want to get the Excel Object function (last function at the bottom) more sub-routine-ish (meaning more able to be inserted anywhere without problems) but that's it for tonight. I will continue working tomorrow. Let me know if anything exciting happens while trying to run this version.

Kevin

Yes jpm I was extremely wierded out by it as well. Basically, I was trying to split the CREATE statement the table name as a delimiter and it kept splitting wrong. I dont know what the problem was... once I figured out a way around it I kept going.

Database_1.au3

Edited November 14, 2005 by blink314

[blink314]

One other thing to note: I commented out all owc object creates except for owc11. If you have other versions you may need to manually change this.

Ptrex: see if this next version gives you the errors and let me know. I'm done for tonight (Ground School for three hours) but tomorrow I can bugfix.

Kevin

[ptrex]

@blink314

I have tested your latest version, and the only 1 error that came up.

Line 1473 ;.ViewOnlyMode = True I had to comment out. (Than it runs OK except for the GUI problems)

I told you before that putting the EXCEL GUI in place of the ListVist, is not a good idea.

Here are some main reasons why :

- You will have to build in International character setting support

Example if the data from my example db is loaded, all numbers with a "," as decimal separator, are not

recognized correctly.

Randallc and I have been down this road for a long time, and it does work out. This is a time consuming

and frustrating activity to get it all correct.

- You will always have problems with the CONTROL position (and resizing)

- Columns don't fit the data in it after refreshing

My advise : Is to move back to the previous layout version, and use the OWC only for Export.

Some of us have been around this long enough, to see where the pitfalls are.

Please don't waste your valuable time on this. There are much more interesting things to discover.

Once it is all working correctly, I can show you lot's of other interesting SQL tips and tricks.

Second Advise is to store the SQL Qry, VIews, Triggers, Functions in the database, as I was telling you before.

Let me explain this more in detail.

I am working with Oracle and MS SQL as an administrator for years.

Non of the SQL databases ever store there SQL outside the DB.

One example is the "SELECT name,sql FROM sqlite_master WHERE tbl_name='Test'". If this SQL description was not stored in the SQLite dll. How could you ever have made this application to work ?

Which does not mean that once they are saved in the DB, that you can export them anymore and save them outside the DB. But that's an other question.

Even if you would store them outside the DB, this would be a waste. Because each DB has a different datamodel, meaning that you can' t apply most of the queries to an other DB. So it has no sence of doing so.

That's why rule number 1, is never store your procedure outside the DB.

Please follow my advise, this will prevent you a lot a frustration and a waste of time.

I hope input can help you move on faster in the right direction.

I tested the Nwind.db and it opened up correctly !!

You have made quite an achievement, that' s for sure. Congratulation

But now the real interesting parts get starting.

[ptrex]

@blink314

I saw that you have put the swith in line 69 to get the to LV back as an option.

This is very wise !!!

But when activating this, there is an error coming in line 605, variable not declares $SSROW.

And when opening the DB the data is not loaded in the GUI and the GUI is not correctly regenerated.

But this is definitely the way to go. Don' t bet everything on the OWC but leave this as an option.

GUI improvements on Excel object line 1495. Don' t use variables for building the GUIctrl.

Because the will be incorrect when maximizing the windows. Use these parameters to to build the Excem ctrl.

$GUI_ActiveX = GUICtrlCreateObj($oExcel, 260, 5, 730, 385)    
        GUICtrlSetStyle ( $GUI_ActiveX,  $WS_VISIBLE )
        GUICtrlSetResizing($GUI_ActiveX,$gui_dockAuto)

Also put the .EnableResize = True to FALSE line 1489

This is the best you can get the cotrol to behave in the GUI. When maximizing the first time it will jump away.

The next times it works OK. I don' t have an explanation for this behaviour. Maybe JPM knows ?

This is a question for the Developers.

Edited November 15, 2005 by ptrex

[blink314]

THanks for checking this out ptrex. Responses to your statements:

- As you noted the listview will have it's own option... however, also as you noted, I have not tried it and I dont expect it to work correctly yet! Since I have Office 2000 at home I downloaded the OWC v11 and was playing with it. I too got the error about ViewOnlyMode. I think it's because we arent using Office 2003. Here at work, with Office 2003, it works fine. Also note that if you dont have the version of Office corresponding to the OWC tools you cant modify the controls except programmatically. I dont mind because I have no other option since OWC9 does not support the Spreadsheet object. "the data doesnt fit the columns after refreshing." Like the ListVIew you can fix this in Excel... I just havent yet. There are two reasons I love the Excel Object: large number of lines, and you can actually know the exact row AND column the user has selected. For these two reasons I will continue to use the Excel object as my default. Support for the listview will continue however!

- Regarding international character support. This is one of those things that I will not even be attempting, sorry. As I stated before, this program is being coded for some personal and work projects I have. I thank everyone for their feedback and I will continue to try to implement things and fix bugs that people bring up. But, there is only so much that I (as a non-programmer and covert SQL learner at work) can do. I understand that there are people who use other character sets other than english, but I dont have the time at work (I can only put off real work for so long!) or at home to implement this. Also the "Excel as a ListView" somewhat falls under this category. I will try to get this mostly working for all cases, but full support is unlikely. Like I said, sorry about this.

- I've come around to your idea about storing SQL in the database (even before you wrote what you wrote above). I will be working on this today.

- I'll try your advice concerning the Excel Object today as well.

Thanks for the feedback ptrex! I'll continue working today after I do some of the real work in front of me. Thanks!

Kevin

[ptrex]

@blink314

Thanks for the feedback.

I understand your position.

But than again what you have done so far, is to good to just leave it as it is.

If you come to a stage where you say, this is it for me. I would be very pleased to pick up what's there, and continue adding extra features. (I have still got some nice things in mind)

This way you and we all of course, can benifit from the efforts you have put in already.

PS: I have posted a in GUI section a question regarding the Excel object behaviour.

Speak to you later.

[blink314]

No problem. I still have many things to add... and one of those is I want to better document the code and make it easier to understand. The whole reason I posted this in the first place is so other people could do what they wanted with it! But, there is much to be done yet!

Kevin

[ptrex]

OK Kevin.

Keep on posting and I will see where I can help !!

[jpm]

$GUI_ActiveX = GUICtrlCreateObj($oExcel, 260, 5, 730, 385)    
        GUICtrlSetStyle ( $GUI_ActiveX,  $WS_VISIBLE )
        GUICtrlSetResizing($GUI_ActiveX,$gui_dockAuto)

Also put the .EnableResize = True to FALSE line 1489

This is the best you can get the cotrol to behave in the GUI. When maximizing the first time it will jump away.

The next times it works OK. I don' t have an explanation for this behaviour. Maybe JPM knows ?

This is a question for the Developers.

Is it what I need to look at.

I am not sure I get the exact picture can you post what to use and how to use to repro the behavior you are fighting with?

Thanks

[blink314]

jpm: the problem can be seen by trying to maximize the gui window. The tabs resize to fill the window but the excel object just sits where it was. It doesnt even move over to make room for the expanded treeview on the left.

Another GUI issue is that I couldnt get the Excel Object to show up on a tab. It just wouldnt show. I could right click on it and it would respond. BUt there was no visual cue or left click response. Right now, I kind of like the arrangement I have without the excel object on tabs, but it might be something that activex gui controls have problems with.

One more thing. Could you take a look at the arguements I use when creating the GUI? The two that I have in there are required for the Excel Object to work correctly. Taking out one (I forget the specifics, sorry) takes away the window controls (the X and the resize) while the other one allows the Excel Object to redraw without causing the screen to flicker annoyingly. However, as the GUI works right now, some of the controls at the bottom of the screen do not show correctly and are not redrawn correctly.

Those are the GUI issues I can think of. I havent read Ptrex's other post... but hopefully this gives some background.

Thanks for looking,

Kevin

[ptrex]

Another GUI issue is that I couldnt get the Excel Object to show up on a tab. It just wouldnt show

A little correction here blink314. I is possible to show the control on a TAB if you use the parameters I showed you before.

$GUI_ActiveX = GUICtrlCreateObj($oExcel, 260, 5, 730, 385)    
        GUICtrlSetStyle ( $GUI_ActiveX,  $WS_VISIBLE )
        GUICtrlSetResizing($GUI_ActiveX,$gui_dockAuto)

If you follow my amendmends the GUI will show and resize correctly (Even on a TAB)

see also post #111 for more details.

But the problem is still as I described before that. Once you maximize the window and click in the EXCEL object it will jump (only onces ??)

Next time when you minimize the window and maximize the windows it will behave OK.

Please make first the corrections and give it a try.

Edited November 15, 2005 by ptrex

[ptrex]

blink314

adding the properties GUICtrlSetStyle ( -1, $WS_VISIBLE )

To each control this is not showing correctly on the TAB solves the missing controls.

Like this in line 308

$SQLInsert = GUICtrlCreateInput("", $xBorderL + 10, $SQLinsertY + $SQLlabelHeight, 500, 100)
GUICtrlSetStyle ( -1,  $WS_VISIBLE )

So this leaves only the problem of EXCEL object jumping around the 1st time when maximazing the window.

Please add the corrections and let me and JPM know if it's OK like this.

[blink314]

I changed the Excel Object create to be fixed numbers and it works but this is mildly distressing since the whole power of the GUI layout is that I can change one control and have the others move to follow suit. I cant do that by explicitly stating a controls coordinates! Oh well, see what happens,

Kevin