使用PHP PDO将数组传递给SQL Server函数/存储过程

I've gotten to a point where I absolutely need some clean way to safely pass lists/arrays from php to SQL server stored procedures and table value functions. The PHP SQL server driver still does not support table valued parameters, according to Microsoft docs.

我已经到了一个点,我绝对需要一些干净的方法来安全地将列表/数组从php传递到SQL服务器存储过程和表值函数。根据Microsoft文档,PHP SQL服务器驱动程序仍然不支持表值参数。

In another question, using XML was suggested as an alternative.

在另一个问题中,建议使用XML作为替代方案。

Does anyone have a code sample for passing the equivalent of a TVP using an XML stream and PHP PDO or another clean alternative?

有没有人有一个代码示例,用于使用XML流和PHP PDO或其他干净的替代方案传递TVP的等效内容?

2 个解决方案

#1

The primitive, but foolproof solution, is to pass it as a delimited string, and use a SPLIT function in your proc to convert the string to a table that you can then JOIN to.

原始但却万无一失的解决方案是将其作为分隔字符串传递,并在proc中使用SPLIT函数将字符串转换为可以加入的表。

Google SQL SPLIT FUNCTION to get free cut-n-paste code.

Google SQL SPLIT FUNCTION可获得免费的剪切代码。

#2

You could use xml, but a better, more compact idea would be to use JSON.

您可以使用xml,但更好,更紧凑的想法是使用JSON。

$myArray = array("thing 1", "thing 2", "thing 3");
$sql = $pdo->prepare("INSERT INTO `table` (`array_data`) VALUES (:myArray)");
$sql->execute(array(":myArray"=>json_encode($myArray)));

Then when you pull the data back out of the database you can convert it back into an array with:

然后,当您将数据从数据库中拉回时,您可以将其转换回数组,其中包含:

$myArray = json_decode($res['myArray'], true);

#1