1

I have the following procedure

DROP PROCEDURE IF EXISTS find_transactions_past_5_days;

DELIMITER $$
    CREATE PROCEDURE find_transactions_past_5_days (IN input_date DATE) 
        BEGIN
            SELECT *
            FROM customer c
            INNER JOIN customer_account ca ON c.customer_id = ca.customer_id
            INNER JOIN account a ON ca.account_id = a.account_id
            INNER JOIN transaction t ON a.account_id = t.account_id;
        END $$
DELIMITER ;

CALL find_transactions_past_5_days ("2002-05-22");

How when I call it it is giving me the error:

Static analysis:

1 errors were found during analysis.

Missing expression. (near "ON" at position 25)
SQL query: Copy Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation

#2014 - Commands out of sync; you can't run this command now

, I've tried executing the query without the procedure working fine. However, I cant see what I've done wrong with the stored procedure everything seems to conform with the maria DB documentation. Does anybody know the cause of this issue?

I read online sometimes it if a column name cannot be found, however like I mentioned I did try the select query on its own, which gave me a result with no errors.

Software used: XAMPP Mysql

9
  • I have the following procedure ?? You do not use input parameter - what's the reason? it is giving me the error You can see that the problematic stament is not a part of the procedure. Check your client software, access library/framework and connector for the issue source.
    – Akina
    Dec 16, 2021 at 13:31
  • Incorrect datetime value: '2021-09-17 24:23:11' for column transaction.transaction_date
    – Akina
    Dec 16, 2021 at 13:35
  • Issue not reproduced. dbfiddle.uk/…
    – Akina
    Dec 16, 2021 at 13:37
  • @Akina what seems to be the problem there, is this not the correct format? Dec 16, 2021 at 13:52
  • @Akina are you saying the issue is not with the code but with phpmyadmin maybe? Dec 16, 2021 at 13:56

1 Answer 1

0

See https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html#:~:text=If%20you%20get%20Commands%20out,you%20have%20called%20mysql_free_result()%20.

That page says that you are fetching the results from the Stored Proc incorrectly. An SP returns multiple "resultsets" and you need extra client code to look in each resultset to get the rows that were produced.

It looks like your SP has only 1 resultset with some number of rows.

4
  • I am only using this in PHPMyAdmin with XAMPP, so where would i put those commands? Dec 16, 2021 at 23:00
  • In the PHP code after processing the results of the select. Search for examples of that command.
    – Rick James
    Dec 17, 2021 at 2:28
  • I never use php code, iam just trying to get it to run in mysql and xampp in the "SQL" section Dec 17, 2021 at 15:38
  • OK. Other APIs have other have similar requirements. Personally, I don't write an SP to do something simple like a plain SELECT.
    – Rick James
    Dec 17, 2021 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.