Board index » delphi » output parameter

output parameter


2005-12-01 11:57:54 AM
delphi60
here is my proc and the putput parameter is always null !!!
Create PROCEDURE TRANS_PROC
RETURNS (
ASSNO_VAL VARCHAR(20) CHARACTER SET WIN1252)
AS
DECLARE VARIABLE MLOCNO INTEGER;
DECLARE VARIABLE ROOMNO INTEGER;
DECLARE VARIABLE ORG_ROOMNO INTEGER;
DECLARE VARIABLE ORG_SLOCNO INTEGER;
DECLARE VARIABLE ORG_MLOCNO INTEGER;
DECLARE VARIABLE SLOCNO INTEGER;
DECLARE VARIABLE ASSNO VARCHAR(20) CHARACTER SET WIN1252;
begin
FOR SELECT ASSNO, MLOCNO, SLOCNO, ROOMNO
FROM STOCK_ASSETS
INTO :ASSNO, :MLOCNO, :SLOCNO, :ROOMNO
DO BEGIN
ASSNO_VAL = :ASSNO;
IF (NOT EXISTS(
SELECT 1 FROM ORG_ASSETS
WHERE (ORG_ASSETS.ASSNO = :ASSNO) )) THEN begin
insert into ORG_ASSETS (ASSNO, ASSDESC, ASSQTY, GRPNO,
MLOCNO, SLOCNO, ROOMNO, AFE, NOTES, ASSET_CHECKED,
MANUAL_CHECKED, AUTO_CHECKED, CHECKED_DATE, CHECKED_QTY,
ASSET_ADDED, ADDED_DATE, ASSET_SOLD, SOLD_DATE,
ASSET_S{*word*99}ED, S{*word*99}ED_DATE, S{*word*99}ED_MLOCNO, ASSET_MOVED,
MOVED_DATE, MOVED_MLOCNO, MOVED_SLOCNO, MOVED_ROOMNO)
VALUES (:ASSNO, 'not specified', NULL, NULL,
:MLOCNO, :SLOCNO, :ROOMNO, NULL, NULL, 1, NULL, NULL,
NULL, NULL, 1, NULL, 0, NULL, 0, NULL, NULL, 0,
NULL, NULL, NULL, NULL);
end
else begin
update ORG_ASSETS set ASSET_CHECKED = 1
where ORG_ASSETS.assno = :ASSNO;
/* test mloc */
select MLOCNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_MLOCNO;
if (:ORG_MLOCNO <>:MLOCNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_MLOCNO = :MLOCNO
where ORG_ASSETS.assno = :ASSNO;
/* test sloc */
select SLOCNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_SLOCNO;
if (:ORG_SLOCNO <>:SLOCNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_SLOCNO = :SLOCNO
where ORG_ASSETS.assno = :ASSNO;
/* test room */
select ROOMNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_ROOMNO;
if (:ORG_ROOMNO <>:ROOMNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_ROOMNO = :ROOMNO
where ORG_ASSETS.assno = :ASSNO;
end
/*when sqlcode -350 do*/
/*exception EXP_FK_ERR;*/
end
delete from STOCK_ASSETS;
suspend;
END
 
 

Re:output parameter

Quote
here is my proc and the putput parameter is always null !!!

Create PROCEDURE TRANS_PROC
RETURNS (
ASSNO_VAL VARCHAR(20) CHARACTER SET WIN1252)
AS
DECLARE VARIABLE MLOCNO INTEGER;
DECLARE VARIABLE ROOMNO INTEGER;
DECLARE VARIABLE ORG_ROOMNO INTEGER;
DECLARE VARIABLE ORG_SLOCNO INTEGER;
DECLARE VARIABLE ORG_MLOCNO INTEGER;
DECLARE VARIABLE SLOCNO INTEGER;
DECLARE VARIABLE ASSNO VARCHAR(20) CHARACTER SET WIN1252;
begin
FOR SELECT ASSNO, MLOCNO, SLOCNO, ROOMNO
FROM STOCK_ASSETS
INTO :ASSNO, :MLOCNO, :SLOCNO, :ROOMNO
DO BEGIN
If this doesn't return any rows, ASSNO_VAL will keep it's
previous value (NULL once not intialized before).
Quote
ASSNO_VAL = :ASSNO;
IF (NOT EXISTS(
SELECT 1 FROM ORG_ASSETS
WHERE (ORG_ASSETS.ASSNO = :ASSNO) )) THEN begin

insert into ORG_ASSETS (ASSNO, ASSDESC, ASSQTY, GRPNO,
MLOCNO, SLOCNO, ROOMNO, AFE, NOTES, ASSET_CHECKED,
MANUAL_CHECKED, AUTO_CHECKED, CHECKED_DATE, CHECKED_QTY,
ASSET_ADDED, ADDED_DATE, ASSET_SOLD, SOLD_DATE,
ASSET_S{*word*99}ED, S{*word*99}ED_DATE, S{*word*99}ED_MLOCNO, ASSET_MOVED,
MOVED_DATE, MOVED_MLOCNO, MOVED_SLOCNO, MOVED_ROOMNO)
VALUES (:ASSNO, 'not specified', NULL, NULL,
:MLOCNO, :SLOCNO, :ROOMNO, NULL, NULL, 1, NULL, NULL,
NULL, NULL, 1, NULL, 0, NULL, 0, NULL, NULL, 0,
NULL, NULL, NULL, NULL);

end
else begin
update ORG_ASSETS set ASSET_CHECKED = 1
where ORG_ASSETS.assno = :ASSNO;

/* test mloc */
select MLOCNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_MLOCNO;
if (:ORG_MLOCNO <>:MLOCNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_MLOCNO = :MLOCNO
where ORG_ASSETS.assno = :ASSNO;

/* test sloc */
select SLOCNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_SLOCNO;

if (:ORG_SLOCNO <>:SLOCNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_SLOCNO = :SLOCNO
where ORG_ASSETS.assno = :ASSNO;

/* test room */
select ROOMNO from ORG_ASSETS
where ORG_ASSETS.assno = :ASSNO into :ORG_ROOMNO;

if (:ORG_ROOMNO <>:ROOMNO) then
update ORG_ASSETS set ASSET_MOVED = 1, MOVED_ROOMNO = :ROOMNO
where ORG_ASSETS.assno = :ASSNO;
end

/*when sqlcode -350 do*/
/*exception EXP_FK_ERR;*/
end

delete from STOCK_ASSETS;
suspend;
Do not use SUSPEND if you "execute" this procedure. Suspend should
only go into "select-able" stored procedures.
Quote
END
How are you testing this procedure?
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:output parameter

with IB expert and with delphi ??
 

Re:output parameter

prc_exe.StoredProcName := 'TRANS_PROC';
try
prc_exe.ExecProc;
except
showmessage('error on value '+prc_exe.Params[0].AsString);
end;
//prc_exe is a TIBStoredProc
thanks
 

Re:output parameter

Quote
with IB expert and with delphi ??
Mind you, I believe IBExpert will also display 1 row
with <null>even though it is an EMPTY resultset.
HOW are you testing it? Via an EXECUTE or SELECT
or how?
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:output parameter

Quote
prc_exe.StoredProcName := 'TRANS_PROC';
try
prc_exe.ExecProc;
except
showmessage('error on value '+prc_exe.Params[0].AsString);
end;

//prc_exe is a TIBStoredProc
But your procedure doesn't raise an exception, so the ShowMessage
will never execute.
If your procedure DOES raise an exception for whatever reason,
all the code in the "begin..end" block that triggered the exception will
be "undone". If it then finds an exception handler it will stop there, else
it will "move up" a block.
So assigning parameters won't work in this case.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:output parameter

so back to my first question how I can get a field value from
for select loop when exception occur ?
I have a foreign key constraint on the table was being updated
by the proc and it raises and Bill told me to use output
parameter
 

Re:output parameter

Quote
so back to my first question how I can get a field value from
for select loop when exception occur ?
Hmm, I don't know - I wonder if you can.
Wait, perhaps -- catch the exception in the procedure by
doing a WHEN and then assign the value, would that work?
Quote
I have a foreign key constraint on the table was being updated
by the proc and it raises and Bill told me to use output
parameter
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com