Hi Rob

Don't worry, you don't have an IBO problem. When you change a SP in the
server, you must do it without any other connections to the server. If
another connection changes the SP, that change is not visible to your
connection until you close it and reopen.

You must change your SP in code? If so, you can try do drop it and create it
again, instead of using recreate.

Artur

----- Original Message -----
From: "Rob Schuff" <rob@...>
To: "IBOList" <ibobjects@yahoogroups.com>
Sent: Thursday, May 02, 2002 10:46 PM
Subject: [IBO] stumped on editSQL problem


> 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
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
>
>
>
>
>
___________________________________________________________________________
> 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 the Yahoo! Terms of Service.
>
>