en-us locale but not always ???

[Filip Van Wynsberghe]

Hi,

Here in Belgium, as in most West-European countries, we use a comma as decimal separator and a dot as thousands separator. As I've seen on the forum, Basic4ppc always uses the "en-US" locale for conversions to avoid locale specific problems...

It is a pitty that Basic4ppc does not take the Windows locale settings into account, but I can 'live' with this limitation. I really needed a decimal comma, we can take care of this.

However, the reader.getValue() function takes the Windows local into account... I have a value 0.5 in a (SqLite) database and when getting this value into a numeric variable I'm getting a value of 5 !!! Reader.getvalue() gives me 0,5 which is transformed into 5 as Basic4ppc 'ignores' comma's (at least if they are in front of the decimal point).

Of course I can take care about this too, but all this is prone to errors, especially if there are some situations where you have to do a conversion and in other cases you shouldn't...

Is there a way (some settings) to avoid conversions (in some cases) and to have Basic4ppc to be consistent in all cases? Or are there some work-arounds? All suggestions are wellcome.

Are there other known (library) functions also behaving this way?

Thanks for your feedback.

 

[Erel]

Can you upload a small database and a program that demonstrate this issue?

 

[Filip Van Wynsberghe]

Exemple

Hi Erel,

Here's a small exemple. I have a form where I can enter a value in a first textbox. Then this value is saved in a database, and immediatly afterwards retrieved and shown in a second textbox. To complete the exemple, I store the value from the second textbox in the database, and retrieve the value in a third textbox.

When entering 1.5 in the first textbox and having the regional settings of my PC being 'English (United States)', then this works fine: i have 1.5 shown in the 2nd and 3th textbox.

However, When entering 1.5 in the first textbox and having the regional settings being 'Dutch (Belgium)', then I have 1,5 shown in the 2nd and 1 in the 3th textbox.

 

[Filip Van Wynsberghe]

Additional exemple

Hi Erel,

I've chenged the previous exemple a little bit: When I temporarily store the value from a reader.getvalue() in a variable before moving it into a form field, I have even more strange results:

Entering 1.5 in the first textbox (textbox1), storing it into a database, retrieving it directly in a form field (textbox2), I get 1,5. However, retrieving the value in a numeric variable and then assigning this variable to a formfield (textbox4), I get 15 !! This is of course doing the test with regional settings 'Dutch (Belgium').

Whe doing the test with regional setting English (United States), it works normal.

 

[Basic4Life]

There's an additional library for local formatting Local library.
The numeric object should be able to handle your database format and your local format and allow for switching between them.

 

[Filip Van Wynsberghe]

There's an additional library for local formatting Local library.
The numeric object should be able to handle your database format and your local format and allow for switching between them.

Thanks for your reply, Basic4Life. I've been searching around on the forum, and I've seen that locale library too and actually, I might be using it, but this is not going to help me I fear.

The library allows you to format values, but my problem starts before the formatting and must be somewhere in the interaction between basic4ppc and the sql library (I assume).

When I read a value of 1.5 from a (sqlite) database into a numeric variable (eg myvar = dbreader.getvalue()), then the value of the numeric variable (internally, before showing on the form) = 15!! Reading that same value directly into a form field (eg textbox.text = dbreader.getvalue(), then the result = 1,5 (with a comma and thus <> 1.5)

textbox.text = dbreader.getvalue() ==> textbox.text = 1,5

myvar = dbreader.getvalue())
textbox.text = myvar ==> textbox.text = 15

 

[mjcoon]

Thanks for your reply, Basic4Life. I've been searching around on the forum, and I've seen that locale library too and actually, I might be using it, but this is not going to help me I fear.

I don't understand why you say that. You have already accepted that native B4PPC doesn't help with locale. This means that whenever you are converting, explicitly or not, between a string and numeric you have to use a locale-aware converter such as the library cited. IIRC all SQLITE fields are actually held as strings, so you are going to get conversions and will need to use the library. This is annoying, but should work...

Mike.

 

[Basic4Life]

Thanks for your reply, Basic4Life. I've been searching around on the forum, and I've seen that locale library too and actually, I might be using it, but this is not going to help me I fear.

