Verwenden von Tabellenwertparametern (PHP)
Anwendbar auf
- Microsoft-Treiber 5.10.0 für PHP für SQL Server
Einführung
Sie können Tabellenwertparameter verwenden, um mehrere Datenzeilen an eine Transact-SQL-Anweisung oder eine gespeicherte Prozedur zu senden. Sie müssen keine temporäre Tabelle erstellen. Um einen Tabellenwertparameter mit den PHP-Treibern zu verwenden, müssen Sie einen benutzerdefinierten Tabellentyp mit einem Namen deklarieren, wie in den Beispielen auf dieser Seite gezeigt wird.
Verwenden eines Tabellenwertparameters mit einer gespeicherten Prozedur
In den folgenden Beispielen wird davon ausgegangen, dass die folgenden Tabellen, der Tabellentyp und die gespeicherte Prozedur vorhanden sind:
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
Die PHP-Treiber verwenden die zeilenbezogene Bindung für Tabellenwertparameter (TVPs), und Sie müssen den Typnamen als nicht leere Zeichenfolge angeben. In diesem Beispiel lautet der Name TVPParam. Die TVP-Eingabe ist im Wesentlichen ein Schlüssel-Wert-Paar, wobei der TVP-Typname ein Schlüssel und die Eingabedaten ein geschachteltes Array sind. Beispiel:
$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(?, ?, ?, ?)}";
Verwenden des SQLSRV-Treibers
Sie können sqlsrv_query oder sqlsrv_prepare mit sqlsrv_execute aufrufen. Im folgenden Beispiel wird der vorherige Anwendungsfall demonstriert:
$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);
Darüber hinaus können Sie sqlsrv_send_stream_data verwenden, um TVP-Daten nach der Ausführung zu senden. Beispiel:
$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);
Verwenden des PDO_SQLSRV-Treibers
Dies ist ein vergleichbares Beispiel für die Verwendung des PDO_SQLSRV-Treibers. Sie können „prepare“ bzw. „execute“ mit bindParam verwenden und die TVP-Eingabe als PDO::PARAM_LOB angeben. Wenn Sie dies nicht tun, erhalten Sie den folgenden Fehler: 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) {
...
}
Wenn Ihre gespeicherte Prozedur nur Eingabeparameter verwendet, können Sie bindValue anstelle von bindParam verwenden.
Verwenden eines anderen Schemas als des dbo-Standardschemas
Wenn Sie nicht das dbo-Standardschema verwenden, sollten Sie den Schemanamen angeben. Auch wenn der Schemaname ein Leerzeichen enthält, sollten Sie keine Trennzeichen wie [ oder ] verwenden.
$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);
Verwenden eines Tabellenwertparameters ohne gespeicherte Prozedur
Sie können Tabellenwertparameter ohne gespeicherte Prozeduren verwenden. Betrachten Sie das folgende Beispiel:
CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)
CREATE TABLE test_table (id INT PRIMARY KEY)
Verwenden des SQLSRV-Treibers
Dies ist ein Beispiel für die Verwendung eines benutzerdefinierten Schemas:
$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);
Verwenden des PDO_SQLSRV-Treibers
Dies ist ein Beispiel für die Verwendung des dbo-Standardschemas:
$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();