Compartir por

Uso de parámetros con valores de tabla (PHP)

  • Artigo

Descargar controlador PHP

Aplicable a

  • Controladores de Microsoft 5.10.0 para PHP para SQL Server

Introducción

Puede usar parámetros con valores de tabla para enviar varias filas de datos a una instrucción Transact-SQL o a un procedimiento almacenado. No es necesario crear una tabla temporal. Para usar un parámetro con valores de tabla con los controladores PHP, declare un tipo de tabla definido por el usuario con un nombre, como se muestra en los ejemplos de esta página.

Uso de un parámetro con valores de tabla con un procedimiento almacenado

En los ejemplos siguientes se asume que existen las tablas, el tipo de tabla y el procedimiento almacenado siguientes:

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

Los controladores PHP usan el enlace por filas para los parámetros con valores de tabla (TVP) y debe proporcionar el nombre de tipo como una cadena no vacía. En este ejemplo, el nombre es TVPParam. La entrada TVP es básicamente un par clave-valor con el nombre de tipo TVP como clave y los datos de entrada como una matriz anidada. Por ejemplo:

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

Uso del controlador SQLSRV

Puede llamar a sqlsrv_query o sqlsrv_prepare con sqlsrv_execute. En el ejemplo siguiente se muestra el primer 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);

Además, puede usar sqlsrv_send_stream_data pare enviar datos de TVP después de la ejecución. Por ejemplo:

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

Uso del controlador PDO_SQLSRV

Este es un ejemplo equivalente cuando se usa el controlador PDO_SQLSRV. Puede usar prepare/execute con bindParam y especificar la entrada de TVP como PDO::PARAM_LOB. Si no, recibirá este error: 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) {
    ...
}

Si el procedimiento almacenado solo toma parámetros de entrada, puede usar bindValue en lugar de bindParam.

Uso un esquema distinto del esquema dbo predeterminado

Si no usa el esquema dbo predeterminado, debe proporcionar el nombre del esquema. Incluso si el nombre del esquema contiene un carácter de espacio, no use delimitadores como [ o ].

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

Uso de un parámetro con valores de tabla sin un procedimiento almacenado

Puede usar parámetros con valores de tabla sin procedimientos almacenados. Considere el ejemplo siguiente:

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

Uso del controlador SQLSRV

Este es un ejemplo cuando se usa un esquema definido por el usuario:

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

Uso del controlador PDO_SQLSRV

Este es un ejemplo cuando se usa el esquema dbo predeterminado:

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

Vea también