The Server machine is running windows server 2008 and has Danish regional settings applied for windows.
All client machines also have Danish regional settings.
We have applied English as the default locale in the server admin pages, with number, integer and date also having english formats.
Our solution does not have the menu enabled on the smart client.
Our problem is that the smart client is using ‘,’ for decimal places and we cannot seem to get it to use’ .’
We are actually installing this system at our clients headqurters today so it is urgent that we have this fixed.
Can anybody comment on this issue.
We are running Java 6 update 13 on both clients and server
Update
We have just tested setting the locale from the smart client and this works fine, our setting are exactly the same as what is in the app server backend.
Why would this not be being reflected in the client??
jcompagner:
default locale is not pushed to the client because that is a setting that java gets from the system so should be correct.
Also if your userbase is a mixed base, then some do want to see this: “10,000.00” and others want to see “10.000,00” because that is what the know.
Johan, as a follow on from McCourt’s post we like a little more clarity please. If we cant set locale info from the server we have a couple of questions:
In what circumstances does the ‘Server’ Locale settings take effect?
How can we allow users to see their preferred number format (in Denmark currently so that would be 3,25 for example) but still have the code and sql generated use the correct format of 3.25?
Running a single app for mixed users (some UK some not) how can we allow some to use a different locale setting where we don’t expose the Servoy toolbar, considering that at the moment we are having to use the i18n setting in solution startup to force all clients to UK? Is it necessary for us to create a user preference box so they can set it themselves.
Right now, if the local client shows 3,25 on screen then that is what the SQL run for searches etc uses. Should we be applying some technique to ensure the sql fired by Servoy is using ‘.’ for numbering and not ‘,’?
Very much appreciate your feedback on this as its a challenge for us with a client right now.
Cheers
BTW Much Kudos to Patrick for once again loaning us the shovel to dig our selves out of a hole !
In what circumstances does the ‘Server’ Locale settings take effect?
only for server side clients like batchclients or other server side clients where we cant get it from the browser request.
Kahuna:
2. How can we allow users to see their preferred number format (in Denmark currently so that would be 3,25 for example) but still have the code and sql generated use the correct format of 3.25?
this works fine by default, what you see is a string (parsed/formatted) representation of an Number. But when the sql uses it it will not be a string but just that Number object.
So it is just presentation.
Kahuna:
3. Running a single app for mixed users (some UK some not) how can we allow some to use a different locale setting where we don’t expose the Servoy toolbar, considering that at the moment we are having to use the i18n setting in solution
startup to force all clients to UK? Is it necessary for us to create a user preference box so they can set it themselves.
why would it be necessary to create a preference box? Just let webstart/java configure that client to use the locale that they have set on there system.
Kahuna:
4. Right now, if the local client shows 3,25 on screen then that is what the SQL run for searches etc uses. Should we be applying some technique to ensure the sql fired by Servoy is using ‘.’ for numbering and not ‘,’?
Kahuna wrote:
2. How can we allow users to see their preferred number format (in Denmark currently so that would be 3,25 for example) but still have the code and sql generated use the correct format of 3.25?
this works fine by default, what you see is a string (parsed/formatted) representation of an Number. But when the sql uses it it will not be a string but just that Number object.
So it is just presentation.
Thanks for that feedback Johan - but we are obviously doing something wrong because the number format seen on screen was actually used in the SQL string - which of course failed. It seems the format as shown was actually adopted for the SQL and we had an error like "Error in SQL near ‘,’ " - and this was discovered to be a number being used as formatted instead of as an object?
The only way we could fix it was to force the i18n string in solution startup?
how is that sql build up? Was it a query generated purely by servoy or custom?
What was the exact query that was used? was it an between query or something?
jcompagner:
how is that sql build up? Was it a query generated purely by servoy or custom?
What was the exact query that was used? was it an between query or something?
It is a getDataSetByQuery and takes values from a foundset as arguments
We build a SQL string with fields directly from the foundset of the form, then use that in the query. One point that perhaps is influencing the situation is that we apply a number format using utilities to the field like this:
var vLongRateFormated = utils.numberFormat(vLongRate, 2); and used like this:
if (vRateType == ‘S’) {vCorrRateType = 'corrosion_condition.cc_short_corr_rate = '+ vShortRateFormated}
if (vRateType == ‘L’) {vCorrRateType = 'corrosion_condition.cc_long_corr_rate = '+ vLongRateFormated}
if (vRateType == ‘A’) {vCorrRateType = 'corrosion_condition.cc_av_loss = '+ vAvRateFormated}
//application.output('Av Rate ’ +vAvRate+ ’ vRateType ‘+vRateType+’ Corr rate SQL ’ +vCorrRateType)
//Setup surface type groups
if (allAreas != 1 && vAllANCDGroups !=1)
//Use All Complex’
{
var listSQL = "SELECT "+
"corrosion_condition.cc_id, "+
"corrosion_condition.cc_short_corr_rate, "+
"corrosion_condition.cc_long_corr_rate, "+
"corrosion_condition.cc_av_loss "+
"FROM " +
"corrosion_condition "+
"Inner Join component_detail ON corrosion_condition.cd_id = component_detail.cd_id "+
"Inner Join analysis_codes ON corrosion_condition.cc_analysis_code = analysis_codes.ad_analysis_code "+
“WHERE “+
“component_detail.cd_complex = '” +vComplex +”’ AND " +
“analysis_codes.ad_grouping = '” + vANCDGroups +”’ AND " +
vCorrRateType
}
var datasetList = databaseManager.getDataSetByQuery(‘tci_test’,listSQL,null,-1);
If we don’t run the i18n statement on solution start this query takes the number with a ‘,’ and if we do it correctly takes it as ‘.’
Should the formatting effect the SQL generated like this Johan?
Yes, your formatting beforehand and building SQL enrirely as a string including values is where you fail.
You should always use the third parameter in the getDataSetByQuery() method:
getDataSetByQuery(server, query, arrayOfValues, numberOfRowsToRetrieve),
then you build your query using ‘?’ placeholders.
This doesn’t prevent you from doing some fancy tests to build your query, though…
See for example how your code could be:
var vCorrRateType;
if (vRateType == 'S') vCorrRateType = 'corrosion_condition.cc_short_corr_rate = ?';
if (vRateType == 'L') vCorrRateType = 'corrosion_condition.cc_long_corr_rate = ?';
if (vRateType == 'A') vCorrRateType = 'corrosion_condition.cc_av_loss = ?';
var listSQL;
if (allAreas != 1 && vAllANCDGroups !=1)
//Use All Complex'
{
listSQL = "SELECT\
corrosion_condition.cc_id, \
corrosion_condition.cc_short_corr_rate, \
corrosion_condition.cc_long_corr_rate, \
corrosion_condition.cc_av_loss \
FROM \
corrosion_condition \
inner join component_detail ON corrosion_condition.cd_id = component_detail.cd_id \
inner join analysis_codes ON corrosion_condition.cc_analysis_code = analysis_codes.ad_analysis_code \
WHERE \
component_detail.cd_complex = ? AND \
analysis_codes.ad_grouping = ? AND " + vCorrRateType;
}
var parameters = [vComplex, vANCDGroups];
if (vRateType == 'S') parameters.push(vShortRate); // don't use strings formatted here, put the real objects!
if (vRateType == 'L') parameters.push(vLongRate);
if (vRateType == 'A') parameters.push(vRateType);
var datasetList = databaseManager.getDataSetByQuery('tci_test', listSQL, parameters ,-1);
When you pass that array, use real objects (integers, floats, booleans, etc…) don’t make strings out of your data, this is where you fail!
Internally, this is using a PreparedStatement where all the placeholders ‘?’ are replaced with the objects and these objects will be used by the driver to create the correct SQL statement for the database.
Patrick - I get that we need to use the prepared statements / parameters instead of the hand crafted SQL strings. Old habits die hard!
However what I cant understand is why using the number formatting like this
var vLongRateFormated = utils.numberFormat(vLongRate, 2);
that we should get out a string instead of a number? Isnt the utils.numberFormat(vLongRate, 2) ensuring whatever is in the column is treated as a number?
We used this because the number used was sometimes the wrong size. From my code can you pin point where we end up creating the number as a string?
The function utils.numberFormat() returns a string, not a number. I guess you could use Number(utils.numberFormat()) to round it up and then turn it back into a number again.
ROCLASI:
The function utils.numberFormat() returns a string, not a number.
Doh… how logical is that?
very logical, if you format something to something that then last something is always a string…
if you format a date then the result is a string.
if you format a number then the result is a string.
thats the whole point of formatting, there you transform and object (date/number) to a user readable string in a format you want to have
like utils.dateFormat(dateobject, “dd/mm/yyyy”);
or utils.numberFormat(numberObject,“#,###.00”);