The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream




    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,781 other followers

Archive for the ‘Database Development’ Category

Firebird; testing for NULL and equality in practice: IS [NOT] DISTINCT FROM

Posted by jpluimers on 2017/06/14

Boy, I totally missed that this was introduced back then in Firebird 2.0:

 In Firebird 2 and higher, you test for null-encompassing equality with DISTINCT. This has already been discussed, but here’s a quick recap. Two expressions are considered:
  • DISTINCT if they have different values or if one of them is NULL and the other isn’t;
  • NOT DISTINCT if they have the same value or if both of them are NULL.

[NOT] DISTINCT always returns true or false, never NULL or something else. Examples:

if (A is distinct from B) then...
if (Buyer1 is not distinct from Buyer2) then...


Source: Testing for NULL and equality in practice


Posted in Database Development, Development, Firebird | Leave a Comment »

Applications that scale badely on High-DPI Displays: How to Stop the Madness – via: SQLServerCentral

Posted by jpluimers on 2017/05/10

Many applications still scale badly on High-DPI displays: dialogs way too small, icons you need a microscope for, etc.

SSMS in High-DPI Displays: How to Stop the Madness – SQLServerCentral explains a great trick that works for many applications, for intance:

The trick comes down to enabling the PreferExternalManifest registry setting and then create a manual manifest for the application that forces the application to use “bitmap scaling” by basically telling it does not support “XP style DPI scaling”.

You name manifest file named after the exe and stored it in the same directory as the exe.

After that, you also have to rename the exe to a temporary name and then back in order to refresh the cache.

A quote from the trick:

In Windows Vista, you had two possible ways of scaling applications: with the first one (the default) applications were instructed to scale their objects using the scaling factor imposed by the operating system. The results, depending on the quality of the application and the Windows version, could vary a lot. Some scaled correctly, some other look very similar to what we are seeing in SSMS, with some weird-looking GUIs. In Vista, this option was called “XP style DPI scaling”.

The second option, which you could activate by unchecking the “XP style” checkbox, involved drawing the graphical components of the GUI to an off-screen buffer and then drawing them back to the display, scaling the whole thing up to the screen resolution. This option is called “bitmap scaling” and the result is a perfectly laid out GUI.

In order to enable this option in Windows 10, you need to merge this key to your registry:

Windows Registry Editor Version 5.00


Then, the application has to be decorated with a manifest file that instructs Windows to disable DPI scaling and enable bitmap scaling, by declaring the application as DPI unaware. The manifest file has to be saved in the same folder as the executable (ssms.exe) and its name must be ssms.exe.manifest. In this case, for SSMS 2014, the file path is “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe.manifest”.

Paste this text inside the manifest file and save it in UTF8 encoding:

This “Vista style” bitmap scaling is very similar to what Apple is doing on his Retina displays, except that Apple uses a different font rendering algorithm that looks better when scaled up. If you use this technique in Windows, ClearType rendering is performed on the off-screen buffer before upscaling, so the final result might look a bit blurry.The amount of blurriness you will see depends on the scale factor you set in the control panel or in the settings app in Windows 10. Needless to say that exact pixel scaling looks better, so prefer 200% over 225% or 250% scale factors, because there is no such thing as “half pixel”.


Source: SSMS in High-DPI Displays: How to Stop the Madness – SQLServerCentral

Posted in Database Development, Delphi, Development, Eclipse IDE, Java, Java Platform, Software Development, SQL, SQL Server, SSMS SQL Server Management Studio | 4 Comments »

Fixing 0x858C001E error on SQL Server 2012/2014 updates

Posted by jpluimers on 2017/03/16

A long time ago I wrote about Fixing 84b40000 error on SQL Server 2008 updates (like KB2977321 and KB2285068).

The same holds for error 0x858C001E errors when updating SQL Server 2012 and 2014:

For x86 systems, ensure these directories are not compressed:

C:\Program Files\Microsoft SQL Server
C:\Program Files\Microsoft SQL Server Compact Edition

For x64 systems, ensure these directories are not compressed:

C:\Program Files\Microsoft SQL Server
C:\Program Files x86\Microsoft SQL Server
C:\Program Files x86\Microsoft SQL Server Compact Edition



Posted in Database Development, Development, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a Comment »

Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes

Posted by jpluimers on 2017/02/15

