CODEWITHSUNDEEP
mysqlshellstored-proceduresassociative-array
Divya P
Divya P

Reputation: 1

Associative array with mysql stored procedure in shell scripting

Here is my input table:

queue_id | queue_name|
----------------------
qid1     |  qname1   |
----------------------

My output should be

key      | value     |
----------------------
queueId  | qid1      |
queueName| qname1    |
----------------------

where as map[queue_id]=queueId map[queue_name]=queueName

Here is the script that I have


    #!/bin/bash
    keyPair[queue_name]="queueName"
    keyPair[queue_id]="queueId"
    .....
    CREATE PROCEDURE queue_migration()
    BEGIN
    DECLARE rowcount INT DEFAULT 0;
    DECLARE colcount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE col_name VARCHAR(255);
    DECLARE col_names CURSOR FOR
      SELECT column_name
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE table_name = 'table_name' AND table_schema = 'dbname'
      ORDER BY ordinal_position;
    Get the row count into rowCount
    Get the column count into colCount
    SET i = 0;
    WHILE i= colcount THEN
                    CLOSE col_names;
                    LEAVE the_loop;
               END IF;
               FETCH col_names INTO col_name;

               INSERT INTO logy SELECT CONCAT(\"See ${keyPair[\",col_name,\"]} here\");
               SET j

         = j + 1;
                END LOOP

     the_loop;
            SET i = i + 1;
    END WHILE;

I am able to see the col_name properly(queue_id and queue_name). But the mapping fails. When i do ${keyPair['queue_name'], I am getting queueName. But ${keyPair[col_name]} is empty. Suggestions please.

Upvotes: 0

Views: 695

Answers (1)

Rick James
Rick James

Reputation: 142208

In order to 'construct' a query in a Stored Routine, you need to prepare and execute it. No substitution occurs the way you are hoping. Something like:

SET @stmt = CONCAT( 'INSERT INTO logy SELECT CONCAT("See ${keyPair["',
                    col_name,
                    '"]} here');
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(Apologies if that is not quite correct.)

Upvotes: 0

Related Questions