Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#5709 closed defect (fixed)

Ogr Oci locale not well supported

Reported by: giorgiomugnaini Owned by: Even Rouault
Priority: normal Milestone: 2.0.0
Component: default Version: unspecified
Severity: normal Keywords: oci, locale, comma, number, double
Cc:

Description

Performing queries on a database with italian locale (decimal numbers have comma "," as decimal separator ), we observe that GetFieldAsDouble?() returns wrong results.

Change History (7)

comment:1 Changed 7 years ago by giorgiomugnaini

Examining the code of:

 int OGROCISession::EstablishSession( const char *pszUserid, 
                                     const char *pszPassword,
                                     const char *pszDatabase )

we have applied the following patch to grocisession.cpp, row 289.

	OGROCIStatement     oSetNLSNumericFormat( this );
	if( oSetNLSNumericFormat.Execute( "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '" ) != CE_None )
		return FALSE;

This patch seems to fix aboveseen problems:

comment:2 Changed 7 years ago by Even Rouault

This is an issued I encountered too, and workarounded it as indicated below, but your fix is probably better

<li> It might be necessary to define the environment variable NLS_LANG to "American_America.UTF8" to avoid issues with floating point numbers being truncated to integer on non-English environments.</li>

comment:3 Changed 7 years ago by Jukka Rahkonen

"American_America.UTF8" helps also with translating non-ASCII characters from shapefiles on Finnish locale but it has remained as a secret for me how it should be done in a reliable way. Sometimes it works, sometimes not. As a workaround I convert shapefiles first into Spatialite. Don't you have any troubles with the Italian characters with accents?

comment:4 Changed 7 years ago by Jukka Rahkonen

We have noticed that while NLS_LANG to "American_America.UTF8" does help us with writing non-ASCII characters into Oracle, it does not help when we read decimal numbers from Oracle. Numbers get truncated to integers what ever we try.

If "desc table" from Oracle shows column type as FLOAT(126) then the decimals comes correctly but if the type is shown as NUMBER, NUMBER(38) or something like that then the decimals are truncated.

comment:5 Changed 7 years ago by Even Rouault

Milestone: 2.0
Resolution: fixed
Status: newclosed

trunk r28434 'OCI: force NLS_NUMERIC_CHARACTERS to ". " (patch by giorgiomugnaini, #5709)'

With that, and combined with r28429 (#3415) and general fixes related to locale issues in GDAL seems to solve most/all locale issues with OCI. I can now run ogr_oci.py autotests with French locale (in the sense of the process locale) and Oracle configured with French locale settings as well.

So hopefully problems solved.

comment:6 Changed 7 years ago by Jukka Rahkonen

At least I can now read non-truncated decimal numbers from Oracle with Finnish locale which is great.

comment:7 Changed 6 years ago by Even Rouault

Milestone: 2.02.0.0

Milestone renamed

Note: See TracTickets for help on using tickets.