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:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`months_between` $$
CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
BEGIN
  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;
RETURN @num_months;
END $$
 
DELIMITER ; 

Hope it helps!

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 Firebird-1.5.3.4870-0_embed_win32.zip
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)

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

CREATE INDEX idx3 ON employee
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:

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

It's plan is:

PLAN (EMPLOYEE ORDER IDX3)

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

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

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

PLAN SORT ((EMPLOYEE NATURAL))

The index is correctly ignored.
And a query like:

SELECT SUBSTRING(dept_no FROM 1 FOR 2), first_name 
FROM employee 
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":

PLAN (EMPLOYEE ORDER RDB$FOREIGN8)

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

SELECT 
*
FROM rdb$indices 
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.

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
database:

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!!

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:

...
on error resume next
dim adoConn
dim adoRS
dim counter
set adoConn = Server.CreateObject("ADODB.Connection")
set adoRS = Server.CreateObject("ADODB.Recordset")
adoConn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_1_5\examples\EMPLOYEE.FDB;UID=SYSDBA;PWD=masterkey"
adoConn.Open
adoRS.ActiveConnection = adoConn
if adoConn.errors.count = 0 then
response.write "Fields In The 'Contacts' Table:"
adoRS.Open "select e.first_name, e.last_name, d.department, p.proj_name from employee e right outer join
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
ep.proj_id = p.proj_id", adoConn
nfields = adoRS.fields.count
while not adoRS.EOF
for i = 0 to nfields -1
response.write adoRS.fields(i).name & ": " & adoRS.fields(i).value
& " "
next
response.write "
"
adoRS.MoveNext
wend
else
response.write "ERROR: Couldn't connect to database"
end if
adoRS.Close
if adoConn.errors.count = 0 then
response.write "
Second example:
"
adoRS.Open "select count(e.emp_no) as emp_4_proj, d.department, p.proj_name from employee e right outer join
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
ep.proj_id = p.proj_id group by d.department, p.proj_name", adoConn
nfields = adoRS.fields.count
while not adoRS.EOF
for i = 0 to nfields -1
response.write adoRS.fields(i).name & ": " & adoRS.fields(i).value
& " "
next
response.write "
"
adoRS.MoveNext
wend
else
response.write "ERROR: Couldn't connect to database"
end if
adoConn.Close
Set adoRS = nothing
Set adoConn = nothing
... 

Using transactions within ASP pages:

if adoConn.errors.count = 0 then
'here we begin a transaction
adoConn.begintrans
'now we add a row to the “country” table
adoConn.execute "insert into country values ('Portugal', 'Escudo')"
'we commit the transaction, thus saving changes
adoConn.CommitTrans
'now we start another transaction
adoConn.begintrans
'here we delete the newly added row
adoconn.execute "delete from country where country = 'Portugal'"
'this time we rollback the transaction, not saving the changes
adoconn.rollbacktrans
'this query will show that the commit and rollback sequence has been executed and the row with ‘Portugal’ and ‘Escudo’ is present
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)