0

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.

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

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

Community
  • 1
  • 1
phosplait
  • 285
  • 3
  • 15
  • guessing... Would it be useful to be able to convert from JSON to XML that i believe SQL handles quite well? This may be useful: [A lossless xml to json and json to xml converter (and csv/xlsx/yaml)](https://github.com/DracoBlue/Craur). – Ryan Vincent May 01 '15 at 14:12

2 Answers2

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.

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

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

You could use xml, but a better, more compact idea would be to use 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);
I wrestled a bear once.
  • 22,983
  • 19
  • 69
  • 116
  • This is not exactly what I'm looking for. I'm not trying to store the array in the database, rather I want to read it into a stored procedure or user defined function as a parameter and be able to join it to other tables within sql server so I can use it as part of an inline select query if that makes any sense. – phosplait Apr 30 '15 at 20:41