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 1,498 other followers

Archive for the ‘InterBase’ Category

Quickly generate queries for all non-system tables in your database in Firebird or InterBase

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 »

VM disk sizes

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.

Read the rest of this entry »

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 »

Some notes on what errors you get when using a gds32.dll not matching your Firebird or InterBase

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 database
  • I/O error during "CreateFile (open)" operation for file "C:\PROGRAMDATA\EMBARCADERO\INTERBASE\GDS_DB\EXAMPLES\DATABASE\EMPLOYEE.GDB"
  • connection rejected by remote interface

Since 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 »

SQL: “where not exists … having” formulation; anti-join alternative

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:

A search on SO for code:"EXISTS (SELECT 1" yields 5,048 results.
A search on SO for code:"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 find SELECT 1 more intuitive. It’s like saying “if at least one exists”.
Is SELECT * more intuitive?

–jeroen

 

 

Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »

Visual Representation of SQL Joins – CodeProject

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:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN
  • LEFT JOIN EXCLUDING INNER JOIN
  • RIGHT JOIN EXCLUDING INNER JOIN
  • OUTER JOIN EXCLUDING INNER JOIN

Note:

  • the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
  • the opposite of OUTER JOIN is empty set.

But the diagram is usually speaks for itself.

–jeroen

Source: Visual Representation of SQL Joins – CodeProject

Read the rest of this entry »

Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »

 
%d bloggers like this: