Sign in to follow this  
Followers 0
Zohar

Is there a Small Database Tutorial in AutoIt?

28 posts in this topic

Hi

 

I used Databases in the past in several programming languages,

and I need to do some (simple) database operations in AutoIt too now.

 

Is there a short tutorial for that?

And also, What DataBase type is most recommended?

 

Thank you

Zohar

Share this post


Link to post
Share on other sites



Zohar,

There is pretty good SQLite library installed with AutoIt - I found it relatively easy to use once I had read this site to get a handle on how SQL worked. :)

I understand jchd is currently writing a tutorial, but if you look in his sig there are some other SQLite links to try while you are waiting. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

It all depends on your particular use case. For local DB storage, I strongly recommend SQLite which has support in standard AutoIt installation.

Try to specify your use context a bit more precisely.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Hi Melba

 

OK, so you say generally SQlite is recommended, in AutoIt, due to the existing library.

Great.

I am already familiar with Database programming as I wrote(in other programming languages).

 

Hi jchd:

It all depends on your particular use case. For local DB storage, I strongly recommend SQLite which has support in standard AutoIt installation.

Try to specify your use context a bit more precisely.

 

What I need is quite simple:

I need to create 1 table only,

a big table,

it will have aprox 7 columns,

and I need to Add rows to it, and then be able to Edit specific rows.

(that's why a database will be more appropriate here, than a file)

 

So SQlite it will be.

 

For creating the Database file(and the table in it), what is usually used?

Some external program? or AutoIt comes with one?

Share this post


Link to post
Share on other sites

Zohar,

For creating the Database file(and the table in it), what is usually used?

Go and look in the library and all will become clear. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Go and look in the library and all will become clear. ;)

 

Oh..

I just did :)

From the help:

_SQLite_Open

--------------------------------------------------------------------------------

Opens/creates a SQLite database. 

 

So the library help, is recommending to Programmatically create the Database(and the required Table(s) in it).

Is there also some utility with GUI that is used?

Is it AutoIt specific, or any SQlite program that can create a database with tables, will do?

Edited by Zohar

Share this post


Link to post
Share on other sites

I always strongly recommend using SQLite Expert (the free version will do) for experimenting/playing/using SQLite databases.

One more note: SQLite is by far the most used RDBMS ever but it's a serverless engine; Due to bugs in all networked file locking protocols currently available (including Windows, Linux, Solaris, whatever) it isn't advicable to access an SQLite DB from a network using the bare library (some wrappers overcome this limitation). OTOH, local concurrent access is no problem.

1 person likes this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Great, thank you,

I will download and experiment with SQLite Expert.

(I am used to working with Visual Studio's tools, and it looks very similar)

Thank you for the tip regarding server usage.

For now what I do is only for local, single user use.

 

BTW, one more thing that I am curious about:

When creating web applications, you almost always see MySQL being used.

Also most hosting packages come built in with support for MySQL.

 

So for consistency sake,

why should we use in AutoIt SQlite and not MySQL for example?

 

Let's say someone already has all the tools/etc for working with MySQL, wouldn't it be a good idea to use MySQL in AutoIt too?

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

it would b e good idea. I have wamp localy instaled that i use on daily basis to connect to mysql with autoit when ever i need it to test something.

dont see no problem using MySQL with autoit

i prefer to use >EzMySql because of no need for ODBC things.

Edited by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Thank you bogQ

 

I searched in google regarding SQLite vs MySQL, and found a short and useful article:

 

How to Decide Between SQLite and MySQL:
http://www.sobbayi.com/blog/software-development/decide-sqlite-mysql/

If the link doesn't work, it can be found here:

http://web.archive.org/web/20131111024411/http://www.sobbayi.com/blog/software-development/decide-sqlite-mysql/

 

One of the more important differences described there, is that

SQLite is a Serveless database, accessed locally as a file, via a DLL,

and MySQL is generally a Server database, requiring installation, configuration, etc.

 

So I assume that's why AutoIt is used more often with SQLite...

Edited by Zohar

Share this post


Link to post
Share on other sites

a very simple "first step" >example


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

There are advantages with SQLite. Order depends on your context and use. First - it is ... lite: self-contained and doesn't need installation, configuration or maintainance for a large range of contexts. Second - it is very fast (DB in memory or not). Third - it is easily extendable or customizable with add-on or overloading functions or virtual tables. Fourth the DB file (every DB is essentially one file) is portable accross any implementation of SQLite. Fifth - it is the most used DB engine because of previous reasons: in almost every smartphone, GPS, tablet, instance of Mozilla or Adobe software whatever the underlying hardware/software platform. Finally it's public domain for any use you can dream of.

Of course it has many more advantages but if I list them all I'll be accused of being biaised o:)

1 person likes this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

a very simple "first step" >example

 

Thank you

Very concise and useful example :)

BTW are you DaleHolm with a new nick?

 

Third - it is easily extendable or customizable with add-on or overloading functions or virtual tables.

 

Can you say a little about this?

What useful add-ons one should look into, for example?

Edited by Zohar

Share this post


Link to post
Share on other sites

Thank you

Very concise and useful example :)

You are welcome

 

BTW are you DaleHolm with a new nick?

ha, good joke :D

... Mr. Dale is a big gorilla

I am just a little monkey... :P

1 person likes this

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

Zohar,

What add-on to use depends entirely on your need(s) ! I've writen a few myself but not knowing anything about your context makes it very hard to recommend X, Y or Z over nothing.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

ha, good joke :D

 

;)

 

Zohar,

What add-on to use depends entirely on your need(s) !

I've writen a few myself but not knowing anything about your context makes it very hard to recommend X, Y or Z over nothing.

 

Well,

For this specific program I will not need any add-on, since it's extremely simple.

But to know some existing add-ons, so If in the future I might need them, could be handy..

Share this post


Link to post
Share on other sites

Lookup "unifuzz" with the forum search feature. Browse the docs and release notes of SQLite for more.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

OK Thank you.

BTW, I am used to creating Databases and Tables in Visual Studio,

here in SQLite Expert (Personal) it's quite similar, however I have 1 small problem:

I created a sample table called T_Users, with 3 columns:

ID,

Name,

Age.

 

Now I want the ID coulmn to be PK+Identity.

So in SQLite Expert, I want to the Indexes tab,

added the ID column as an index,

and set the Primary checkbox to true.

 

What about AutoIncrement?

Where do I set it?

Or If I added the ID column as an Index, then the DB will know to AutoIncrement byitself?

1pzg5y.png

(for some reason the AutoIncrement checkbox is disable for me..)

Edited by Zohar

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

There is something special with row ID (identity in some other engine).

Unless you explicitely create one, SQLite will automatically make one column called rowid which is not listed but which you can access or list explicitely. The correct type is integer not null primary key autoincrement (integer and not int). Autoincrement is only available in this case.

More details in the SQLite documentation (see signature).

In the sample case you mention, DDL would be something like:

CREATE TABLE "tbl" (
  "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "Name" CHAR NOT NULL COLLATE NOCASE, 
  "Age" INT DEFAULT 0, 
  CONSTRAINT "KcAge" CHECK(Age between 0 and 130));
Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0