strange TUniTable UPDATE statement

[acastiello]

I´m getting really annoyed with UniDAC strange behaviour when generating UPDATE statements. I guess a table should be updated using only primary keys (that´s one of their basic uses) unless instructed to go other way (with an UpdateObject or so) and the option to include all fields in the update or using only the PK should be available as it was in the BDE had with the UpdateMode property. I have the following table (FB 2.5 Unidac 3.6)

CREATE DOMAIN FACTURA_DEF AS INTEGER;
CREATE DOMAIN CLIENTE_DEF AS INTEGER;
CREATE DOMAIN REGISTRO_DEF AS
CHAR(1) CHARACTER SET NONE
CHECK (value in ('R','A','C',null))
COLLATE NONE;
CREATE DOMAIN BOOLEAN AS
CHAR(1) CHARACTER SET NONE
CHECK (VALUE IN ('T','F'))
COLLATE NONE;
CREATE DOMAIN FOLIO_DEF AS BIGINT CHECK (value > 0);
CREATE DOMAIN SERIE_DEF AS
VARCHAR(10) CHARACTER SET NONE
COLLATE NONE;


CREATE TABLE FACTURAS (
FACTURA_ID FACTURA_DEF NOT NULL,
FACTURA_FECHA TIMESTAMP NOT NULL,
FACTURA_FECHA_VENCIMIENTO TIMESTAMP NOT NULL,
FACTURA_CLIENTE_ID CLIENTE_DEF NOT NULL,
FACTURA_DESCUENTO SMALLINT,
FACTURA_ESTADO REGISTRO_DEF DEFAULT 'R' NOT NULL,
FACTURA_DIRECTA BOOLEAN default 'F',
FACTURA_IVA DOUBLE PRECISION,
FACTURA_MONTO NUMERIC(10,2),
FACTURA_CFD_FOLIO FOLIO_DEF,
FACTURA_CFD_SERIE SERIE_DEF
);

FACTURA_ID is the PK and CFD_FOLIO and CFD_SERIE are both a single (unique) FK to a CFDS table

ALTER TABLE FACTURAS ADD CONSTRAINT FACTURA_FOLIO_SERIE_UNQ UNIQUE (FACTURA_CFD_FOLIO, FACTURA_CFD_SERIE);
ALTER TABLE FACTURAS ADD CONSTRAINT FACTURA_CFD_REF FOREIGN KEY (FACTURA_CFD_FOLIO, FACTURA_CFD_SERIE) REFERENCES CFDS (CFD_FOLIO, CFD_SERIE);

I´m using a TUniTable (can´t change to query) that other than TableName property has all the default values. Two strange things are happening:


a) When the user changes a record that has FACTURA_IVA and FACTURA_MONTO with non null values the TUniTable executes the (optimistic) lock:


SELECT NULL FROM FACTURAS
WHERE
FACTURA_ID = ? AND FACTURA_FECHA = ? AND FACTURA_FECHA_VENCIMIENTO = ? AND FACTURA_CLIENTE_ID = ? AND FACTURA_DESCUENTO = ? AND FACTURA_ESTADO = ? AND FACTURA_DIRECTA = ? AND FACTURA_IVA = ? AND FACTURA_MONTO = ? AND FACTURA_CFD_FOLIO IS NULL AND FACTURA_CFD_SERIE IS NULL
FOR UPDATE WITH LOCK


:Old_FACTURA_ID(Integer,IN)=83669
:Old_FACTURA_FECHA(DateTime,IN)=03/09/2010
:Old_FACTURA_FECHA_VENCIMIENTO(DateTime,IN)=03/09/2010
:Old_FACTURA_CLIENTE_ID(Integer,IN)=487
:Old_FACTURA_DESCUENTO(SmallInt,IN)=9
:Old_FACTURA_ESTADO(String[1],IN)='A'
:Old_FACTURA_DIRECTA(String[1],IN)='F'
:Old_FACTURA_IVA(Float,IN)=143.16
:Old_FACTURA_MONTO(Float,IN)=894.76

when both columns are null, the TniTable makes no lock and goes directly to the (erroneous) UPDATE which is

b) UPDATE FACTURAS
SET
FACTURA_IVA = ?
WHERE
FACTURA_CFD_FOLIO IS NULL AND FACTURA_CFD_SERIE IS NULL

why the TniTable wants to UPDATE via a foreign key instead of the PK? All other tables correctly update using such PK

The above update is generated in either situation about the FACTURA_IVA and FACTURA_MONTO columns

My guess is that TUnitable is confusing the unique constraint with the PK but in other similar tables in this database, I´m having no such problem

I again insist and ask for a property like UpdateMode in the TUniDatasets or at least, in the TUniTable, but in the mean time, what can be done?. Help please

Regards,
Alvaro Castiello

[AndreyZ]

Hello,

1) I have checked the locking. TUniTable locks tables correctly. After locking I couldn't change any field of the FACTURAS table in another application (I have used IBExpert). For locking TUniTable uses all fields, and this is a correct behaviour that allows avoiding data loss.

2) You can solve this problem by setting the KeyFields property to FACTURA_ID. We will investigate this question. As soon as we get any results, we will let you know.

[acastiello]

Hi Andrey

1) I have checked the locking. TUniTable locks tables correctly. After locking I couldn't change any field of the FACTURAS table in another application (I have used IBExpert). For locking TUniTable uses all fields, and this is a correct behaviour that allows avoiding data loss.

The strange fact is not the fields included, that is ok. The weird fact is that sometimes it does the lock and some others it doesn´t. It seems such bahaviour is based on FACTURA_IVA and FACTURA_MONTO being both null or not. During the life of the record, these columns can have these values:

• FACTURA_IVA FACTURA_MONTO
  null null new created record
  not null null created record in which the user directly entered the value to the override the system behaviour (this is the one I´m interested now)
  * not null processed record despite the value of FACTURA_IVA (Although after processing FACTURA_IVA will always be not null). A trigger avoids modifying or deleting such record

2) You can solve this problem by setting the KeyFields property to FACTURA_ID. We will investigate this question. As soon as we get any results, we will let you know.

That made it, thank you

[AndreyZ]

I cannot reproduce the problem with locking. Please try composing a small sample to demonstrate the problem and send it to andreyz*devart*com.