 | 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 2025/05/14
A few years back I bumped in this cool [Wayback/Archive] Thread by @asadotzler on Thread Reader App on early Firefox history (from before it was called Phoenix or Firebird, heck from before Phoenix was created!).
It is important to keep telling these bits of history as they are fundamental to understand the Web Browser landscape as it is now.
Great material that complements Wikipedia articles like these:
Posted in Database Development, Development, Firebird, Firefox, History, Power User, Web Browsers | Leave a Comment »
Posted by jpluimers on 2022/10/27
I knew there was JSFiddle for live playing around with JavaScript and more in your browser, so I wondered if there was a similar site for databases and SQL queries.
There are, so here are a few database fiddle sites: SQL playgrounds where you can live play with SQL queries (sometimes even without an underlying example database).
All via [Wayback/Archive.is] database fiddle – Google Search:
Posted in Conference Topics, Conferences, Database Development, DB2, Development, Event, Firebird, JavaScript/ECMAScript, JSFiddle, MariaDB, MySQL, OracleDB, PL/SQL, PostgreSQL, Scripting, Software Development, SQL, SQL Server, SQLite, T-SQL | Leave a Comment »
Posted by jpluimers on 2021/07/08
For my link archive:
–jeroen
Posted in Database Development, Delphi, Development, Firebird, Software Development | 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/09/11
The RDB$RELATIONS.RDB$RELATION_TYPE was introduced in Firebird 2.1 with these enumeration values:
enum rel_t {
rel_persistent = 0,
rel_view = 1,
rel_external = 2,
rel_virtual = 3,
rel_global_temp_preserve = 4,
rel_global_temp_delete = 5
};
You should access it assuming NULL means zero (0), so you better treat it using [WayBack] COALESCE() like COALESCE(RDB$RELATIONS.RDB$RELATION_TYPE, 0).
Background information:
RDB$RELATIONS.RDB$RELATION TYPE–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2019/09/05
Since I likely need these [WayBack] SQL Language Changes in the future (some breaking Firebird 2.x habits).
The odd thing is that I got there looking for “no column name specified for column number 1 in derived table” when doing some weird CTE query things in Firebird 2.5x
–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2019/08/28
Sometimes you want to count data in all tables on a database to get a feel for the orders of magnitude, but you cannot use the approximated example in How to speed up Count(*) in Interbase/Firebird – Stack Overflow as those require primary keys.
Then the below script can help: it generates the right queries as a union all ordering by the count in the tables.
Example generated code on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
SQL
--------------------------------------------------------------------------------------------------
select 128 as id, 'COUNTRY' as name, count(*) from "COUNTRY" union all
select 129 as id, 'JOB' as name, count(*) from "JOB" union all
select 130 as id, 'DEPARTMENT' as name, count(*) from "DEPARTMENT" union all
select 131 as id, 'EMPLOYEE' as name, count(*) from "EMPLOYEE" union all
select 133 as id, 'PROJECT' as name, count(*) from "PROJECT" union all
select 134 as id, 'EMPLOYEE_PROJECT' as name, count(*) from "EMPLOYEE_PROJECT" union all
select 135 as id, 'PROJ_DEPT_BUDGET' as name, count(*) from "PROJ_DEPT_BUDGET" union all
select 136 as id, 'SALARY_HISTORY' as name, count(*) from "SALARY_HISTORY" union all
select 137 as id, 'CUSTOMER' as name, count(*) from "CUSTOMER" union all
select 138 as id, 'SALES' as name, count(*) from "SALES" order by 3
Example output on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
ID NAME COUNT 133 PROJECT 6 128 COUNTRY 14 137 CUSTOMER 15 130 DEPARTMENT 21 135 PROJ_DEPT_BUDGET 24 134 EMPLOYEE_PROJECT 28 129 JOB 31 138 SALES 33 131 EMPLOYEE 42 136 SALARY_HISTORY 49
The generation code below uses a few tricks:
select rdb$relation_namefrom rdb$relationswhere rdb$view_blr is nulland (rdb$system_flag is null or rdb$system_flag = 0);ID the negation of SCORE)SELECT p.name, p.score, COUNT(others.score) + 1 AS "rank"FROM players pLEFT JOIN players others ON others.score > p.scoreGROUP BY 1, 2ORDER BY "rank"The rank helps me distinguish the last row (for the order by 3 clause) and other rows (for the union all clauses).
Generation code:
with tables(id, name) as (
-- http://www.firebirdfaq.org/faq376/
select r.RDB$RELATION_ID as id, trim(r.RDB$RELATION_NAME) as name
from RDB$RELATIONS r
where 1=1
and (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0)
and r.RDB$VIEW_BLR is null
order by 1
),
ranked_tables(id, rank, name) as (
-- http://www.firebirdfaq.org/faq343/
select tables.ID, count(others.id)+1 as "rank", tables.NAME
from tables
left join tables others on others.ID < tables.ID
group by "ID", "NAME"
order by "rank"
),
parts(id, rank, name, suffix) as (
select ranked_tables.id,
ranked_tables.rank,
ranked_tables.name,
case
when ranked_tables.rank = 1 then 'union all' -- first
when ranked_tables.rank = (select count(*) from tables) then 'order by 3' --last
else 'union all' -- middle
end as suffix
from ranked_tables
)
select -- parts.id, parts.rank, parts.name, parts.suffix,
'select '||parts.id||' as id, '''||parts.name||''' as name, count(*) from "'||parts.name||'" '||parts.suffix||'' as SQL
from parts
order by parts.id
–jeroen
Posted in Database Development, Development, Firebird, Software Development, SQL | Leave a Comment »
Posted by jpluimers on 2019/06/12
Reminder to self: sort out why a while ago I had trouble with a nested [WayBack] CASE construct causing parsing anomalies in 2.5.8.
–jeroen
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Posted by jpluimers on 2019/05/30
Great article: Testing for NULL and equality in practice [WayBack]
Most important table piece I learned:
Testing (in)equality of A and B in different Firebird versions
Test type Firebird version <= 1.5.x >= 2.0 Equality Inequality
–jeroen
Posted in Database Development, Development, Firebird, SQL | Leave a Comment »
The Wiert Corner - irregular stream of stuff 