IDS Forum

Re: Determining the current database

Posted By: Jack Parker
Date: Friday, 27 February 2015, at 4:07 p.m.

In Response To: Re: Determining the current database (Art Kagel)

Something I=E2=80=99ve used in the past:

CREATE FUNCTION my_db() RETURNING CHAR(128);

DEFINE my_session integer;
DEFINE my_db char(128);

SELECT DBINFO('sessionid')

INTO my_session

FROM systables

WHERE tabid=3D1;

SELECT distinct odb_dbname

INTO my_db

FROM sysmaster:sysopendb

WHERE odb_sessionid=3Dmy_session

AND odb_dbname not in ('sysmaster'); -- exclude these, because =
our session also connected to them

RETURN my_db;

END FUNCTION;

> On Feb 27, 2015, at 2:28 PM, Art Kagel <art.kagel@gmail.com> wrote:
>=20
> Jacob:=20
>=20
> The syssqlcurall table exists in the sysmaster database in IDS but=20
> sysmaster is not present in SE (or OnLine 5.xx for that matter). The =
only=20
> way to discover the current database in SE from an application is to =
use=20
> getenv( "DBPATH" ) which would be the path to the database directory =
that=20
> the sqlexec process uses to find the database files. SE only has one=20=

> database per "server" (though there is no server in any real sense). =
You=20
> can't use that method with OnLine or IDS because DBPATH was repurposed =
in=20
> OnLine 4.xx and later to be a list of failover databases (similar to =
how=20
> sqlhosts groups are used in IDS). The CSDK still supports the use of =
DBPATH=20
> in that way except for ESQL/C v2.90. Someone decided DBPATH wasn't =
used=20
> any longer and removed it from that release. It was reinstated in the =
CSDK=20
> v3.00 released with IDS v10.00.=20
>=20
> Art=20
>=20
> Art S. Kagel, President and Principal Consultant=20
> ASK Database Management=20
> www.askdbmgt.com=20
>=20
> Blog: http://informix-myview.blogspot.com/=20
>=20
> Disclaimer: Please keep in mind that my own opinions are my own =
opinions=20
> and do not reflect on the IIUG, nor any other organization with which =
I am=20
> associated either explicitly, implicitly, or by inference. Neither do=20=

> those opinions reflect those of other individuals affiliated with any=20=

> entity with which I am affiliated nor those of the entities =
themselves.=20
>=20
> On Fri, Feb 27, 2015 at 11:58 AM, Paul Watson <paul@oninit.com> wrote:=20=

>=20
>> I assume you have tried DBINFO('dbname') ? I have no idea where =
DBINFO=20
>> exists is SE mind you=20
>>=20
>> Cheers=20
>> Paul=20
>>=20
>>> -----Original Message-----=20
>>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of=20
>>> JACOB SALOMON=20
>>> Sent: Friday, February 27, 2015 10:44 AM=20
>>> To: ids@iiug.org=20
>>> Subject: Determining the current database [34731]=20
>>>=20
>>> Greetings.=20
>>> This is a minor epiphany, motivated by a detail of a hack I'm trying =
to=20
>> write.=20
>>>=20
>>> In any session, like in ESQL or Perl, the code often does not know =
shat=20
>> is=20
>> the=20
>>> current database. And it's usually not necessary, except perhaps for =
the=20
>>> purpose of displaying information or in a report.=20
>>>=20
>>> Googling for this, I found this gem by Art Kagel, dating back nearly =
12=20
>> years:=20
>>> http://www.justskins.com/forums/current-database-name-158989.html=20
>>>=20
>>> Art's suggestion:=20
>>> select sqc_currdb=20
>>> from syssqlcurall=20
>>> where sqc_sessionid =3D dbinfo('sessionid');=20
>>>=20
>>> I tried it as a regular user in my 11.5 server and discovered that=20=

>>> syssqlcurall is not in the database. Reasonable: The answer referred =
to=20
>>> sysopendb. I found a few others that were actually ways to get the=20=

>> current=20
>>> DBSERVERNAME but not the database. And still others where I had no =
select=20
>>> permission on some column.=20
>>>=20
>>> Then I came up with this little item:=20
>>>=20
>>> select dbsname=20
>>> from sysmaster:systabnames=20
>>> where partnum =3D (select partnum from systables where tabid =3D 1) =
;=20
>>> The subquery runs in the current database while the outer query =
operates=20
>> in=20
>>> sysmaster.=20
>>>=20
>>> The only drawback is that it would not work for Standard Engine. In =
my=20
>> case,=20
>>> this query is part of a much larger query involving sysdbspaces =
other=20
>> items=20
>>> that apply only to IDS. But it would be nice to know a more general =
way.=20
>>>=20
>>> Any takers?=20
>>>=20
>>> Thanks.=20
>>>=20
>>> -- Jacob S.=20
>>>=20
>>>=20
>>> **********************************************************=20
>>> *********************=20
>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=

>>=20
>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=

>>=20
>>=20
>=20
> --001a113ecaac825d58051016e04d=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=

>=20

Messages In This Thread

• Determining the current database
  JACOB SALOMON -- Friday, 27 February 2015, at 11:44 a.m.
  • RE: Determining the current database
    Paul Watson -- Friday, 27 February 2015, at 11:58 a.m.
    • Re: Determining the current database
      Art Kagel -- Friday, 27 February 2015, at 2:28 p.m.
      • Re: Determining the current database
        Jack Parker -- Friday, 27 February 2015, at 4:07 p.m.
  • Re: Determining the current database
    RICARDO HENRIQUES -- Friday, 27 February 2015, at 2:06 p.m.
    • Re: Determining the current database
      Art Kagel -- Friday, 27 February 2015, at 2:21 p.m.
    • Re: Determining the current database
      JACOB SALOMON -- Friday, 27 February 2015, at 2:57 p.m.
      • Re: Determining the current database
        RICARDO HENRIQUES -- Friday, 27 February 2015, at 4:58 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.