[FIXED] Using stored procedures with cakephp 3.x

Issue

I am trying to use a SQL Server stored procedure with cakephp 3.x, but I get a database error.

This is my controller:

use Cake\Datasource\ConnectionManager;

if($this->request->is('post')){
    $data = $this->request->getData();
    $param1 = $data['playId'];
    $param2 = $data['cardboard'];

    if(is_numeric($param1) && is_numeric($param2)){        
        $param1 = $data['playId'];
        $param2 = $data['cardboard'];

        $conn = ConnectionManager::get('ssql');

        $requested = $conn->query(
            'call searchPrem(?, ?)', 
            [$param1, $param2]
        )->fetchAll('assoc');


        //$exec = debug($requested);
    }
       echo json_encode( $requested );
}

I get this SQL error:

Error: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '@P1'.

How can I execute a stored procedure and retrieve the result data?

Solution

I found the solution:

$conn = ConnectionManager::get('ssql');
$sql = $conn->execute("yourfunction @id = $playId, @type = $type")->fetchAll('assoc'); 

Answered By - Leoh