PHP PDO Throws Error while passing an array to SQL Server Table Valued Parameter

56 Views Asked by At

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.

0

There are 0 best solutions below