MySQL – Using ‘system pwd’ Command with CONCAT() Function

concatMySQL

How can I use 'system pwd' command with MySql CONCAT() function? I tried

select concat('test', system pwd);

gives me

ERROR 1583 (42000): Incorrect parameters in the call to native function 'concat'

Is there any way? My database version 5.1

Best Answer

system is not a recognized MySQL server command. This will only work on the MySQL command line interface "mysql". With a command like this:

mysql> system pwd
/tmp

you will get the client's current directory, nothing to do with the server. You cannot use that information on server side, for executing queries. If you want to concat the pwd with something else, use a script at client side (bash, php). For example:

$ echo $(pwd)test
/tmptest
$ mysql  -e "SELECT concat('$(pwd)', 'test')" # there is not reason to do this at all
+------------------------+
| concat('/tmp', 'test') |
+------------------------+
| /tmptest               |
+------------------------+

While there is a custom non-standard UDF library to make MySQL server interact with the operating system, I do not see a valid use in your case.

If you want to know the directory where MySQL is installed, where the datafiles are, etc., you can execute:

mysql> SHOW GLOBAL VARIABLES like '%dir';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| basedir                   | /usr/local/mysql         |
| datadir                   | /usr/local/mysql/data/   |
...

ALTERNATIVE

What you need is to simulate the BENCHMARK function yourself.

Here is some sample code for you to try

drop database if exists mybmark;
create database mybmark;
use mybmark
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $$
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN

    DECLARE answer INT;

    SELECT 1+2 INTO answer;

END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `mybmark`.`mybenchmark` $$
CREATE PROCEDURE `mybmark`.`mybenchmark` (loop_count INT,expr varchar(128))
BEGIN
    DECLARE dt1,dt2,dtdiff,ndx INT;

    SET dt1 = UNIX_TIMESTAMP();
    SET ndx = loop_count;
    SET @sql = expr;
    PREPARE stmt FROM @sql;
    WHILE ndx > 0 DO
        EXECUTE stmt;
        SET ndx = ndx - 1;
    END WHILE;
    DEALLOCATE PREPARE stmt;
    SET dt2 = UNIX_TIMESTAMP();
    SET dtdiff = dt2 - dt1;

    SELECT dt1,dt2,dtdiff;

END $$
DELIMITER ;

To benchmark the testproc procedure, just pass the call to the testproc procedure as a parameter. For example to call the testproc procedure 1,000,000 times, do this:

mysql> call mybmark.mybenchmark(1000000,'CALL mybmark.testproc()');
+------------+------------+--------+
| dt1        | dt2        | dtdiff |
+------------+------------+--------+
| 1333474085 | 1333474102 |     17 |
+------------+------------+--------+
1 row in set (16.80 sec)

Query OK, 0 rows affected (16.80 sec)

mysql>

Give it a Try !!!

Best Answer

Related Solutions

Mysql – How to install MySQL 5.1 on Ubuntu 10.10

MySQL – How to Use ‘select benchmark’ Command on a Procedure

ALTERNATIVE

Related Question