Saturday, August 26, 2006

MySQL function: months_between

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:

  2. DROP FUNCTION IF EXISTS `test`.`months_between` $$
  3. CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
  4. BEGIN
  5. SELECT IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) > 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) - (MID(date_end, 9, 2) < style="color: rgb(102, 204, 102);">(date_start, 9, 2)), IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) < 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) + (MID(date_start, 9, 2) < style="color: rgb(102, 204, 102);">(date_end, 9, 2)), (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))))) INTO @num_months;
  6. RETURN @num_months;
  7. END $$

Hope it helps!

Thursday, August 17, 2006

Flamerobin as a GUI for embedded Firebird

In general you just need to have Flamerobin connect to Embedded Firebird through local protocol, my setup with Firebird 1.5.3 (Firebird 2.0 RC3 is giving me troubles!) is:

1. Pick fbembed.dll from
2. Copy it to C:\Program Files\FlameRobin (the Flamerobin installation folder, whatever it's named
3. Rename it to fbclient.dll
4. Register a new server in Flamerobin, without specifying the hostname and port (leave them blank)

5. Register a new database under that server, if you correctly left the hostname blank it will try to connect to something like "sysdba@c:\program files\employee.fdb"

5.1. Specify a password, because Flamerobin seems to want it, but remember that it will not be checked in the embedded version (privileges still exist!!)
6. You might need to copy more files from the embedded distribution to the flamerobin folder, like firebird.msg for messages, firebird.conf, ib_util.dll and the whole "intl" folder for connections with specific charsets!

Note that this allows me to keep also Firebird 2.0 RC3 installed, network connection to firebird 2 databases (new OnDiskStructure) will work, while it will fail if you try to connect as embedded (local protocol)

Wednesday, August 16, 2006

The case for function based indexes - part two

Some time ago I wrote about function based indexes, showing a PostgreSQL based case, right now I'll show a Firebird based example, due to the fact that Firebird 2.0 supports Expression Indexes, which are my good old function based indexes.
This example will be based on the sample database which ships with Firebird, named EMPLOYEE.FDB.
As the old example was based on extracting a substring from a code, I'll show you a similar one, this will also highlight syntax differencies between Firebird and PostgreSQL

  1. CREATE INDEX idx3 ON employee
  2. computed BY (SUBSTRING(dept_no FROM 1 FOR 2));

Now you see that I created an index named idx3 on table employee made of the first two chars of column dept_no (substring starts from position 1 and goes on for the number of chars specified).
After committing changes (with Firebird you can rollback data definition statements!) you can check if the index is picked up with a query like:

  1. SELECT SUBSTRING(dept_no FROM 1 FOR 2), first_name
  2. FROM employee
  3. ORDER BY SUBSTRING(dept_no FROM 1 FOR 2);

It's plan is:


which clearly shows that the index is picked up!
Note that a query like:

  1. SELECT SUBSTRING(dept_no FROM 1 FOR 2), first_name
  2. FROM employee
  3. ORDER BY 2;

Which means "order by first_name" results in a plan like:


The index is correctly ignored.
And a query like:

  1. SELECT SUBSTRING(dept_no FROM 1 FOR 2), first_name
  2. FROM employee
  3. ORDER BY dept_no;

Results in yet another plan, in which an index built on the whole dept_no column is chosen over the "expression index":


A quick glance to all the indexes existing on this table can be taken from this query:

  2. *
  3. FROM rdb$indices
  4. WHERE rdb$relation_name = 'EMPLOYEE';

MySQL Control Center, live after death???

Hey, looks like someone decided to give a spin to the old MySQLCC (aka Mysql control center) , see Sourceforge project page by releasing version 0.9.7.

Friday, August 11, 2006

OpenOffice Calc and Firebird through ODBC

This is meant to be a quickstarter in using OpenOffice 2.0.3 and Firebird on Windows.
Of course many of this steps are common to any other database used in OpenOffice, so I hope
others will be able to use this doc as a source.
I'll be using the EMPLOYEE.FDB sample database that comes with Firebird.
First of all you need to install the ODBC driver for Firebird, you can download the latest and
greatest version from here (development site).
You can safely choose the “Deployment install” unless you plan to do some ODBC related
development of course ;-)

Having installed the driver now you need to create a “System DSN” an ODBC datasource
accessible from all users of your machine.
Go to “Control Panel” -> “Administrative Tools” -> “ODBC Datasources” -> “System DSN” ->
“Add” and choose the newly installed “Firebird/Interbase ® Driver”.
You'll get a configuration form, in which basically select the database and specify username and
password, leave other options for more advanced users.
(Sorry if some screenshots are in italian and others in english, but my home PC is a bit of a mess ...)

