Re: SQL for current database
Rick Ward wrote:
>
> Can anyone supply me with some SQL to run against Online V7.13, which
> will return the name of the database this session is currently
> connected to ?
Rick,
I can't test these steps because I am stuck with 7.11 for now and the
dbibinfo does not quite cut it. But I'll try anyway. I'll use 4gl code
for clarity; you can later try to stick this into one SQL statement with
subqueries. The sequence below needs to be modified if your session has
multiple database connections, or even a cursor for a query that gets
data from two databases.
select unique dbinfo('sessionid') -- Get my session ID
into mysessionid
from systables
select rowidlk -- Get a list of database locks
into mydbs_rowid -- held by my current session
from sysdatabases@sysmaster -- As noted, I assume 1 lock now
where owner = mysessionid
and dbsname = "sysmaster"
and tabname = "sysdatabases"
select name
from sysdatabases@sysmaster
where rowid = mydbs_rowid
Note that sysdatabases is a real table with rows and columbns. It
always has been one since 4.0, merely not accessible via SQL in pre-DSA
releases of OnLine. We also hope that nobody ever fragments any of the
tables in sysmaster.
Now let's try it in a single query:
select name -- Database name
from sysdatabases@sysmaster
where rowid
in (select rowidlk
from sysdatabases@sysmaster
where dbsname = "sysmaster"
and tabname = "sysdatabases"
and owner = (select unique dbinfo('sessionid')
from systables)
)
Now, isn't that intuitive?
I once had the time to request more features to the dbinfo function in
SQL, like the name of the current database, complete user profile, and
lots of stuff I haven't thought of. Meantime, you can put the above
into a stored procedure and call it whenever you need to.
As I said, I could net test it in my version of the software because
dbinfo does not understand the 'sessionid' stuff in 7.11, so please post
the results of your test and [inevitable] improvements.
See'ya on the 'Net!
--
-- Jake (Lost in thought and won't ask for directions)
. .
_..-'( )`-.._
./'. '||\\\\. }\\_/{ .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
\\ \\ \\ V / / /
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+