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.
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.
And my actual coding for the stored procedure"
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
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;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
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
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.
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.
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 !!
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
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, 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.
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()
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];
When you run the sp on the sql server directly
Declare @Recvid int
Exec ins_new_importidocinfo @RECID=@Recsvd,@amtsrcvd='
Output from the pop what the exec command would be and use the exact same thing to run on the SQL.
Declare @Recvid int
Exec ins_new_importidocinfo @RECID=@Recsvd,@amtsrcvd='
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial