Partilhar via

Usar parâmetros com valor de tabela (PHP)

Descarregar o driver PHP

Aplicável a

  • Microsoft Drivers 5.10.0 para PHP para SQL Server

Introdução

Pode usar parâmetros com valores de tabela para enviar várias linhas de dados para uma instrução Transact-SQL ou para um procedimento armazenado. Não precisas de criar uma tabela temporária. Para usar um parâmetro com valores de tabela com os drivers PHP, declare um tipo de tabela definido pelo utilizador com um nome, como mostrado nos exemplos nesta página.

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

Os seguintes exemplos assumem que existem as seguintes tabelas, tipo de tabela e procedimento armazenado:

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 binding por linha para parâmetros de tabela (TVPs), e deve-se fornecer o nome do tipo como uma cadeia não vazia. Neste exemplo, o nome é TVPParam. A entrada TVP é, essencialmente, um par chave-valor, onde o nome do tipo TVP é a chave, e os dados de entrada são apresentados como um array aninhado. 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

Pode chamar sqlsrv_query ou sqlsrv_prepare com sqlsrv_execute. O exemplo seguinte mostra o caso de uso anterior:

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

Usa o driver PDO_SQLSRV

Este é um exemplo equivalente ao usar o driver PDO_SQLSRV. Pode utilizar o prepare/execute com bindParam e especificar a entrada TVP como um PDO::PARAM_LOB. Se não, 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 teu procedimento armazenado só aceitar parâmetros de entrada, podes usar bindValue em vez de bindParam.

Use um esquema diferente do esquema DBO padrão

Se não estiveres a usar o esquema DBO predefinido, então deves fornecer o nome do esquema. Mesmo que o nome do esquema contenha um carácter 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 valores de tabela sem um procedimento armazenado

Pode usar parâmetros com valores de tabela sem procedimentos armazenados. Considere o seguinte exemplo:

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 ao usar um esquema definido pelo utilizador:

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

Usa o driver PDO_SQLSRV

Este é um exemplo ao usar o esquema DBO predefinido:

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

Consulte também

  • Last updated on