Link to home
Start Free TrialLog in
Avatar of Marthaj
MarthajFlag for United States of America

asked on

PHP and using a stored procedure to insert money value

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]
/****** Object:  StoredProcedure [dbo].[ins_new_importIDocInfo]    Script Date: 12/6/2019 7:46:07 PM ******/
ALTER PROCEDURE [dbo].[ins_new_importIDocInfo] 
   @StoreId int,
   @Supplier nvarchar(50),
   @IDoc nvarchar(50),
   @AmtRcvd money,
   @DateRcvd datetime,
   Insert Into importIDocInfo(StoreId,Supplier,IDoc,AmtRcvd,DateRcvd)

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(sqlsrv_errors(), true));
                        if (sqlsrv_execute($mystmt))
                                while ($res = sqlsrv_next_result($mystmt))
							//  	print_r($myparams); 
							//	 print_r(sqlsrv_errors(), true);
                               $primaryid = $myparams[@RECID];
			// echo 'Primary Key Returned   ' .  $primaryid . '<BR><br>';
	return $primaryid;

Open in new window

Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

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

Open in new window

Avatar of Marthaj


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.
if you run the command in SSMS you will see the resulting value which you can insert into your table
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
Avatar of Marthaj


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.
Avatar of Marthaj


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 !!
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
Please post the command you are submitting versus the command in the php ..
Avatar of Marthaj


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

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

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.
Avatar of Marthaj
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial