Re: Find out database
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.
------------------------------------------------------------------------
#include <stdlib.h>
#include <unistd.h>
#include <stdio.h>
EXEC SQL INCLUDE sqlca;
int curdbs( char *dbs )
{
EXEC SQL BEGIN DECLARE SECTION;
int mypid;
char *mydb;
EXEC SQL END DECLARE SECTION;
mydb = dbs;
mypid = getpid(); /* Get my process ID */
/* The following assumes I do not have multiple connections,
* though some refinements can be made to accommodate this.
* Just get the current connection name and find session id
* for that connection.
*
* Sequence: From my pid
* ==> syssessions, getting my session ID
* ==> syslocks, getting Database locks I am holding
* ==> sysdatabases, for the name of databases with shared
locks
*/
EXEC SQL
select name
into :mydb
from sysmaster:sysdatabases d,
sysmaster:syslocks l,
sysmaster:syssessions s
where s.pid = :mypid
and d.rowid = l.rowidlk
and l.dbsname = 'sysmaster'
and l.tabname = 'sysdatabases'
and l.owner = s.sid
and d.name != 'sysmaster';
return sqlca.sqlcode == 0; /* Return TRUE if I got it */
}
int main( int argc, char **argv )
{
EXEC SQL BEGIN DECLARE SECTION;
char mydb[37], db[37];
EXEC SQL END DECLARE SECTION;
strcpy( db, argv[1] );
EXEC SQL DATABASE :db;
curdbs( mydb );
printf( "Database: %s.\\n", mydb );
}
------------------------------------------------------------------------
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.
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
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!
--
-- Jake (In pursuit of undomesticated aquatic avians)
+----------------------------------------------------------+
|Aside from that, how did you enjoy the play, Mrs. Lincoln?|
+----------------------------------------------------------+