I want to pass an array to a Table Valued Parameter in a MSSQL Stored Procedure using PHP PDO Drivers.
I am getting an error:
Uncaught PDOException: SQLSTATE[IMSSP]: An invalid type for parameter 8 was specified. Only booleans, integers, floating point numbers, strings, and streams may be used as parameters
$serialtablejsonstr = $_POST["serialtablearrstr"];
$serialtablearr = json_decode($serialtablejsonstr,true);
$tvpType = "SerialInfoTblType";
$tvpInput = array($tvpType => $serialtablearr);
$con = connectDatabasePDO();
$sqladdoldstock = "EXEC StoreEntry
@type = 2,
@ItemId = :item,
@ItemDetail = :itemspecs,
@guarantee = :gaurantee,
@Warranty = :warranty,
@Amount = :unitamount,
@itemtype = :itemtype,
@TenderId = :istender,
@FirmId = :firmid,
@Serialinfotbl = :serialtable,
@FirmInvoiceNo = :firminvoiceno";
$stmtaddoldstock = $con->prepare($sqladdoldstock);
$stmtaddoldstock->bindParam(':item', $item);
$stmtaddoldstock->bindParam(':itemspecs', $itemspecs);
$stmtaddoldstock->bindParam(':gaurantee', $gaurantee);
$stmtaddoldstock->bindParam(':warranty', $warranty);
$stmtaddoldstock->bindParam(':unitamount', $unitamount);
$stmtaddoldstock->bindParam(':itemtype', $itemtype);
$stmtaddoldstock->bindParam(':istender', $istender);
$stmtaddoldstock->bindParam(':firmid', $firmid);
$stmtaddoldstock->bindParam(':serialtable', $tvpInput, PDO::PARAM_LOB);
$stmtaddoldstock->bindParam(':firminvoiceno', $firminvoiceno);
$stmtok = $stmtaddoldstock->execute();
$arr = $stmtaddoldstock->errorInfo();
if(!$stmtok)
{
echo "Error";
}
else
{
echo "Success";
}
$con = null;
$stmtaddoldstock = null;
I tried to pass array as JSON String with json_encode() as PDO::PARAM_STR.
I redeveloping/replicating an application in PHP that was previously developed in .NET .
In the .NET version the table valued parameters were passed using Datatables, but I not able to do that in PHP.
I have already gone through these steps Use table-valued parameters (PHP). But seems like I am missing something.