hi artur,

I think you may be misunderstanding. The stored procedures are already
compiled and work just fine. I made them and tested them a few days ago. I
am not changing the DDL for the stored procs. I am just trying to use them
in this TIBOQuery.

and I have done so with other TIBOQueries just fine. this is the first one
however where the stored procedure involves a join. But that shouldn't
matter. And again, I know the stored procedure works correctly as I have
tested it with another tool (ib expert). It seems to be somehow relate to
how I am setting up the TIBOQuery.

thanks

rob

----- Original Message -----
From: "Artur Anjos" <arsoft@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, May 02, 2002 2:59 PM
Subject: Re: [IBO] stumped on editSQL problem


> 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.
> >
> >
>
>
>
>
___________________________________________________________________________
> 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 http://docs.yahoo.com/info/terms/
>
>