Avatar of Marthaj
Marthaj
Flag 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]
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

PHPMicrosoft SQL Server

Avatar of undefined
Last Comment
Marthaj

8/22/2022 - Mon
David Johnson, CD

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

Open in new window

Marthaj

ASKER
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.
David Johnson, CD

if you run the command in SSMS you will see the resulting value which you can insert into your table
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
arnold

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
Marthaj

ASKER
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.
Marthaj

ASKER
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 !!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
arnold

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
arnold

Please post the command you are submitting versus the command in the php ..
Marthaj

ASKER
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

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.
ASKER CERTIFIED SOLUTION
Marthaj

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.