The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 2,087 other followers

Archive for the ‘SQL’ Category

FireDAC can do DBMS back-end conditional SQL via Conditional Substitution

Posted by jpluimers on 2020/09/17

Though the field-types mentioned in the problem and solution are equal (so either is wrong), the solution in [WayBackI have a little problem with FireDAC and the TStringField and TWideStringField design time generation… – Juan C. Cilleruelo – Google+ pointed out by Jeff Weir is interesting: FireDAC supports conditionals that depend on the DBMS back-end, so you can differentiate between them.

The feature is called Conditional Substitution and has been present ever since AnyDAC (which got bought by Embarcadero, transformed into FireDAC, then after Idera bought Embarcadero, the main developer got pink-slipped).

The AnyDAC documentation is in the wayback machine, though you have to disable the onload event in order to read it.

The [Archive.is] XE5: Preprocessing Command Text (FireDAC) – RAD Studio documentation is not much different from the current state [Archive.is].

More background reading is at [WayBack] www.freepascal.org/~michael/articles/anydac2/anydac2.pdf and Cary Jensen covered it in his 2017 course on FireDAC of which you can see the free ToC.

Example from that thread:

SELECT ART.CD_ITEM                ,
       ART.CD_FAMILY              ,
       ART.CD_CATALOGUE           ,
       CAT.DS_CATALOGUE           ,
       FAM.DS_FAMILY              ,
{IF MSSQL}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN CAST('Y' AS NVARCHAR) 
          ELSE CAST('N' AS NVARCHAR) 
       END HAS_CONFIGURATION      ,
{fi}
{IF FIREBIRD}
       CASE WHEN EXISTS(SELECT 1 FROM CONFIGURATIONS COM WHERE COM.CD_PARENT = ART.CD_ITEM)
          THEN 'Y'  
          ELSE 'N'  
       END HAS_CONFIGURATION      ,
{fi}
       ART.DS_ITEM                ,
       ART.CD_TAX                 ,
       TAX.DS_TAX                 ,
       TAX.PRC_TAX               ,
...

Given the problem statement, the casts likely should have been VARCHAR instead of NVARCHAR, but the construct can be very powerful.

–jeroen

Posted in Database Development, Delphi, Development, Firebird, InterBase, Software Development, SQL, SQL Server | Leave a Comment »

Joins explained.

Posted by jpluimers on 2020/08/26

Turn your head 90 degrees counter-clockwise around the longitudinal axis [WayBack] Joins explained. – Kristian Köhntopp – Google+

Wait, let me help you:

Read the rest of this entry »

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

browser – Clearing old browsing data in Chrome instead of newer data – Super User

Posted by jpluimers on 2020/06/25

[WayBackbrowser – Clearing old browsing data in Chrome instead of newer data – Super User had a few possibilities, but eHistory disappeared from the Chrome store (apparently due to some unknown violation), so the way that works best now is to hack the History database which is a SQLite file as mentioned in

[WayBackHow can I delete all web history that matches a specific query in Google Chrome – Super User: For literal values of “query”…You can even query your Chrome history using SQL. (Firefox too: see below. Of course, the appropriate file path will have to be changed).

If you really want you can hack the history frame chrome://history-frame/: [WayBack] How can I delete all web history that matches a specific query in Google Chrome – Super User

–jeroen

Posted in Chrome, Database Development, Development, Google, Power User, Software Development, SQL | Leave a Comment »

In SQL Server use `SET NOCOUNT ON` so tools taking the last modified record count won’t be confused by your trigger.

Posted by jpluimers on 2020/04/07

Interesting read: Time eating bug of the day… – Fabian S. Biehn – Google+.

TL;DR: in SQL Server use SET NOCOUNT ON so tools taking the last modified record count won’t be confused by your trigger.

Source: [WayBackTime eating bug of the day: I used a TADOQuery.ExecSQL (on Berlin) for an Up…

Related: [WayBack] sql server – ADODB affected rows return trigger’s affected rows – Stack Overflow

–jeroen

Posted in Database Development, Delphi, Development, Office VBA, Scripting, Software Development, SQL, SQL Server | Leave a Comment »

SQL code smells

Posted by jpluimers on 2019/10/31

A while back I bumped into [WayBack] SQL Code Smells – Simple Talk, which is an extensive article covering all sorts of SQL related code smells.

It reminds me that one day I need to dig up some old links on other code smells as well.

–jeroen

via: [WayBackMartin Fowler on Twitter: “It’s an old anti-pattern, and sadly is still going strong: The Entity Service Antipattern.”

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

 
%d bloggers like this: