I use an IB_Query to insert and update some fields
from a table and i customized the insertsql(and editsql) in order
to set some fields "noneditable/noninsertable".

I use Firebird WI-V6.2.794 Firebird 1.0 and IBO4 4.2.Gc

I'll present the facts first.
Table FACT_IMP is created with this script.
/* Begin script*/
create domain D_ID numeric(18) default 0 check(Value > 0) not null;
create domain D_ORD_NR integer default 0 check(Value>=0) not null;
create domain D_Intreg integer default 0 not null;
create domain D_Data_F date not null;
create domain D_String60 VarChar(60);
create domain D_Pret Numeric(18,2) default 0 not null;
create domain D_Boolean Char(1) default '0'// 1 = True; 0 = False
Check ((Value='1') or (Value='0')) not null;


/* Facturi de import */
create table Fact_imp (Id D_ID primary key,
Nr_fact D_ORD_NR,
Data_fact D_Data_F,
Year_Fact D_Intreg, // Auto. Updated by Self
Lista_Fact D_String60,
Nr_DVOT D_ORD_NR,
Data_DVOT D_Data_F,
Year_DVOT D_Intreg, // Auto. Updated by Self
Nr_TE D_ORD_NR, // Manual. DVE Italy
Data_TE Date,
Year_TE D_Intreg, // Auto. Updated by Self
Termen D_Intreg,
Curs D_Pret,
Nr_Contract D_ORD_NR,
Orig_Rom D_Boolean, // Manual. Originara Romania
Val_IN D_Pret, // Auto. Updated by elem_imp
Val_OUT D_Pret); // Auto. Updated by elem_imp
create generator gen_fact_imp_id;

set term ``;
create trigger tr_Fact_imp_bu for Fact_imp
before update
as
begin
new.Year_fact=Extract(Year from new.Data_fact);
new.Year_DVOT=Extract(Year from new.Data_DVOT);
new.Year_TE=Extract(Year from new.Data_TE);
end``
create trigger tr_Fact_imp_bi for Fact_imp
before insert
as
begin
new.val_in=0;
new.val_out=0;
new.Year_fact=Extract(Year from new.Data_fact);
new.Year_DVOT=Extract(Year from new.Data_DVOT);
new.Year_TE=Extract(Year from new.Data_TE);
end``
set term ;``
/* End script*/

IB_Query:

The sql is like this:

SELECT *
FROM FACT_IMP
where id=:myid

I use the generatorlink with
id=GEN_FACT_IMP_ID
to get the next id

InsertSQL:

INSERT INTO FACT_IMP(
ID, /*PK*/
LISTA_FACT,
NR_FACT,
DATA_DVOT,
DATA_FACT,
DATA_TE,
TERMEN,
CURS,
ORIG_ROM)
VALUES (
:ID,
:LISTA_FACT,
:NR_FACT,
:DATA_FACT,
:DATA_FACT,// Here appears to start the problem (it is set to 0)
:DATA_FACT,// This time it is null and gives a problem for before insert
trigger
:TERMEN,
:CURS,
:ORIG_ROM)

