Archive for the 'Database' Category

May 26 2012

Firebird vs Oracle – First impressions

Published under Database,English,Firebird,Oracle

I and my team recently managed the development of a huge database with Oracle. The same database already exists and successfully lives thanks to Firebird SQL.

The main goal of this development is the creation of a cross-platform application, able to work both with Firebird SQL and with Oracle at the same time.

We reached the goal after a lot of headache and frustration due to the subtle, and often incredible, differences between the two platforms. In this post I list the main differences we faced to. The next table summarize the major topics, this topics will be better discussed in a next post.

This analysis is not intended to be exhaustive and general for all types development. This comparison is based on the experience of a team that reaches great results following the "best practices" in designing a Firebird SQL and expects similar results, and possibly similar winning strategies, in developing with Oracle .

 Topic

Oracle 11g

Firebird 2.5
Installation Cumbersome with a lot of unexpected incidents Simple and straightforward
Resource eating As much RAM as possible

A lot of services running

 Minimal, proportional to DB size

Two services running
Logs Widespread on the machine

Mainly non human readable

 One log for all
Documentation Huge Not well structured
Support form the community A lot of discussion forums and blogs

Low quality

Difficulties to find the right solution due different approaches and philosophies

 Sparse specific forums

Great support from Firebird enthusiasts
Empty strings Automatically transformed to NULL Differentiation of empty strings and NULL strings
INTEGER Automatically converted to NUMBER(38) INTEGER
NULLs in expression NULL operators gives NULL results

String CONCATENATION has different behaviour: NULL treated as empty string

 SQL-92 Compliant: NULL operators gives NULL results
Stored procedure as Tables Possible with "FROM Table()" construct Possible with "FROM" construct
SELECT INTO with empty result set Fails Processed with NULL
IF EXISTS() Non present. Workaround with tricks  (for loop) Present
SELECT FIRST SKIP or SELECT ROWS WHERE ROWNUM

Due to the prefiltering effect of the where clause, and odd solution should be used:

select * from (select .... order by) where rownum

 SQL-92 compliant
SELECT COALESCE() with no rows Returns empty result set Executes the COALESCE
Index on Foreing Keys Explicit creation required Automatically created
CONTAINING Workaround: upper(filed) LIKE upper('%pattern%') Native

Comments Off

Oct 04 2010

Firebird 2.5 released

Published under Database,Firebird

Finally the new 2.5 version of Firebird has been released!

Al lot of new feature included in this version, the most intriguish and interesting to me are:

  • System audit tracing and user trace sessions via the Services API, making it possible to monitor and analyse everything going on in a database in real time
  • Asynchronous cancellation of connections
  • Regular expression support using the SIMILAR TO predicate
  • Enhanced access to stored procedures in view definitions
  • Support for WHERE SOME_COL = ? OR ? IS NULL predications

Comments Off

Apr 10 2010

Why Firebird? Facts for decision makers

Published under Database,Firebird,Freeware and Open source

The new main release of Firebird is coming with a lot of new features, SQL extensions and architectural improvements.

This Open Source Database is one of the best database solution available at this moment. This database engine encapsulates in a FREE solution :

  • Rich and powerfull SQL language
  • Record versioning (no locks) - Multi generation architecture
  • Fully scalable in size from Mb to Tb
  • Fully scalable in number of users
  • Simple and with small footprint  installation
  • Multiplatform server
  • Native connection in many languages

If would you like a deeper analysis about why Firebird could be a good deal, take a look at the presentation why-firebird-fact-for-decision-makers.

There are some myths around firebird spread with the aim of hiding the real power of this solution. In the document Firebird_myths these myths are refuted.

Comments Off

Next »