PHP 7.4 and Stored Procedure using PDO problem

Hello,

Two of the incoming values are money - $strIdocMoney and $strFees.
And one is a datetime - $wrkFixedEmailSentDate.

But you bind them as  PDO::PARAM_STR
What is the stored procedure expecting?

Just clicked to fast...
You may also inspect the stored procedure as well...
If there is an error handler inside the stored procedure, the PHP script get not aware about an error. 

Thank you for responding.
Below is the stored procedure originally used in PHP 5.6

USE [RegionsUSA]
GO
/****** Object:  StoredProcedure [dbo].[ins_new_importIDocInfo]    Script Date: 11/24/2020 8:05:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ins_new_importIDocInfo]
   @RegionId int,
   @Supplier nvarchar(50),
   @IDoc nvarchar(50),
   @AmtRcvd money,
   @DateRcvd datetime,
   @Fees money,
   @RECID INT OUTPUT as
   Insert Into importIDocInfo(RegionId,Supplier,IDoc,AmtRcvd,DateRcvd,Fees)
               Values(@RegionId,@Supplier,@IDoc,@AmtRcvd,@DateRcvd,@Fees)      
           
SELECT @RECID = SCOPE_IDENTITY()
RETURN @RECID

Select allOpen in new window

and  also the original  params array in my php 5.6 coding (just a part to show how the incoming data was described.)

$procedure_params = array(
         array(&$myparams['RECID'], SQLSRV_PARAM_OUT),
         array(&$myparams['RegionId'], SQLSRV_PARAM_IN),
         array(&$myparams['Supplier'], SQLSRV_PARAM_IN),
         array(&$myparams['IDoc'], SQLSRV_PARAM_IN),
         array(&$myparams['AmtRcvd'],SQLSRV_PARAM_IN,null,SQLSRV_SQLTYPE_MONEY),
         array(&$myparams['DateRcvd'], SQLSRV_PARAM_IN,null,SQLSRV_SQLTYPE_DATETIME),
         array(&$myparams['Fees'], SQLSRV_PARAM_IN,null,SQLSRV_SQLTYPE_MONEY));

Select allOpen in new window

As you will notice, I defined datetime and money.
However, in using PDO, I find no equivalents for those two descriptors. datetime and money.
I am of the mind that is where the problem is.
So  how do I  to solve it ??
So, I am not sure where to go from here. I find it odd that there isn't PDO descriptors for datetime and money.

I am thinking at this point, I could create a  stored procedure that just returns the unique record key, then use an insert statement to update the values to that record based on the primary key returned. But I am sure others have encountered this issue. I just haven't found how they resolved it.(:

So, following the comments in this article, the binding seem to be ok...
https://www.php.net/manual/en/pdo.constants.php

The question is, what the stored procedure is doing with that,
One option is to convert the values inside the MSSQL stored procedure...

The variable strPrimaryKeyIdoc I cannot find somewhere...

Can you make sure, that the connection works?
(You may have a look into the SQL Server log...)

I assume nothing is written into the SQL table, right?

Thank you for responding.
The Stored procedure merely inserts the values I pass to it into the record and returns the
unique primary key, @rRECID, of the record that was just inserted - that is the unique primary key of the record.
And that bit of information is used/needed in other function in the application.
$strPrimaryKeyIdoc was not corrected and it's okay because it is just writing a message to a customized processing log. But it should be changed to $primaryid ( and I will)
Nothing is inserted into the SQL table, and the only error message I receive is "C:\wamp\www\Emails\Emails-V7.php:793:null" which is the line where I retrieve the output parameter @RECID.
I am sure others have faced this problem, but I sure can not find their solution(s).

Hello,

just oine question for clarifiaction.
YOu used PDO before or have you rewritten this frp PHP 7.4?

as nothing is inserted....
If inside the try path an error occures, then the catch path should be triggered.
As this seems not to be the case....
Either the error happens before the try, means before or inside the connect, or the error is not recognized.
But as far as try or catch is executed, you should get something from your $strLogMsg Log message.

Do you get the echos before the connect?

The connect as it you should see in MS SQL Server log. You may enable the in the target database:


The you should see the login.
You may also echo out in PHP the UID and Password to see, how it looks like... 

Thank you for responding. I am upgrading this application from PHP 5.6 and no, I am a ,newbie at using PDO.
I am going to check out your suggestions. 
 I also think one of the problems is the record is 'not available' until I move off the record.
 In PHP 5.6 I had to move to next row to be able to retrieve the primary key of the newly inserted record.
 So, I am going to insert coding to do so, by  moving to next row.
And I may need to set TRANS COUNT OFF in my stored procedure.
No, nothing before the echoes  I have tested the connection and am able to successfully connect.
I will post again after my experimenting...sigh.
 

So, then the next step is the call into the stored procedure...
See some examples here...
https://stackoverflow.com/questions/9982466/executing-a-stored-procedure-with-pdo-sqlsrv

Just wondering why no error is triggered.

Thank you for responding.
 I changed the type of error reporting and that has help.
I had found that bit of coding earlier in the day and it didn't work for me.
I even changed the stored procedure to set NOCOUNT ON.
But with the above coding, I probably need to adjust the stored procedure  to accept the added input.
I will need to check on how to do so.

Okay, no progress - doesn't matter how I approach it, no records are inserted, no primary key returned.
Can it be that I cannot use PDO with MS SQL 2014 ? If not, then what do I use ?? Especially with stored procedures  whither they return a value or not??
PDO seems to work fine with select/ insert queries but I have not tried a delete commend yet.
I am posting my table definition, stored procedure and my current coding.
I have confirmed that I do have a successful database connection.
I tried to just insert a record using the stored procedure without trying to retrieve the primary key and that didn't work.
 No error message, just won't do it.
Doesn't matter what I do, it simply will not return primary key nor insert records and I need both to happen.
Any help would be appreciated.
This is my table definition:

   [dbo].[ins_importInfo]

   [IDocKey] [int] IDENTITY(1,1) NOT NULL,
   [RegionId] [int] NULL,
   [Supplier] [nvarchar](65) NULL,
   [IDoc] [nvarchar](50) NULL,
   [AmtRcvd] [money] NULL,
   [DateRcvd] [date] NULL,
   [DateProcessed] [datetime] NULL,
   [VerifyDate] [datetime] NULL,
   [Fees] [money] NULL,
   [LastReviewDate] [datetime] NULL,
   [Source] [nvarchar](50) NULL

Select allOpen in new window

This is my stored procedure:

USE [MYDATA]
GO
/****** Object:  StoredProcedure [dbo].[ins_importInfo]    Script Date: 6/26/2021 11:50:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ins_importInfo] 

   @RegionId int,
   @Supplier nvarchar(50),
   @IDoc nvarchar(50),
   @AmtRcvd money,
   @DateRcvd datetime,
   @Fees money,
   @RECID INT OUTPUT as
   Insert Into importInfo(RegionId,Supplier,IDoc,AmtRcvd,DateRcvd,Fees)
               Values(@AgencyId,@Supplier,@IDoc,@AmtRcvd,@DateRcvd,@Fees)       
           
SELECT @RECID = SCOPE_IDENTITY()
RETURN @RECID

Select allOpen in new window

And this is my function:

Function InsDocInfoRec($wrkRecvDate, $emailSendDate, $strIdocMoney, $strIDOCNbr)
{ 
   // DEFINE DB VARS
    $servername = $_SESSION['TestServer'];
    $dbname = $_SESSION['RegionDb'];
    $strRegionId = $_SESSION['RegionId'];
   
// INSERTS ONE PER TEXT FILE
   $srtRegionId = trim($_SESSION['RegionId']) ;
   $strSecTotal = trim($strIdocMoney);
   $strSecTotal = preg_replace('/[^0-9.-]/', '', $strSecTotal);
   $strSupplier = $_SESSION['GlobalSupplier'];
   $strFees = 0;
   
// NEW DATE FORMAT
$timestamp = strtotime($emailSendDate);
$wrkFixedEmailSentDate = date("Y-m-d", $timestamp);
ECHO '<BR><BR><BR>REGION ID: ' . $srtRegionId;
ECHO '<BR>SUPPLIER: ' . $strSupplier;
ECHO '<BR>IDOCNBR: ' . $strIDOCNbr;
ECHO '<BR>VENDOR/DETAIL TOTAL: ' . $strSecTotal;
ECHO '<BR>DATE RECEIVED: ' . $wrkFixedEmailSentDate  . '<BR><BR>';

    try {
    
       $connect = new PDO("sqlsrv:Server=$servername;Database=$dbname", $_SESSION['uid'], $_SESSION['pwd']);
        if($connect)
        {
            echo '<BR>INSIDE OF IF CONNECT';
          $insRec ='{:RECID = "CALL ins_importInfo(:RegionId,:Supplier,:IDoc,:AmtRcvd,:DateRcvd,:Fees)}';
          $stmt = $connect->prepare($insRec);
         
         $stmt->bindParam(':RECID',$RECID,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
         $stmt->bindParam(':RegionId',$srtAgencyId,PDO::PARAM_STR); 
         $stmt->bindParam(':Supplier',$strSupplier,PDO::PARAM_STR); 
         $stmt->bindParam('IDoc',$strIDOCNbr,PDO::PARAM_STR); 
         $stmt->bindParam(':AmtRcvd',$strSecTotal,PDO::PARAM_STR); 
         $stmt->bindParam(':DateRcvd',$wrkFixedEmailSentDate,PDO::PARAM_STR); 
         $stmt->bindParam(':Fees',$strFees,PDO::PARAM_STR);

         $stmt->execute();
         $stmt->nextRowset(); //SKIP INSERT RESULT
         $result = $stmt->fetch(PDO::FETCH_ASSOC);
         echo "<BR>Return value: " .  $RECID;

         $strLogMsg = 'IMPORTIDOCINFO-INSERTED: ' . strval($srtAgencyId)
                      . '-' . strval($strSupplier) . '-' . strval($strIDOCNbr)
                      . '-' .  strval($strSecTotal) . '-'      .strval($wrkFixedEmailSentDate)
                      . '-PRIMARY KEY:' . strval($primaryid) . PHP_EOL;
            RtnWriteLogMsg($strLogMsg);
        }
    } catch (PDOException $e) {
          echo $e->getMessage();
          $errmsg = $e->getMessage();
          echo '<BR>UNABLE TO INSERT RECORD INTO InsDocInfoRec</br>';
          echo '<BR>ERROR MSG: ' . $errmsg;
          echo '<BR>EXITING APPLICATION</br>';
          echo '<BR><BR> ** UNABLE TO INSERT DETAIL LINE INTO InsDocInfoRec **'; 
          $strLogMsg = 'SQL STATEMENT FAILED WITH ERROR: ' . $errmsg . PHP_EOL;
          RtnWriteLogMsg($strLogMsg);
          $nbr = ($nbr + 1);
          die($e);
      }
      
return $RECID;                         
}

Select allOpen in new window

Any help and/or suggestion is appreciated. Thank you in advance.

OK, you put the insert now on the PDO site...
Different method, buit if itdoes the job...., it is fine...

I guess the major problem is, that you send stings to a SQL stored procedure which SQL server will not accept. How SQL server communicates with the PDO call is a different point of few. The call usually delivers a status value back, which doesn't neccessarily be indicated as an error on the PDO side.

If you send non conform valued to a storec procedure, you have to handle the values inside the stored procedure..
So i.e. a money variuable comes as string, the stored procedure hast to CAST this variable into a money data type, otherwise SQL with not do anything.

So far, if you still want to play around with your old construction, you first may to start with a more simpler construction.
Copy your procedures (PDO as well as SQL) and remove first all non string values.

If you then aware about the base construction and can make sure it works, you can add the other values and see, how you can get them working on the SQL server side.

So, the solution is to resolve all errors step by step, starting at the basic structures and then to expand it in single steps. If you have too much error sources, you never knwo where to start. 

Thank you for responding. Good advice !
 It just surprised me that PDO didn't have descriptors for values of money and datetime- that threw me off.
Way off ! And I forgot about SQL deals differently with incoming data.
 In PHP 5.6, I could describe the incoming data as to money, datetime etc. in my coding when describing the parameters.
I am going to work at it, revamping the stored  procedure, as I want to know how to do it using that methodology.
Thank you again.  
*NOTE - in this application, since it is a stand-alone application that executes unattended - I am comfortable with using lastInsertId()  especially  I am using begin transaction - which isolates my transaction from any transaction that  is occurring at the same time, which is possible in a multi-user application. So the concern of the wrong primary key being returned is non-existent.