The monitor shows on prepare
/*---
PREPARE STATEMENT
TR_HANDLE = 98241200
STMT_HANDLE = 98241272
SELECT *
FROM FACT_IMP
where id=? /* myid */
PLAN (FACT_IMP INDEX (RDB$PRIMARY13))
FIELDS = [ Version 1 SQLd 18 SQLn 30
FACT_IMP.ID = <NIL>
FACT_IMP.NR_FACT[NR_FACT] = <NIL>
FACT_IMP.DATA_FACT[DATA_FACT] = <NIL>
FACT_IMP.YEAR_FACT[YEAR_FACT] = <NIL>
FACT_IMP.LISTA_FACT[LISTA_FACT] = <NIL>
FACT_IMP.NR_DVOT = <NIL>
FACT_IMP.DATA_DVOT = <NIL>
FACT_IMP.YEAR_DVOT = <NIL>
FACT_IMP.NR_TE = <NIL>
FACT_IMP.DATA_TE = <NIL>
FACT_IMP.YEAR_TE = <NIL>
FACT_IMP.ID_CLIENT = <NIL>
FACT_IMP.TERMEN = <NIL>
FACT_IMP.CURS = <NIL>
FACT_IMP.NR_CONTRACT = <NIL>
FACT_IMP.ORIG_ROM = <NIL>
FACT_IMP.VAL_IN = <NIL>
FACT_IMP.VAL_OUT = <NIL> ]
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 98241200
STMT_HANDLE = 98241404
INSERT INTO FACT_IMP(
ID, /*PK*/
LISTA_FACT,
NR_FACT,
DATA_DVOT,
DATA_FACT,
DATA_TE,
ID_CLIENT,
TERMEN,
CURS,
ORIG_ROM)
VALUES (
? /* ID */ ,
? /* LISTA_FACT */ ,
? /* NR_FACT */ ,
? /* DATA_FACT */ ,
? /* DATA_FACT */ ,
? /* DATA_FACT */ ,
? /* ID_CLIENT */ ,
? /* TERMEN */ ,
? /* CURS */ ,
? /* ORIG_ROM */ )
FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
SECONDS = 0.010
----*/

This far all looks ok to me, and i do insert:

/*---
PREPARE STATEMENT
TR_HANDLE = 98241200
STMT_HANDLE = 98241008
SELECT GEN_ID ( GEN_FACT_IMP_ID, 1 )
FROM RDB$DATABASE
PLAN (RDB$DATABASE NATURAL)
FIELDS = [ Version 1 SQLd 1 SQLn 1
GEN_ID = 0 ]
----*/
/*---
EXECUTE2 DSQL
TR_HANDLE = 98241200
STMT_HANDLE = 98241008
PARAMS = [ ]
FIELDS = [ Version 1 SQLd 1 SQLn 1
GEN_ID = 19 ]
SELECT COUNT: 1
----*/

Still working good. Now i post and the params become useless:

/*---
EXECUTE STATEMENT
TR_HANDLE = 98241200
STMT_HANDLE = 98241404
PARAMS = [ Version 1 SQLd 10 SQLn 10
FACT_IMP.ID = 19
FACT_IMP.LISTA_FACT[LISTA_FACT] = '0'
FACT_IMP.NR_FACT[NR_FACT] = 1

_Here_is_shown_the_problem_

FACT_IMP.DATA_FACT[DATA_FACT] = '08 Apr 2002'
[DATA_FACT] = <n> '17 Nov 1858'
// It posts 0 for the second data_fact
[DATA_FACT] = <NULL>
and null for the third !!! Why this behaviour?



FACT_IMP.ID_CLIENT = 5
FACT_IMP.TERMEN = 0
FACT_IMP.CURS = 0
FACT_IMP.ORIG_ROM = '1' ]
ERRCODE = 335544347
----*/
/*---
INTERPRETE BUFFER =
ERRCODE = 57
----*/
/*---
INTERPRETE BUFFER = validation error for column YEAR_TE, value "*** null
***"
ERRCODE = -1
----*/

I tried the same insert with DSQL and it works perfectly
but the monitor output is very diferent:

/*---
EXECUTE STATEMENT
TR_HANDLE = 10655456
STMT_HANDLE = 10655528
PARAMS = [ Version 1 SQLd 10 SQLn 10
[ID] = 10001
[LISTA_FACT] = '0'
[NR_FACT] = 1
[DATA_FACT] = '08 Apr 2002'
[DATA_FACT] = '08 Apr 2002'
[DATA_FACT] = '08 Apr 2002'
[ID_CLIENT] = 6
[TERMEN] = 0
[CURS] = 0
[ORIG_ROM] = '1' ]
INSERT COUNT: 1
----*/

All data_fact have the same value as expected!

Can you help me trace this in IBO or i should not do such
insertsqls(because they're wrong)?

--
Best regards,
Application Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@...