doubi4814
2014-04-25 08:53
浏览 56
已采纳

PHP sqlsrv存储过程有时没有返回结果

I have this strange problem with PHP Sqlsrv calling a store procedure. Sometimes it return result, sometimes not. When debugging with netbean php, it popup Socket exception occurred. When the browser made the request, the connection get reset and does not return any error.

I am using the sqlsrv library of codeigniter

function result_object()
    {
        if (count($this->result_object) > 0)
        {
            return $this->result_object;
        }

        // In the event that query caching is on the result_id variable
        // will return FALSE since there isn't a valid SQL resource so
        // we'll simply return an empty array.
        if ($this->result_id === FALSE OR $this->num_rows() == 0)
        {
            return array();
        }

        $this->_data_seek(0);
        while ($row = $this->_fetch_object())
        {
            $this->result_object[] = $row;
        }

        return $this->result_object;
    }

function result($type = 'object')
    {
        if ($type == 'array') return $this->result_array();
        else if ($type == 'object') return $this->result_object();
        else return $this->custom_result_object($type);
    }

$result = null;
        do{
            $result = $this->result(); //error happen on this line.
              log_message("error", print_r($result,true)); // if added this line, the result is okay most of the time
            }while( sqlsrv_next_result($this->result_id));

EDIT : Okay, this is getting more strange, if I add this line after $result then it works MOST of the TIME . log_message("error", print_r($result,true));

EDIT 2 : Okay, just anything to do with logging message into the file. Such as like this will work also. Seem to do with output buffering ? log_message("error", print_r("abc123",true));

EDIT 3 :

'hostname'  => '192.168.8.165',
'username'  => 'xxx',
'password'  => 'xxx',
'database'  => 'dbname',
'dbdriver'  => 'sqlsrv',
'dbprefix'  => '',
'pconnect'  => FALSE,
'db_debug'  => TRUE,
'cache_on'  => FALSE,
'cachedir'  => '',
'char_set'  => 'utf8',
'dbcollat'  => 'utf8_unicode_ci',
'swap_pre'  => '',
'autoinit'  => TRUE,
'stricton'  => FALSE
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtrb96410 2014-04-29 03:34
    已采纳

    Finally, it's the CURSOR issue of the sqlsrv_driver.php , in that file, find this SQLSRV_CURSOR_STATIC and replace it with SQLSRV_CURSOR_CLIENT_BUFFERED . Now it return result without having to use that log_message("error", "abc123"); And even execute 1000 time of my SP there's no problem.

    Found solution from https://stackoverflow.com/a/16796421/660810

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • drnxnbf199049 2014-04-28 18:17

    I am really not that familiar with codeigniter library for PHP to connect to SQL Server. Here is a article from a developer that was able to get it to work.

    http://www.manjustudio.com/2013/01/09/codeigniter-mssql-sqlsrv/comment-page-1/

    However, connecting to SQL server database uses the tabular data stream format at the lowest level.

    enter image description here

    Regardless of network interface protocol or higher level libraries like ODBC or ADO, all traffic goes to the server in TDS format.

    One way to debug this issue.

    1 - Write TSQL test code to call your stored procedure many times (10K). This call can be made right in a SSMS query window. Do you get any unexpected failures?

    If so, it is a TSQL coding issue.

    2 - Write PHP test code to call your stored procedure many times (10K). Do you get any unexpected failures?

    If so, it is a PHP coding issue.

    When trying the above two issues, if the error does not occur, then it will be harder to find.

    Here are some things to check.

    A - How reliable is the network connection from the web server to the sql server?

    B - Are there any know issues for the codeigniter library? Search forums.

    C - I did notice that the native client might need to be installed to use the library. Is the client and server libraries the same release level?

    D - Is the SQL Server database on the current service release? If not, update to current SP or CU.

    E - Create a server side trace to capture all the traffic for this call. See if you can capture the exact call that breaks the camels back.

    F - Check the logs (SQL, IIS or APACHE, WINDOWS or UNIX) for any issues. Make sure that the failure is not due to latent system issue.

    In short, hopefully using the above suggestions will narrow down your issue to a certain data set or certain section of your system that is causing the problem.

    Please write back with more detailed information after you done some investigation on your part.

    • John The Crafty DBA
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题