Database Access - Any "Best Practice" Suggestion ?

[UmmDoughnuts]

As I've stated before, AutoIT is a terrific tool. Very useful. I have now written many small scripts that either include "data" hardcoded into the script or "data" which is read from a simple text file. Up to this point, that has worked quite well. Now I find that much of this "data" is being copied/pasted into (or read by) multiple scripts and I'd like to put this "commonly used data" into a "database" to allow easy "updates" that would be reflected in all scripts once a change was made.

For example, I now have a text file containing a list of computer names, IP Addresses, etc. that is used by a number of different scripts. I'd like, for example, to be able to perform a SQL type query to select a "subset" of that list - say to populate a GUI control. Yes, I can do this by reading the text file and writing "custom code" in each script... But, as the number of scripts increases, using some "standard" query method seems to be a much better idea.

I've read perhaps HUNDREDS of posts on this topic - discussing MySQL, ACCESS, Excel etc. as "database hosts" and things like ODBC vs. ADO etc. for an access method. My question is... Is there a "best practice" at this point for doing "basic queries" like this ? Right now, any returned recordsets would be "small" - but I'd prefer to start off using a method that would allow for future growth - like using a few "joined tables" and perhaps returning a few hundred records.

Any suggestions on the "best way" to proceed ? I have MS ACCESS and MS EXCEL available and could load MySQL, for example, if that is "better" in some way...

[PsaltyDS]

Any suggestions on the "best way" to proceed ? I have MS ACCESS and MS EXCEL available and could load MySQL, for example, if that is "better" in some way...

Have you tried the example scripts in the help file under the _SQLite*() functions? There are also demonstrations on this forum for using ADO to access .mdb and .xls files as databases. Search is your friend.

Edited April 3, 2008 by PsaltyDS

[UmmDoughnuts]

Have you tried the example scripts in the help file under the _SQLite*() functions? There are also demonstrations on this forum for using ADO to access .mdb and .xls files as databases. Search is your friend.

Thank you for the reply... And... Yes, as a matter of fact I have... And I've successfully coded a "sample" ACCESS and EXCEL "test" using my data. In fact, because there are SO many "options" available with AutoIT I was looking for input - from those whom have lots of "experience" using databases with AutoIT - and whom have used "larger datasets" (which I currently do not) - which method or methods might be "best".

My preference - if I go back and "recode" a lot of scripts - would be to do it "right" using the "best" methodology available... The one that fits not only my "current needs" of small datasets (like pulling data to fill a listbox, for example) - but also "future needs" (where I can see, perhaps wanting to query a few joined tables and return a few thousand records). I'd prefer to "recode" them all "consistently" if possible.

As you noted, for example, there is support for SQLite... And I've seen people use MySQL as well. My "question" was intended to ask - do "experienced users" think it's best to use a "more robust" database (particularly if future DB requests query for more than the "few records" of data that I currently use) from the start... For efficiency purposes, for example... If so, what is considered "best". I realize each person might have their own "favorite" - but I figured enough different "experts" have used databases by now that there might be some "best practice"... But I read LOTS of posts and could not see a "clear winner"... :-)

[PsaltyDS]

The future scale of the data set "...a few joined tables and return a few thousand records" sounds like SQLite to me (non-expert opinion). Big enough have a real database engine, but not big enough to require a dedicated server install, like MySQL. The initial dataset doesn't sound like a database at all, more like an .ini file!

[UmmDoughnuts]

The future scale of the data set "...a few joined tables and return a few thousand records"; sounds like SQLite to me (non-expert opinion). Big enough have a real database engine, but not big enough to require a dedicated server install, like MySQL. The initial dataset doesn't sound like a database at all, more like an .ini file

Thanks... I will take a closer look at SQLite... And yes, I did initially use an INI file... And then I changed to "text" files... And the reason I'm "thinking" about going to a database solution is that... In the beginning I simply had a single list of items, like "computer names". Nine or ten scripts "read" those names and did some work. Everything was fine. Then I needed, for example, to know "computer name" and "model" so I added a "second field" to the text file. Then I had to modify all 9/10 scripts to handle both fields... (Even though a few of them didn't really use the 2nd field data - the would generate "errors" because the format of the file changed...)

This was OK until each record expanded to be SEVERAL fields. I got tired of "updating" code everywhere each time the "data" changed. Particularly when SOME of the scripts didn't even use the "new" fields. So, then I tried the "database" idea using an EXCEL spreadsheet as my data source. That way, I could "query" only the fields required in a particular script - and other scripts were not impacted by the new field data.

Now, as I stated, I'm trying to decide (before I get too far along in the "change all of my scripts" process) what the "best" solution is... As I am "generally lazy" and want to do as little script "maintenance" as possible.

A "database solution" (vs. INI for example) is desirable because - as I mentioned - I can see the "future need" of doing selects, sorts or perhaps some "joined table queries" to supply the data the script needs. (And doing some "logging" to a database file would quickly exceed the current record limit of EXCEL 2003...)

I suppose that the REAL problem is that AutoIT has been SO useful that I am constantly coming up with new scripts to do "little things" - but many of them need "a little data" supplied to them - and I'd like to code all of them consistently... :-)

[amfony]

Hi guy - just to let you know i had the same scenario when i needed to bump up from long xls files (65k row limit) of logging to a DB solution, i just bit the bullet and went with MySQL, not hard to install - not hard to setup, but runing it on a win2k3 server was an issue, so i used the second latest stream version.

Only part was that was a bit of a dud was that i was running autoitScripts on client to input to this DB and i had to install the ODBC driver on all clients, not a big deal as there is a msi for it, and i just re-imaged all my clients with the driver pre-installed.

Id recommend DB - cause now as an after thought ive amlagamated alot of OTHER things into the same server, and also run PHP to provide live reports.

[UmmDoughnuts]

Hey... Thanks for the input... I can definitely see lots of "OTHER" things that a database would handle much better than text files and XLS files... I figured, after reading literally HUNDREDS of posts about SQLite and LiteX vs. the SQLite.au3 UDF that there would (by now) be some "best practice"... But, now that you have successfully used MySQL I'm thinking that it might be the best way to go... Guess I'll wait a little while longer and see if ptrex or piccaso or any of the others from the DB threads chime in. If not, I guess I go with the MySQL / ODBC route...

I might eventually have the "need" to use a DB on W2K3 Server - so I am curious about your comment about needing to use "the second latest stream version" ? I had previously loaded MySQL on a "test" box (Windows XP) and it seemed to work fine... If you have any "hints" for using with W2K3 Server that you'd like to pass along I'd be interested in hearing them... :-) And, thanks again for the feedback.

[amfony]

hey donuts - what i was saying about the win2k3 mysql problem is that mysql (as last i tried to install it) wouldnt actually finalise install when using the latest version of Mysql onto win2k3 sp1.

Check this out: http://forums.mysql.com/read.php?11,102464,102464

So - as i am actually using mysql primarily for logging, rather then relational data extraction or any advanced features of a db server set, i quite happiliy opted to install mySql 4.1.

So mysql 5.0 has the install error, 5.1 is a RC (and since i am using it in a production environment id eir on the side of stability) and 6.0 is an alpha, so there you go.

Hope this helps mate

[ptrex]

@UmmDoughnuts

SQLite is a very good choise in combination with AU3.

No ODBC driver needed. No installation needed.

Small footprint and size limits are not an issue.

The only restriction you need to take in account is that SQLite does not support multi user updates properly.

But otherwise it is the way the go.

regards,

ptrex

[amfony]

hi ptrex,

Sorry - just for my own knowledge now and away from the thread. SqLite is a server-less entity, where does it run? where do you set it up so to speak. How can it be used for logging, obviously over long long periods of time, if it is not running in a server-liek instance?

Thanks mate!

[PsaltyDS]

hi ptrex,

Sorry - just for my own knowledge now and away from the thread. SqLite is a server-less entity, where does it run? where do you set it up so to speak. How can it be used for logging, obviously over long long periods of time, if it is not running in a server-liek instance?

Thanks mate!

SQLite is a "client-side" .dll, accessing a possibly remote shared database file with some basic record locking capability. The logging database would just be a file on a share somewhere. The "clients" each run their own instance of the .dll (i.e. in a local AutoIt script) and _SQLiteOpen() is performed on the remote file:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $sLogDbPath = "\\LogServer\LogShare\LogFile.db", $hLogDb

