ExchangeRate problem

[GoogleCodeExporter]

e-commerce version: 3.1 23/10/2013

SilverStripe version: 3.1 from github

In my spanish server, when I select another currency the following error occurs:

[User Error] Couldn't run query: UPDATE "Order" SET "CurrencyUsedID" = 3, 
"ExchangeRate" = 1,44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903 
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near '44942, "LastEdited" = 
'2013-10-24 20:10:21' where "ID" = 903' at line 1
GET 
/shoppingcart/setcurrency/USD/?SecurityID=3b0a38496ab63fde877047306a1504fba1577b
1d

Line 592 in 
/var/www/vhosts/sendasoft.com/httpdocs/framework/model/MySQLDatabase.php
Source

583     }
584 
585     public function databaseError($msg, $errorLevel = E_USER_ERROR) {
586         // try to extract and format query
587         if(preg_match('/Couldn\'t run query: ([^\|]*)\|\s*(.*)/', $msg, 
$matches)) {
588             $formatter = new SQLFormatter();
589             $msg = "Couldn't run query: \n" . $formatter->formatPlain($matches[1]) . 
"\n\n" . $matches[2];
590         }
591 
592         user_error($msg, $errorLevel);
593     }
594 
595     /**
596      * Return a boolean type-formatted string
597      *
598      * @param array $values Contains a tokenised list of info about this data 
type

Trace

    Couldn't run query: UPDATE "Order" SET "CurrencyUsedID" = 3, "ExchangeRate" = 1,44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903' at line 1
    MySQLDatabase.php:592
    MySQLDatabase->databaseError(Couldn't run query: UPDATE "Order" SET "CurrencyUsedID" = 3, "ExchangeRate" = 1,44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903' at line 1,256)
    MySQLDatabase.php:144
    MySQLDatabase->query(UPDATE "Order" SET "CurrencyUsedID" = 3, "ExchangeRate" = 1,44942, "LastEdited" = '2013-10-24 20:10:21' where "ID" = 903)
    Database.php:633
    SS_Database->manipulate(Array)
    DB.php:245
    DB::manipulate(Array) 

The problem occurs for the conversion of the ExchangeRate amount to string:
"ExchangeRate" = 1,44942

This should be:
"ExchangeRate" = 1.44942

Regards,
Jose A.

Original issue reported on code.google.com by supp...@sendasoft.com on 24 Oct 2013 at 6:32

[GoogleCodeExporter]

I am having a look here: 
http://spain.angloinfo.com/money/banking/numerical-formats-standards/.  It 
appears that the format in the SQL is correct, but that the DB does not 
understand it?

Original comment by nfranc...@gmail.com on 26 Oct 2013 at 8:28

• Changed state: PeerReview

[GoogleCodeExporter]

When SilverStripe convert the number 1.4492 to a string with 
mysqli::real_escape_string (MySQLDatabase.php) put a comma instead of a point. 
This causes that the SQL statement considered the number 1,4492 two 
values​​, which produces the error. Apparently this is an error of 
SilverStripe.

What do you think?

Regards,
Jose A.

Original comment by supp...@sendasoft.com on 26 Oct 2013 at 6:55

[GoogleCodeExporter]

"When SilverStripe convert the number 1.4492 to a string with 
mysqli::real_escape_string (MySQLDatabase.php) put a comma instead of a point. 
This causes that the SQL statement considered the number 1,4492 two 
values​​, which produces the error. Apparently this is an error of 
SilverStripe." 

can you prove this?  If so, we need to raise a ticket with SS.  Let me know.

The error could also be within ecommerce, inserting the wrong value into the 
SQL statement.  Let me know. 

Nicolaas

Original comment by nfranc...@gmail.com on 27 Oct 2013 at 4:53

• Added labels: Milestone-Release2.0

[GoogleCodeExporter]

I have opened a ticket in silverstripe-framework.
From what I've researched the problem is in the function addslashes 
(MySQLDatabase.php)
I give more details at this link.
https://github.com/silverstripe/silverstripe-framework/issues/2606
Please check it to see if it is correct.

Thanks,
Regards,
Jose A.

Original comment by supp...@sendasoft.com on 27 Oct 2013 at 7:40

[GoogleCodeExporter]

Now this works fine.
Nicolaas What have you done?
Was it a problem of ecommerce module?

Thanks,
Regards,
Jose A.

Original comment by supp...@sendasoft.com on 28 Oct 2013 at 1:42

[GoogleCodeExporter]

No, sorry, I was wrong.
This problem is not solved yet. :(

Regards,
Jose A.

Original comment by supp...@sendasoft.com on 28 Oct 2013 at 1:52

[GoogleCodeExporter]

Waiting on Jose. I think this is part of e-commerce and should be fixed ASAP!

Original comment by nfranc...@gmail.com on 29 Oct 2013 at 2:01

• Added labels: Priority-Critical
• Removed labels: Priority-Medium

[GoogleCodeExporter]

This problem is because the silverstripe-fluent module uses setlocale(LC_ALL, 
$locale).
Apparently the framework is not well prepared for changes of locale using this 
function.
It's something that should solve silverstripe.
Definitely, the framework does not work properly when using this function.

If the ecommerce module generates SQL statements can be also affected by this 
function.

https://github.com/silverstripe/silverstripe-framework/issues/2606

Regards,
Jose A.

Original comment by supp...@sendasoft.com on 29 Oct 2013 at 6:58

[GoogleCodeExporter]

This is a silverstripe wide issue... Lets hope it is solved soon.

https://github.com/silverstripe/silverstripe-framework/issues/2606

Original comment by nfranc...@gmail.com on 26 Apr 2014 at 9:59

• Changed state: WontFix