comma problem

Anon

Hello there,

We use in Holland for a decimal separator a ',' ( comma ) The comma is also used to separate values in insert strings. We use a exestring like this:

UPDATE TBL_M_CMP SET M_CMP_GEOMETRY = MDSYS.SDO_GEOMETRY(2002,NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(0,1 , 0,15, 0,44 , 0,34)) WHERE M_CMP_ID = 168;

When we try to let oracle eat this string oracle rejects it!!! ( of course )

How do i solve the problem with the comma's We already tried the '\' as a datalinkescape character but that also fails.

regards Ralph

[deleted]

Try processing the values with number_format() in PHP to format them with a dot instead of a comma.

Parhaps you can also get Oracle to eat your data by quoting it.

riese

i always have best results when i quote everything that is not sql.

e.g.
UPDATE TBL_M_CMP SET M_CMP_GEOMETRY = MDSYS.SDO_GEOMETRY('2002',NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY('1','2','1'), MDSYS.SDO_ORDINATE_ARRAY('0,1' , '0,15', '0,44' , '0,34')) WHERE M_CMP_ID = '168';

(this works fine for me)

Anon

Hi Ralph,

I had something similar to this last week (I'm in Spain) with MySQL.

In my case the client had provided a MySQL file with all prices marked with a comma, eg. 123,34 and 345,35 etc. The Db was reading these but displaying them incorrectly as it would read no further than the comma (so all values became 123,00 and 345,00 etc).

I was able to solve this finally as the client was able to export the SQL file from their Db (Corel Paradox) with the dot in place of the comma. If that isn't an option then you need to process the file to change those commas before the data is loaded into the database.

Importing the data with the dot does not mean all numbers need be displayed with a dot - in PHP the number_format() function allows you to specify the conventions used for the display of decimals.

Hope this helps,

Gary