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 ONGOSET QUOTED_IDENTIFIER OFFGOALTER 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
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
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 !!
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
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.
Open in new window