Partager via

Utiliser des paramètres table (PHP)

  • Article

Télécharger le pilote PHP

Applicable à

  • Microsoft Drivers 5.10.0 pour PHP pour SQL Server

Introduction

Vous pouvez utiliser des paramètres table pour envoyer plusieurs lignes de données à une procédure stockée ou une instruction Transact-SQL. Vous n’avez pas besoin de créer une table temporaire. Pour utiliser un paramètre table avec les pilotes PHP, déclarez un type de table défini par l’utilisateur avec un nom, comme indiqué dans les exemples de cette page.

Utiliser un paramètre table avec une procédure stockée

Les exemples suivants supposent que les tables, le type de table et la procédure stockée suivants existent :

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

Les pilotes PHP utilisent une liaison au niveau ligne pour les paramètres table (TVP) et vous devez fournir le nom du type sous forme de chaîne non vide. Dans cet exemple, le nom est TVPParam. Une entrée TVP est essentiellement une paire clé-valeur avec un nom de type TVP comme clé et des données d’entrée sous forme de tableau imbriqué. Par exemple :

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

Utiliser le pilote SQLSRV

Vous pouvez appeler sqlsrv_query ou sqlsrv_prepare avec sqlsrv_execute. L’exemple suivant illustre le cas d’usage précédent :

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

Vous pouvez aussi utiliser sqlsrv_send_stream_data pour envoyer une exécution de la publication de données TVP. Par exemple :

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

Utiliser le pilote PDO_SQLSRV

Il s’agit d’un exemple équivalent lors de l’utilisation du pilote PDO_SQLSRV. Vous pouvez utiliser prepare/execute avec bindParam et spécifier l’entrée TVP en tant que PDO::PARAM_LOB. Dans le cas contraire, vous obtenez cette erreur : 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 votre procédure stockée prend uniquement des paramètres d’entrée, vous pouvez utiliser bindValue au lieu de bindParam.

Utiliser un schéma autre que le schéma dbo par défaut

Si vous n’utilisez pas le schéma dbo par défaut, vous devez fournir le nom du schéma. Même si le nom du schéma contient un espace, n’utilisez pas de délimiteurs comme [ 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);

Utiliser un paramètre table sans procédure stockée

Vous pouvez utiliser des paramètres table sans procédure stockée. Prenons l’exemple suivant :

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

Utiliser le pilote SQLSRV

Voici un exemple d’utilisation d’un schéma défini par l’utilisateur :

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

Utiliser le pilote PDO_SQLSRV

Voici un exemple d’utilisation du schéma dbo par défaut :

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

Voir aussi