Usar parâmetros com valor de tabela (PHP)

  • Artigo

Baixar o driver PHP

Aplicável a

  • Microsoft Drivers 5.10.0 para PHP para SQL Server

Introdução

Você pode usar parâmetros com valor de tabela para enviar várias linhas de dados para uma instrução Transact-SQL ou um procedimento armazenado. Você não precisa criar uma tabela temporária. Para usar um parâmetro com valor de tabela com os drivers PHP, declare um tipo de tabela definido pelo usuário com um nome, conforme mostrado nos exemplos nesta página.

Usar um parâmetro com valor de tabela com um procedimento armazenado

Os exemplos abaixo pressupõem que existem as seguintes tabelas, tipos de tabela e procedimentos armazenados:

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

Os drivers PHP usam associação de linha para TVPs (parâmetros com valor de tabela) e você precisa fornecer o nome do tipo como uma cadeia de caracteres não vazia. Neste exemplo, o nome é TVPParam. A entrada TVP é essencialmente um par chave-valor com o nome do tipo TVP como a chave e os dados de entrada como uma matriz aninhada. Por exemplo:

$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(?, ?, ?, ?)}";

Usar o driver SQLSRV

Você pode chamar sqlsrv_query ou sqlsrv_prepare com sqlsrv_execute. O seguinte exemplo mostra o antigo caso de uso:

$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);

Além disso, você pode usar sqlsrv_send_stream_data para enviar dados TVP após a execução. Por exemplo:

$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);

Usar o driver PDO_SQLSRV

Este é um exemplo equivalente ao usar o driver PDO_SQLSRV. Você pode usar preparar/executar com bindParam e especificar a entrada TVP como um PDO::PARAM_LOB. Caso contrário, você receberá este erro: 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) {
    ...
}

Se o procedimento armazenado usar apenas parâmetros de entrada, você poderá usar bindValue em vez de bindParam.

Usar um esquema diferente do esquema dbo padrão

Se você não estiver usando o esquema dbo padrão, forneça o nome do esquema. Mesmo que o nome do esquema contenha um caractere de espaço, não use delimitadores como [ ou ].

    $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);

Usar um parâmetro com valor de tabela sem um procedimento armazenado

Você pode usar parâmetros com valor de tabela sem procedimentos armazenados. Considere o exemplo a seguir:

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

Usar o driver SQLSRV

Este é um exemplo de quando usar um esquema definido pelo usuário:

$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);

Usar o driver PDO_SQLSRV

Este é um exemplo de quando usar o esquema dbo padrão:

$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();

Confira também