1

I'm using the SQLDataProvider to call a procedure that returns a list of records but I can't get it to work

what i'm trying is:

 $dataProvider = new SqlDataProvider([
            'sql' => "CALL ErroresEnHoras(:project_id, :fecha_iniciop, :fecha_finp, :proyecto)",
            'params' =>
            [
                ':project_id' => ($this->proyectoid == '' || is_null($this->proyectoid)) ? 0 : $this->proyectoid,
                ':fecha_iniciop' => ($filtrosModel->fecha_inicio == '' ? null : $filtrosModel->fecha_inicio ),
                ':fecha_finp' => ($filtrosModel->fecha_fin == '' ? null : $filtrosModel->fecha_fin ),
                ':proyecto' => ($filtrosModel->proyecto == '' ? null : $filtrosModel->proyecto)
            ],
            'totalCount' => 335,
            'pagination' => [
                'pageSize' => 10,
            ],
        ]);

the error:

SQLSTATE[42000]: Syntax error or access violation: 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 'LIMIT 10' at line 1 The SQL being executed was: CALL ErroresEnHoras(0, NULL, NULL, NULL) LIMIT 10

I know why I get the error, because the query is going wrong. Is there another way to do it?

Thank you very much in advance

Nicolas Morales Galindo
  • 21
  • 3
  • 1
    the pagination uses `LIMIT`, this works fine with `UPDATE`/ `SELECT` / `DELETE` queries - but you are calling a stored procedure, which does not support that. depending on what your stored procedure does, you could switch to a view and select from there – Ste Bächler Jan 09 '20 at 14:11
  • If number of records returned by procedure is fixed and small, you may try to query them all and use `ArrayDataProvider` - it should work fine as long you don't have work wit thousands of records. – rob006 Jan 09 '20 at 14:14

1 Answers1

0

Your mistake is, that stored procedures don't return anything really, while stored functions return only one value. So there are not direct solution, not even with ArrayDataProvider.

The workaround is to use an intermediate table for the communication. You could pass a unique query ID to your procedure and you insert the result in the body of the procedure into intermediate table with the query ID.

Use the data provider on the intermediate table. After the processing is ready you can remove the records with the query ID from the intermediate table.

Tibor Nagy
  • 1,185
  • 1
  • 15
  • 28
  • This is not true. It is true that stored procedures doesn't have return value as functions but they can return a resultset. See this example https://www.db-fiddle.com/f/2vhVQUUUEsVHZrnnJePf14/0 – Michal Hynčica Jan 10 '20 at 13:36
  • @MichalHynčica you are right, but I do not see any solution to use the result set in a Yii SQLDataProvider. – Tibor Nagy Jan 10 '20 at 18:01