SQLRDD and windows' region numeric formats

[Claudio H]

Hi

According to windows' numeric format (decimal symbol) a direct query returns different results for a numeric value

For example if the real result is 2000
- when decimal separator is ".' (point) query returns 2000.00
- when decimal separator is "," (comma) query returns 200000.00

What do I have to set for my query to get the real result no matter how the workstation is configured?

Regards
Claudio H

[dlzc]

Dear Claudio H:

SQLRDD depends on the SQL engine. Which SQL engine are you using?

What OS is running on the server, and what on the application?

Just trying to get to a more complete problem definition. May not be required.

If the data stored with a decimal point, but read expecting a comma, will strip the decimal point. I would not be surprised if SQL stores the decimal point (or comma) as part of the string. It is not stored as a float, but a string of characters. The "thousands separators" are probably removed before storage.

Why don't you write to the database the different ways, and read back what you've stored?

David A. Smith

[Ariel Paredes]

Hi, Claudio H

i use sqlrdd
with sql server 2019
with driver msodbcsql-17-x64.msi
I connect without problem in the terminals
even if they change region
example:

#include "sqlrdd.ch"

#define RECORDS_IN_TEST 1000
#define SQL_DBMS_NAME 17
#define SQL_DBMS_VER 18

Request SQLEX
Request SR_ODBC

Request DBFNTX
Request DBFDBT

Request HB_LANG_ES
Request HB_GT_WVT_DEFAULT
Request HB_GT_WVT

PROCEDURE MAIN(cRDD)

Local nCnn, i, xCon
Local hDatos := Hash()

HSetAACompatibility(hDatos, .T.)

Cls
SetMode(25,80)

HB_LANGSELECT('ES')
HB_SETCODEPAGE('ES')

#pragma TEXTHIDDEN(1)
hDatos["Myip"] := "xxxxxxxxxx.dyndns.org"
hDatos["MyPort"] := ",1480"
hDatos["MyDatabase"]:= "mybase"
hDatos["UserName"] := "demo"
hDatos["Password"] := "demodemo"
cModo := " DEMO (Remoto)"
#pragma TEXTHIDDEN(0)

If cRDD == NIL
cRDD = "SQLRDD"
Endif

RDDSetDefault(cRDD)

SET AUTOPEN OFF
SET AUTOSHARE TO 1
SET TALK OFF
SET STATUS OFF
SET ECHO OFF
SET CONFIRM OFF
SET BELL OFF
SET DELETED ON
SET SCORE OFF
SET SAFETY OFF
SET SCOREBOARD OFF
SET CENTURY ON
SET EPOCH TO 1960
SET DATE FORMAT TO "DD/MM/YYYY"
SET DATE FRENCH

SETCANCEL(.F.)

Try
SR_SETSQL2008NEWTYPES(.F.)

cCon:="Driver={ODBC Driver 17 for SQL Server};Server="+hDatos["Myip"]+hDatos["MyPort"]+";Database="+hDatos["MyDatabase"]+";Uid="+hDatos["UserName"]+";Pwd="+hDatos["Password"]

nCnn := SR_AddConnection(CONNECT_ODBC, cCon )

if nCnn <= 0
Alert("Fallo de conexión ...")
SR_EndConnection(nCnn)
Cls
Quit
EndIf

Catch
Alert("No se conecta ...")
Cls
Quit
End
Return

good luck

[Claudio H]

Hi all

I have no problem connecting to the DB (Ariel), using MSSQL with {ODBC Driver 17 for SQL Server}
The workstation running the xHb app (David) has windows 10 OS, the server where MS-SQL is installed is a windows server machine.

I'm inserting a value in a numeric column [importe numeric (15,2)] for example 2000 (no decimals, no decimal separator included)

When retrieving the data via SQLRDD:
cSQLCommand:="SELECT SessionID, Importe FROM MOBINVSE"
aAlivios:=ARRAY(0)
nSQLRet:=oSQL:Exec(cSQLComm,,.T.,@aAlivios,,,,,,,.F.)

If windows' numeric local setting for decimal separator is "." (point) aAlivios[1][2] value is 2000 but if decimal separator is "," (comma) the returned value in the array element is 200000

I can change this setting back and forth as many times as I wish and the result changes according to this setting.

I'm guessing it has to do with an internal SQLRDD setting but couldn't find any helping reference in the docs.

Any idea?

Regards
Claudio H

[dlzc]

Dear Claudio H:

