Wednesday, September 14, 2011

Firebird DB management

Normally I use a program called 'EMS SQL Manager 2005 for InterBase and Firebird Lite' for managing my Firebird databases (adding tables and fields, or correcting data) but every now and then there are problems with it. Then I use a much smaller program (both in scope and size) called 'MyWiSql', but this too has its problems. Slowly at the back of my mind, a need has been growing to write my own tool which would combine the best aspects of both programs and today I wrote that tool.

Here's a screen capture of the program

The program reads the database aliases and locations from the registry (that's the advantage of writing my own program - but of course, no one else can use the program); once a database has been chosen, its tables are selected, along with the fields of the first table. Queries can be made against the database (selects, updates, deletes, changing table structure) and the results can be shown in a different pane. The program keeps a log of the queries - I often find that when using one of the management tools, I issue a 'select' query, update a value then issue the 'select' query again to ensure that the change has been made. Now I can simply copy the original query from the log instead of typing it again.

Normally, one has to press the 'execute' button in order to execute a query but this quickly became a nuisance so I added some code which causes the query to execute whenever a semicolon is typed (this character is normally used as an SQL statement separator).

I even added a pane which displays the SQL trace, but the results of this have been disappointing. I had hoped to see the execution plan (which MyWiSQL manages to display) but I have yet to achieve this. Once can hope, but there seems to be very little information about the SQLMonitor component, which displays this information. I may have to resort to reverse engineering MyWiSQL, but I'm very doubtful about this.

Otherwise, this will be a very useful addition to my programming toolbox.

No comments: