Sysmaster Stored Procedure No Longer Working

We have recently carried out an IDS upgrade from 7.30.UC2 to 7.31.UC2 to apply a number of bug fixes. We now find that as a result of the upgrade (only the engine was upgraded - no tools), a sysmaster stored procedure that yields the current database has ceased to operate. The code within the procedure is as follows :- -- -- Informix-owned stored procedure to return current database -- Executed as follows :- -- -- select sysmaster:pr_curr_db() from systables where tabid = 1; -- -- courtesy of Jonathan Leffler -- -- -- DROP PROCEDURE pr_curr_db; CREATE PROCEDURE pr_curr_db() RETURNING CHAR(4); DEFINE s CHAR(4); SELECT ODB_DBName INTO s FROM SysMaster:SysOpenDB WHERE ODB_SessionID = (SELECT DBINFO("sessionid") FROM SysTables WHERE TabID = 1) AND ODB_IsCurrent = "Y"; RETURN s; END PROCEDURE; Regardless of which database we are attached to when the above procedure is executed, it always appears to return "sysmaster" as the current database. We have cured this problem (apparently) by creating the above procedure in each of the databases to which our application attaches. Anyone out there (Jonathan ??) got any ideas as to why this should have ceased to work merely by upgrading from 7.30.UC2 to 7.31.UC2 ? Regards Glyn Balmer ICL Teamserver M754i SCO Openserver 5.0.4 Informix Dynamic Server 7.31.UC2 -- If it always works, why don't parachutists pull the emergency 'chute first? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Solution: Place the function in each database and execute it without the database qualifier. Why the change? I don't know. Art S. Kagel Glynnie wrote: > > We have recently carried out an IDS upgrade from 7.30.UC2 to 7.31.UC2 > to apply a number of bug fixes. We now find that as a result of the > upgrade (only the engine was upgraded - no tools), a sysmaster stored > procedure that yields the current database has ceased to operate. The > code within the procedure is as follows :- > > -- > -- Informix-owned stored procedure to return current database > -- Executed as follows :- > -- > -- select sysmaster:pr_curr_db() from systables where tabid = 1; > -- > -- courtesy of Jonathan Leffler > -- > -- > -- DROP PROCEDURE pr_curr_db; > CREATE PROCEDURE pr_curr_db() RETURNING CHAR(4);> > DEFINE s CHAR(4); > > SELECT ODB_DBName INTO s > FROM SysMaster:SysOpenDB > WHERE ODB_SessionID = (SELECT DBINFO("sessionid") > FROM SysTables WHERE TabID = 1) > AND ODB_IsCurrent = "Y"; > RETURN s; > END > PROCEDURE; > > Regardless of which database we are attached to when the above > procedure is executed, it always appears to return "sysmaster" as the > current database. We have cured this problem (apparently) by creating > the above procedure in each of the databases to which our application > attaches. > > Anyone out there (Jonathan ??) got any ideas as to why this should have > ceased to work merely by upgrading from 7.30.UC2 to 7.31.UC2 ? > > Regards > > Glyn Balmer > > ICL Teamserver M754i > SCO Openserver 5.0.4 > Informix Dynamic Server 7.31.UC2 > > -- > If it always works, why don't parachutists > pull the emergency 'chute first? > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.