Jump to content

SQLite Report Generator


GreenCan
 Share

Recommended Posts

For the SQLite developers...

New version 1.0.0.6

This is a Report Generator for SQLite Database.

post-42917-0-69157100-1365274531_thumb.j

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 :

[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 by GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

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

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)

Link to comment
Share on other sites

New version 1.0.0.6

See post

Brgds

GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

  • 2 weeks later...

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". :D

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!

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Hi water,

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

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

Cheers, Greencan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

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.

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)

Link to comment
Share on other sites

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

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

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.

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

  • 3 years later...
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

 

Link to comment
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
 Share

×
×
  • Create New...