_SQLite_Startup()
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
$hLogDb = _SQLite_Open($sLogDbPath) ; Open Log database
If @error > 0 Then
    MsgBox(16, "SQLite Error", "Can't Open Database!")
    Exit -1
EndIf

; Do logging stuff

_SQLite_Close()
_SQLite_Shutdown()

Simultaneous writes from multiple connections might require some handling. I don't have the experience to fill you in on that. The mechanism is there (I think it returns $SQLITE_BUSY status and you just try again till it goes through), but I haven't played with it. Multiple simultaneous reads are no problem.

[UmmDoughnuts]

@UmmDoughnuts

SQLite is a very good choise in combination with AU3.

No ODBC driver needed. No installation needed.

Small footprint and size limits are not an issue.

The only restriction you need to take in account is that SQLite does not support multi user updates properly.

But otherwise it is the way the go.

regards,

ptrex

ptrex,

THANKS for the reply... Based on the "code sample" you put in a later post, has it now been "decided" that using the UDF is a "better approach" than using the LiteX product ? I thought, from reading earlier posts, that you "preferred" LiteX ? (I could be wrong, as I've read HUNDREDS of posts going back a long time...)

If the UDF is the way to go... Is there a current "link" for getting the "latest and greatest" UDF... And, is there any "documentation" for the "latest version" somewhere ? I use the latest "production" version of AutoIT (no Betas at this point)...

Also, I don't forsee any issue with "multiple user updates"... I'm pretty much planning to use a database as a "consistent" replacement for supplying data to quite a number of scripts that currently each use their own "text" or "ini" file - but each of which contains a lot of "duplicated / shared" data which makes updating scripts a "high maintenance activity" when things change... So mostly a "read" activity at this point... Although... I did like the idea of "logging" to a database... Hmmm... Guess there are MORE things to script now ! lol

Thanks again...

[PsaltyDS]

ptrex,

THANKS for the reply... Based on the "code sample" you put in a later post, has it now been "decided" that...

Just for clarity, the code and opinions just posted were mine, not ptrex's. I'd hate to see ptrex blamed for any boneheadedness in one of my posts...

[ptrex]

@PsaltyDS

Nice example give for those who never used SQLite.

Ofcourse you are entitled to claim what "s yours.

Anyhow, my mixed feeling still stands about SQLite and LiteX.

I still more like the syntax of LiteX, but for practical reason I work more with SQLite.

No installation needed, just run and go.

Added :

Ofcourse I could create a registration less DLL of LiteX ?!

Why did't I think of this before

Regards,

ptrex

Edited April 7, 2008 by ptrex

[UmmDoughnuts]

Just for clarity, the code and opinions just posted were mine, not ptrex's. I'd hate to see ptrex blamed for any boneheadedness in one of my posts...

@PsaltyDS

Nice example give for those who never used SQLite.

Ofcourse you are entitled to claim what "s yours.

Anyhow, my mixed feeling still stands about SQLite and LiteX.

I still more like the syntax of LiteX, but for practical reason I work more with SQLite.

No installation needed, just run and go.

Added :

Ofcourse I could create a registration less DLL of LiteX ?!

Why did't I think of this before

Regards,

ptrex

ptrex / PsaltyDS

Sorry for the "mix up"... Didn't mean to "mis-quote" anyone... Honestly I was thinking (if I chose to use SQLite) of using LiteX because I think I found documentation online (can't seem to find the link right now) which seemed to imply that LiteX was pretty stable - not changed much - and was pretty "straigt forward" in usage.

I still am not sure if I've ever found the "latest" information for the UDF... Do either of you happen to know if the information in the latest production version (v3.2.10.0) is "latest and greatest" with regard to the UDF ? And is the info in the Help File "up to date" for is there another place I should look ?

And, thanks (again) for the info. I appreciate it.

Edited April 7, 2008 by UmmDoughnuts

[ptrex]

@UmmDoughnuts

The info on the UDF in the Help file is still uptodate.

You can get started with that.

Regards,

ptrex