Executing stored procedure in php laravel

Question

I am using Laravel 7 and I want to show the results from the stored procedure. My code is given below. When I execute the stored procedure with parameters in SQL Server, it's showing data. But in Laravel application data is not showing.

Please, help me to find the problem.

$serverName = env("DB_HOST");
$connectionInfo = array( "Database"=>env("DB_DATABASE"), "UID"=>env("DB_USERNAME"), "PWD"=>env("DB_PASSWORD") );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
  die( print_r( sqlsrv_errors(), true));
}
$tsql= " EXEC USP_Daily_TA_Punching_Detailswith_OT '$employee','$datefrom','$dateto'";
$getResults= sqlsrv_query($conn, $tsql);
      $data = array();
      if ($getResults == FALSE)
      {
      echo '';
      }
      else {
       //$data[] ='';
      do
      {
        while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC))
        {
         $data[] = $row;
        }
      }
       while (sqlsrv_next_result($getResults));
      }
      if(count($data)>0){
       sqlsrv_free_stmt($getResults);
      $total_row = count($data);
     }

Does this answer your question? [How to execute Stored Procedure from Laravel](https://stackoverflow.com/questions/34497063/how-to-execute-stored-procedure-from-laravel) — maiorano84, Jun 10 '20 at 06:35
Well, this is not exactly `Laravel` code, because you are using _sqlsrv__ functions from PHP Driver for SQL Server. Do you want to fix this code without using a Laravel approach? — Zhorov, Jun 10 '20 at 06:37
i tried with laravel, but its showing error "SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '000010993'. (SQL: exec USP_Daily_TA_Punching_Detailswith_OT('000010993','2020-06-01','2020-06-10'))" — ManUtd, Jun 10 '20 at 06:48

Zhorov · Accepted Answer · 2020-06-10T11:07:41.720

1

Always try to use parameters in your statements to prevent possible SQL injection issues. As an additional note, use unambiguous date format, when you pass date values to SQL Server:

Example using PHP Driver for SQL Server:

<?php
// Connection
$serverName = env("DB_HOST");
$connectionInfo = array(
    "Database"=>env("DB_DATABASE"), 
    "UID"=>env("DB_USERNAME"), 
    "PWD"=>env("DB_PASSWORD")
);
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

// Statement
$employee = '000010993';
$datefrom = '20200601'; 
$dateto   = '20200610';
$tsql     = "EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?";
$params   = array($employee, $datefrom, $dateto);
$getResults = sqlsrv_query($conn, $tsql, $params);
if ($getResults === false) {
    die(print_r(sqlsrv_errors(), true));
}   

// Results
$data = array();
do {
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        $data[] = $row;
    }
} while (sqlsrv_next_result($getResults));

// End
sqlsrv_free_stmt($getResults);
sqlsrv_close($conn);
$total_row = count($data);
?>

Example using Laravel:

<?php

...
$employee = '000010993';
$datefrom = '20200601'; 
$dateto   = '20200610';
DB::select("SET NOCOUNT ON; EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?", array($employee, $datefrom, $dateto));

...
?>

edited Jun 10 '20 at 11:07

answered Jun 10 '20 at 07:06

Zhorov

28,486
6
27
52

Using PHP, when i print $data, there is no data..showing [] Using laravel, its showing "SQLSTATE[IMSSP]: The active result for the query contains no fields. (SQL: EXEC USP_Daily_TA_Punching_Detailswith_OT 000010993, 2020-06-01, 2020-06-10)" – ManUtd Jun 10 '20 at 07:22
@ManUtd Can you change the stored procedure and place `SET NOCOUNT ON` as first line in the procedure? – Zhorov Jun 10 '20 at 07:24
@ManUtd Or execurte `sqlsrv_next_result($getResults)` to skip possible information messages: `sqlsrv_next_result($getResults); while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {$data[] = $row;}`. – Zhorov Jun 10 '20 at 08:01
i think problem with stored procedure, when i change to some test stored procedure, its working, but this stored procedure not showing data. how can i see the errors in stored procedure in php. when i execute stored procedure in sql server i am getting data, but here in my application no data. – ManUtd Jun 10 '20 at 08:11
when i print print_r(sqlsrv_errors(), true), its showing Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]2020-06-01 [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]2020-06-01 ) ) whats that means? – ManUtd Jun 10 '20 at 08:20
@ManUtd How do you pass date values. Use unambiguous date format ('20200601'). – Zhorov Jun 10 '20 at 09:12
1

By using laravel, i include SET NOCOUNT ON at beginning. DB::select("SET NOCOUNT ON; EXEC ......"). Now its working!.. Thanku @Zhorov – ManUtd Jun 10 '20 at 11:08

score 0 · Answer 2 · edited Jun 10 '20 at 06:39

0

Try the below to call the store procedure in laravel

DB::select("call USP_Daily_TA_Punching_Detailswith_OT('".$employee."','".$datefrom."','".$dateto."')");

edited Jun 10 '20 at 06:39

Dale K

25,246
15
42
71

answered Jun 10 '20 at 06:34

Birendra Singh

93
12

its showing "SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '000010993'. (SQL: call USP_Daily_TA_Punching_Detailswith_OT('000010993','2020-06-01','2020-06-10'))" – ManUtd Jun 10 '20 at 06:41
can you try without quotes for first parameter as it is showing error here. – Birendra Singh Jun 10 '20 at 06:45
same error "SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '000010993'. (SQL: call USP_Daily_TA_Punching_Detailswith_OT(000010993,'2020-06-01','2020-06-10'))" – ManUtd Jun 10 '20 at 06:51

Executing stored procedure in php laravel

2 Answers2

Linked