Is there a Small Database Tutorial in AutoIt?

[Zohar]

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

[Melba23]

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

[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.

[Zohar]

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?

[Melba23]

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

[Zohar]

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 November 27, 2013 by Zohar

[jchd]

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.

[Zohar]

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?

[JohnOne]

 

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

There is no reason, just that you original question warranted the SQLite answer.

[bogQ]

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 November 27, 2013 by bogQ

[Zohar]

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 December 11, 2014 by Zohar

[Gianni]

a very simple "first step" >example

[jchd]

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

[Zohar]

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 November 28, 2013 by Zohar

[Gianni]

Thank you

Very concise and useful example

You are welcome

 

BTW are you DaleHolm with a new nick?

ha, good joke

... Mr. Dale is a big gorilla

I am just a little monkey...

[jchd]

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.

[Zohar]

ha, good joke

 

 

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..

[jchd]

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

[Zohar]

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?

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

Edited November 29, 2013 by Zohar

[jchd]

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 November 29, 2013 by jchd