CODEWITHSUNDEEP
mysqlstored-proceduressql-insertdatabase-table
phemor
phemor

Reputation: 299

calling stored procedure inserts NULLs into all columns MySQL

I've created a stored procedure in MySQL to help debug something, however when i call the stored procedure it inserts NULL values into my table for all columns

Table

CREATE TABLE `EncryptionDebug` (
`ObservedValue` mediumtext COLLATE utf8_unicode_ci,
`PublicKey` mediumtext COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Stored procedure

DELIMITER //

CREATE PROCEDURE `EncryptionDebug`(IN `ObservedValue` MEDIUMTEXT, IN     `PublicKey` MEDIUMTEXT)
MODIFIES SQL DATA
BEGIN
    DECLARE `ObservedValue` MEDIUMTEXT;
    DECLARE `PublicKey` MEDIUMTEXT;

        INSERT INTO `EncryptionDebug` (`ObservedValue`,`PublicKey`) VALUES     (ObservedValue,PublicKey);
END//

DELIMITER ;

Calling the procedure like so

CALL EncryptionDebug('test','test');

Returns NULL for both columns when i SELECT * FROM EncryptionDebug

Thanks

Upvotes: 1

Views: 154

Answers (1)

wchiquito
wchiquito

Reputation: 16551

From the documentation:

13.6.4.2 Local Variable Scope and Resolution

...

A local variable should not have the same name as a table column.

...

One option to try:

DELIMITER //

CREATE PROCEDURE `EncryptionDebug1`(
    `ObservedValue` MEDIUMTEXT,
    `PublicKey` MEDIUMTEXT
)
MODIFIES SQL DATA
BEGIN
    /*
    DECLARE `ObservedValue` MEDIUMTEXT;
    DECLARE `PublicKey` MEDIUMTEXT;
    */
    INSERT INTO `EncryptionDebug` (`ObservedValue`, `PublicKey`)
    VALUES
    (`ObservedValue`, `PublicKey`);
END//

DELIMITER ;

SQL Fiddle demo

Recommendation: Avoid naming parameters and variables as columns of your tables, here the cause: SQL Fiddle.

Upvotes: 0

Related Questions