Subject: Finding out your current database in SPL From: Jonathan Leffler To: Informix NewsGroup 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 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 $" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created on: Fri Sep 24 16:34:34 PDT 1999 # Created by: jleffler at Informix Software Inc. # # Files archived in this archive: # currentdb.spl # #-------------------- if [ -f currentdb.spl -a "$1" != "-c" ] then echo shar: currentdb.spl already exists else echo 'x - currentdb.spl (2213 characters)' sed -e 's/^X//' >currentdb.spl <<'SHAR-EOF' X{ "@(#)$Id: currentdb.spl,v 1.7 1999/09/24 23:34:09 jleffler Exp $" X# X# Stored procedure CURRENT_DATABASE written by Jonathan Leffler X# (jleffler@informix.com), based on a tip from John Lysell X# (jlysell@informix.com), with corrigenda from Raj Muralidharan X# (rmurali@informix.com) and Tue Hejlskov Larsen (tue@informix.com). X# The version dependency was discovered by Glynnie . X# X# In IDS versions prior to 7.31.UC2, you can create this stored X# procedure in the SysMaster database (but you have to be user informix X# to get the necessary permissions), and then any user in any database X# can run it (or call it in a SELECT statement) and get the name of the X# current database. You can drop the owner part if you are not using a X# MODE ANSI database: X# X# EXECUTE PROCEDURE sysmaster:"informix".current_database() X# EXECUTE PROCEDURE sysmaster:current_database() X# X# However, in 7.31.UC2 and above, it appears that the stored procedure X# will always return 'sysmaster', so you have to install this procedure X# in each database and run it. That leaves open the question of whether X# a generalized tool like this is worth it compared with a simple minded X# stored procedure: X# CREATE PROCEDURE current_database() RETURNING VARCHAR(128); X# RETURN "mydbsname"; X# END PROCEDURE; X# The main advantage of the more complex procedure is that the procedure X# text is identical for all databases, whereas the simple procedure has X# to be customized per database. "Yer pays yer money and yer takes yer X# pick", as they almost say. X# As of 1999-09-24, it is unknown whether this problem affects 9.20 X# servers. X# X# The size of the return parameter needs to be 128 to allow for X# the (currently forth-coming) 9.2x and later servers where the X# names of databases, tables and columns is increased to 128. X# The quoting conventions used should be safe even with DELIMIDENT X# set in the environment. X} X XCREATE PROCEDURE current_database() RETURNING VARCHAR(128); X X DEFINE s VARCHAR(128); X X SELECT ODB_DBName X INTO s X FROM SysMaster:"informix".SysOpenDB X WHERE ODB_SessionID = DBINFO('sessionid') X AND ODB_IsCurrent = 'Y'; X X RETURN s; X XEND PROCEDURE; SHAR-EOF chmod 440 currentdb.spl if [ `wc -c