RE: Finding out your current database in SPL

Thanks for the SQL, it solved my problem. Thanks a lot. Wayne Sheldon Computer Manager Airflow Streamlines plc Production Division -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Wednesday, May 12, 1999 9:18 PM To: informix-list@iiug.org Subject: Re: Finding out your current database in SPL Sheldon, Wayne wrote: > > Hi > > In my SPL I need to run a SYSTEM command that executes a batch file, > which will then update the relevant database, but I cant any way of passing > the current database to the system. > > We also have other databases which also need to execute the batch file, > > I have a work-around, which is to create separate procedures for each > database which execute separate batch files. > This isn't very tidy as the procedures files have to be kept separate for > each database. I assume that you cannot simply add an argument to the stored procedure to pass the database name as an argument?! Try this query in the stored procedure: SELECT dbsname FROM systables st, sysmaster:systabnames stn WHERE st.tabid = 1 AND st.partnum = stn.partnum; This will work as long as your current database supports Informix logging (ie not ANSI and not UNLOGGED). Art S. Kagel

In article <7he006$s2q$1@news.xmission.com>, "Sheldon, Wayne" <w.sheldon@airflow-streamlines.co.uk> wrote: > > Thanks for the SQL, it solved my problem. > > Thanks a lot. > > Wayne Sheldon > Computer Manager > Airflow Streamlines plc > Production Division > > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: Wednesday, May 12, 1999 9:18 PM > To: informix-list@iiug.org > Subject: Re: Finding out your current database in SPL > > Sheldon, Wayne wrote: > > > > Hi > > > > In my SPL I need to run a SYSTEM command that executes a batch file, > > which will then update the relevant database, but I cant any way of > passing > > the current database to the system. > > > > We also have other databases which also need to execute the batch file, > > > > I have a work-around, which is to create separate procedures for each > > database which execute separate batch files. > > This isn't very tidy as the procedures files have to be kept separate for > > each database. > > I assume that you cannot simply add an argument to the stored procedure > to pass the database name as an argument?! Try this query in the > stored procedure: > > SELECT dbsname > FROM systables st, sysmaster:systabnames stn > WHERE st.tabid = 1 > AND st.partnum = stn.partnum;> > This will work as long as your current database supports Informix > logging (ie not ANSI and not UNLOGGED). > > Art S. Kagel > Wayne We had a similar problem and picked up on a posting by the illustrious Mr Leffler which we now use (nay, depend upon!) in our production environment, thus :- -- -- Informix-owned stored procedure to return current database -- -- courtesy of Jonathan Leffler -- -- DROP PROCEDURE pr_curr_db; CREATE PROCEDURE pr_curr_db() RETURNING CHAR(4); DEFINE s CHAR(4); SELECT ODB_DBName INTO s FROM SysMaster:SysOpenDB WHERE ODB_SessionID = (SELECT DBINFO("sessionid") FROM SysTables WHERE TabID = 1) AND --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---