Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread current database name
Wed, Apr 4 2007 12:27 PMPermanent Link

Andrey Lesitsyn
Hello.

EDB 1.0.1b1

How I can get the current database name from pure SQL?
Wed, Apr 4 2007 4:59 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Andrey,

<< How I can get the current database name from pure SQL? >>

What do you mean by "current database name" ?  Do you mean the current
database name being used in a TEDBQuery component ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Apr 5 2007 8:06 AMPermanent Link

Andrey Lesitsyn
Tim,

I mean:  how I can access the current database name from PURE SQL.
May be the next example helps to explain better:

CREATE PROCEDURE MY_BACKUPDATABASE (IN BackupDirName VARCHAR(128))
BEGIN
 DECLARE xDatabaseName VARCHAR(30);
 DECLARE xBackupName VARCHAR(30);
 DECLARE xBackupDirName VARCHAR(128);
 DECLARE Cmd varchar(512);

 SET xDatabaseName = 'RBEDB'; /* I need to get the current database name here */
/*SET xDatabaseName = CURRENT_DATABASENAME();*/
/*--------------------------------------------------*/

 SET xBackupName = xDatabaseName + '_Backup';
 SET xBackupDirName = '''' + BackupDirName + '''';

 SET Cmd = 'BACKUP DATABASE "' + xDatabaseName +
   '" AS "' + xBackupName +
   '" TO ' + xBackupDirName +
   ' DESCRIPTION ''xxx'' COMPRESSION 6 INCLUDE CATALOG';

 EXECUTE IMMEDIATE Cmd;
END

------------------------------------------------------
Problem #2
-----------
EDB such has useful SQL functions as "CURRENT_USER()" etc.
Very often the next SQL function are alsow useful:
 -- CURRENT_DATABASENAME()
 -- CURRENT_VERSION()

------------------------------------------------------
Problem #3
-----------
1. select CURRENT_DATE() from DUMMY -- its OK (in Oracle)
2. select CURRENT_DATE() from DUAL -- its OK (in ASA)
3. select CURRENT_DATE() from ??? Configuration.DataBases where ??? -- its a problem
The third select returns more than 1 record. Its bad.

What are you can to advise ?
May be You can to include "DUMMY" table in the "Configuration" or "Information" ?
------------------------------------------------------

Best regards,
Andrey
Thu, Apr 5 2007 12:14 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Andrey,

<< I mean:  how I can access the current database name from PURE SQL. May be
the next example helps to explain better: >>

Yes, you can't get the current database name via a function at this time.
However, I will add it to the list of SQL/PSM functions that we plan on
adding in upcoming versions (CURRENT_SESSIONNAME() and CURRENT_SESSIONID()
are two others that we plan on adding, for example).

<< 1. select CURRENT_DATE() from DUMMY -- its OK (in Oracle)
2. select CURRENT_DATE() from DUAL -- its OK (in ASA)
3. select CURRENT_DATE() from ??? Configuration.DataBases where ??? -- its
a problem
The third select returns more than 1 record. Its bad. >>

Are you executing this from within a stored procedure ?  If so, then just
use:

SET MyVariable = CURRENT_DATE();

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Apr 9 2007 5:02 AMPermanent Link

Andrey Lesitsyn
Tim,

I am executing "select CURRENT_DATE() as D from ???" from TxxxQuery.
Then I need to access MyQuery.FieldByName('D').As...
This i only one example, but the problem is: "from DUMMY", "from DUAL" etc.

Do you uderstand what I mean?

--
Andrey
Mon, Apr 9 2007 7:23 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Andrey,

<< I am executing "select CURRENT_DATE() as D from ???" from TxxxQuery.
Then I need to access MyQuery.FieldByName('D').As...
This i only one example, but the problem is: "from DUMMY", "from DUAL" etc.
>>

Okay, I was under the impression that you were calling it from within a
stored procedure.  For now, you'll simply have to use a one-row table to get
what you want until EDB adds support for INTO and output parameters for
dynamic queries.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image