CODEWITHSUNDEEP
mysqlperlstored-procedures
paper.plane
paper.plane

Reputation: 1197

Facing issue while creating mysql stored procedure using perl script

sub callSP()
{
    my $db_handle = new MP::ATCA::SQLExec(
                                         MYSQL_DB,
                                         MYSQL_PORT,
                                         AUTO_COMMIT
                                        );
   if (!defined($db_handle))
   {
       # Connection/Validation error
       return 1;
   }

   my $sqlStmt = qq(DROP PROCEDURE IF EXISTS xyz);
   my $errStr = $db_handle->Execute($sqlStmt);
   if ($errStr ne "")
   {
     print("DROP PROCEDURE failed\n");
     return 0;
   }

   $sqlStmt = qq(CREATE PROCEDURE xyz()
    BEGIN

    update myTable set
    A=REPLACE(A, '\\', ''),
    B=REPLACE(B, '\\\\', ''),
    C=REPLACE(C, '\\\\', '')
    where A LIKE '["[%';

    commit;
END);
   print $sqlStmt . "\n";
   $errStr = $db_handle->Execute($sqlStmt);
   if ($errStr ne "")
   {
     print("CREATE PROCEDURE failed\n");
     return 0;
   }

   $sqlStmt = "CALL xyz()";
   $errStr = $db_handle->Execute($sqlStmt);
   if ($errStr ne "")
   {
     print("EXECUTE PROCEDURE failed\n");
     return 0;
   }
}

The above function drops stored procedure xyz if exists then creates it and executes the same. The stored procedure has only one update statement. But the procedure creation is failing for me. However the fault seems to be in the line A=REPLACE(A, '\\', ''), which is a part of the update statement. If I remove this line then everything works fine.

The intention of the update statement is to remove \(backslash) and \\(double backslashes) from the values of the columns A and B,C respectively.

However if I copy the same definition from the $sqlStmt = qq(procedure_definition); from the above perl function to a text file say xyz.txt and do a mysql > source xyz.txt on my mysql prompt then also it gets created successfully and I am able to execute it. But I need to create the procedure from perl script. Please help.

Upvotes: 0

Views: 80

Answers (1)

ThisSuitIsBlackNot
ThisSuitIsBlackNot

Reputation: 24073

qq{} interpolates backslash escape sequences; \\ is an escape sequence that generates a single backslash.

So this:

qq{A=REPLACE(A, '\\', ''),}

generates the string:

A=REPLACE(A, '\', ''),

MySQL also interprets backslash escape sequences, so the second argument to REPLACE is ', ​ (single quote, comma, space). This is obviously not what you want.

If you use qq{}, you need to escape all backslashes:

$sqlStmt = qq(
    CREATE PROCEDURE xyz()
    BEGIN

    update myTable set
    A=REPLACE(A, '\\\\', ''),
    B=REPLACE(B, '\\\\\\\\', ''),
    C=REPLACE(C, '\\\\\\\\', '')
    where A LIKE '["[%';

    commit;
    END
);

But it would be better to use a here-doc, which does no interpolation:

$sqlStmt = <<'SQL';
    CREATE PROCEDURE xyz()
    BEGIN

    update myTable set
    A=REPLACE(A, '\\', ''),
    B=REPLACE(B, '\\\\', ''),
    C=REPLACE(C, '\\\\', '')
    where A LIKE '["[%';

    commit;
    END
SQL

Upvotes: 2

Related Questions