Delphi programmers often store TDateTime (be it date, time or date+time values) as floating points in the database. They take the easy way out basically because TDateTime is nothing but a typed alias for the Delphi Double type (which is equivalent to the IEEE 754 double-precision binary floating-point format: binary64 type) .

Actually, TDateTime is equivalent to the OLE Automation date (which is described in the remarks of  DateTime.ToOADate Method (System)) which has an epoch of  midnight at 1899-12-30 (to be compatible with Lotus-123 including its leap-year-bug, background on that is at Why are the Microsoft Office file formats so complicated? (And some workarounds) – Joel on Software).

Anyway: the definition is this:

An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.

The base OLE Automation Date is midnight, 30 December 1899. The minimum OLE Automation date is midnight, 1 January 0100. The maximum OLE Automation Date is the same as DateTime.MaxValue, the last moment of 31 December 9999.

The sample values are remarkably the same as the ones for TDateTime:

Value Description
0 December 30, 1899; 12:00 A.M.
2.75 January 1, 1900; 6:00 P.M.
-1.25 December 29, 1899; 6:00 A.M.
35065 January 1, 1996; 12:00 A.M.

The below SQL example uses the Firebird date/time/timestamp casting shortcuts:

select date      '1899-12-30'              + 42452.670590278 as Date20160323,
       timestamp '1899-12-30 00:00:00.000' + 42452.670590278 as DateTime20160323160539,
       time       '00:00:00'                + 60*60*24 * 0.5 as Noon,
       timestamp '1899-12-30 00:00:00.000' + 0          as DateTime18991230Midnight,
       timestamp '1899-12-30 00:00:00.000' + 2.75           as DateTime190001011800,
       timestamp '1899-12-30 00:00:00.000' + -1.25          as DateTime189912290600,
       timestamp '1899-12-30 00:00:00.000' + 35065          as DateTime19960101Midnight
from   rdb$database

Which – using DMY date format and 24 hour clock format settings – results in:

DATE20160323 DATETIME20160323160539 NOON       DATETIME18991230MIDNIGHT DATETIME190001011800 DATETIME189912290600 DATETIME19960101MIDNIGHT CONSTANT
24-3-2016    23-3-2016 16:05:39     12:00:00   30-12-1899               1-1-1900 18:00:00    28-12-1899 18:00:00  1-1-1996

Which basically taught me a new thing about firebird: Times are calculated in seconds, so date fractions need to be multiplied by 60 * 60 * 24.

You can see this in the following query and results:

select (cast('Now' as time) - cast('00:00:00.000' as time)) / (24 * 60 * 60) as DelphiTime,
        cast('Now' as time) - cast('00:00:00.000' as time) as SecondsSinceMidnight,
        cast('Now' as date) - cast('1899-12-30' as date) as DelphiDate,
        cast('Now' as timestamp) - cast('1899-12-30 00:00:00.000' as timestamp) as DelphiDateTime,
        24 * 60 * 60 as SecondsPerDay,
        cast('Now' as date) as "Date",
        cast('Now' as time) as "Time",
        cast('Now' as timestamp) as "TimeStamp"
from rdb$database
0,4366               37722,284            42835       42835,436600509      86400                10-4-2017  10:28:42     10-4-2017 10:28:42      

Note this post is complementary to Date format converter from Text or Unix/Mac/Filetime/Microsoft to virtually any readable form (which mentions the wrong Microsoft epoch as it should be 1899-12-30 midnight): the above shows how to do the conversion to readable dates in Firebird (might work in InterBase as well, but I’ve not used that for a long time).


PS: for Microsoft SQL Server: passing dates to the sql server: pass it as float – 2. -2 is the difference between delphi tdatetime start date and mssql start date.


Posted in Algorithms, Database Development, Development, Firebird, Floating point handling, Software Development | 2 Comments »

How to speed up Count(*) in Interbase/Firebird – Stack Overflow

Posted by jpluimers on 2017/02/14

The drawback of how the multigenerational architecture is implemented:

Even when an index is available on the column or columns included in the COUNT, all records must be visited in order to see if they are visible under the current transaction isolation.

The benefits of having far less locking than other architectures means you need to be careful using the COUNT operator on large result-sets.

In many systems the exact result of a COUNT isn’t very important, so you can use the workaround referred from How to speed up Count(*) in Interbase/Firebird – Stack Overflow


via: Select count(*) is slow

Posted in Database Development, Development, Firebird, InterBase | Leave a Comment »

%d bloggers like this: