We help IT Professionals succeed at work.
Get Started

PHP and using a stored procedure to insert money value

dogsareit
dogsareit asked
on
68 Views
Last Modified: 2019-12-06
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
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE