 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
Posted by jpluimers on 2025/07/02
Since I keep forgetting which DBMS uses which method to select just a plain value without a table. I always remember it as my search phrase [Wayback/Archive] SELECT from DUAL, but actually better titled like the question below:
[Wayback/Archive] sql – SELECT from nothing? – Stack Overflow
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »
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 [WayBack] I 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 »
Posted by jpluimers on 2019/08/06
For my link archive in case of link rot: [WayBack/Archive.is] 28800 Embarcadero DBX ADO.NET 2.0 driver for InterBase (version 17.0)
Note these are the same as the ones shipping with the Interbase XE7 developer edition.
The less old alternative from Embarcadero alternative is the ODBC driver: [WayBack/Archive.is] 28975 Embarcadero InterBase ODBC Driver for Windows, 32-bit and 64-bit
Maybe it is a good idea to look into [WayBack] All IBProvider news Firebird and InterBase driver for OLE DB and ADO.NET
Posted in .NET, Database Development, Development, InterBase, Software Development | Leave a Comment »
Posted by jpluimers on 2018/07/26
Change at will:
select 'select * from ' || r.rdb$relation_name as query
from rdb$relations r
where 1=1
and r.rdb$system_flag <> 1 -- no system relations
and r.rdb$view_source is null -- only tables
order by r.rdb$relation_name
For the EMPLOYEE demo database, this results in:
select * from COUNTRY
select * from CUSTOMER
select * from DEPARTMENT
select * from EMPLOYEE
select * from EMPLOYEE_PROJECT
select * from JOB
select * from PROJECT
select * from PROJ_DEPT_BUDGET
select * from SALARY_HISTORY
select * from SALES
–jeroen
Posted in Database Development, Development, Firebird, InterBase, Software Development | Leave a Comment »
Posted by jpluimers on 2018/06/29
I forgot to schedule the post below. It is still relevant if you create a machine with lots of Delphi versions on it.
Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, Database Development, Delphi, Delphi 2007, Delphi XE, Delphi XE2, Delphi XE3, Delphi XE4, Delphi XE5, Development, Firebird, InterBase, Power User, Software Development, Windows, Windows 8 | 2 Comments »
Posted by jpluimers on 2018/06/28
These were some of the errors and error fragments I got when I had the wrong gds32.dll or wrong Database engine:
file C:\PROGRAMDATA\EMBARCADERO\INTERBASE\GDS_DB\EXAMPLES\DATABASE\EMPLOYEE.GDB is not a valid databaseI/O error during "CreateFile (open)" operation for file "C:\PROGRAMDATA\EMBARCADERO\INTERBASE\GDS_DB\EXAMPLES\DATABASE\EMPLOYEE.GDB"connection rejected by remote interfaceSince I used IBX, they were all inside EInterbaseError exception instances.
The bad thing: with IBX you cannot specify your gds32.dll: you have to ensure the right version/architecture is loaded by your executable.
–jeroen
via: Source: Delphi, IBX and the Turkish I problem
Posted in Database Development, Development, Firebird, InterBase | Leave a Comment »
Posted by jpluimers on 2018/06/26
I need to write up some notes, but there are some links that will help me:
It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id); SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);I am not considering NULL or “fun variants” which don’t seem intuitive to me.
SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);
The question popped up in comments just now. I researched the manuals of the most popular RDBMS:
- MS SQL seems to favor
SELECT *in the manual.- The example in the PostgreSQL 9.4 manual uses
SELECT 1.- Oracle 11g has
SELECT *in the language reference.- MySQL 5.7 has
SELECT *in the reference manual but alsoSELECT 1in the comments.- SQLite has no example in the language reference.
A search on SO for
code:"EXISTS (SELECT 1"yields 5,048 results.
A search on SO forcode:"EXISTS (SELECT *"yields 5,154 results.
Updated links and counts 07.2015.So
SELECT *has the popular vote and the big commercial RDBMS on its side.
I findSELECT 1more intuitive. It’s like saying “if at least one exists”.
IsSELECT *more intuitive?
–jeroen
Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2017/08/02
I thought I posted a reference to this a long time ago, but didn’t.
It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:
The article explains these in greater detail:
Note:
But the diagram is usually speaks for itself.
–jeroen
Source: Visual Representation of SQL Joins – CodeProject
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2017/07/11
A few notes when making scripts that create indexes based if they do (not) exist (yet):
–jeroen
Posted in Database Development, Development, InterBase | Leave a Comment »
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 the MGA (multi-generational architecture) 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
See also:
–jeroen
Posted in Database Development, Development, Firebird, InterBase | Leave a Comment »
The Wiert Corner - irregular stream of stuff 