CODEWITHSUNDEEP
stored-proceduresmariadb
OlammieConcept
OlammieConcept

Reputation: 21

MariaDB Insert Stored Procedure With Parameter

I migrated my company database from MySQL to another hosting firm not knowing the hosting firm uses MariaDB, upon try to create my stored procedure with my IN parameter, MariaDB is seeing the parameter as a columnn. See the stored procedure code below with the error :

    CREATE  PROCEDURE ADD_WITHDRAWAL_A(IN withdrawalcode_p VARCHAR(25), IN id_p VARCHAR(8), IN amount_p VARCHAR(12), IN datewithdrawn_p VARCHAR(35), IN approved_p VARCHAR(8))

     START TRANSACTION;

     INSERT INTO Withdrawals(WithdrawalCode, IDD, Amount, DateWithdrawn, Approved) 
     VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

     UPDATE account SET AccountBalance = AccountBalance - amount_p WHERE IDD = id_p LIMIT 1;

     COMMIT;

    ***** AFTER RUNNING THE ABOVE CODE, MARIADB GAVE THIS ERROR :

    Error
    SQL query:

    INSERT INTO WithdrawalRequest( WithdrawalCode, IDD, Amount, DateWithdrawn, Approved ) 
    VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

MySQL said: Documentation

#1054 - Unknown column 'withdrawalcode_p' in 'field list'

The column name is WithdrawalCode and not 'withdrawalcode_p', 'withdrawalcode_p' is a parameter passed in to the stored procedure but the server is seeing it as a field name. I spoke with the hosting firm and they said their database is MariaDB and not MySQL. This same code worked in MySQL server.

I will appreciate any useful help rendered here.

Upvotes: 1

Views: 17321

Answers (1)

elenst
elenst

Reputation: 3987

You forgot to set non-default delimiters and to wrap the procedure body into BEGIN/END, which is necessary since it has more than one statement.

What happens in your case is that the procedure created with body START TRANSACTION, and the rest is considered to be a set of ordinary statements. Instead, try

DELIMITER $$

CREATE  PROCEDURE ADD_WITHDRAWAL_A(IN withdrawalcode_p VARCHAR(25), IN id_p VARCHAR(8), IN amount_p VARCHAR(12), IN datewithdrawn_p VARCHAR(35), IN approved_p VARCHAR(8))
BEGIN

     START TRANSACTION;

     INSERT INTO Withdrawals(WithdrawalCode, IDD, Amount, DateWithdrawn, Approved) 
     VALUES (withdrawalcode_p, id_p, amount_p, datewithdrawn_p, approved_p);

     UPDATE account SET AccountBalance = AccountBalance - amount_p WHERE IDD = id_p LIMIT 1;

     COMMIT;

END $$

DELIMITER ;

Upvotes: 7

Related Questions