Saturday, December 30, 2006

Creating an auto incrementing column in Firebird

or in any database that supports triggers and generators (aka sequences) ...
The answer is well known however the question comes out quite often, be it in an Oracle, Firebird or PostgreSQL forum.
Well, the PostgreSQL guys took a radical approach and embedded the common solution into a datatype named SERIAL, what follows is an example aimed at the Firebird newbie.

Say you have a table like this

  1. CREATE TABLE new_one
  2. (
  3. col1 integer NOT NULL,
  4. col2 CHAR(10),
  5. PRIMARY KEY (col1)
  6. );
You want col1 to behave like an autoincrement/identity field.
You create a generator

  1. CREATE GENERATOR my_gen_id;
(you could set more options).
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:

  1. CREATE TRIGGER autoincrementor_id FOR new_one
  2. ACTIVE BEFORE INSERT POSITION 0
  3. AS
  4. BEGIN
  5. IF (NEW.col1 IS NULL) THEN
  6. NEW.col1 = GEN_ID(my_gen_id,1);
  7. END
And as shammat pointed out here "Basically the same technique as with Oracle's sequences".

To retrieve the current value after an insert (if you aren't using Firebird 2.1 RETURNING clause):

SELECT GEN_ID( MY_GEN_ID, 0 ) FROM RDB$DATABASE;


Addendum:
Sometimes people might want to empty the table and reset the "autoincrement" id (a questionable approach IMHO if that value has any kind of meaning).
This can be accomplished by a simple:

SET GENERATOR MY_GEN_ID TO 1; 

Where 1 is the chosen restart value.
NOTE that it will be the current value so your trigger will start with 2, you might want to set it to 0 instead of 1.

Addendum 2:
Firebird 2 introduced a new (and better) synthax fully documented in the Firebird Generator Guide.

Addendum 3:
Firebird 3.0, thanks to Adriano dos Santos Fernandes
will support IDENTITY columns, so no need to use this trick anymore.

Sunday, December 10, 2006

An Opensource approach to engineering

I love this guys!



Image extracted with GIMP and it's excellent SIOX tool

Sunday, November 26, 2006

How to use BLOB field in VB 6 with Firebird

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.
Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.
You'll notice that basically the only different thing is the connection string!

  1. 'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
  2. Dim conn As ADODB.Connection
  3. Set conn = New ADODB.Connection
  4. conn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_2_0\examples\empbuild\test.fdb;UID=SYSDBA;PW D=masterkey"
  5. conn.CursorLocation = adUseClient
  6. conn.Open
  7. 'OPEN RECORDSET FOR WRITING
  8. Dim rs As ADODB.Recordset
  9. Set rs = New ADODB.Recordset
  10. Dim mystream As ADODB.Stream
  11. Set mystream = New ADODB.Stream
  12. mystream.Type = adTypeBinary
  13. rs.Open "SELECT * FROM files WHERE 1=0", conn, adOpenStatic, adLockOptimistic
  14. rs.AddNew
  15. mystream.Open
  16. mystream.LoadFromFile "c:\adaptor.jpg"
  17. rs!file_id = 1
  18. rs!file_name = "adaptor.jpg"
  19. rs!file_size = mystream.Size
  20. rs!file_blob = mystream.Read
  21. rs.Update
  22. mystream.Close
  23. rs.Close
  24. 'OPEN RECORDSET TO READ BLOB
  25. rs.Open "Select * from files WHERE files.file_id = 1", conn
  26. mystream.Open
  27. mystream.Write rs!file_blob
  28. mystream.SaveToFile "c:\newimage.jpg", adSaveCreateOverWrite
  29. mystream.Close
  30. rs.Close
  31. 'OPEN RECORDSET FOR UPDATE OF BLOB COLUMN
  32. rs.Open "Select * from files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic
  33. mystream.Open
  34. mystream.LoadFromFile "c:\adaptor.jpg"
  35. rs!file_blob = mystream.Read
  36. rs.Update
  37. mystream.Close
  38. rs.Close
  39. 'OPEN RECORDSET TO READ UPDATED IMAGE
  40. rs.Open "Select * from files WHERE files.file_id = 1", conn
  41. mystream.Open
  42. mystream.Write rs!file_blob
  43. mystream.SaveToFile "c:\newupdatedimage.jpg", adSaveCreateOverWrite
  44. mystream.Close
  45. rs.Close
  46. conn.Close
  47. MsgBox "Success! Check your C:\ directory for newimage.jpg and newupdatedimage.jpg"

Wednesday, November 08, 2006

Custom ordering for your results

... or, taming the ORDER BY clause.
Say you want to implement a custom ordering for your queries, as an example, you want to display each customer's orders with shipped orders first, then waiting orders and open orders last.
That's ordering by the 'status' column and you can't use alphabetical ordering!
In this case you'll have to implement some kind of logic in your order by clause, and a CASE is very handy, like this:

  1. SELECT * FROM SALES
  2. ORDER BY
  3. cust_no,
  4. CASE WHEN order_status = 'shipped' THEN 1
  5. WHEN order_status = 'waiting' THEN 2
  6. WHEN order_status = 'open' THEN 3
  7. ELSE 4
  8. END;

Note how the ordering is performed on the values substituted by the CASE statement and not
for the original column values.
This example is based on Firebird's EXAMPLE.FDB database.

The MySQL guys could also use a trick like the FIELD function

... ORDER BY FIELD(order_status,'shipped','waiting','open')

but this is a NON standard way of doing things that I don't recommend at all.

Thursday, November 02, 2006

Of correlated and uncorrelated subqueries

Many SQL questions on Devshed forums are easily solved using correlated and uncorrelated subqueries, but many don't know of them at all, or at least are not shure about the syntax, usage and performance implications, they sometimes rave about a stored procedure to loop and select other values ...
The definition (taken from O'Reilly's "Learning SQL on SQL Server 2005") of a correlated subquery:
A correlated subquery is an inner subquery whose information is referenced by the main, outer query such that the inner query may be thought of as being executed repeatedly.
And the definition of an uncorrelated subquery:
A noncorrelated subquery is a subquery that is independent of the outer query. In other words, the subquery could be executed on its own.
This is all fine and dandy, but what's the use?
I'll put up two simple examples of usage, which should make clear which kind of questions can be answered with (un)correlated subqueries:

Example 1: get the number of "open" orders, the number of "waiting" orders and the open vs. waiting ratio

  1. SELECT
  2. COUNT(*) AS o_open,
  3. (SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS o_waiting,
  4. (COUNT(*))/
  5. (SELECT COUNT(*) FROM sales WHERE order_status = 'waiting') AS open_waiting_ratio
  6. FROM SALES WHERE order_status = 'open';

You can see that the queries are uncorrelated, and they better be, as the where conditions are different.

Example 2: we want to see the customer number, the amount of the customer's last order and the total amount ordered by each customer

  1. SELECT
  2. s.cust_no,
  3. s.total_value AS order_value,
  4. (SELECT SUM(i_s.total_value) FROM sales i_s WHERE i_s.cust_no = s.cust_no) total_ordered
  5. FROM sales s
  6. WHERE
  7. s.order_date =
  8. (SELECT MAX(ii_s.order_date) FROM sales ii_s WHERE ii_s.cust_no = s.cust_no);

You can see that both subqueries are correlated, the first one gets total amount ordered for each customer, the second one gets the last order date (field is datetime) and helps determine the last order in the main query.

This should make you aware that some kind of set based logic is often disguised as procedural and usually leads to ugly cursor usage in stored procedures instead of direct queries by the inexperienced.

Another example of a correlated subquery to display customers and one randomly selected purchase order for each of them:

  1. SELECT
  2. a.CUST_NO,
  3. a.CUSTOMER,
  4. b.PO_NUMBER
  5. FROM
  6. CUSTOMER a
  7. INNER JOIN
  8. sales b
  9. ON a.CUST_NO = b.CUST_NO
  10. WHERE
  11. b.PO_NUMBER =
  12. (
  13. SELECT
  14. first 1 c.PO_NUMBER
  15. FROM
  16. sales c
  17. WHERE
  18. c.CUST_NO = b.CUST_NO
  19. ORDER BY
  20. rand()
  21. )


Both examples are run against Firebird's standard EMPLOYEE.FDB.


Correlated subqueries are also useful to specify updates and deletes on a table conditioned to values of another table (instead of joining them) see this example:

  1. UPDATE
  2. stops s
  3. SET
  4. s.inservice=1
  5. WHERE EXISTS
  6. (
  7. SELECT
  8. ls.stopid
  9. FROM
  10. linestop ls
  11. WHERE
  12. s.stopid=ls.stopid
  13. AND
  14. ls.signid=79
  15. );

Saturday, October 28, 2006

Firebird's explain PLAN

After a post on Devshed forums I'm putting here an excerpt from a little tutorial about Firebird I wrote some time ago, this is about the explain plan of a query:

More on speeding … the Explain plan

An extremely useful feature of relational databases is the “explain plan”,which
shows us how the database engine is going to execute a query. It allows the DBA to
check if indexes are used and how tables are queried. One of the most effective ways of
using it is to use the Firebird option “PLANONLY” which means that the query to be
analyzed is submitted to the server and the plan retrieved, but the query is not executed!

Note that this is also an effective way of checking the SQL syntax of a statement, much
like Oracle’s parse statement.

The planonly option can be set to ON or OFF at your option, remember to turn it
off to obtain results for your queries.

The plan is accessed through ISQL this way:

>isql
SQL> CONNECT “C:\firedata\first_db.fdb” user ‘SYSDBA’ password ‘guess_me’;

SQL> SET PLANONLY ON;
SQL> SELECT a.emp_no, b.currency from employee a inner join country b on
a.job_country = b.country;

After submitting the query we retrieve the plan, which shows usage of the primary key of
table country:

PLAN JOIN (A NATURAL,B INDEX (RDB$PRIMARY1))
SQL> SET PLANONLY OFF;

This way we can check the execution of more complex queries and find areas of
improvement

You can see an example of it's usage here.

Sunday, October 22, 2006

Audacity 1.3.2 is coming










See release notes for Audacity 1.3.2

Handy MySQL function

Here is a simple function that can help coping with wrong database design, when you have a mix of NULLs and blank fields meaning the same, no value, which they shouldn't.

  1. DELIMITER $$
  2. DROP FUNCTION IF EXISTS `test`.`is_initial` $$
  3. CREATE FUNCTION `test`.`is_initial` (f varchar(255)) RETURNS BOOL
  4. BEGIN
  5. SET @is_initial = false;
  6. CASE f
  7. WHEN NULL THEN SET @is_initial = TRUE;
  8. WHEN '' THEN SET @is_initial = TRUE;
  9. ELSE SET @is_initial = FALSE;
  10. END CASE;
  11. RETURN @is_initial;
  12. END $$
  13. DELIMITER ;

Saturday, September 16, 2006

Something you'll need for shure in Poland

Well, if you don't want your laptop to lie dead soon the first thing to buy when in Poland is an adaptor like this from DPM Elektronik.

Tuesday, September 05, 2006

Php and SOAP, getting to the corporate business intelligence repository

What follows is a snippet taken from a very old experiment I did, integrating a php application with BusinessObject's WebIntelligence 2.7.x through it's (very experimental) webservices toolkit, which was really promising but Java and .NET oriented ;)


  1. // include the class
  2. include("nusoap.php");
  3. // create a instance of the SOAP client object
  4. // remember that this script is the client,
  5. // accessing the web service provided by BusinessObjects
  6. $soapclient = new soapclient("http://192.168.1.99:8080/wiws/services/wiws.businessobjects.com");
  7. // uncomment the next line to see debug messages
  8. // $soapclient->debug_flag = 1;
  9. // set up an array containing input parameters to be
  10. // passed to the remote procedure
  11. $params = array(
  12. 'userName' => 'Administrator', // username
  13. 'password' => '', // password
  14. 'deltaUTC' => 0, // utc delta
  15. );
  16. // invoke the method on the server
  17. $result = $soapclient->call("login", $params);
  18. // print the session ID
  19. print_r($result);
  20. // show corporate documents
  21. //$params3 = array(
  22. // 'session' => $result,
  23. // 'listType' => 'corporate',
  24. // 'category' => '',
  25. // 'sortOn' => 0,
  26. // 'refresh' => 1,
  27. //);
  28. //$corporate = $soapclient->call("getDocumentList", $params3);
  29. // if (is_array($corporate['resultElements']))
  30. // {
  31. // foreach ($corporate['resultElements'] as $r)
  32. // {
  33. // echo "
  34. " .
  • // echo "
    ";
  • // echo $r['snippet'] . "(" .
  • //$r['cachedSize'] . ")";
  • // echo "

    ";

  • // }
  • // }
  • // logout
  • $params2 = array(
  • 'session' => $result, // sessionid
  • );
  • // invoke the method on the server
  • $result2 = $soapclient->call("logout", $params2);
  • // print the results of the search
  • print_r($result2);
  • ?>

  • Php integration was done through nuSOAP.

    I know that it's old and not really about a widespread subject, but it was really interesting and somehow fun to build single sign on between WebI and phpBB :-D

    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:

    1. DELIMITER $$
    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 $$
    8. DELIMITER ;

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

    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:

    PLAN (EMPLOYEE ORDER IDX3)

    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:

    PLAN SORT ((EMPLOYEE NATURAL))

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

    PLAN (EMPLOYEE ORDER RDB$FOREIGN8)

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

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