Sunday, March 22, 2020

Delphi Tip of the Day - Object Inspector Search

I was watching an excellent video on how to use the batch move capabilities within Delphi to import text data into a database and had one of those "What the heck just happened" moments. The object inspector was only displaying the "Name" property.

What the heck just happened...

I rewound the video by about 20 seconds and paid careful attention to what was going on. The presenter used the "search" box associated with the Object Inspector. What? I had no idea you could do that. Wow! Game Changer!

I've been staring at the object inspector for years and never even saw the search box even though it's right there, plain as day, in my face. Sure beats scrolling up and down through all the properties anytime I wanted to make a change.

Game Changer!

Alternate Game Changer!

I'd like to thank Bruce McGee for the following "Quick Edit" tip:

Right-click on a control at design time and select Quick Edit from the drop-down menu. It works for VCL and FMX controls. The VCL image on DocWiki is not correct. It actually reads "Caption" instead of "Text" in my version of Delphi.

Semper Fi,
Gunny Mike

Saturday, March 21, 2020

Delphi - SQLite Sample Projects

I've been exploring SQLite within Delphi. There are several sample SQLite applications that come with Delphi if you selected to install the samples during setup. I've put together the list of SQLite samples that are available.

The common samples file path is located here:
..\Studio\20.0\Samples\Object Pascal\Database\FireDAC\Samples\

FireDAC.SQLiteIniFile Demo Sample 

This sample uses FireDAC to connect to an SQLite database that emulates the structure and the operations of an INI File.


FireDAC.TFDLocalSQL MegaFMX Sample 

The sample shows different TFDLocalSQL applications.

Comp Layer\TFDLocalSQL\MegaFMX\LSQLMega.dproj

FireDAC.SQLite Encryption Sample 

This sample demonstrates how to encrypt/decrypt an SQLite database.

DBMS Specific\SQLite\Encryption\SQLite_Encrypt.dproj

Controlling Database Access Rights 

The SQLite is an embedded DBMS. This implies that it is a single user DBMS and does not need such concepts as a user, access rights, and so on. Still, some application can benefit from an access right control, for example:

  •  An application can restrict rights depending on an end-user license. A demo license means less possibilities, a full license provides all possibilities. 
  • Multi-tier data access frameworks can use their own user concept and control data access rights using some generic approach. 

 Again, SQLite provides a unique feature allowing you to authorize or not SQL commands. To work with this API, a Delphi application should set the OnAutorize event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened.

DBMS Specific\SQLite\OnAuthorize\SQLite_OnAuthorize.dproj

Hooking Database Updates 

SQLite provides an unique API allowing you to monitor all updates to a database. This feature can be used, for example, to log all updates to a DB. To work with this API, a Delphi application should set the OnUpdate event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened.

DBMS Specific\SQLite\OnUpdate\SQLite_OnUpdate.dproj

Custom Collations 

SQLite stores and handles all character data either in UTF8 or UTF16, depending on the OpenMode connection parameter. When SQLite needs to compare or sort a character data, it has to know what rules to use for this. The rules are known as a collation.

DBMS Specific\SQLite\UserCollation\SQLite_UserColl.dproj

Custom Functions 

SQLite does not support the stored procedure or function concept, as it allows you to use the host language environment to extend the engine functionality. SQLite allows you to register host language functions in the SQLite engine and use them in the SQL commands. FireDAC simplifies this by introducing the TFDSQLiteFunction component.

DBMS Specific\SQLite\UserFunc\SQLite_UserFunc.dproj

The SQLite R*Tree Module 

An R-Tree is a special index that is designed for doing range queries. R-Trees are most commonly used in geospatial systems where each entry is a rectangle with minimum and maximum X and Y coordinates. Given a query rectangle, an R-Tree is able to quickly find all entries that are contained within the query rectangle or which overlap the query rectangle.

This idea is easily extended to three dimensions for use in CAD systems. R-Trees also find use in time-domain range look-ups. For example, suppose a database records the starting and ending times for a large number of events. A R-Tree is able to quickly find all events that were active at any time during a given time interval, or all events that started during a particular time interval, or all events that both started and ended within a given time interval. And so forth. 

DBMS Specific\SQLite\UserRTree\SQLite_UserRTree.dproj

Title: SQLite 

Brief Description: This snippet shows you how to populate a listbox from a SQLite database using FireDAC and LiveBindings. See the section called "Accessing a Database" in the Mobile Tuturials topic in the documentation for more information and licensing requirements.

Platforms supported: iOS, Android

..\Mobile Snippets\FireDACSQLite\FireDAC_SQLite.dproj

Semper Fi,
Gunny Mike

Delphi Tip of the Day - OnShow SetFocus or Form1.ActiveControl

I'm creating a simple database program to explore how SQLite works within Delphi. I want to be able to switch between three databases: Album, Test, and World. So I added a TComboBox and populated the Items property with three values; Album, Test, World. I also set TextHint := 'Choose Database...';

object ComboBox1: TComboBox
    Left = 8
    Top = 8
    Width = 121
    Height = 21
    ParentShowHint = False
    ShowHint = False
    TabOrder = 0
    TextHint = 'Choose database...'
    Items.Strings = (

When I ran the program the TextHint for the ComboBox did not display:

That is not what I was expecting. The Combox was the active control which negates being able to see the TextHint "Choose database..."

So I added a little piece of code to the form's onShow event.

procedure TForm1.FormShow(Sender: TObject);

Now when I run the program right from the get-go the StatusBar componet has the focus and the ComboBox displays as intended.

Sometimes it's the small things that make all the difference!

An alternative  to using the OnShow event was suggested in the comments.

Form1.ActiveControl := StatusBar1

I love Delphi! 

Semper Fi
Gunny Mike