Fill in the needed blanks and test connection(make shure firebird is running!!)

Now we got the ODBC datasource up and running, it's time to run OpenOffice Base to create an
OpenOffice datasource, which will then be available in all OpenOffice applications.
The wizard will guide us through the setup process, first of all we'll connect to an existing ODBC

So we'll have to select the ODBC datasource (DSN) created before (clicking the “Browse” button
will bring out the ODBC datasource list:

Ok, now keep clicking to complete the wizard, again, DON'T FORGET to test the datasource!
It's definition will be saved in an .odb file which will be reusable in all OpenOffice apps.

It's time to actually use OpenOffice Calc to analyze data in the Firebird database, open the app and go to “Data” -> “Data pilot”:

Nice, isn't it? Read the rest in PDF version!!

Tuesday, August 01, 2006

ASP and Firebird

This is really a copy of a PDF file I made available on my old website, I'm posting it here because I'm trying to clean up things.
It's about Firebird and ASP pages, the most interesting thing is that it shows how to use transactions within ASP pages, which is not so common.

Connect to Firebird and retrieve data in ASP:

  1. ...
  2. on error resume next
  3. dim adoConn
  4. dim adoRS
  5. dim counter
  6. set adoConn = Server.CreateObject("ADODB.Connection")
  7. set adoRS = Server.CreateObject("ADODB.Recordset")
  8. adoConn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_1_5\examples\EMPLOYEE.FDB;UID=SYSDBA;PWD=masterkey"
  9. adoConn.Open
  10. adoRS.ActiveConnection = adoConn
  11. if adoConn.errors.count = 0 then
  12. response.write "Fields In The 'Contacts' Table:"
  13. adoRS.Open "select e.first_name, e.last_name, d.department, p.proj_name from employee e right outer join
  14. department d on e.dept_no = d.dept_no left outer join employee_project ep on e.emp_no = ep.emp_no left outer join project p on
  15. ep.proj_id = p.proj_id", adoConn
  16. nfields = adoRS.fields.count
  17. while not adoRS.EOF
  18. for i = 0 to nfields -1
  19. response.write adoRS.fields(i).name & ": " & adoRS.fields(i).value
  20. & " "
  21. next
  22. response.write "
  23. adoRS.MoveNext
  24. wend
  25. else
  26. response.write "ERROR: Couldn't connect to database"
  27. end if
  28. adoRS.Close
  29. if adoConn.errors.count = 0 then
  30. response.write "

    Second example:

  31. adoRS.Open "select count(e.emp_no) as emp_4_proj, d.department, p.proj_name from employee e right outer join
  32. department d on e.dept_no = d.dept_no left outer join employee_project ep on e.emp_no = ep.emp_no left outer join project p on
  33. ep.proj_id = p.proj_id group by d.department, p.proj_name", adoConn
  34. nfields = adoRS.fields.count
  35. while not adoRS.EOF
  36. for i = 0 to nfields -1
  37. response.write adoRS.fields(i).name & ": " & adoRS.fields(i).value
  38. & " "
  39. next
  40. response.write "
  41. adoRS.MoveNext
  42. wend
  43. else
  44. response.write "ERROR: Couldn't connect to database"
  45. end if
  46. adoConn.Close
  47. Set adoRS = nothing
  48. Set adoConn = nothing
  49. ...

Using transactions within ASP pages:

  1. if adoConn.errors.count = 0 then
  2. 'here we begin a transaction
  3. adoConn.begintrans
  4. 'now we add a row to the “country” table
  5. adoConn.execute "insert into country values ('Portugal', 'Escudo')"
  6. 'we commit the transaction, thus saving changes
  7. adoConn.CommitTrans
  8. 'now we start another transaction
  9. adoConn.begintrans
  10. 'here we delete the newly added row
  11. adoconn.execute "delete from country where country = 'Portugal'"
  12. 'this time we rollback the transaction, not saving the changes
  13. adoconn.rollbacktrans
  14. 'this query will show that the commit and rollback sequence has been executed and the row with ‘Portugal’ and ‘Escudo’ is present
  15. adoRS.Open "select * from country", adoConn

Note that your decision on transaction usage and the commit/rollback sequence must be business driven, no guesswork at all!!
The original paper is available here (PDF)