Determining the current database
Jacob Salomon asked how a session (ESQL/Perl) can find the name of the current database, noting that the old syssqlcurall trick needs sysmaster and his own sysmaster:systabnames/partnum join won't work on SE. Paul Watson and Ricardo Henriques pointed out the simple answer: SELECT DBINFO('dbname') FROM systables WHERE tabid=1 (syssqlcurall also works if qualified as sysmaster:syssqlcurall). Jack Parker offered an SPL function using sysmaster:sysopendb. Art Kagel noted SE has no sysmaster, so there you'd read getenv("DBPATH"); Ricardo added DBINFO support on SE is limited. Jacob accepted DBINFO as the solution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Greetings.
This is a minor epiphany, motivated by a detail of a hack I'm trying to write.
In any session, like in ESQL or Perl, the code often does not know shat is the
current database. And it's usually not necessary, except perhaps for the
purpose of displaying information or in a report.
Googling for this, I found this gem by Art Kagel, dating back nearly 12 years:
http://www.justskins.com/forums/current-database-name-158989.html
Art's suggestion:
select sqc_currdb
from syssqlcurall
where sqc_sessionid = dbinfo('sessionid');
I tried it as a regular user in my 11.5 server and discovered that
syssqlcurall is not in the database. Reasonable: The answer referred to
sysopendb. I found a few others that were actually ways to get the current
DBSERVERNAME but not the database. And still others where I had no select
permission on some column.
Then I came up with this little item:
select dbsname
from sysmaster:systabnames
where partnum = (select partnum from systables where tabid = 1) ;The subquery runs in the current database while the outer query operates in
sysmaster.
The only drawback is that it would not work for Standard Engine. In my case,
this query is part of a much larger query involving sysdbspaces other items
that apply only to IDS. But it would be nice to know a more general way.
Any takers?
Thanks.
-- Jacob S.
I assume you have tried DBINFO('dbname') ? I have no idea where DBINFO
exists is SE mind you
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> JACOB SALOMON
> Sent: Friday, February 27, 2015 10:44 AM
> To: ids@iiug.org
> Subject: Determining the current database [34731]
>
> Greetings.
> This is a minor epiphany, motivated by a detail of a hack I'm trying to
write.
>
> In any session, like in ESQL or Perl, the code often does not know shat is
the
> current database. And it's usually not necessary, except perhaps for the
> purpose of displaying information or in a report.
>
> Googling for this, I found this gem by Art Kagel, dating back nearly 12
years:
> http://www.justskins.com/forums/current-database-name-158989.html
>
> Art's suggestion:
> select sqc_currdb
> from syssqlcurall
> where sqc_sessionid = dbinfo('sessionid');>
> I tried it as a regular user in my 11.5 server and discovered that
> syssqlcurall is not in the database. Reasonable: The answer referred to
> sysopendb. I found a few others that were actually ways to get the current
> DBSERVERNAME but not the database. And still others where I had no select
> permission on some column.
>
> Then I came up with this little item:
>
> select dbsname
> from sysmaster:systabnames
> where partnum = (select partnum from systables where tabid = 1) ;> The subquery runs in the current database while the outer query operates
in
> sysmaster.
>
> The only drawback is that it would not work for Standard Engine. In my
case,
> this query is part of a much larger query involving sysdbspaces other
items
> that apply only to IDS. But it would be nice to know a more general way.
>
> Any takers?
>
> Thanks.
>
> -- Jacob S.
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
You will find the table syssqlcurall on the sysmaster database:
infx1210@infxsrv:informix-> dbaccess tardis -
Database selected.
> SELECT sqc_currdb
> FROM sysmaster:syssqlcurall
> WHERE sqc_sessionid = DBINFO( 'sessionid' );
sqc_currdb tardis
1 row(s) retrieved.
>
The other way is to use the DBINFO function with the 'dbname' option:
> SELECT DBINFO( 'dbname' )
> FROM systables
> WHERE tabid = 1;
(expression) tardis
1 row(s) retrieved.
>
You can find the docs of the DBFUNCTION, including the 'dbname' option, in the
below link:
http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/id
s_sqs_1484.htm
DBINFO can be call on all databases and has options worth noting.
Keen regards.
There is no sysmaster database in standard engine (SE)!
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Feb 27, 2015 at 2:06 PM, RICARDO HENRIQUES <
ricardoaireshenriques@gmail.com> wrote:
> Hi,
>
> You will find the table syssqlcurall on the sysmaster database:
> infx1210@infxsrv:informix-> dbaccess tardis -
>
> Database selected.
>
> > SELECT sqc_currdb
> > FROM sysmaster:syssqlcurall
> > WHERE sqc_sessionid = DBINFO( 'sessionid' );>
> sqc_currdb tardis
>
> 1 row(s) retrieved.
>
> >
>
> The other way is to use the DBINFO function with the 'dbname' option:
> > SELECT DBINFO( 'dbname' )
> > FROM systables
> > WHERE tabid = 1;
>
> (expression) tardis
>
> 1 row(s) retrieved.
>
> >
>
> You can find the docs of the DBFUNCTION, including the 'dbname' option, in
> the
> below link:
>
>
>
http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/id
s_sqs_1484.htm
>
> DBINFO can be call on all databases and has options worth noting.
>
> Keen regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1141bcceafa36c051016c7bc
Jacob:
The syssqlcurall table exists in the sysmaster database in IDS but
sysmaster is not present in SE (or OnLine 5.xx for that matter). The only
way to discover the current database in SE from an application is to use
getenv( "DBPATH" ) which would be the path to the database directory that
the sqlexec process uses to find the database files. SE only has one
database per "server" (though there is no server in any real sense). You
can't use that method with OnLine or IDS because DBPATH was repurposed in
OnLine 4.xx and later to be a list of failover databases (similar to how
sqlhosts groups are used in IDS). The CSDK still supports the use of DBPATH
in that way except for ESQL/C v2.90. Someone decided DBPATH wasn't used
any longer and removed it from that release. It was reinstated in the CSDK
v3.00 released with IDS v10.00.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Feb 27, 2015 at 11:58 AM, Paul Watson <paul@oninit.com> wrote:
> I assume you have tried DBINFO('dbname') ? I have no idea where DBINFO
> exists is SE mind you
>
> Cheers
> Paul
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > JACOB SALOMON
> > Sent: Friday, February 27, 2015 10:44 AM
> > To: ids@iiug.org
> > Subject: Determining the current database [34731]
> >
> > Greetings.
> > This is a minor epiphany, motivated by a detail of a hack I'm trying to
> write.
> >
> > In any session, like in ESQL or Perl, the code often does not know shat
> is
> the
> > current database. And it's usually not necessary, except perhaps for the
> > purpose of displaying information or in a report.
> >
> > Googling for this, I found this gem by Art Kagel, dating back nearly 12
> years:
> > http://www.justskins.com/forums/current-database-name-158989.html
> >
> > Art's suggestion:
> > select sqc_currdb
> > from syssqlcurall
> > where sqc_sessionid = dbinfo('sessionid');> >
> > I tried it as a regular user in my 11.5 server and discovered that
> > syssqlcurall is not in the database. Reasonable: The answer referred to
> > sysopendb. I found a few others that were actually ways to get the
> current
> > DBSERVERNAME but not the database. And still others where I had no select
> > permission on some column.
> >
> > Then I came up with this little item:
> >
> > select dbsname
> > from sysmaster:systabnames
> > where partnum = (select partnum from systables where tabid = 1) ;> > The subquery runs in the current database while the outer query operates
> in
> > sysmaster.
> >
> > The only drawback is that it would not work for Standard Engine. In my
> case,
> > this query is part of a much larger query involving sysdbspaces other
> items
> > that apply only to IDS. But it would be nice to know a more general way.
> >
> > Any takers?
> >
> > Thanks.
> >
> > -- Jacob S.
> >
> >
> > **********************************************************
> > *********************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113ecaac825d58051016e04d
Thank you Paul and Ricardo. I was certain there had to be a simpler way. But my search through the PDF manuals turned up so many search results I missed the answer sitting there in front of me. Among 3894 other search results. :-) Yes, the dbinfo('dbname') would likely work just fin in SE as well. Again thanks. Now let me get the egg off my face... http://jimfairthorne.files.wordpress.com/2009/06/egg-on-face.jpg -- Jacob S.
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
Actually, I've skipped the part you've mentioned SE and the quest for an unified solution. Not sure, not a SE user, but I believe DBINFO is not fully supported on SE. I think it only has: DBSPACE sqlca.sqlerrd1 sqlca.sqlerrd2 sessionid But, again, I'm not a SE user. Art already gave an answer on how to find out on SE from an application. Keen regards,