hi folks,

I am using stored procedures to update all tables and have several working
except for one and I'm curious if someone can help me understand what is
going wrong here. I'm using D5 update 1, FB 0.94 and IBO 4.2Fn. I have
verieifed the that SPs work correctly and I have seen the SQL Monitor trace.
Whenever I execute an update I can see from the SQL trace that the select
stored procedure is getting called (with the correctly entered data) instead
of the update stored procedure. Below are relevant data including monitor
trace, TIBOPquery properties, and ddl.

many many thanks in advance!

rob

************************************

/*---

EXECUTE STATEMENT

TR_HANDLE = 61814648

STMT_HANDLE = 61811788

PARAMS = [ Version 1 SQLd 3 SQLn 3

PRO_DIMENSIONS_ROLES_S.DIMENSIONNUM = 6

PRO_DIMENSIONS_ROLES_S.ROLENUM = 21

PRO_DIMENSIONS_ROLES_S.DIMVALUE = '1' ] <---- here';s the data I entered,
but using the wrong stored procedure

----*/

/*---

EXECUTE ROW FETCH

TR_HANDLE = 61814648

STMT_HANDLE = 61811580

PARAMS = [ Version 1 SQLd 2 SQLn 2

PRO_DIMENSIONS_ROLES_S.ROLENUM[BIND_0] = 21

PRO_DIMENSIONS_ROLES_S.DIMENSIONNUM[BIND_1] = 6 ]

----*/

/*---

OPEN ROW CURSOR

STMT_HANDLE = 61811580

NAME = C178609652170504150

----*/

/*---

FETCH

STMT_HANDLE = 61811580

FIELDS = [ Version 1 SQLd 4 SQLn 4

PRO_DIMENSIONS_ROLES_S.ROLENUM = 21

PRO_DIMENSIONS_ROLES_S.DIMENSIONNUM = 6

PRO_DIMENSIONS_ROLES_S.DIMVALUE = <NULL>

PRO_DIMENSIONS_ROLES_S.DIMDESC = 'Protected Health Information' ]

----*/

/*---

FETCH

STMT_HANDLE = 61811580

FIELDS = [ Version 1 SQLd 4 SQLn 4

PRO_DIMENSIONS_ROLES_S.ROLENUM = 21

PRO_DIMENSIONS_ROLES_S.DIMENSIONNUM = 6

PRO_DIMENSIONS_ROLES_S.DIMVALUE = <NULL>

PRO_DIMENSIONS_ROLES_S.DIMDESC = 'Protected Health Information' ]

ERRCODE = 100

----*/



I have set the following properties of the TIBOQuery object:

object qryDimRoles: TIBOQuery
DeleteSQL.Strings = (
'EXECUTE PROCEDURE'
' pro_Dimensions_roles_d'
' ('
' :DIMENSIONNUM,'
' :ROLENUM'
' )')
EditSQL.Strings = (
'EXECUTE PROCEDURE'
' pro_Dimensions_roles_u'
' ('
' :DIMENSIONNUM, /*PK*/'
' :ROLENUM, /*PK*/'
' :DIMVALUE'
' )')
InsertSQL.Strings = (
'EXECUTE PROCEDURE'
' pro_Dimensions_roles_i'
' ('
' :DIMENSIONNUM, /*PK*/'
' :ROLENUM, /*PK*/'
' :DIMVALUE'
' )')
KeyLinks.Strings = (
'rolenum'
'dimensionnum')
KeyLinksAutoDefine = False
RequestLive = True
SQL.Strings = (
'SELECT'
' *'
'FROM'
' pro_dimensions_roles_s'
'WHERE'
' rolenum=:rolenum')
end












Here's the underlying table:

CREATE TABLE DIMENSIONS_ROLES (
ROLENUM DM_KEYFIELD NOT NULL,
DIMENSIONNUM DM_KEYFIELD NOT NULL,
DIMVALUE DM_DIMVALUE);

/* Primary keys definition */

ALTER TABLE DIMENSIONS_ROLES ADD CONSTRAINT PK_DIMENSIONS_ROLES PRIMARY KEY
(ROLENUM, DIMENSIONNUM);

And the SUID stored procedures are:

SELECT:

CREATE PROCEDURE PRO_DIMENSIONS_ROLES_S
RETURNS (
ROLENUM INTEGER,
DIMENSIONNUM INTEGER,
DIMVALUE CHAR(1) CHARACTER SET NONE,
DIMDESC VARCHAR(100) CHARACTER SET NONE)
AS
BEGIN
FOR
SELECT
dr.ROLENUM,
dr.DIMENSIONNUM,
dr.DIMVALUE,
d.DIMDESC
FROM
DIMENSIONS_ROLES dr
JOIN
DIMENSIONS d ON dr.dimensionnum=d.dimensionnum
INTO
:ROLENUM,
:DIMENSIONNUM,
:DIMVALUE,
:DIMDESC
DO BEGIN
SUSPEND;
END
END

CREATE PROCEDURE PRO_DIMENSIONS_ROLES_U (
ROLENUM INTEGER,
DIMENSIONNUM INTEGER,
DIMVALUE CHAR(1) CHARACTER SET NONE)
AS
BEGIN
UPDATE DIMENSIONS_ROLES
SET DIMVALUE = :DIMVALUE
WHERE (ROLENUM = :ROLENUM) AND (DIMENSIONNUM = :DIMENSIONNUM);
END

CREATE PROCEDURE PRO_DIMENSIONS_ROLES_I (
ROLENUM INTEGER,
DIMENSIONNUM INTEGER,
DIMVALUE CHAR(1) CHARACTER SET NONE)
AS
BEGIN
INSERT INTO DIMENSIONS_ROLES (
ROLENUM,
DIMENSIONNUM,
DIMVALUE)
VALUES (
:ROLENUM,
:DIMENSIONNUM,
:DIMVALUE);
END

CREATE PROCEDURE PRO_DIMENSIONS_ROLES_D (
ROLENUM INTEGER,
DIMENSIONNUM INTEGER)
AS
BEGIN
DELETE FROM DIMENSIONS_ROLES
WHERE (ROLENUM = :ROLENUM) AND (DIMENSIONNUM = :DIMENSIONNUM);
END