Subject | Re: [IBO] Re[2]: IBO 4.2Ib violation of Fk... |
---|---|
Author | Eduardo Jedliczka |
Post date | 2003-03-17T20:23:36Z |
Helen, and other member list, I found an answer for my problem.
It´s very easy. Only Clear the InsertSQL, UpdateSql and DeleteSql and set
the Name of MasterTable in KeyRelation, and set RequestLive true.
Only It... ( I think is a good idea, the ibo team try the upper situation...
and do not able any person use the option generate for table if the select
have one join).
Only to memory refresh, the error do when try to insert a record in a table,
close this table, insert data in other table, and try a second time insert
data in the first table...
A last think: Why the propert KeyRelation have this name ???? I think have
much others signficative names....
[]s
=======================
Eduardo Jedliczka
Gerasoft - Informática
Apucarana - PR - Brasil
=======================
It´s very easy. Only Clear the InsertSQL, UpdateSql and DeleteSql and set
the Name of MasterTable in KeyRelation, and set RequestLive true.
Only It... ( I think is a good idea, the ibo team try the upper situation...
and do not able any person use the option generate for table if the select
have one join).
Only to memory refresh, the error do when try to insert a record in a table,
close this table, insert data in other table, and try a second time insert
data in the first table...
A last think: Why the propert KeyRelation have this name ???? I think have
much others signficative names....
[]s
=======================
Eduardo Jedliczka
Gerasoft - Informática
Apucarana - PR - Brasil
=======================
----- Original Message -----
From: "Eduardo Jedliczka" <eduardo@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, March 13, 2003 9:06 AM
Subject: [IBO] Re[2]: IBO 4.2Ib violation of Fk...
> Delphi 6 SP2, IBO 4.2Ib, FB 1.0.821... ( long e-mail )
>
> First off all, Helen thanks to answer!
> HB> FK violations occur when you try to write a value to the foreign
> HB> key column, that does not exist in the master table's primary key.
> HB> It is fairly common for such FK violations to occur where the PK
> HB> and the FK are of a non-precise data type, or include columns
> HB> of non-precise data type. Float, double precision and timestamp
> HB> are non-precise.
>
> Ok. I know this, but when all the Primary key columns are Integer ( or
> ShortInt ) ???
> And I not have any problem (except this) with PK´s or FK´s.
> I made the home lesson, and read all Firebird PDF´s.
>
> HB> Since this appears to be a metadata or casting problem, we need to
> HB> see the metadata of the tables which your queries access.
>
> Ok. I put here the metadata... Obvious have others domains, but are all
> char_XXX and VarChar_XXX !
>
> SET SQL DIALECT 3;
>
> SET NAMES WIN1252;
>
> CREATE DATABASE 'C:\Arquivos de programas\GeraSoft\Dados\gerasoft.fb'
> USER 'SYSDBA' PASSWORD 'masterkey'
> PAGE_SIZE 4096
> DEFAULT CHARACTER SET WIN1252;
>
> CREATE DOMAIN BOLEANO AS CHAR(1) DEFAULT 'N' NOT NULL CHECK ((VALUE
> IN('S','N'))) COLLATE PXW_INTL850;
> CREATE DOMAIN D_C AS CHAR(1) DEFAULT 'D' NOT NULL CHECK ((VALUE
> IN('D','C'))) COLLATE PXW_INTL850;
> CREATE DOMAIN PJ_PF AS CHAR(1) DEFAULT 'J' NOT NULL CHECK ((VALUE
> IN('J','F'))) COLLATE PXW_INTL850;
> CREATE DOMAIN SEXO AS CHAR(1) DEFAULT 'M' NOT NULL CHECK ((VALUE
> IN('M','F'))) COLLATE PXW_INTL850;
>
> CREATE DOMAIN CURRENCY AS NUMERIC(15,2) DEFAULT 0;
> CREATE DOMAIN DATA AS DATE;
> CREATE DOMAIN INTEIRO AS INTEGER;
> CREATE DOMAIN MEMO AS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
> CREATE DOMAIN NUMQTDE AS NUMERIC(15,3);
> CREATE DOMAIN PERCENT AS NUMERIC(6,2);
> CREATE DOMAIN SMALLINTEIRO AS SMALLINT;
>
> CREATE TABLE CA_CTA (
> CODEMPRESA SMALLINTEIRO NOT NULL,
> CODCONTA SMALLINTEIRO NOT NULL,
> NOME VARCHAR_60 NOT NULL COLLATE PXW_INTL850,
> MENSAL BOLEANO
> );
>
> CREATE TABLE CA_HIS (
> CODEMPRESA SMALLINTEIRO NOT NULL,
> CODHISTORICO SMALLINTEIRO NOT NULL,
> NOME VARCHAR_60 NOT NULL,
> DEBITO SMALLINTEIRO NOT NULL,
> CREDITO SMALLINTEIRO NOT NULL
> );
>
> CREATE TABLE CA_LCT (
> CODEMPRESA SMALLINTEIRO NOT NULL,
> CODFILIAL SMALLINTEIRO NOT NULL,
> DATA DATA NOT NULL,
> NUMLANC INTEIRO NOT NULL,
> CODHISTORICO SMALLINTEIRO NOT NULL,
> VALOR CURRENCY NOT NULL,
> COMPLEMENTO MEMO,
> CODUSUARIO SMALLINTEIRO NOT NULL
> );
>
> CREATE TABLE CA_SCT (
> CODEMPRESA SMALLINTEIRO NOT NULL,
> CODFILIAL SMALLINTEIRO NOT NULL,
> CODCONTA SMALLINTEIRO NOT NULL,
> SALDOINICIAL CURRENCY,
> TIPOSALDO D_C
> );
>
> CREATE TABLE GE_FIL (
> CODEMPRESA SMALLINTEIRO NOT NULL,
> CODFILIAL SMALLINTEIRO NOT NULL,
> FANTASIA VARCHAR_30 NOT NULL COLLATE PXW_INTL850,
> CNPJ CHAR_18 COLLATE PXW_INTL850
> );
>
> ALTER TABLE CA_CTA ADD CONSTRAINT PK_CA_CTA PRIMARY KEY (CODCONTA,
> CODEMPRESA);
> ALTER TABLE CA_HIS ADD CONSTRAINT PK_CA_HIS PRIMARY KEY (CODHISTORICO,
> CODEMPRESA);
> ALTER TABLE CA_LCT ADD CONSTRAINT PK_CA_LCT PRIMARY KEY (NUMLANC,
> CODEMPRESA);
> ALTER TABLE CA_SCT ADD CONSTRAINT PK_CA_SCT PRIMARY KEY (CODCONTA,
> CODFILIAL, CODEMPRESA);
> ALTER TABLE GE_FIL ADD CONSTRAINT PK_FIL PRIMARY KEY (CODFILIAL,
> CODEMPRESA);
>
> ALTER TABLE CA_HIS ADD CONSTRAINT FK_CA_HIS_CREDITO FOREIGN KEY (CREDITO,
> CODEMPRESA) REFERENCES CA_CTA (CODCONTA, CODEMPRESA) ON UPDATE CASCADE;
> ALTER TABLE CA_HIS ADD CONSTRAINT FK_CA_HIS_DEBITO FOREIGN KEY (DEBITO,
> CODEMPRESA) REFERENCES CA_CTA (CODCONTA, CODEMPRESA) ON UPDATE CASCADE;
> ALTER TABLE CA_LCT ADD CONSTRAINT FK_CA_LCT_CODEMPRESA FOREIGN KEY
> (CODFILIAL, CODEMPRESA) REFERENCES GE_FIL (CODFILIAL, CODEMPRESA) ON
UPDATE
> CASCADE;
> ALTER TABLE CA_LCT ADD CONSTRAINT FK_CA_LCT_CODHISTORICO FOREIGN KEY
> (CODHISTORICO, CODEMPRESA) REFERENCES CA_HIS (CODHISTORICO, CODEMPRESA) ON
> UPDATE CASCADE;
> ALTER TABLE CA_LCT ADD CONSTRAINT FK_CA_LCT_CODUSUARIO FOREIGN KEY
> (CODUSUARIO) REFERENCES GE_USU (CODUSUARIO) ON UPDATE CASCADE;
> ALTER TABLE CA_SCT ADD CONSTRAINT FK_CA_SCT_CODCONTA FOREIGN KEY
(CODCONTA,
> CODEMPRESA) REFERENCES CA_CTA (CODCONTA, CODEMPRESA) ON UPDATE CASCADE;
>
> HB> You run the risk of trashing your database if you attempt
> HB> to remove constraints whilst active transactions are using
> HB> the affected objects. Always perform metadata changes on
> HB> a shut-down database with Owner having exclusive access.
>
> Sorry, but when I talk to remove constraints, I simply delete de GDB file
> and re-create w/o the constraints!
> I never do any structure change in a production database!
>
> HB> Your comments are not meaningful. If you want us to help you,
> HB> you need to show us the code of your function, the affected
> HB> metadata and snippets to show us how the values are being passed
> HB> to the parameters.
>
> Well, Now my select is:
>
> Select L.CodEmpresa, L.CodFilial, F.Fantasia as NomeFilial,
> L.Data, L.NumLanc,
> L.CodHistorico, H.Nome AS NomeHistorico,
> H.Debito, D.Nome as NomeContaDebito,
> H.Credito, C.Nome as NomeContaCredito,
> L.Valor, L.Complemento,
> L.CodUsuario, U.Nome as NomeUsuario
> From CA_LCT L
> inner Join Ca_His H on ((H.CodHistorico=L.CodHistorico) and
> (H.CodEmpresa=L.CodEmpresa))
> inner Join GE_FIL F on ( F.CodEmpresa=L.CodEmpresa and
> F.CodFilial=l.CodFilial )
> inner Join GE_USU U on ( U.CodUsuario=L.CodUsuario )
> inner Join Ca_Cta D on (D.CodConta=H.Debito and
D.CodEmpresa=H.CodEmpresa)
> inner Join Ca_Cta C on (C.CodConta=H.Credito and
C.CodEmpresa=H.CodEmpresa)
> Where L.CodEmpresa=:CodEmpresa and L.NumLanc=:NumLanc
>
> the open parameters are:
>
> CodEmpresa = 54
> NumLanc = 1
>
> the insert values are
> :CODEMPRESA = 54 ( integer )
> :NUMLANC = 07 ( integer )
> :CODFILIAL = 01 ( integer )
> :DATA = today ( date insert by delphi )
> :CODHISTORICO = 01 ( integer )
> :VALOR = 200.00 ( Currency )
> :COMPLEMENTO = '' ( blank string / not null )
> :CODUSUARIO = 01
>
> this is a CASH control! Offcourse, first of all I Insert the enterprise (
> empresa ) data in the ge_FIL,
> I insert the user (usuario) data in table GE_USU,
> I insert the account´s (conta) data in the CA_CTA,
> I insert the possible historics list ( hostóricos ) data in the CA_HIS,
> and I try to do a transaction with debit and credit in CA_LCT.
>
> HB> Those are Delphi errors. 20934532761459.2342 is not an integer.
> HB> You must be trying to cast a string such as '20934532761459.2342'
> HB> to integer, or using the .Value method to cast a variant with an
> HB> Extended format into an Integer field.
>
> This message is in IBExpert! In IBOconsole this record not show!
>
> No! When I insert any data ( by my application ) it works fine! And,
> I change form, printer a report, insert any data in other table, All fine!
> Finnaly when I back to this form, and try to insert other record, All
> program
> works, and debugging it from delphi the data put in IBO fields are all ok
> ( I use showmessage ), and the breakpoint enter in QY.post and... error!
> In some cases the message is "The record is not inserted" but In great
part
> of time the error message is "Violation of FK" and changes the name of
Fk!.
>
> It´s fun! If I do a:
> Connection.Disconnect;
> Application.ProcessMessages;
> Connection.Connect;
> In FormShow it´s works fine all time! But in some places I can´t do that
> because other forms can call this form!
>
> HB> Show us, for example, the code where you assign values to parameters.
> Helen, Most part of tiem I use IB_Edit!
> or I do it:
>
> dm.Qy.FieldByName('CodFilial').AsInteger := 0;
> dm.Qy.FieldByName('Data').AsDate := StrToDate(Data);
>
> HB> Comments on your DFM excerpt:
> HB> It does not provide any information about any SQL that is passing
> HB> foreign key values.
> HB> I am also suspicious of your EditSQL. You should not be allowing
> HB> users to have update access to primary key columns.
>
> HB> Helen
>
> One more time, very thanks! Sorry for my poor english!
>
>
>
>
___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
> without the need for BDE, ODBC or any other layer.
>
___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
> keyword-searchable FAQ, community code contributions and more !
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>