Использование параметров, структурированных в виде таблицы (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 используют привязку по строкам для табличных параметров (TVP), и имя типа необходимо указать в виде непустой строки. В этом примере используется имя TVPParam. Входные данные TVP по сути представляют собой пару «ключ-значение», где ключом служит имя типа TVP, а значением — входные данные в виде вложенного массива. Например:

$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. Можно использовать prepare/execute с bindParam и указать входной табличный параметр (TVP) как 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();

Связанные материалы

  • Last updated on