SQLite_Easy - A simplified SQLite interface

[chrshea]

Nov 8, 2011

Please Note

A couple of people have raised concerns and I will address them here:

• I did not have SQLite V3 support in the version of Autoit that I was using. It was admittedly an older version and it was my fault that I didn't check out the latest version. Be that as it may, this package is intended to work with SQLite V3 so if you have the latest version of Autoit, you probably already have a version of the DLL in place. The SQLIte_Easy module references the DLL by the name SQLite_v3.dll.au3. I will be updating the documentation and checking this out with the latest version of Autoit when I get a chance.
• This UDF is intended to be both easy and simple. Many of the potential uses of a database are simple and those are the ones that this is good for. It can handle a single database and a single table or multiple tables if they are joined. It doesn't attempt to support all of the possible options in SQLIte. If your application requirement (today or potentially in the future as it evolves) is more complex than this, then you should use the SQLite UDF directly.

___________________________________________________________________________________

The SQLite UDF makes accessing an SQLite database in AutoIt pretty easy. I found however; that it still took me more time to put it all together than it should have. I spent a bit of extra time and assembled the package that I wanted but didn't find. It includes:

• The SQLite_Easy UDF (a simplified interface in front of the standard SQLite UDF)
• Examples of the most common database functions (using SQLite_Easy)
• A link to download SQLite Expert, a good free SQLite Manager utility.
• SQLite_v3.dll.au3 - The dll interface needed for SQLite Version 3 databases. If you have a recent version of Autoit, then you probably already have a version of this (but maybe under a different name).

The examples are quite simple so they may be useful for those who aren't very familiar with using a database.

Note: I take no credit for the V3 DLL interface. I found that on the Forum and just gave it a new, more consistent name.

Version 1.1 Changes - Aug 23, 2011

The changes in this Version are mostly about making it easier to code SQLite requests and to access the data from a database table.

• Simplified Table Inserts

  SQLite only provides the traditional format for inserts where you list the fields and then list the data for each one. MySQL allows you to use the same format as an update ( field1='aaaa',field2='bbb'...). SQLite_Easy now returns the ROWID for an Insert which you need to use a similar approach for SQLite.

• SQLite_String: Simplified Strings for SQL Commands

  If you are defining an SQL string (e.g. Insert or Update) in Autoit and the column values are mostly variables, then the string becomes a long awkward series of concatenated literals and variables. This function allows you to embed the variables in the input string. The output string has the values of the variables inserted in place of the variable names. Thus, you can provide an input string such as "Update people set field1='$a1', field2='$a2' ..."

• SQLite_Col: Use Associative Indices

  Accessing the data from an SQLite_Read normally involves retrieving the data from an array using an index number (based on the relative position of the column). In other languages like PHP, you can also access the col data using the name of the column as the index. You can't normally do this in Autoit since it doesn't support associative arrays. The SQLite_Col function provides the ability to reference the column data returned by an SQLite_Read using the column name. This makes the code easier to create and easier to read; and, it separates the code from the structure / order of the table columns.

To download the package, click here. Edited November 9, 2011 by chrshea

[chrshea]

Quite a few people have downloaded the initial version and I hope that it has helped them to get started using SQlite. Any feedback would be appreciated.

As I have used it some more myself, I have found some things lacking that I got used to using in MySQL. I am working on an updated version of SQLite_Easy to add some of these capabilities. The main improvements are as follows:

• Inserts Using the Same Format as an Update

  The standard version of the SQL Insert requires a string of field names and then a string of values for those fields. I find it tedious and

  error-prone to code one of these. For an Update (and for an Insert in MySQL), you can use the format "set a='xxxxx', b='yyyy' and I find this much easier. With this format you only need to include the fields that you have values for. The rest will have the default values. I will be adding some support so this format can be used for Inserts.

• Simplified Strings for SQL Commands

  In AutoIt, you can't include a variable in a string. You have to break-up the string where you want to include the variable, concatenate the variable then concatenate the rest of the string. This can make the coding of an SQL command more complicated than it should be. You end up with something like:

  $q_string = "update sales set "
  $q_string &= "order_num='" & '' & "', "
  $q_string &= "entry_date='"  & $today & "', "
  $q_string &= "first_name='" & $first_name & "', "
  $q_string &= "last_name='" & $last_name & "', "
  etc.

  I have added the ability to code it as:

  "update sales set order_num='', entry_date='$today', first_name='$first_name', last_name='$last_name'  etc.

  making it a lot simpler and clearer.

• The Ability to Use Field Names as a Column Index

  In Autoit, the column index to access the data after a read is an integer (the same as for any other array). This means that the program code will probably be affected by a change in the structure of the table that inserts or deletes a column in the table (except at the end of the table). The PHP approach which allows for the column name to be used as the index insulates the program from changes in the table structure (except when a field is deleted that was used in the program). This takes away the fear of making a table change once you have programs that access that table. I will be providing support to allow it to be coded as:

  $Sqlite_data_query_rows[$table_col]

  where $table is the specific table that you are using and col is the specific column. With a bit of effort, you could obviously code variables such as these into your program. I will be providing a small utility to create the include file to do it for you. Any time the table is changed, this can be re-run to generate the new include file.

_______________________________________________________________________________

The initial version of SQLite_Easy was built to handle one table at a time. This can satisfy a lot of needs but there are times when you may need to access 2 or more tables in parallel. I recently had this situation myself and I was able to work around it by using a Join. In most cases, if you are using more than one table, there will be some connection between them. As an example, if you want all instances of Sales along with those Problems where there is a match with a Sales record/row, then an SQL command such as:

select * from sales, problems where sales.order_num LIKE problems.order_num and sales.last_name like 'Blow'

will not do it. It will only return the Sales records where there is a Problem record. If you drop the condition that links the order numbers, you end up with every Problem record being combined with every Sales record. That isn't what you want either.

After a bit of research and experimenting, I found that the solution was:

select * from sales LEFT JOIN problems ON sales.order_num LIKE problems.order_num Where sales.last_name like 'Blow'

(I actually had more conditions but this is the simplified version).

Using a LEFT JOIN with the ON keyword gives you all of the Sales records, whether there is a Problem record or not. You can combine this with a WHERE clause as I have done here, to add additional criteria.

I will be looking at making it possible to independently access multiple tables in a future version but for now, Joins can probably handle the majority of situations.

[Reaper HGN]

Hey, this is great. I had started to do something very similar to this, but your implementation is much "cleaner" than mine. This will allow me to easily add the sqlite backend db to my projects.

Thanks for sharing

[chrshea]

You're very welcome and thanks for the feedback. It's important to have an easy-to-use database as part of the toolkit when putting together any application that needs to store and access data. I toyed with ini files and other flat files for a while then I decided that it was time to invest some time and see if I could get SQLite (or MySQL) to work. Once I got it all together, it did turn out to be pretty easy to use. It is already part of any new applications that I put together. How to store and access the data is no longer a question mark.

[chrshea]

A new and improved Version 1.1 has been released as of today.

[VasiliyRS]

Is it possible to get an image off databese to display it ? Any examples ?