テーブル値パラメーターの使用 (PHP)

  • [アーティクル]

PHP ドライバーのダウンロード

次に対して適用可能

  • Microsoft SQL Server 用 Drivers 5.10.0 for PHP

はじめに

テーブル値パラメーターを使用すると、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_execute を使用して、sqlsrv_query または sqlsrv_prepare を呼び出すことができます。 次の例では、以前の使用例を示しています。

$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 を使用して、実行後に TVP データを送信する場合があります。 次に例を示します。

$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 ドライバーを使用する場合と同等の例です。 bindParam で prepare/execute を使用し、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) {
    ...
}

ストアド プロシージャで入力パラメーターのみが使用される場合は、bindParam ではなく bindValue を使用できます。

既定の 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();

関連項目