Re: Find out database

>From: Jacob Salomon <jake@apparel.net> >Date: Tue, 22 Jul 1997 21:07:35 GMT >X-Informix-List-Id: <news.40732> > >Art S. Kagel refined my pid->session->lock->database algorithm. What he >produced is logically equivalent to my clumsier first effort. Thus, the >critiques below apply equally to both versions of the query. > >I am merely returning the favor by adding minor modifications for >clarity, and some additional commments. [...Source Code Omitted...] >I ran the above query in SQL and it took a while to execute the first >time. I also tested my hypothesis that a transactions that crosses >databases would confuse the algorithm. I did a BEGIN WORK in one >database, ran a query on tables in another database, then reran the >above query. Sure enough, it displayed both databases. > >Furthermore, what if I have reason to be working in sysmaster? The above >query would skip my current database. Did anybody notice the alternative solution which I posted on this thread on 17th July (according to my outgoing message log)? I posted a stored procedure called current_database(). It does not get confused by a multi-database transaction or by SysMaster being the current database. Is there some OnLine environment where this doesn't work (the obvious answer being 5.0x, but the same restriction applies to Jake's solution)? >To Informix: >There has long been a feature request to allow users to determine the >name of the current database, the transaction state of the current >database and similar functions. If we can SELECT SITENAME, why not be >able to SELECT DATABASE, or SELECT SESSIONID? Somehow, this missed DBINFO('sessionid') tells you the session id in 7.2x and later versions;I've not checked when it was introduced. With the current_database() SP, you can determine the database. Granted it isn't as convenient as a single keyword (that would be nice), but it is better than nothing. Transaction state is not currently available, and it is difficult to implement it non-destructively. You probably have to attempt to do BEGIN WORK; if it fails because you're in a TX, you have the answer. If it succeeds, you weren't in a TX and need to terminate the TX you just started. If it fails because TXs aren't available, then you have an answer, but how do you communicate it? Maybe you can use something like this SP: CREATE PROCEDURE tx_state() RETURNING VARCHAR(14); DEFINE errcode INTEGER; ON EXCEPTION IN (-256, -535) SET errcode IF errcode = -256 THEN RETURN "TX-Unavailable"; ELIF errcode = -535 THEN RETURN "In-TX"; END IF; END EXCEPTION BEGIN WORK; ROLLBACK WORK; RETURN "No-TX"; END PROCEDURE; This works, but it cannot be used in a SELECT statement (you have to use EXECUTE PROCEDURE) because it does transaction operations. >inclusion in Tim's "Wish List". (Interestingly enough, I recall seeing a >request for transaction state -am I in a transaction now?- made it onto >the list. Grumble big time! Tim posted a first version of the list; you didn't notice the omission? Nor me. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>