Subject | Re: [IBO] Update SQL & Parameters |
---|---|
Author | sdbeames |
Post date | 2002-02-02T05:05:09Z |
Well I've nearly got it working, but not quite....
To recap, MODEL_ID is the param I'm trying to feed to my "detail"
TIB_Querys' (called IdentQry) SQL proc AND InsertSQL proc.
I set up the parameters in the "master" tables' AfterScroll handler
(I'm not using MasterSource & MasterLinks for this)....
void __fastcall TEqptForm::ModelQryAfterScroll(TIB_Dataset
*IB_Dataset)
{
if (IdentQry->Active)
IdentQry->Close();
IdentQry->ParamByName("MODEL_ID")->AsInteger =
ModelQry->FieldByName("MODEL_ID")->AsInteger;
IdentQry->ParamByName("DOC_FILE_ID")->AsInteger =
DocFileQry->FieldByName("DOC_FILE_ID")->AsInteger;
IdentQry->Open();
}
My Select proc looks like...
SET TERM !! ;
CREATE PROCEDURE EQPT_IDENT_SELECT_PROC (Model_Id INTEGER,
Doc_File_Id INTEGER)
RETURNS (Ident_Id INTEGER, Member CHAR(1), Ident VARCHAR(60), ModelId
INTEGER)
AS
BEGIN
ModelId = :Model_Id; /* ModelId = Model_Id; -> no different! */
for select IDENT_ID, IDENT
from EQPT_IDENT
where (MODEL_ID=:Model_Id)
into :Ident_Id, :Ident
do
BEGIN
if (exists(select 1 from DOC_MATCH m
where m.IDENT_ID=:Ident_Id
and m.DOC_FILE_ID=:Doc_File_Id))
then Member='T';
else Member='F';
suspend;
END
END !!
SET TERM ; !!
This works fine when tested with IB_SQL.
My IdentQry SQL is......
SELECT IDENT_ID
, MEMBER
, IDENT
, MODELID
FROM EQPT_IDENT_SELECT_PROC(:MODEL_ID, :DOC_FILE_ID)
which also seems to work fine, displaying the correct values for
MODELID in a grid.
The Insert proc is.....
SET TERM !! ;
create procedure EQPT_IDENT_INSERT_PROC(IDENT_ID integer, MODEL_ID
integer, IDENT varchar(60))
as
begin
insert into EQPT_IDENT(IDENT_ID, MODEL_ID, IDENT) values
(:IDENT_ID, :MODEL_ID, :IDENT);
end!!
SET TERM ; !!
I'm trying InsertSQL like.......
INSERT INTO EQPT_IDENT(
IDENT_ID, /*PK*/
MODEL_ID,
IDENT)
VALUES (
:IDENT_ID,
:MODELID,
:IDENT)
but it stores nothing for MODEL_ID!
It's as if the returned column is not available for the InsertSQL,
but it prepares just fine.
Any suggestions please?
V4.2.Fp, Firebird 0.9.4.41.
Thanks for reading,
Steve
To recap, MODEL_ID is the param I'm trying to feed to my "detail"
TIB_Querys' (called IdentQry) SQL proc AND InsertSQL proc.
I set up the parameters in the "master" tables' AfterScroll handler
(I'm not using MasterSource & MasterLinks for this)....
void __fastcall TEqptForm::ModelQryAfterScroll(TIB_Dataset
*IB_Dataset)
{
if (IdentQry->Active)
IdentQry->Close();
IdentQry->ParamByName("MODEL_ID")->AsInteger =
ModelQry->FieldByName("MODEL_ID")->AsInteger;
IdentQry->ParamByName("DOC_FILE_ID")->AsInteger =
DocFileQry->FieldByName("DOC_FILE_ID")->AsInteger;
IdentQry->Open();
}
My Select proc looks like...
SET TERM !! ;
CREATE PROCEDURE EQPT_IDENT_SELECT_PROC (Model_Id INTEGER,
Doc_File_Id INTEGER)
RETURNS (Ident_Id INTEGER, Member CHAR(1), Ident VARCHAR(60), ModelId
INTEGER)
AS
BEGIN
ModelId = :Model_Id; /* ModelId = Model_Id; -> no different! */
for select IDENT_ID, IDENT
from EQPT_IDENT
where (MODEL_ID=:Model_Id)
into :Ident_Id, :Ident
do
BEGIN
if (exists(select 1 from DOC_MATCH m
where m.IDENT_ID=:Ident_Id
and m.DOC_FILE_ID=:Doc_File_Id))
then Member='T';
else Member='F';
suspend;
END
END !!
SET TERM ; !!
This works fine when tested with IB_SQL.
My IdentQry SQL is......
SELECT IDENT_ID
, MEMBER
, IDENT
, MODELID
FROM EQPT_IDENT_SELECT_PROC(:MODEL_ID, :DOC_FILE_ID)
which also seems to work fine, displaying the correct values for
MODELID in a grid.
The Insert proc is.....
SET TERM !! ;
create procedure EQPT_IDENT_INSERT_PROC(IDENT_ID integer, MODEL_ID
integer, IDENT varchar(60))
as
begin
insert into EQPT_IDENT(IDENT_ID, MODEL_ID, IDENT) values
(:IDENT_ID, :MODEL_ID, :IDENT);
end!!
SET TERM ; !!
I'm trying InsertSQL like.......
INSERT INTO EQPT_IDENT(
IDENT_ID, /*PK*/
MODEL_ID,
IDENT)
VALUES (
:IDENT_ID,
:MODELID,
:IDENT)
but it stores nothing for MODEL_ID!
It's as if the returned column is not available for the InsertSQL,
but it prepares just fine.
Any suggestions please?
V4.2.Fp, Firebird 0.9.4.41.
Thanks for reading,
Steve