Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?

You can run system hostname from a client but you can't make system calls from SP. I don't see it any of the global variables or statuses.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

I can see my hostname on my PC

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql>

This works in MySQL 5.x

SELECT variable_value INTO @local_hostname
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

The query works directly in the mysql client:

mysql> SELECT variable_value INTO @local_hostname
    -> FROM information_schema.global_variables
    -> WHERE variable_name = 'hostname';
Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

mysql>

Here is quick SP I wrote in MySQL 5.5.12 on my PC

DELIMITER $$

DROP PROCEDURE IF EXISTS `junk`.`ShowHostName` $$
CREATE PROCEDURE `junk`.`ShowHostName` ()
BEGIN

    SELECT variable_value INTO @local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';
    SELECT @local_hostname;

END $$

DELIMITER ;

Then run CALL junk.ShowHostName();

mysql> CALL junk.ShowHostName();
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here it is as a function

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetHostName` $$
CREATE FUNCTION `junk`.`GetHostName` () RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN

    DECLARE local_hostname VARCHAR(64);

    SELECT variable_value INTO local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';

    RETURN local_hostname;

END $$

DELIMITER ;

Now, just SELECT it:

mysql> SELECT junk.GetHostName();
+--------------------+
| junk.GetHostName() |
+--------------------+
| LW-REDWARDS2       |
+--------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

share|improve this answer
    
facepalm I looked right over it. Thx. –  atxdba Jan 18 '12 at 1:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.