Calling stored procedure with parameter Cakephp 4

1

I’m trying to call a stored procedure which works fine (phpMyAdmin).

In the controller:

$segments = $connection
->execute('CALL SegmentsPerArea(@area_id)')

// ->execute(‘CALL SegmentsPerArea(:area_id)’,[‘area_id’=>@area_id])
->fetchAll(‘assoc’);

echo ‘

’; print_r($segments); echo ‘
’ shows I’m not getting data back to the controller.

I’v added

SELECT ‘Called segments per area’ AS Output;
SELECT area_id AS Parameter;

to the stored procedure, I’m getting Output back in the controller, not the Parameter.

So, I think I’m not able to correctly pass the area_id to the stored procedure (tried 2 ways). Googling doesn’t give much info, and if, solutions are cakephp 2 and 3. Searching the cookbook with ‘call stored procedure’ gives no hits.

What’s the solution?

2
  • Overlooked the ‘@’, replaced with ‘$’
  • The ’ ‘CALL’ has to be ’ "CALL’
  • There’s something not ok with delimiters in stored procedure?

I only got Output in controller with this select sequence in stored procedure:
SELECT ‘Called segments per area’ AS Output;
SELECT area_id AS Parameter;

I only get Parameter in controller with this sequence:
SELECT area_id AS Parameter;
SELECT ‘Called segments per area’ AS Output;

Looks like only the first select in stored procedure is executed. The same effect when not setting the delimiters in sql. What I’m not getting is that all selects run when executing the stored procedure in phpmyadmin.

3

Just using 1 select in the stored procedure gives me the wanted data (Only works with one result set?).