Firebird tricks

FirebirdFirebird database, not a huge fan.. Do have to cope though so here are some tips I’ve spent some painful hours collecting through my work. I will try to add more later.

The client

Working with any databases without a good client to test your queries is no good. I’ve tried a couple and found that FlameRobin is the best for me. It’s simple, but get the job done. It runs on all significant platforms.

Looking for gds32.dll?

Flamerobin will not run on Windows without the gds32.dll library. This dll is included in the firebird server. Install the firebird server and make sure the gds32.dll file is placed in the system32 folder and you’ll be fine – You do not need to run the firebird server for this to work.

Limit and skip

To limit and skip results in firebird you write your queries like so:

SELECT FIRST 20 SKIP 20 * FROM table ORDER BY id

Firebirds “NOW()”

To select records with date ranges you can use functions like current_date or current_time:

SELECT * FROM table WHERE renewal_date > current_date

If you want records older than 30 days ago:

SELECT * FROM table WHERE date_added < current_date - 30

Group by week

This might be the firebird challenge of my life =) I hope this saves you some time.. This query will give you the sum of some field grouped by weeks. You even get the correct week number from firebird.

SELECT FIRST 20 SUM(o.price), extract(year from o.created) as "Year",
   (extract(yearday from o.created) - extract(weekday from o.created-1) + 7) / 7 as "Week"
FROM orders o
GROUP BY 2,3
ORDER BY 2 DESC, 3 DESC

Notice the “GROUP BY 2,3”. This translates to group by the second element of the selected column, then the third. You have the same pattern in the “ORDER BY 2 DESC, 3 DESC”. This will display the results neatly ordered by year, and then week within that year.

Subquery in where

This is a scenario where we have a subscriptions table and a periode table. We want to extract a list of all subscriptions that are inactivated and expired.

SELECT (SELECT FIRST 1 p.to_date FROM periode p WHERE p.id = a.periode_id ORDER BY p.to_date DESC) to_date, s.username, s.active, s.state
FROM subscription s
WHERE a.active = 0 AND (SELECT FIRST 1 p.to_date FROM periode p WHERE p.id = a.periode_id ORDER BY p.to_date DESC) < current_time
    • Harriv
    • October 29th, 2010

    EXTRACT(WEEK from TIMESTAMP) works with Firebird 2.1+

    Documentation here: http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-extract.html#langrefupd21-intfunc-extract-week

  1. In the extract by week example, why you just didnt use: extract(week from o.created)? Afaiu, it will give the same result as your math.

  2. @Carlos
    @Harriv
    Good argument, but that would give different and false result. I’ve tested this with version 2.1.2 and the query would group then give per october 2010 a row of week 53 of year 2010.
    Exactly how this error occurs requires further investigation as all the week leading to the week “53” seems to match with my suggested solution.
    Insights are welcome!

    • Neoseifer
    • November 5th, 2010

    Flamerobin is the best ? Have you test IBExpert ? Even the free version seems to be better than FlameRobin, no ?

  3. Yes, I have. It’s been a while though so maybe I should have another look at it. I like Flamerobins simplicity..

  1. October 29th, 2010