Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

European session format can cause a query to return different results depending on whether it connects to a database server or shared memory connection.

If a query is resolved on the server or client it can return different results depending on the session numeric separator.

A session started with European numeric format fails to return records when executing a query on a database started with American numeric format.

Appservers using European numeric format connecting to a database via a server or via a shared memory connection can return different results.

The string function converts a decimal field value according to the SESSION:NUMERIC-DECIMAL-POINT.

CAUSE:

The problem relates to database server and client side query resolution, and more specifically where this is done with client server and shared memory connections when the client and server are using different numeric formats. The reason why this issue arises is because of the STRING function and its reliance on the SESSION:NUMERIC-DECIMAL-POINT. For example the following loop:
for each customer no-lock where
lookup(string(customer.balance),"12175.59;6814.35;165.35",";") > 0:
display customer.name FORMAT "X(20)" customer.balance
string(customer.balance) format "x(25)".
end.

Returns records when the client is using American numeric format because the STRING function returns values according to SESSION:NUMERIC-DECIMAL-POINT, which is ".".

If the client uses European numeric format (-numsep 46 -numdec 44) and connects to the database via database broker, then the query will also return the expected records because the query is resolved on the server and the server numeric-decimal-separator is ".".

However, if the client is started with European numeric format and connects to the database via shared memory, the above loop will not return any records because the query is resolved on the client. In this situation, in order to get the query to work you would have to specify:
lookup(string(customer.balance),"12175,59;6814,35;165,35",";") > 0:
This becomes a big problem when using European load balancing Appservers on different machines that connect to an American format database. The Appserver on the same machine as the database connects to the database via shared memory, but the appserver on a separate machine connects via a broker. Subsequently depending on which appserver the client connects to, means different results can be obtained.

Ultimately, the problem is that the query resolution is being done either on the client or on the server, but this code has no control over this. In addition, because the FOR EACH loop is using the STRING function, any index defined on the customer balance field is ignored for bracketing. Also, because the STRING conversion of the balance field is evaluated for each record, the FOR EACH is inefficient.
For these reasons it is always a bad idea to use a STRING function on a database field in a where clause. In fact you would also have a similar problem with dates.

FIX:

The way to resolve this is pass the decimal values to the server in their natural internal formats and avoid strings wherever possible. For example the following code resolves the problem regardless of client/server startup parameters:
decval1 = 12175.59.
decval2 = 6814.35.
decval3 = 165.35.

for each customer where customer.balance = decval1 or
cutomer-balance = decval2 or
customer-balance = decval3 etc....
... end.


Alternatively, with a query use the Quoter function. For example:

decval1 = 12175.59.
decval2 = 6814.35.
decval3 = 165.35.
qh:QUERY-PREPARE("FOR EACH customer WHERE customer.balance = " +
QUOTER(decval1) +
"OR customer.balance = " + QUOTER(decval2) +
"OR customer.balance = " + QUOTER(decval3)).