SQLite Report Generator

[GreenCan]

For the SQLite developers...

New version 1.0.0.6

This is a Report Generator for SQLite Database.

The script supports up to two dynamic parameters per report, see the 'SQLite Reports.ini' file for more details. Single Date, period, string or number input are possible

I provide a full functional example using the Chinook Demo Database (http://chinookdatabase.codeplex.com), so everyone can test it. All required files are contained in the zip file. You can download the zipfile in the link hereunder.

I created 20 different reports, from which 4 are system reports and one is not linked to any table.

Features:

• Up to 2 dynamic Parameters via input dialog box per report
• Multi-line fields will display in a separate Window when you click on the cell containing multiline data (which cannot be displayed in a listview, if you click on a normal cell, the window will disapear
• Double click on a row and a new Window will display the row vertically, usefull for rows with many columns.
• Export to Excel
• Fast, even with several thousands of rows (see Track report)
• SQL that do not deliver data (no rows, no columns) will display a popup message instead (for example using the VACUUM command)

Of course you have to know SQL but I guess that everyone who programs SQLite will, right?

The SQL scripts are stored in the ini file, where 3 sections ‘Titles, SQL and Parameters’ contain all the elements required for a report.

For simplicity, I decided to store the SQL script in the ini file as a single line. The drawback is that the SQL is not easy to read or to maintain, but look at the example ' Invoice with details' where I join 8 tables. It's absolutely functional.

Read the [sql readme] section for more details about dynamic parameters. I think its usage is pretty obvious.

Know issues:

When compiled as 64bit, double click does not function in the main menu, I don't know why but I cannot get this fixed. If anyone knows, I would be pleased to get some help on this.

All the other functions, including the double click in the ListView do work. The script will use SQLite3.dll or SQLite3_x64.dll if compiled as a x64 App.

The script is a simplified version of my multi-platform Database Report Generator using Active-X DB connection. This SQLite report script (1600 lines) has very basic features compared to the latter (for example it fully integrates the ExcelChart UDF developed by water) but it has 13000 lines and is not provided has open source (sorry L, an early version was released on this forum in 2009 but for Oracle only)

I hope this one will be useful for you, I had some fun with it, took me 3 evenings to do the job.

Enjoy.

GreenCan

Updates

1.0.0.6:

• Bug fixes (thanks jpm)
• Better GUI windows management
• Excel export optimized
• _COMError implemented

Full package: http://users.telenet.be/GreenCan/AutoIt/SQLite_Reports_1.0.0.6.zip (If you downloaded the full package of the previous version, you don't have to download it again)

SQLite Reports 1.0.0.6.au3

SQLite Reports.ini :

expandcollapse popup

[General]
;Database=Chinook_Sqlite.sqlite
Database=Chinook_Sqlite_AutoIncrementPKs.sqlite
DateFormat=YYYY-MM-DD
[Titles]
Report=Album
Report=sqlite_sequence (shows number of records for each table)
Report=Artist
Report=Customer
Report=All Employees
Report=Employees birthday - period
Report=Employees birthday - Name contains
Report=Genre
Report=Invoice
Report=Invoice - amount between
Report=Track
Report=MediaType
Report=Invoice with details - one invoice selection
Report=Playlist
Report=PlaylistTrack
Report=Non-Database - Date formats examples
Report=System - Tables and fields (sqlite_master)
Report=System - List Tables of DataBase (sqlite_master)
Report=System - SQLite version
Report=System - Database cleanup (Vacuum)

[SQL]
Report=SELECT AlbumId, Title, ArtistId FROM Album
Report=SELECT name,seq FROM sqlite_sequence
Report=SELECT ArtistId, Name FROM Artist ORDER BY Name
Report=SELECT CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId FROM Customer ORDER BY CustomerId
Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee
Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE BirthDate between '%d1%' AND '%d2%'
Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE LastName || ' ' || FirstName like '%%1%%'
Report=SELECT GenreId, Name FROM Genre
Report=SELECT InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice
Report=SELECT InvoiceId, CustomerId, Date(InvoiceDate), BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice WHERE Total between %1% AND %2%
Report=SELECT TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice FROM Track
Report=SELECT MediaTypeId, Name FROM MediaType

Report=SELECT Invoice.InvoiceId, Date(Invoice.InvoiceDate) as "Invoice Date", Customer.FirstName, Customer.LastName, Invoice.BillingAddress, Invoice.BillingCity, Invoice.BillingState, Invoice.BillingCountry, Invoice.BillingPostalCode, Track.Name as "Track Name", Album.Title as "Album", MediaType.Name as "Media", Genre.Name as "Genre", Artist.Name as "Artist", Track.Composer, InvoiceLine.UnitPrice, InvoiceLine.Quantity, Invoice.Total FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId INNER JOIN Customer ON Invoice.CustomerId = Customer.CustomerId INNER JOIN Track ON InvoiceLine.TrackId = Track.TrackId INNER JOIN Album ON Track.AlbumId = Album.AlbumId INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId INNER JOIN Genre ON Track.GenreId = Genre.GenreId INNER JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId WHERE Invoice.InvoiceId = %1%

Report=SELECT * FROM Playlist
Report=SELECT * FROM PlaylistTrack
Report=SELECT date('now') as "today", date('now','start of month','+1 month','-1 day') as "last day current month", datetime(1092941466, 'unixepoch') as "unix timestamp", datetime(1092941466, 'unixepoch', 'localtime') as "unix timestamp, compensate for, local timezone", strftime('%s','now') as "current unix timestamp", julianday('now') - julianday('1776-07-04') as "days since US Independence", strftime('%s','now') - strftime('%s','2004-01-01 02:34:56') as "Time elapsed", date('now','start of year','+9 months','weekday 2') as "first Tuesday in October", (julianday('now') - 2440587.5)*86400.0
Report=select * from sqlite_master
Report=SELECT name FROM sqlite_master WHERE type='table'
Report=SELECT sqlite_version() as "sqlite version" --, sqlite_source_id() as "sqlite source_id"
Report=VACUUM

[Parameters]
Report=
Report=
Report=
Report=
Report=
Report=
Report=Name contains|string will match any position in FirstName or LastName
Report=
Report=
Report=Total between|For fraction use a dot (eg: 5.95),and|For fraction use a dot (eg: 5.95)
Report=
Report=
Report=Invoice ID|
Report=
Report=
Report=
Report=
Report=
Report=
Report=

Edited April 10, 2013 by GreenCan

[jpm]

In case it helps.

The X64 problem is due to a bad definition of $tagNMHDR. It is always better to use the UDF ones.

More if you want to use the latest SQlite DLL you can just use a copy of SQLite.dll.au3 instead of including your own copy of those needed file.

it is also not needed in your code to startup/shutdown every time when you open/close.

I attached a working copy ilustrating what I mention.

Cheers

JP

[GreenCan]

Hi jpm,

Thank you very much for finding what i did wrong. I greatly appreciate...

And you are right, I have been rather clumsy to put Startup and shutdown in a loop.

Concerning simply using _SQLite_Startup() versus _SQLite_Startup(@ScriptDir & "SQLite3.dll")

I think I did that because I once installed an SQLite app on a system without Autoit and without a connection to Internet and it didn't work (as no download was possible)

Thanks again

Cheers

Edited April 9, 2013 by GreenCan

[jchd]

Also specifying a local path to the DLL allows you to run a newer version of the library, or one with encryption or some fancy compile option (not recommended unless you know where you put your fingers).

[GreenCan]

New version 1.0.0.6

See post

Brgds

GreenCan

[water]

Hi GreenCan!

wonderful script! I have no need for it at the moment but I'm sure I will remember this thread should the need arise

A minor bug you need to fix.

In the OP you write "for example it fully integrates the ExcelChart UDF developed by water". This should be replaced by "for example it fully integrates the ExcelChart UDF developed by water and me".

You did a enormous part of the development work. Either by passing code to be included or by discussing design decisions with me!

So the ExcelChart UDF is the work of you and me!

[GreenCan]

Hi water,

Thank you, I appreciate very much that you like it.

ExcelChart: That is too much honor, you are a real gentleman . You don't database ExcelCharts yet? What about BLOB-ing them into SQLite?

Cheers, Greencan

[water]

Hi GreenCan,

I have to admit that I haven't used our ExcelChart for any productive work here.

How would you database ExcelCharts? Would this need the ExcelChart UDF to be modified?

Greetings

water

[GreenCan]

I was just thinking out loud, teasing you... You don't use Excel charts in a PP?

The first issue is choosing which database for the storage. I use Oracle most of the time, but MS SQL and SyBase could be used too

SQLite is great for small projects. Don't understand me wrongly, SQLite is great but maybe not the most appropriate in a business environment. For my private playground however, SQLite is perfect and quite close to the syntax of Oracle SQL.

Making a multiDBplatform Chart storage is nearly impossible and way out o scope I guess.

So, what could be the benefits of storing charts in a Database?

- Speed

- Pro-activeness

- Management benefit

What to store?

- entire sheet or graph alone (bitmap or something else?)

How?

- Via a scheduled job, a kind of engine producing updated graphs in a loop?

I don't think that ExcelChart should be modified for this, but a shell application (the engine) would be required.

Worth more thinking about this? Is someone waiting for this?

Grtz

Greencan

[jchd]

May I ask what are the expected benefits of storing charts? They would need recomputing after any change of the underlying data.

That reminds me of Spatialite: using SQLite as an efficient storage and a portable graphics engine for a astounding result.

One of the multiple strengths of SQLite is that it's so portable accross platforms. All that is required to have a cross platform charting layer is some kind of portable external viewer.

Now cross-engine and cross-platform is clearly close to unreachable.

[GreenCan]

jchd,

Yes, good question. Personally I think there is little benefit. But I surely would like experimenting with Binary Large Objects first (I didn't touch the topic yet), and make my conclusions afterwards

GreenCan

PS. A bit off topic but FYI, Aqua Data Studio 13 now natively supports SQLite

[jchd]

SQLite blobs work like a charm. The good side is that you can reserve a maximum size for your blob and read/write it incrementally. Also since SQLite is dynamically typed, you can store Postscript text or JPG image inside any column regardless of its declared type. At retrieval, you can test for stored data type and handle it based on what it actually is.

A effective charting feature for SQLite could be "as simple as" an aggregate extension function producing e.g. postscript data for chart representation. It's very easy for instance to produce histograms in text form (strings of varying lengths of ******).

Aqua Data Studio isn't for a mere mortal like me. When/if I need fancy graphical representation from databases (my only choice is SQLite since it offers 500% of what I need) I'll use Wolfram Mathematica interface as I'm a paid user of Mathematica and its low- and high-level graphical possibilities are unbounded.

[reybol7]

Helo sir @GreenCan, can i ask where is the function for EXPORT EXCEL coz i can't find it in your program, im new in autoit and i need a script that put my data in sql to excel and your export to excel was very fast. thanks in advance. Thanks for sharing your program though its hard for me to understand and catch the varable where's going to .

[GreenCan]

reybol7,

I understand that you are getting confused.

The export button does not create the csv file as such because the csv file is (already) created before the ListView gets populated.

I got the best performance by doing this, especially when your SQL results in a large amount of rows

1. Line 274: Run the SQL

2. Line 287-295: Create the csv file in @tempfolder using the ExportData() function

3. Line 319: Convert the csv file to array $aResult using ProgAndy's excellent _ParseCSV function

4. Line 333-348 Populate the Listview from Array $aResult

Consequently, the export button just moves the csv file to My Documents folder, opens Excel and does some basic formatting.

---

I found following function in my snippet archive, that might be of help to you. The function exports ListView data to a csv file.

The particularity of this export function is that it will export the Listview exactly how it is displayed, If you move a column, the export will also move the column, if you hide a column, the column will not be exported.

_ExportListView.au3

GreenCan

Edited April 29, 2013 by GreenCan

[reybol7]

@GreenCan, Thanks for the reply, and sorry for late response, ill study your _ExportListView.au3 as soon as my winXP desktop is fix, right now i having trouble with my win7 coz of various errors popping up while in my winXP don't have any errors. i really appreciate, thanks again .

[robertocm]

Dear GreenCan,

Many Thanks,

I'm getting this error:

Can't Load Database
C:\Reports\Chinook_Sqlite_AutoIncrementPKS.sqlite @extended 21

I have this file in the same folder (Chinook_Sqlite_AutoIncrementPKs.sqlite)

Best Regards,

[shai]

this is old topic.

 

anyway.  extended error 21 means "Library used incorrectly"

[robertocm]

4 hours ago, shai said:

this is old topic.

 

anyway.  extended error 21 means "Library used incorrectly"

Many Thanks shai,

Now is solved for me:

1. i've looked in the forum for the error you mentioned, and found this code to check my sqlite3.dll file
2. Got an error loading the sqlite3.dll library (perhaps because the file was from 2014)
3. In the AutoIt Help, in the section about the funtion _SQLite_Startup: there's a link for downloading SQLite files
4. Got the last available version (SQLite3_version.zip). unziped and changed the name to the same: sqlite3.dll