PHP and using a stored procedure to insert money value

dogsareit
dogsareit used Ask the Experts™
on
I am using PHP 5.6.25, developing localhost -Windows 10.
I am passing a money value to my stored procedure. The money value was extracted from an email. It contains a decimal point - no currency symbol etc. -
i.e. 1234.67
 I have trimmed the value and when I view it via I.E view source, it doesn't appear that I am picking up any trash, and that doesn't mean it isn't just because I can "see" it displayed. But my stored procedure sure doesn't like the value I am passing.
I have used this very same stored procedure, under like circumstances i.e. pulling data, including a money amount  that only contains a decimal point, and it works every time.
The table column is defined as money.
If I set my variable to a value i.e. $mymoney = '123.66' - it will insert the record every time. I read about money_format but that is invalid in Windows.
What must I do to ensure that I have stripped the parsed value to just numbers a  decimal point ?? What am I failing to understand ?? or Do ??
Thank you.



I have included my stored procedure.
USE [SomeDB]
GO
/****** Object:  StoredProcedure [dbo].[ins_new_importIDocInfo]    Script Date: 12/6/2019 7:46:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ins_new_importIDocInfo] 
   @StoreId int,
   @Supplier nvarchar(50),
   @IDoc nvarchar(50),
   @AmtRcvd money,
   @DateRcvd datetime,
   @RECID INT OUTPUT as
   Insert Into importIDocInfo(StoreId,Supplier,IDoc,AmtRcvd,DateRcvd)
               Values(@StoreId,@Supplier,@IDoc,@AmtRcvd,@DateRcvd)       
           
SELECT @RECID = SCOPE_IDENTITY()
RETURN @RECID

Open in new window



And my actual coding for the stored procedure"
               $myparams['RECID'] = intval(-2);
                $myparams['StoreId'] = $srtStoreId;
                $myparams['Supplier'] = $supplier;
                $myparams['IDoc'] = $wrkIDOCNbr;
	       $myparams['AmtRcvd'] = $strChkTotalMoney;
                $myparams['DateRcvd'] = $wrkFixedEmailSentDate;
          
                $procedure_params = array(
                    array(&$myparams['RECID'], SQLSRV_PARAM_OUT),
                    array(&$myparams['StoreId'], 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));

               	$myconnect = sqlsrv_connect($sname,$wrkinfo);
                $sqltxt = "EXEC ins_new_importIDocInfo @RECID= ?, @StoreId= ?, @Supplier= ?, @IDoc= ?, @AmtRcvd= ?, @DateRcvd= ? ";

				 $mystmt = sqlsrv_prepare($myconnect,$sqltxt, $procedure_params); 
                if ($mystmt == false)
                  {
                        trigger_error(sqlsrv_errors(), E_USER_ERROR);
						print_r($myparams); 
		                (print_r(sqlsrv_errors(), true));
		                sqlsrv_free_stmt($mystmt);
	                    sqlsrv_close($myconnect);
		                echo 'ERROR ** UNABLE TO CREATE IMPORTIDOCINFO RECORD';
                   }else{
                        if (sqlsrv_execute($mystmt))
                          {
                                while ($res = sqlsrv_next_result($mystmt))
								{
									
								}
							//  	print_r($myparams); 
							//	 print_r(sqlsrv_errors(), true);
                               $primaryid = $myparams[@RECID];
						  }	  
				  }                  
             sqlsrv_free_stmt($mystmt);
			 sqlsrv_close($myconnect);
			// echo 'Primary Key Returned   ' .  $primaryid . '<BR><br>';
	return $primaryid;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Select (cast('12345.678' as decimal(10,2)))as [RecievedMoney];

Open in new window

Author

Commented:
I am confused by your select statement. I mean if I was selecting etc, I understand.
 BUt I am inserting the money  value via parameters passed to a stored procedure into the table.
Top Expert 2016

Commented:
if you run the command in SSMS you will see the resulting value which you can insert into your table
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Distinguished Expert 2017

Commented:
Double check the order of the parameters you are passing.

You gave them in an incorrect order and adding unnecessary info
Your sp looks for storied, supplier, idoc, amt..,datercvd, recid

The sp call is exec sp ?, ?, ?, ?, ?

I.e if you exec the sp directly it should match the entry in the php script.

Writeout the SQL string in sqlttxt and confirm that works on the SQL server as written

Author

Commented:
Thank you for responding.
This is a stored procedure that is returning the SCOPE_IDENTITY of the newly inserted record.
RECID returns the SCOPE_IDENTITY of the newly inserted record. It is Parameter OUT not in.

Author

Commented:
Dave, I will try that. In (stored procedures) to execute the procedure and pasted the values from my IE and it inserted it just fine. Strange thing is this is the exact coding and stored procedure which I use in extracting/inserting for another client. And it works just fine. There is very little difference between the two as to way the data is extracted and the type etc. But there is something with that money value, and I sure don't see it.
Nor did I expect it either !!
Distinguished Expert 2017

Commented:
Look at your

Does the sp work directly being executed?

my point deals with whether the thing shoukd be defined as a transaction.

It is up to you whether the scope_identity is returned in a variable or the answer

You could if not mistaken return (select @recvid)

You are not checking
Distinguished Expert 2017

Commented:
Please post the command you are submitting versus the command in the php ..

Author

Commented:
Arnold, I don't quite understand what you are asking/wanting. My directions as to in/out are clearly defined in the php coding, and selecting and returning the RECID is clearly defined. AS they are in the sproc.
How I structured the coding and the stored procedure was what I learned with classes with MS.

This is contained within my sproc to return the scope_identity
SELECT @RECID = SCOPE_IDENTITY()

Open in new window


My parameters define what and which direction in PHP which matches the sproc.
This line below retrieves the RECID  - AFTER moving off the record - which is correct.
You can not retrieve the primary id of a newly inserted record until after you move off the record.

  $primaryid = $myparams[@RECID];

Open in new window

Distinguished Expert 2017

Commented:
When you run the sp on the sql server directly

Declare @Recvid int

Exec ins_new_importidocinfo @RECID=@Recsvd,@amtsrcvd='123.12'....

Output from the pop what the exec command would be and use the exact same thing to run on the SQL.
Thank you both for helping and responding.
Here is what happened. When I extracted the money amount, and displayed it, the money appeared along with some whitespace. I trimmed the money amount to eliminate the whitespace- aft and fore.
but it still had something hanging on to it, which did not show in "View Source" in I.E.
How I found it was I executed a strlen on the money value, and WOW !
It should have only been 8 positions long, including the decimal point, but strlen returned a
length of 21 ! So, I scrubbed to only contain numerals a decimal point.
I do not know what those characters were but they are gone now and it inserts all values
as I wanted and returns the proper SCOPE_IDENTITY of the newly inserted record.
Again, I thank you both.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial