SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unable to connect sqlsrv php call stored procedure

    how to achieve php + sqlsrv + SP.

    Code PHP:
    $query = "{CALL call_user_name}";
     
    if ( ($res = sqlsrv_query( CONNSTR, $query)) )
    {
       do 
       {
          if ( sqlsrv_num_fields($res) ) // one way to check if results returned.
     
          {
             while( ($row = sqlsrv_fetch_array( $res, $type)) )
             {
                $data[] = $row; 
             }
          }else{echo "sqlsrv_num_fields not working";}
       } while ( sqlsrv_next_result($res) ) ;
       sqlsrv_free_stmt($res); // not essential, but good form if your script does lots of other stuff.
    }else{
           echo "SQL SRV QUERY NOT RAN ";die( print_r( sqlsrv_errors(), true));
    }

    Code PHP:
    Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -28 [code] => -28 [2] => The active result for the query contains no fields. [message] => The active result for the query contains no fields. ) )

  2. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    219
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    From a google search
    http://www.yiiframework.com/forum/in...turns-nothing/

    =>
    Hi All,

    I found a solution. The problem is when the stored procedure does not use 'SET NOCOUNT ON;'. All the operations in the stored procedure have results and maybe, the first one not have fields and it print something like '(1 row(s) affected)'.
    For example, I wrote a stored procedure with many insert commands and at the end, it printed OK or an error code. From my model, I ran the sql statement using queryAll method. For each insert mssql return result saying '(1 row(s) affected)' and no fields. I used 'SET NOCOUNT ON;' in the stored procedure and the only one result was OK or the error code.

    Thank you all,

    Diego
    Not tested but you may try.

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As per above suggestion, i just added this " SET NOCOUNT ON; " before the SELECT QUERY command. but still not working.

  4. #4
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also looks that thread specking with MSSQL function, here i am looking solution using sqlsrv. please share your exp.

  5. #5
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    problem fixed . i dont added any command in my stored procedure

    php sqlsrv stored procedure

    $serverName = "DB-HOST"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"DB-NAME", "UID"=>"DB-USERNAME", "PWD"=>"DB-PASSWORD");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    if( $conn ) {
    echo "Connection established.<br />";
    }else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
    }

    //Here my SP required one argument. so i passed one argument. this @param and stored procedure argument name should be same(case sensitive)
    $sql = " { call myspworks ( @param=? ) } ";
    $param1 = 'TABLE02-01';
    $params = array(array(&$param1, SQLSRV_PARAM_IN));
    $stmt = sqlsrv_prepare($conn,$sql,$params);

    if ($stmt===false) {
    // handle error
    print_r(sqlsrv_errors,true);
    }else{
    if (sqlsrv_execute($stmt)===false) {
    // handle error. This is where the error happens
    print_r(sqlsrv_errors,true);
    }
    else
    {
    $resultsetarr = array();
    while($row = sqlsrv_fetch_array($stmt)){
    $resultsetarr[] = $row;
    }
    print_r($resultArray);
    // success! It never gets here, though.
    }
    }


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •