Usare parametri con valori di tabella (PHP)
Applicabile a
- Versione 5.10.0 dei driver Microsoft per PHP per SQL Server
Introduzione
È possibile usare i parametri con valori di tabella per inviare più righe di dati a un'istruzione o a una stored procedure Transact-SQL. Non è necessario creare una tabella temporanea. Per usare un parametro con valori di tabella con i driver PHP, dichiarare un tipo di tabella definito dall'utente con un nome, come illustrato negli esempi in questa pagina.
Usare un parametro con valori di tabella con una stored procedure
Gli esempi seguenti presuppongono che le tabelle, il tipo di tabella e la stored procedure seguenti esistano:
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
I driver PHP usano l'associazione a livello di riga per i parametri con valori di tabella (TVP) ed è necessario specificare il nome del tipo come stringa non vuota. In questo esempio il nome è TVPParam. L'input dei parametri con valori di tabella è essenzialmente una coppia chiave-valore con il nome del tipo di parametro con valori di tabella come chiave e i dati di input come matrice annidata. Ad esempio:
$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(?, ?, ?, ?)}";
Usare il driver SQLSRV
È possibile chiamare sqlsrv_query o sqlsrv_prepare con sqlsrv_execute. L'esempio seguente mostra il caso d'uso precedente:
$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);
È anche possibile usare sqlsrv_send_stream_data per inviare dati dei parametri con valori di tabella dopo l'esecuzione. Ad esempio:
$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);
Usare il driver PDO_SQLSRV
Questo esempio equivale all'uso del driver PDO_SQLSRV. È possibile usare un'istruzione di preparazione/esecuzione con bindParam e specificare l'input dei parametri con valori di tabella come PDO::PARAM_LOB. In caso contrario, verrà visualizzato l'errore: 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 la stored procedure accetta solo parametri di input, è possibile usare bindValue invece di bindParam.
Usare uno schema diverso dallo schema dbo predefinito
Se non si usa lo schema dbo predefinito, è necessario specificare il nome dello schema. Anche se il nome dello schema contiene un carattere di spazio, non usare delimitatori come [ 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);
Usare un parametro con valori di tabella senza una stored procedure
È possibile usare parametri con valori di tabella senza stored procedure. Si consideri l'esempio seguente:
CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)
CREATE TABLE test_table (id INT PRIMARY KEY)
Usare il driver SQLSRV
Questo esempio è relativo all'uso di uno schema definito dall'utente:
$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);
Usare il driver PDO_SQLSRV
Questo esempio è relativo all'uso dello schema dbo predefinito:
$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();