Hello,

Newbie as to how to call a stored procedure with php.
I am trying to convert a asp script that calls a stored procedure to a php script to do the same.
Here is the basic 1 line call in asp:

totalexercise=usp_cardiovascular_minutes(null,startdate,dateoffset,patientid,null,null)

Here is the asp script code:
I removed the database connection values and calls.

function usp_cardiovascular_minutes(usp_sourcetable,usp_begin,usp_end,usp_patientid,usp_teamnum,usp_center)
		set cmd1=server.CreateObject("ADODB.Command")
		cmd1.CommandTimeout=180
		set cmd1.ActiveConnection=usp_cn1
		cmd1.CommandType=adCmdStoredProc
		cmd1.CommandText="usp_cardiovascular_minutes_diff"

	set param_sourcetable=cmd1.CreateParameter("@sourcetable", adInteger, adParamInput)
	param_sourcetable.Value=usp_sourcetable
	cmd1.Parameters.Append param_sourcetable

	set param_begin=cmd1.CreateParameter("@begin", adDBDate, adParamInput)
	param_begin.Value=usp_begin
	cmd1.Parameters.Append param_begin

	set param_end=cmd1.CreateParameter("@end", adDBDate, adParamInput)
	param_end.Value=usp_end
	cmd1.Parameters.Append param_end

	set param_patientid=cmd1.CreateParameter("@patientid", adInteger, adParamInput)
	param_patientid.Value=usp_patientid
	cmd1.Parameters.Append param_patientid

	set param_teamnum=cmd1.CreateParameter("@teamnum", adVarChar, adParamInput)
	param_teamnum.Value=usp_teamnum
	param_teamnum.Size=5
	cmd1.Parameters.Append param_teamnum

	set param_center=cmd1.CreateParameter("@center", adInteger, adParamInput)
	param_center.Value=usp_center
	cmd1.Parameters.Append param_center

	set usp_rs1=cmd1.execute
	if usp_rs1.eof then
		usp_cardiovascular_minutes=0
	else
		if isnull(usp_rs1("TotalMinutes")) then usp_cardiovascular_minutes=0 else usp_cardiovascular_minutes=int(trim(usp_rs1("TotalMinutes")))
	end if

	cmd1.Parameters.Delete("@sourcetable")
	cmd1.Parameters.Delete("@begin")
	cmd1.Parameters.Delete("@end")
	cmd1.Parameters.Delete("@patientid")
	cmd1.Parameters.Delete("@teamnum")
	cmd1.Parameters.Delete("@center")

	on error resume next
	cmd1.close
	set cmd1=nothing
	usp_rs1.close
	set usp_rs1=nothing
	usp_cn1.close
	set usp_cn1=nothing
	on error goto 0
end function

I have written the following script that generates no result and no error messages:

$sourcetable = NULL;
$begin = date("01/01/2013");
$end = date("03/01/2014");
$patientid = 38544;
$teamnum = NULL;               

$center = NULL;
$returnval = 0;

$params = array(
  array($sourcetable,SQLSRV_PARAM_IN),
  array($begin, SQLSRV_PARAM_IN), 
  array($end, SQLSRV_PARAM_IN),              

  array($patientid,SQLSRV_PARAM_IN),
  array($teamnum, SQLSRV_PARAM_IN), 
  array($center, SQLSRV_PARAM_IN)
);

$query = "{call dbo.usp_cardiovascular_minutes_diff (?,?,?,?,?,?)}";

$result = sqlsrv_query($dbhandle, $query, $params);
if (sqlsrv_num_rows($result)==0) {
   die("No records found.");
}  

while ($item=sqlsrv_fetch_array($result)) {      

  echo $item['usp_cardiovascular_minutes'];
}

Again, I run this and get no errors but also nothing returned as a value.

Thank you all,
Kim H.

    Welcome to PHPBuilder! When posting code, please use the board's bbcode tags (e.g. [noparse]

    ..

    for generic code or

    ..

    for PHP code[/noparse]) as they make your code much easier to read and analyze.

    Few comments/questions I came up with:

    1. While not an error, note that this:

      $begin = date("01/01/2013"); 
$end = date("03/01/2014");

      doesn't really make any sense; you aren't using any format identifiers that [man]date/man will act upon, thus there's no reason to call that function at all.

    2. Where do you define $dbhandle? Can you at least show us some of the connection code with only the login credentials removed? Do you do any sort of validation to ensure that a connection was successfully made?

    3. [man]sqlsrv_query/man returns boolean FALSE if an error occurs, yet you aren't checking for and handling this condition. Why not?

    4. Have you tried a [man]var_dump/man on $result after you call sqlsrv_query() to see what was returned?

    5. Last but certainly not least, do you have error_reporting set to E_ALL (or better) and either display_errors or log_errors set to On?

    Have you tried doing a [man]var_dump/man

      Here are the connection strings to the SQL Server.

      $connectionInfo = array( "Database"=>"$db_database", "UID"=>"$db_username", "PWD"=>"$db_password", "ReturnDatesAsStrings" => true, "CharacterSet" => "utf-8");
      $dbhandle = sqlsrv_connect($db_hostname, $connectionInfo);

      These work perfectly in dozens of other scripts that I use.

      var_dump returns "resource(3) of type (SQL Server Statement)." with options = "SQLSRV_CURSOR_FORWARD"
      with options = "SQLSRV_CURSOR_KEYSET" it returns bool(false):

        khovorka;11038977 wrote:

        Newbie as to how to call a stored procedure with php.

        $sourcetable = NULL;

        Does the stored procedure yield any results when called directly? As in, create a CLI conncetion to the SQL server and manually call that SP using the same values you try to send from your script. Most notably I'm curious about finding out what happens in that SP when you pass NULL for "sourcetable"…

        Also as bradgrafelman suggests, add error checking to your script… Inspect result of issued queris for false

        khovorka;11038977 wrote: 
        $result = sqlsrv_query($dbhandle, $query, $params);
# new code
if ($result === false) {
    # call appropriate SQL server functions to inspect db error code and error text. Log or echo as appropriate

}
          Write a Reply...