[firebird-support] INSERT ... RETURNING and updatable view

[Emil Totev]

Hi

Server Version: WI-V2.5.2.26539 Firebird 2.5 on Win7.

Here is my simplified test case:

SQL Create Database 'test.fdb' user 'sysdba' password 'masterkey';
SQL
SQL create table T (ID integer);
SQL
SQL create view V as select ID from T;
SQL
SQL create sequence SEQ;
SQL
SQL set term ^;
SQL
SQL create trigger T_BI for T before insert as
CON begin
CON   new.ID = next value for SEQ;
CON end^
SQL
SQL create trigger V_BI for V before insert as
CON begin
CON   insert into T values (new.ID);
CON end^
SQL
SQL set term ; ^
SQL
SQL insert into T values (9) returning ID;

  ID

   1

SQL
SQL insert into V values (101) returning ID;

  ID

 101

SQL select * from V;

  ID

   1
   2

SQL

Would you say it is a bug, or just counter-intuitive?

Thanks
Emil


[Non-text portions of this message have been removed]

[firebird-support] Re: INSERT ... RETURNING and updatable view

[ettotev]

--- In firebird-support@yahoogroups.com, Paul Vinkenoog  wrote:

 
 It is often more useful to define the BI trigger as:
 
   create trigger T_BI for T before insert as
   begin
 if (new.ID is null) then new.ID = next value for SEQ;
   end^
 
 (but if you had done that, you wouldn't have detected the inconsistency below 
 ;-))

Well, I've seen that but I don't like the idea. An auto-generated ID should be 
auto-generated in all cases. Imagine for example someone inserting a random 
higher value that will one day come into conflict with the sequence-generated...


 
   insert into T values (new.ID) returning T.id into :new.id;
 
 That would solve the whole problem (he said without having tested it).

Yes, that works, but I consider it a workaround :)
The same thing happens for calculated and DEFAULT fields as well.

Thanks
Emil

[firebird-support] server version

[hugo.larson]

Hello,

Is there a way to determine which version of Firebird is running before 
connection to a Database?

I'm looking in org.firebirdsql.management but cant find anything fitting.

Thanks,
Hugo

Re: [firebird-support] server version

[Paul Vinkenoog]

Hello Hugo,

 Is there a way to determine which version of Firebird is running before 
 connection to a Database?

From within your application, you can connect to the Services Manager and then 
call isc_service_query(), specifying isc_info_svc_server_version in the 
request buffer. This is documented in the IB6 API Guide.

Many clients (e.g. FlameRobin) can also give you this information without 
having to connect to a database first.

Or, from the OS command prompt:

 fbsvcmgr [hostname:]service_mgr -user username -password password 
 -info_server_version

This requires Firebird 2.1 or higher on the client machine.


Hope this helps,
Paul Vinkenoog