IIUG Software Repository Files

Subject: Finding out your current database in SPL
From: Jonathan Leffler <jleffler@informix.com>
To: Informix NewsGroup <informix-list@iiug.org>
cc: software@iiug.org
Date: Fri, 24 Sep 1999 16:35:35 -0700 (PDT)

> Subject: Re: 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);

My version has VARCHAR(128) instead of CHAR(4)...

>SELECT ODB_DBName INTO s
>FROM SysMaster:SysOpenDB
>WHERE ODB_SessionID = (SELECT DBINFO("sessionid")
>                   FROM SysTables WHERE TabID = 1)

My version simply has:
	WHERE ODB_SessionID = DBINFO('sessionid')

>AND ODB_IsCurrent = "Y";
>RETURN s;
>END PROCEDURE;

Also, I am very careful to use "informix".SysOpenDB to avoid problems in
MODE ANSI databases.  And I use single quotes for strings to avoid
problems with DELIMIDENT.

>Regardless of which database we are attached to when the above
>procedure is executed, it always appears to return "sysmaster" as the
>current database.

"sysm" given the code above...

>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 ?

Well, the bad news is I can reproduce the problem with the stored
procedure in the sysmaster database on Solaris 2.6 with IDS 7.31.UC2.
So, at one level, the short answer is "I've no idea why it now fails".

However, having stored procedures which reside in remote databases
consider the local database as the current database as the database on
which to work is problematic.  The query plan for the remote database
does not apply to the local database, in general; the tables referenced
might not even exist.  So, I suspect that the change is related to how
this is handled.  I have no proof of this.

Your workaround seems to be necessary with 7.31.UC2; you know have to
create the procedure per database and execute it without the directory
prefix.  I don't see what else is an option, but someone with a superb
understanding of the SMI (sysmaster database) might be able to help you
out.

>ICL Teamserver M754i
>SCO Openserver 5.0.4
>Informix Dynamic Server 7.31.UC2

Yours,
Jonathan Leffler (jleffler@informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v0.62 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"

Revised version of currentdb.spl -- only the annotations changed.

:	"@(#): shar.sh,v 2.1 1998/06/02 17:13:43 jleffler Exp $"