Jump to content
GreenCan

SQLite Report Generator

Recommended Posts

GreenCan

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
  • Like 2

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

 

 

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 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

 

 

 

Share this post


Link to post
Share on other sites
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).


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
GreenCan

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

 

 

 

Share this post


Link to post
Share on other sites
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". :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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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

 

 

 

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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

 

 

 

Share this post


Link to post
Share on other sites
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.


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

  • Like 1

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

 

 

 

Share this post


Link to post
Share on other sites
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.


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

Share this post


Link to post
Share on other sites
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 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

 

 

 

Share this post


Link to post
Share on other sites
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 ^_^.

Share this post


Link to post
Share on other sites
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,

Share this post


Link to post
Share on other sites
shai

this is old topic.

 

anyway.  extended error 21 means "Library used incorrectly"

Share this post


Link to post
Share on other sites
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

 

  • Like 1

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

  • Similar Content

    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Eminence
      By Eminence
      Hello,
      I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.
      This is my current code:
      Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.
       
      *EDIT
      The date format in the database is by MM/DD/YYYY HH:MM:SS.
    • hugomito
      By hugomito
      Hi all,
      I hope you can help me with this. I need to connect to a Database using Cloudera ODBC Driver for Impala. I installed the driver, created a User DSN (drona2) and tested it successfully (got the message: Successfully connected to data source!) but when I try to connect to the database using a Conneciton String, it simply didn't work.
      I tried connecting to the database using the User DSN, I previously created, that has all parameters needed but got the following error:
           $ProviderDatasource = 'DSN=drona2;'
           $conn_Database = ObjCreate ("ADODB.Connection")
           $conn_Database.ConnectionString = $ProviderDatasource
           $conn_Database.Open
           ==> The requested action with this object has failed.:
           $conn_Database.Open
           $conn_Database.Open^ ERROR
      Also I tried adding all parameters to my connection string as follows but also got the same error:
           $ProviderDatasource = 'Driver=Cloudera ODBC Driver for Impala;Host=MyHost.domain.com;PORT=21050;AuthMech=3;UID=MyUserId@domain.com;PWD=MyPasswordc;Schema=default;SSL=1;AllowSelfSignedCerts=1;AllowHostNameCNMismatch=1;CheckCertRevocation=1;KrbFQDN=_HOTS;KrbServiceName=impala;ServicePrincipalCanonicalization=1'
      I hope someone has used this driver successfully before and can shed some light here.
      If you need additional information, let me know.
      Regards,
    • Seminko
      By Seminko
      In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll
      Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one.
      I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL.
      So I downloaded @jchd's SQLiteExtLoad.au3 as seen here:
      But I'm getting these errors:
      "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires
      If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection.
      I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt.
      Thanks, S.
       
      EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply.
      BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem...
       
      EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.