The library allows you to format values, but my problem starts before the formatting and must be somewhere in the interaction between basic4ppc and the sql library (I assume).

When I read a value of 1.5 from a (sqlite) database into a numeric variable (eg myvar = dbreader.getvalue()), then the value of the numeric variable (internally, before showing on the form) = 15!! Reading that same value directly into a form field (eg textbox.text = dbreader.getvalue(), then the result = 1,5 (with a comma and thus <> 1.5)

textbox.text = dbreader.getvalue() ==> textbox.text = 1,5

myvar = dbreader.getvalue())
textbox.text = myvar ==> textbox.text = 15

I don't understand why you say that. You have already accepted that native B4PPC doesn't help with locale. This means that whenever you are converting, explicitly or not, between a string and numeric you have to use a locale-aware converter such as the library cited. IIRC all SQLITE fields are actually held as strings, so you are going to get conversions and will need to use the library. This is annoying, but should work...

Mike.

As Mike points out, the database returns the number in the local number format as a string. When B4PPC interprets that string as a number, it ignores the comma rather than raising an error. That's why GetValue gives you 1,5 and the variable is 15.
That's why we use the local aware parser.

Attached is your example using the library.
It only works correctly if the correct local decimal seperator is used.

How you want to handle the use of the wrong seperator is up to you.
(I didn't address that in the attached example)
Since B4PPC ignores a wrong seperator rather than throwing an error,
you could either make your own error notification or allow the use of
both seperators by making sure they are handled correctly.

So in your case comma numbers are only used for display and the parsed double from the comma number is used for internal use e.g. calculations or storing in the db.

 

[Filip Van Wynsberghe]

I don't understand why you say that. You have already accepted that native B4PPC doesn't help with locale. This means that whenever you are converting, explicitly or not, between a string and numeric you have to use a locale-aware converter such as the library cited. IIRC all SQLITE fields are actually held as strings, so you are going to get conversions and will need to use the library. This is annoying, but should work...

Mike.

Hi Mike,

Reason why I said "I fear that the locale library wouldn't solve my problem" is that my impression is that the problem occurs at an earlier stage (dbreader.getvalue - SQL library).

In fact, you say that SQLITE fields are actually held as strings, and that is indeed what I thought too. I tought that the field type in SQLITE databases didn't matter. Or, this is not true (unlikely), or, the field type DOES matter for the dbreader.getvalue behaviour (most likely)!!!

By changing the field type to TEXT, or using a function returning a string (eg TRIM) in the select statement on a numeric field (INTEGER, REAL, NUMERIC), the dbreader.getvalue returns me a correct value of 1.5 in both a numeric or string variable for a database field holding 1.5 (where dbreader.getvalue was returning 1,5 in a string variable and 15 in a numeric variable if the database field was a numeric field.

So, defining my numeric fields as TEXT, and using your locale library, will probably solve my problems.

Thanks,

Filip

 

[mjcoon]

So, defining my numeric fields as TEXT, and using your locale library, will probably solve my problems.

I'm sure that is correct. The point is that the SQL help says:

GetValue (Index As Int32) As String

In other words it is always returning a string value to B4PPC which is then doing (correctly or not!) whatever conversion is needed by the "=" operator. The way the SQL field is defined is not going to alter that unless the SQL library is also doing its own conversion. (Which is possible but I cannot analyse your reports well enough to tell!)

So I agree that avoiding any simplistic locale-[in]dependent built-in conversions and doing your own with the help of a library with known rules is probably best...

Mike.

 

[Filip Van Wynsberghe]

The way the SQL field is defined is not going to alter that unless the SQL library is also doing its own conversion. (Which is possible but I cannot analyse your reports well enough to tell!)

Mike.

Mike,

Actually the SQL library must be doing its own (wrong) conversion based on the database field types. Defining database fields as TEXT and not using the locale library, the values returned from the getvalue are correct and always in 'en-us locale' as written elsewhere on the forum. Numeric database fields are converted but are wrong.

Meanwhile I've tested the local library to have numbers displayed according the 'Dutch - Belgian locale' and everything works fine now.

Thanks again:sign0098: