Hi,

I' m facing a problem with IBO_Transaction and the commit it does
I have Autocommit true, ServerAutocommit False.
I have a Stored procedure that's actually a single point of access
for my
table, so depending on input parameters, I do a select, insert update
delete or search on that table.
Now if I want to insert a record, I call upon this SP like this :
'select * from spupdatemytable ', with input params like this
Control_mode = 1 (=insert)
InCode = 1, inAgency = 1, inName = 'TEST',... (= parameters to supply
Field
data for record to insert)
SrchCode = null, SrchName = Null, SrchAgency = 1 (=parameters to
limit
select by) (see below for such a SP example)

The result of this select should be that a record gets inserted, and
that
the SP also returns me the result set of a new select.
However, what happens is that because I call commit after having
done this
select, IBO kindly wants to "refresh" my cursor because commitaction
of
the
dataset is caInvalidateCursor. Which of course re-executes the SP,
resulting
in a PK violation ;-(
I've already tried using commit retaining, but since all this takes
place
on
an Asta server (7x24 active), I don't like the fact of open
transactions.
Setting commitaction to caclose won't help either, because then Asta
doesn't
return the data from the select ;-(

How can I tweak properties so this doesn't happen ?

Here's an example of such a SP, so all this would be a little easier
to
understand <g>

btw all this is with IBO 4.2ha.

many thanks for any time invested in this,

Regards,

Dirk Naudts.



CREATE PROCEDURE SPUPDATEAGENCYUSER(
CONTROL_MODE INTEGER,
LANGUAGEID VARCHAR(3),
INAGUAGSID INTEGER,
INAGUID INTEGER,
INAGUNAME VARCHAR(30),
INAGUPASSWORD VARCHAR(6),
INAGURIGHTS VARCHAR(80),
SRCHAGUAGSID INTEGER,
SRCHAGUID INTEGER,
SRCHAGUNAME VARCHAR(30),
SRCHAGUPASSWORD VARCHAR(6),
SRCHAGURIGHTS VARCHAR(80))
RETURNS (
AGUAGSID INTEGER,
AGUID INTEGER,
AGUNAME VARCHAR(30),
AGUPASSWORD VARCHAR(6),
AGURIGHTS VARCHAR(80),
AGSNAME VARCHAR(35),
ERRORMSG VARCHAR(150))
AS
declare variable res integer;
begin
if (:languageid is null) then
languageid = 'UK';

if (:control_mode = 0) then
begin
/*cmSelect*/
for SELECT

agencyuser.aguagsid,agencyuser.aguid,agencyuser.aguname,agencyuser.agu
passwo
rd,agencyuser.agurights
,agsname FROM AGENCYUSER left outer join agency on agsid = aguagsid
where
(AGUAGSID = :SrchAGUAGSID or :SrchAGUAGSID is null)
and (AGUID = :SrchAGUID or :SrchAGUID is null)
and (AGUNAME like :SrchAGUNAME||'%' or :SrchAGUNAME is null)
and (AGUPASSWORD like :SrchAGUPASSWORD||'%' or :SrchAGUPASSWORD is
null)
and (AGURIGHTS = :SrchAGURIGHTS or :SrchAGURIGHTS is null)

into :AGUAGSID,:AGUID,:AGUNAME,:AGUPASSWORD,:AGURIGHTS,:agsname do
begin
suspend;
end
end
else if (:control_mode = 1) then
begin
/*cmInsert*/
if (exists(select * from AGENCYUSER where AGUAGSID = :InAGUAGSID
and AGUID
=
:InAGUID)) then
begin
select ErrorMsg from applicationerror where errorcode = 160 and
languageid
=
:LanguageId into :Errormsg;
suspend;
end
else
begin
INSERT INTO AGENCYUSER
(AGUAGSID,AGUID,AGUNAME,AGUPASSWORD,AGURIGHTS)
VALUES (:inAGUAGSID,:inAGUID,:inAGUNAME,:inAGUPASSWORD,:inAGURIGHTS);

for SELECT

agencyuser.aguagsid,agencyuser.aguid,agencyuser.aguname,agencyuser.agu
passwo
rd,agencyuser.agurights
,agsname FROM AGENCYUSER left outer join agency on agsid = aguagsid
where
(AGUAGSID = :SrchAGUAGSID or :SrchAGUAGSID is null)
and (AGUID = :SrchAGUID or :SrchAGUID is null)
and (AGUNAME like :SrchAGUNAME||'%' or :SrchAGUNAME is null)
and (AGUPASSWORD like :SrchAGUPASSWORD||'%' or :SrchAGUPASSWORD is
null)
and (AGURIGHTS = :SrchAGURIGHTS or :SrchAGURIGHTS is null)

into :AGUAGSID,:AGUID,:AGUNAME,:AGUPASSWORD,:AGURIGHTS,:agsname do
begin
suspend;
end
end
end
else if (:control_mode = 2) then
begin
/*cmUpdate*/
update AGENCYUSER set
AGUNAME = :InAGUNAME ,AGUPASSWORD = :InAGUPASSWORD ,AGURIGHTS =
:InAGURIGHTS
where
AGUAGSID = :InAGUAGSID and AGUID = :InAGUID;
for SELECT

agencyuser.aguagsid,agencyuser.aguid,agencyuser.aguname,agencyuser.agu
passwo
rd,agencyuser.agurights
,agsname FROM AGENCYUSER left outer join agency on agsid = aguagsid
where
(AGUAGSID = :SrchAGUAGSID or :SrchAGUAGSID is null)
and (AGUID = :SrchAGUID or :SrchAGUID is null)
and (AGUNAME like :SrchAGUNAME||'%' or :SrchAGUNAME is null)
and (AGUPASSWORD like :SrchAGUPASSWORD||'%' or :SrchAGUPASSWORD is
null)
and (AGURIGHTS = :SrchAGURIGHTS or :SrchAGURIGHTS is null)

into :AGUAGSID,:AGUID,:AGUNAME,:AGUPASSWORD,:AGURIGHTS,:agsname do
begin
suspend;
end
end
else if (:control_mode = 3) then
begin
/*cmDelete*/
DELETE FROM AGENCYUSER
where
AGUAGSID = :InAGUAGSID and AGUID = :InAGUID;
for SELECT

agencyuser.aguagsid,agencyuser.aguid,agencyuser.aguname,agencyuser.agu
passwo
rd,agencyuser.agurights
,agsname FROM AGENCYUSER left outer join agency on agsid = aguagsid
where
(AGUAGSID = :SrchAGUAGSID or :SrchAGUAGSID is null)
and (AGUID = :SrchAGUID or :SrchAGUID is null)
and (AGUNAME like :SrchAGUNAME||'%' or :SrchAGUNAME is null)
and (AGUPASSWORD like :SrchAGUPASSWORD||'%' or :SrchAGUPASSWORD is
null)
and (AGURIGHTS = :SrchAGURIGHTS or :SrchAGURIGHTS is null)

into :AGUAGSID,:AGUID,:AGUNAME,:AGUPASSWORD,:AGURIGHTS,:agsname do
begin
suspend;
end
end
else if (:control_mode = 4) then
begin
/*cmfieldinfo*/
SELECT

agencyuser.aguagsid,agencyuser.aguid,agencyuser.aguname,agencyuser.agu
passwo
rd,agencyuser.agurights
,agsname FROM AGENCYUSER left outer join agency on agsid = aguagsid
where
(0=1)

into :AGUAGSID,:AGUID,:AGUNAME,:AGUPASSWORD,:AGURIGHTS,:agsname;
end

END