On Monday, October 3, 2022 at 12:29:26 PM UTC-7, Claudio H wrote:
...

> If windows' numeric local setting for decimal separator is "." (point)
> aAlivios[1][2] value is 2000 but if decimal separator is "," (comma)
> the returned value in the array element is 200000

OK, so the data is stored with the decimal point "recorded", and the comma is stripped as if it were a thousands separator. You are probably correct on "internal SQL setting", and may or may not have to do with the data type SQL thinks it is. Should be a fairly consistent test, were you opening / writing / reading a file, maintained also by others.

I don't know any SQL, so if you know how to contact Luiz (on here, been a long while), maybe ask him to come on here and comment? But SQLRDD is commercial xHarbour, best to be asking them.

David A. Smith

[Claudio H]

David

I already contacted Luiz but unfortunately he quit answering my emails long time ago...

Claudio H

[dlzc]

Dear Claudio H:

On Monday, October 3, 2022 at 2:36:56 PM UTC-7, Claudio H wrote:
> I already contacted Luiz but unfortunately he quit answering
> my emails long time ago...

Hope he is OK.

Commercial xHarbour newsgroup is probably your only hope.

David A. Smith

[Ariel Paredes]

Try

cSQLCommand:="SELECT SessionID, Importe FROM MOBINVSE"

aAlivios :={}
oSql := SR_GetConnection()
*** nSQLRet:=oSQL:Exec(cSQLCommand,,.T.,@aAlivios,,,,,,,.F.)
nSQLRet:=oSql:exec(cSQLCommand,,.t.,@aAlivios) <- here


****************
*Read here
****************
Method Exec()
Executes a SQL statement, optionally retrieving the result set

› Syntax


Exec( <cCommand>, [<lMsg>], [<lFetch>], [<aArray>], [<cFile>, [<cAlias>]], [<nMaxRecords>], [<lNoRecno>], [<cRecnoName>], [<cDeletedName>], [<lTranslate>] ) ==> NIL


› Arguments



<cCommand> SQL command to execute
<lMsg> If .T. (default) generates a run time error if there is an error in SQL
<lFetch> If .F. (default), the result set is not recovered. If .T., the result set is retrieved as the parameters
<aArray> Array that will receive the result set if he informed reference, and if lFetch be with .T.
<cFile> DBF file name that will be used to store the result set in the case lFetch is .T.. This option has priority over the parameter <aArray>, ie, if informed of the name of the file is not written in the result set array of the previous one. <cFile> will be created with the structure of the result set of the SQL statement and will be opened exclusively after the implementation of the Method.
<cAlias> Alias of DBF file to be created in the option above.
<nMaxRecords> Maximum number of rows of result set to recover
<lNoRecno> If .F. (default) includes the Recno() column in the result set. If .T., does not add
<cRecnoName> Recno() Column Name
<cDeletedName> Deleted() Column Name
<lTranslate> If .T. (default) returns the information contained in Multi Language in the current language

[Ella Stern]

Just a guess: try to set the ODBC connection string keyword Regional to "no"
https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver16

[Claudio H]

Ariel, Ella

Thank you both for your suggestions but none worked.
So far only way to success is changing number formats in control panel (decimal symbol and digit grouping symbol)

SSMS returns the correct values, no matter the settings.

Regards
Claudio H

[Ariel Paredes]

Hi, claudio

as an example
I add image

in the terminal change of region
affects excel
but the application
continue in spanish mexico
when i read and record
the server is in spanish mexico
by default

image:
https://mega.nz/file/Euwy1BhK#5KAKiwA61Wqo0dZ4LSnigmKKqqSwvkpPYMrD2D-STDE

[Ella Stern]

I think there is possible to consult programmatically the Windows Registry path
HKEY_CURRENT_USER\Control Panel\International
with key "Locale", or "LocaleName" or "sDecimal" ... and then you can proceed accordingly.

Source:
https://renenyffenegger.ch/notes/Windows/registry/tree/HKEY_CURRENT_USER/Control-Panel/International/index

[Claudio H]

Hi Ella

Thanks for this info.

It really helps as a temporary shortcut while hoping anyone can check and find out if the origin of this issue is within sqlrdd or maybe in xhb's odbc source.

Regards

Claudio H

[Claudio H]

Update

When executing SELECT importe, CAST(importe AS VARCHAR) FROM MYTABLE I'm getting:
50000.00
500.00

Claudio H

[Ella Stern]

I hope this will help people in other edge cases as well.