Firebird Database Use Experience Summary

Transferred from: https://www.oschina.net/question/54100_8615

Recently in rewriting a section of MS SQL stored procedures to Firebird, summed up some experience, Firebird can be said to be the world's smallest supporting stored procedures database, only 2.3M. If you do a small application, there are more than 70 m (+SP3) in the MS SQL Desktop version, and MySQL has 20-30m, or more appropriate.

1. Custom function issues. Access to UDF Library "Rfunc.dll" are denied by server administrator

Took a long time, do not understand why, will rfunc.dll copy to the UDF directory, bin directory, Windows\System32 directory can not solve the problem, Google, the same problem on the Internet, but no solution, the result I re-installed a bit of Firebird on the solution.

2. Definition of variables in stored procedures

A new variable can be defined anywhere in the MS SQL stored procedure, but in Firebird, it can only be defined between as and begin. The Firebird variable cannot be used with the @ symbol, I will be all right.

3. Use of variables in stored procedures

1. Select @i=Count (*) from table1//ms SQL
2. Select Count (*) from table1 into:i//firebird reference variable with colon

4. Embedded SQL,

Firebird support for SELECT * FROM table exists (select ...) or select * FROM table in (select ...)
But does not support select * FROM (SELECT * from ...) A,
I'm going to rewrite this type of view to solve

5.case statements

MSSQL can use field= case (), but Firebbird only supports case () as field

6.mssql getdate () becomes current_date+current_time

1. SELECT * from snartleave where dt_starttime<current_date+current_time

Or

1. SELECT * from snartleave where dt_starttime<current_date| | "| | current_time

7. Return the stored procedure notation for the dataset (Firebird is a bit cumbersome)

1. CREATE PROCEDURE SPVARTST2 (
2. Var_iptartno CHAR (6))
3. RETURNS (
4. Var_artname CHAR (Ten))
5. As
6. Begin
7. For Select v_name1 from snart where V_artno=:var_iptartno into:var_artname do
8. Suspend
9. End

8. Use of autogrow fields (AutoIncrement)

Firebird has a generator (generator) of the east, in the generator record value growth,
And then use the trigger to implement

1. Begin
2. if (new.i_seqno is null) Then
3. begin
4. New.i_seqno = gen_id (gen_t_db_artseqno_id,1);
5. End
6. End

GEN_T_DB_ARTSEQNO_ID is the creation of the generator, see no, that is, different tables different fields can share a generator, gen_id equivalent identity, looks more complex than MSSQL, in fact, is also very simple.

9. Generator reset the automatically added fields in MSSQL to reset it seems to be cumbersome, difficult to control,

Firebird can do this (stored procedure)

1. AGENERATOR=GEN_ID (gen_t_db_artseqno_id,gen_id (gen_t_db_artseqno_id,0) *-1+1);

Agenerator is an shaping variable, it seems to be necessary to install the GEN_ID value to a variable, do not know if there is no better way, do not define an extra variable
Refer to the following URL: http://www.fingerbird.de/generatorguide_body.htm

10 changing data for another table through one table

1. MSSQL:update table1 set cname=b.cname from table1 a inner join table2 as b where a.id=b.id
2. Firebird:update table1 a set cname= (Select CNAME from table 2 b where b where a.id=b.id)

11. How to select the first few records

1. Mssql:select Top * FROM table1
2. Firebird:select First * FROM table1
3. Ib:select * FROM table Rows 10

Transactions in the 12.Firebird stored procedure

In a stored procedure IB/FB does not support opening transactions or ending transactions. Commits are submitted by the caller. That is, fb/ib stored procedures should be designed in one transaction.
In SQL Server, a stored procedure or to open such a transaction:

1. Begin Trancstion
2. Commit Trancstion

But there is no such thing in Fb/ib, it is necessary to open a transaction in the program of the tune, such as:

1. Pfibdtbsmain.starttransaction;
2. Pfibdtbsmain. Commit;

How to prevent the proliferation of null values in Firebird

Originating From: http://dev.csdn.net/article/72/72836.shtm
In the statistics, calculation, merging data, the problem of empty diffusion, sometimes inadvertently emerge, accidentally, will lead to error.
Let me give you an example.
There is a score table achieve, there are four fields: Yuwen, Shuxue, Yingyu, Total, respectively, for the storage of Chinese, maths, English and scores. Now to calculate the student's total score, now write the following SQL statement:

1. Update achieve set Total=yuwen+shuxue+yingyu

This sentence seems to be correct, but sometimes can not produce useful results, for example, there is a student language 80, Math 90, English lack of test, did not fill the score, then the value of the Yingyu field is probably not 0 but null, empty value, to see what the design of the person is thinking. If the value of the Yingyu is null, then, guess what, the total field totals is calculated?
According to the general idea, of course, is 80+90, the student's total score is 170, the English field does not participate in the calculation. But, this is wrong!
As a practical calculation, the student's total score is empty (null).
In a SQL calculation expression, if there is a value of NULL, then the result must be empty! This is the empty value diffusion!!!
So, how to prevent the occurrence of null-value diffusion?
In this regard, SQL Server provides the IsNull () function, and Access provides the NZ () function, which can solve this problem by providing a judgment function: if empty, use a value instead, such as 0 or an empty string. Does Firebird have a similar function?
Yes, that's the function coalesce ().
Simply change the SQL statement above to the following.

1. Update achieve set total=COALESCE (yuwen,0) +COALESCE (shuxue,0) +COALESCE (yingyu,0)

It seems to be a bit cumbersome to write, but it's a safe code.

Firebird Database Use Experience Summary