Kbase P26309: Partitioned SDO does not return the expected set of records.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
SYMPTOM(s):
Query does not return the expected set of records.
Using ADM2.
Occurs only in some queries.
The queries where this occurs involve decimal fields.
SDO is partitioned between client and AppServer.
CAUSE:
The numeric format is not the same between the client and the AppServer.
Usually, when a client builds a query for the SDO, it does so by concatenating the STRING() of a decimal field. For example:
DYNAMIC-FUNCTION('setQueryWhere' IN hSDO,
'customer.balance > ':U + STRING(deMinBalance)).
The query is built on the client side, but is resolved on the AppServer side. Let's see what happens when the client and AppServer numeric formats do not match, assuming that the client is on European format, and the AppServer is on American format, and that deMinBalance is 1234.56:
- The client builds the query string; because it's using the European format the query will be:
customer.balance > 1234,56
^^^^^ European format !
- The string is passed as is to the AppServer.
- The AppServer performs a QUERY-PREPARE() with the given string, but because it' working with the American numeric format, it does not recognize the comma as the decimals separator: it will just ignore the comma, treating it as a non-significant thousands separator; the query will then be:
customer.balance > 123456
Of course this will not return the expected set of records.
The example would work just as well if the numeric formats between the client and the AppServer are swapped: the American client will put in the query string a dot (as a decimal separator) that the European AppServer will just disregard, as a non-significant thousands separator.
FIX:
This problem may be fixed in several ways, as long as the client is aware of the AppServer numeric format before building the final query string.
Then the commas and the dots should be swapped, as in the following:
ASSIGN cMinBalance = STRING(deMinBalance).
IF <client and AppServer numeric formats do not match> THEN DO:
ASSIGN cMinBalance = REPLACE(cMinBalance, ",":U, "|":U)
cMinBalance = REPLACE(cMinBalance, ".":U, ",":U)
cMinBalance = REPLACE(cMinBalance, "|":U, ".":U).
DYNAMIC-FUNCTION('setQueryWhere' IN hSDO,
'customer.balance > ':U + cMinBalance).