Question 239785 in PHP Question posted in
Official manual can be found here.

Receiving the error "An invalid PHP type for parameter 1 was specified" when using a Parameterised query

I am trying to execute a stored procedure which inputs data to my database.

I am setting up my parameters as follows:

($conn has my db creds, $xml contains data from an XML file)

$params = array(
    array($xml->filenumber, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(20)),
    array($xml->lastName, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(25)),
    array($xml->firstName, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(25)),
    array("",  SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(14))
);

The datatypes correspond with the sproc:

@FileNumber varchar(20),
@FamilyName varchar(25),
@GivenName  varchar(25), 
@FileID     varchar(14) output

And then executing it in PHP :

$sql = "EXEC dbo.AddFileHdr @FileNumber = ?, @FamilyName = ?, @GivenName = ?, @FileID =?";
$stmt = sqlsrv_prepare($conn, $sql, $params);
if( !$stmt ) {
 die( print_r( sqlsrv_errors(), true));
}
if(sqlsrv_execute($stmt)){
    while($res = sqlsrv_next_result($stmt)){
//doesnt need to output anything here
    }
  }else{
    die( print_r( sqlsrv_errors(), true));
  }

Expected behavior is for the query to return a 14 character varchar FileID after I insert the 3 fields. I have tested the sproc in SQL Studio and it works fine.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Turns out the issue was because I was not passing a string to the parameter with $xml->child

    This was solved by casting the xml data as a string e.g.: $param2 = (string)$xml->lastName;


  2. 0

    SQLSRV_PHPTYPE_STRING accepts an encoding type constant as parameter:

    $fileId = "";

$params = array(
    array($xml->filenumber, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(20)),
    array($xml->lastName, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(25)),
    array($xml->firstName, SQLSRV_PARAM_IN, SQLSRV_SQLTYPE_VARCHAR(25)),
    array(&$fileId,  SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR))
);

    You can change SQLSRV_ENC_CHAR with other encoding constants as stated here: https://www.php.net/manual/en/sqlsrv.constants.php

    By the way, i suggest you to always use variables for your query parameters and pass it by reference (see $fileId):

    Variables passed as query parameters should be passed by reference instead of by value. For example, pass &$myVariable instead of $myVariable. A PHP warning is raised when a query with by-value parameters is executed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search