0
create function transfer (account_source int, account_dest int, amount decimal) 
returns BOOLEAN

    BEGIN

    DECLARE currency_source VARCHAR(20);
    DECLARE currency_dest VARCHAR(20);
    DECLARE current_balance DECIMAL;
    DECLARE new_amount DECIMAL;

    select balance into current_balance
    from accounts
    where number = account_source;

    IF amount > current_balance THEN
        return false;
    END IF;

    select currency into currency_source
    from accounts
    where number = account_source;

    select currency into currency_dest
    from accounts
    where number = account_dest;

    IF STRCMP(currency_source,'dollar') = 0 THEN
        IF STRCMP(currency_dest,'euro') = 0 THEN
            set new_amount := amount / 1.23;
        ELSE IF STRCMP(currency_dest,'lbp') = 0 THEN
            set new_amount := amount / 1500;
        END IF;
    end if;

    IF STRCMP(currency_source,'euro') = 0 THEN
        IF STRCMP(currency_dest,'dollar') = 0 THEN
            set new_amount := amount * 1.23;
        ELSE IF STRCMP(currency_dest,'lbp') = 0 THEN
            set new_amount := (amount * 1.23) * 1500;
        END IF;
    end if;

    IF STRCMP(currency_source,'lbp') = 0 THEN
        IF STRCMP(currency_dest,'dollar') = 0 THEN
            set new_amount := amount / 1500;
        ELSE IF STRCMP(currency_dest,'euro') = 0 THEN
            set new_amount := (amount / 1.23) / 1500;
        END IF;
    END IF;

    update accounts
    set balance = balance - amount
    where number = account_source;

    update accounts
    set balance = balance + new_amount
    where number = account_dest;

    return true;

    END;

Trying to create this function is giving an error #1064 at line 1, so i can't guess where is the actual mistake i did.

i created 'withdraw' function before it, containing almost same code and it worked, the only difference between transfer function and withdraw function is declaration of varchar(20) type at its begining.

Any help would be appreciated...

moe
  • 23
  • 3
  • Is the exact error message confidential? – Shadow Apr 07 '18 at 21:33
  • i am using phpmyadmin, so as usual the exact error is not mentioned, they just say: check the manual that corresponds to... – moe Apr 07 '18 at 21:48
  • And then there is a part that says ... near ... and there is some sql code. We need that code. – Shadow Apr 07 '18 at 21:50
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 6 – moe Apr 07 '18 at 22:07
  • Note: line 6 = DECLARE currency_source VARCHAR(20) in my code – moe Apr 07 '18 at 22:13
  • Mr. Shadow, adding delimiters did not solve the problem, so why my question is a duplicate ? – moe Apr 07 '18 at 22:27

0 Answers0