分享方式:


步驟 4:使用 PHP 復原連線 SQL

下載 PHP 驅動程式

示範程式經過設計,會使嘗試連接期間的暫時性錯誤導致重試。 (以前置詞 '08'開頭的暫時性錯誤碼,如本附錄所列。) 但查詢命令期間發生的暫時性錯誤,會導致程式在重試查詢命令之前就捨棄連線,並建立新連線。 我們不建議或不鼓勵這種設計選擇。 示範程式會說明一些您可使用的設計彈性。

程式碼範例中大部分的內容都是攔截例外狀況的邏輯。

sqlsrv_query() 函式可用來擷取對 SQL Database 執行之查詢的結果集。 這個函式基本上會接受任何查詢和連線物件並傳回結果集,其可以使用 cursor.fetchone() \(英文\) 來反覆查詢。


    <?php
        // Variables to tune the retry logic.
        $connectionTimeoutSeconds = 30;  // Default of 15 seconds is too short over the Internet, sometimes.
        $maxCountTriesConnectAndQuery = 3;  // You can adjust the various retry count values.
        $secondsBetweenRetries = 4;  // Simple retry strategy.
        $errNo = 0;
        $serverName = "tcp:yourdatabase.database.windows.net,1433";
        $connectionOptions = array("Database"=>"AdventureWorks",
           "Uid"=>"yourusername", "PWD"=>"yourpassword", "LoginTimeout" => $connectionTimeoutSeconds);
        $conn = null;
        $arrayOfTransientErrors = array('08001', '08002', '08003', '08004', '08007', '08S01');
        for ($cc = 1; $cc <= $maxCountTriesConnectAndQuery; $cc++) {
            // [A.2] Connect, which proceeds to issue a query command.
            $conn = sqlsrv_connect($serverName, $connectionOptions);
            if ($conn === true) {
                echo "Connection was established";
                echo "<br>";

                $tsql = "SELECT Name FROM Production.ProductCategory";
                $stmt = sqlsrv_query($conn, $tsql);
                if ($stmt === false) {
                    echo "Error in query execution";
                    echo "<br>";
                    die(print_r(sqlsrv_errors(), true));
                }
                while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                    echo $row['Name'] . "<br/>" ;
                }
                sqlsrv_free_stmt($stmt);
                sqlsrv_close( $conn);
                break;
            } else {
                // [A.4] Check whether the error code is on the list of allowed transients.
                $isTransientError = false;
                $errorCode = '';
                if (($errors = sqlsrv_errors()) != null) {
                    foreach ($errors as $error) {
                        $errorCode = $error['code'];
                        $isTransientError = in_array($errorCode, $arrayOfTransientErrors);
                        if ($isTransientError) {
                            break;
                        }
                    }
                }
                if (!$isTransientError) {
                    // it is a static persistent error...
                    echo("Persistent error suffered with error code = $errorCode. Program will terminate.");
                    echo "<br>";
                    // [A.5] Either the connection attempt or the query command attempt suffered a persistent error condition.
                    // Break the loop, let the hopeless program end.
                    exit(0);
                }
                // [A.6] It is a transient error from an attempt to issue a query command.
                // So let this method reloop and try again. However, we recommend that the new query
                // attempt should start at the beginning and establish a new connection.
                if ($cc >= $maxCountTriesConnectAndQuery) {
                    echo "Transient errors suffered in too many retries - $cc. Program will terminate.";
                    echo "<br>";
                    exit(0);
                }
                echo("Transient error encountered with error code = $errorCode. Program might retry by itself.");
                echo "<br>";
                echo "$cc attempts so far. Might retry.";
                echo "<br>";
                // A very simple retry strategy, a brief pause before looping.
                sleep(1*$secondsBetweenRetries);
            }
            // [A.3] All has gone well, so let the program end.
        }
    ?>