1

Using PHP 8 with MS SQL Server 2014 on Windows Server 2012 R 2. I believe I have updated the PHP drivers for SQL.

Given this sample PHP script:

<?php
        $connectionInfo = array( "Database"=>"$database", "UID"=>"$username", "PWD"=>"$password", "CharacterSet"=>"UTF-8");
        $conn = sqlsrv_connect( $hostname, $connectionInfo);
        
        if( !$conn ) {
             echo "Connection could not be established.<br />";
             die( print_r( sqlsrv_errors(), true));
        }
    $sql = "{CALL countRows2 (?)}";
    $nbrRows = 0;
    $varpar1 = array(&$nbrRows, SQLSRV_PARAM_INOUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT);
    $params = array(&$varpar1); 

     $stmt = sqlsrv_prepare( $conn, $sql, $params);
     if( !$stmt ) {
      die( print_r( sqlsrv_errors(), true));
     }

     error_log("Prepared worked");
     if( sqlsrv_execute ($stmt) === false ) {
        error_log("Execute failed ");
         die( print_r( sqlsrv_errors(), true));
     }


      error_log("Nbr Rows = " . $nbrRows);

?>

and this stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[countRows2] 
    -- Add the parameters for the stored procedure here
    @count INT OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
        BEGIN TRY
            -- Insert statements for procedure here
            SET @count = (SELECT COUNT(*) FROM dbo.entries);

        END TRY
        BEGIN CATCH
            declare @error int, @message varchar(4000), @xstate int;
            select @error = ERROR_NUMBER() , @message = ERROR_MESSAGE();
            raiserror ('Caught exception: %d: %s', 16, 1, @error, @message) ;
        END CATCH
END

I get :

Prepared worked Execute failed Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -16 [code] => -16 [2] => An invalid PHP type for parameter 0 was specified. [message] => An invalid PHP type for parameter 0 was specified. )

)

If I add one extra parameter or omit one on the call, the system does complain about the mismatch in signature so there seems to be some connection between the PHp and the SQL DB.

But why is it complaining about the invalid PHP type?

If I do

$params = array(&$nbrRows); 

$stmt = sqlsrv_prepare( $conn, $sql, $params);
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

error_log("Prepared worked");
if( sqlsrv_execute ($stmt) === false ) {
    error_log("Execute failed ");
      die( print_r( sqlsrv_errors(), true));
}

error_log("Nbr Rows = " . $nbrRows);

No errors are thrown but the $nbrRows is always 0:

Prepared worked Nbr Rows = 0

3
  • Using $params = array(array(&$nbrRows, SQLSRV_PARAM_INOUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT)); should be enough. Or $params = array($varpar1); instead of $params = array(&$varpar1);. Commented Apr 28, 2021 at 14:49
  • Instead of prepare/execute, try sqlsrv_query($conn, $sql, $params); per stackoverflow.com/q/7444516/1255289 Commented Apr 28, 2021 at 15:53
  • Also, do you understand what referencing variables with & is doing? Why are you doing it in this code? Commented Apr 28, 2021 at 15:54

1 Answer 1

0

Thank you for the suggestions.

This works:

$varpar1 = array(&$nbrRows, SQLSRV_PARAM_INOUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT);
$params = array($varpar1);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.