SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Procedure Transaction Code returns "Query OK, 0 rows affected"

    My procedure is calling a simple transaction for updating two tables. It is successfully saved as a procedure, but when called, it doesn't execute the code. It simply returns "Query OK, 0 rows affected". Running in MySQL 5.6.10.

    Am I missing something in my syntax? Perhaps it is error in how it is being executed?

    Code:
    CREATE PROCEDURE transact_account (amount DECIMAL(9,2), description VARCHAR(200), account_type VARCHAR(30), transaction_type VARCHAR(20))
    
    BEGIN
    
    START TRANSACTION;
    
    INSERT INTO transactions
    SELECT null, now(), amount, description, account_id FROM accounts WHERE type = account_type;
    
    IF transaction_type = 'credit'
    
    THEN
    UPDATE accounts
    SET balance = balance + amount
    WHERE type = account_type;
    
    ELSEIF transaction_type = 'debit'
    
    THEN
    UPDATE accounts
    SET balance = balance - amount
    WHERE type = account_type;
    
    END IF;
    
    COMMIT;
    
    END
    
    CALL PROCEDURE transact_account (1000, 'freelance', 'checkings', 'credit');

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,266
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Have you checked to see if there are any accounts like that? Are you sure the type is spelled right? I've heard of checking accounts, but never checkings accounts.

    SELECT null, now(), amount, description, account_id FROM accounts WHERE type = 'checkings';
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out. I had to close my terminal window for it to update the procedure code. But yeah, I do need to fix the wording on that one. Thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •