Поделиться через

Использование возвращающих табличные значения параметров (PHP)

  • Статья

Скачать драйвер PHP

Применимо к

  • Драйверы Майкрософт версии 5.10.0 для PHP для SQL Server

Введение

Параметры, возвращающие табличные значения, можно использовать для отправки нескольких строк данных в инструкцию Transact-SQL или хранимую процедуру. Для этого не нужно создавать временную таблицу. Чтобы использовать возвращающий табличное значение параметр с драйверами PHP, объявите тип таблицы как определяемый пользователем с именем, как показано в примерах на этой странице.

Использование возвращающего табличное значение параметра с хранимой процедурой

В примерах ниже предполагается, что существуют следующие таблицы, их тип и хранимая процедура:

CREATE TABLE TVPOrd(
    OrdNo INTEGER IDENTITY(1,1),
    OrdDate DATETIME,
    CustID VARCHAR(10))


CREATE TABLE TVPItem(
    OrdNo INTEGER,
    ItemNo INTEGER IDENTITY(1,1),
    ProductCode CHAR(10),
    OrderQty INTEGER,
    SalesDate DATE,
    Label NVARCHAR(30),
    Price DECIMAL(5,2),
    Photo VARBINARY(MAX))


--Create TABLE type for use as a TVP
CREATE TYPE TVPParam AS TABLE(
                ProductCode CHAR(10),
                OrderQty INTEGER,
                SalesDate DATE,
                Label NVARCHAR(30),
                Price DECIMAL(5,2),
                Photo VARBINARY(MAX))


--Create procedure with TVP parameters
CREATE PROCEDURE TVPOrderEntry(
        @CustID VARCHAR(10),
        @Items TVPParam READONLY,
        @OrdNo INTEGER OUTPUT,
        @OrdDate DATETIME OUTPUT)
AS
BEGIN
    SET @OrdDate = GETDATE(); SET NOCOUNT ON;
    INSERT INTO TVPOrd (OrdDate, CustID) VALUES (@OrdDate, @CustID);
    SELECT @OrdNo = SCOPE_IDENTITY();
    INSERT INTO TVPItem (OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo)
    SELECT @OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo
    FROM @Items
END

Драйверы PHP используют привязку по строкам для параметров, возвращающих табличные значения, поэтому имя типа необходимо указать как непустую строку. В этом примере используется имя TVPParam. Входными данными возвращающих табличные значения параметров является пара "ключ-значение" с именем типа параметра в качестве ключа и входными данными в качестве вложенного массива. Например:

$image1 = fopen($pic1, 'rb');
$image2 = fopen($pic2, 'rb');
$image3 = fopen($pic3, 'rb');

$items = [
    ['0062836700', 367, "2009-03-12", 'AWC Tee Male Shirt', '20.75', $image1],
    ['1250153272', 256, "2017-11-07", 'Superlight Black Bicycle', '998.45', $image2],
    ['1328781505', 260, "2010-03-03", 'Silver Chain for Bikes', '88.98', $image3],
];

// Create a TVP input array
$tvpType = 'TVPParam';
$tvpInput = array($tvpType => $items);

// To execute the stored procedure, either execute a direct query or prepare this query:
$callTVPOrderEntry = "{call TVPOrderEntry(?, ?, ?, ?)}";

Использование драйвера SQLSRV

Можно вызвать sqlsrv_query или sqlsrv_prepare с sqlsrv_execute. В следующем примере показан первый вариант:

$custCode = 'SRV_123';
$ordNo = 0;
$ordDate = null;
$params = array($custCode,
                array($tvpInput, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_TABLE, SQLSRV_SQLTYPE_TABLE), // or simply array($tvpInput),
                array(&$ordNo, SQLSRV_PARAM_OUT),
                array(&$ordDate, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)));
$stmt = sqlsrv_query($conn, $callTVPOrderEntry, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_next_result($stmt);

Кроме того, можно воспользоваться sqlsrv_send_stream_data, чтобы отправить данные возвращающего табличное значение параметра после выполнения. Например:

$options = array("SendStreamParamsAtExec" => 0);
$stmt = sqlsrv_prepare($conn, $callTVPOrderEntry, $params, $options);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
$res = sqlsrv_execute($stmt);
if (!$res) {
    print_r(sqlsrv_errors());
}

// Now call sqlsrv_send_stream_data in a loop
while (sqlsrv_send_stream_data($stmt)) {
}
sqlsrv_next_result($stmt);

Использование драйвера PDO_SQLSRV

Ниже приведен аналогичный пример с использованием драйвера PDO_SQLSRV. Можно использовать bindParam и указать входные данные возвращающего табличное значение параметра как PDO::PARAM_LOB. Если этого не сделать, возникнет следующая ошибка: Operand type clash: nvarchar is incompatible with ….

try {
    $stmt = $conn->prepare($callTVPOrderEntry);
    $stmt->bindParam(1, $custCode);
    $stmt->bindParam(2, $tvpInput, PDO::PARAM_LOB);
    // 3 - OrdNo output
    $stmt->bindParam(3, $ordNo, PDO::PARAM_INT, 10);
    // 4 - OrdDate output
    $stmt->bindParam(4, $ordDate, PDO::PARAM_STR, 20);
    $stmt->execute();
} catch (PDOException $e) {
    ...
}

Если ваша хранимая процедура принимает только входные параметры, можно использовать bindValue вместо bindParam.

Использование схемы, отличной от схемы dbo по умолчанию

Если вы не используете схему dbo по умолчанию, укажите название схемы. Даже если в нем содержится пробел, не используйте такие разделители, как [ или ].

    $inputs = [
        ['ABC', 12345, null],
        ['DEF', 6789, 'This is a test']
    ];
    $schema = 'Sales DB';
    $tvpType = 'TestTVP';

    // i.e. the TVP type name is "[Sales DB].[TestTVP]"
    $tvpInput = array($tvpType => $inputs, $schema);

Использование возвращающего табличное значение параметра без хранимой процедуры

Можно использовать параметры, возвращающие табличные значения, без хранимых процедур. Рассмотрим следующий пример:

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

Использование драйвера SQLSRV

Это пример использования определяемой пользователем схемы:

$schema = 'my schema';
$tvpName = 'id_table_type';

$tsql = "INSERT INTO [$schema].[test_table] SELECT * FROM ?";
$params = [
[[$tvpname => [[1], [2], [3]], $schema]],
];

$stmt = sqlsrv_query($conn, $tsql, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_free_stmt($stmt);

Использование драйвера PDO_SQLSRV

Это пример использования схемы dbo по умолчанию:

$tsql = "INSERT INTO test_table SELECT * FROM ?";
$tvpInput = array('id_table_type' => [[1], [2], [3]]);

$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $tvpInput, PDO::PARAM_LOB);
$result = $stmt->execute();

См. также