Use SELECT with keys that are not Alphanumeric

Date:13 October 2003
Product/Release:Visual LANSA
Abstract:Generic search using numeric keys in the SELECT command ends with SQL ODBC errors
Submitted By:LANSA Technical Support
Last Review:December 2010

Description:

An interesting problem occurs when using the SELECT command for generic search in LANSA.

When you use a SELECT command for generic search with keys that are numeric you may experience an error.

e.g.

#STD_CODE is an alphanumeric field. #STD_TIME is a numeric field.

Group_By #Key1 (#STD_CODE #STD_TIME)
Select (#Field1 #Field2) RNB1 With_Key(#Key1) Generic(*Yes)
....

If the file contains three keys but the SELECT command only has partial keys in which #STD_TIME is a numeric an error will occur.

Release 10.0.3 Build 2560 Windows NT/2000/XP (32 bit)
Component : My Component
Statement : xx
Message : [Microsoft][ODBC Driver Manager] Function sequence error
Routine : X_DBM_Fetch_Row

The error is not caused by LANSA. It is an SQL ODBC driver error of not being able to accept keys that are numeric fields.

Workaround:

In order to avoid this error, make sure to use alphanumeric fields as keys in the SELECT command when using partial keys for generic search.

If all three keys are used instead of partial keys for the generic search then this error will also not occur.

e.g. #STD_TEXT is an alphanumeric field.

Group_By #Key1 (#STD_CODE #STD_TIME #STD_TEXT)
Select (#Field1 #Field2) RNB1 With_Key(#Key1) Generic(*Yes)
....

In this example, if a file contains three keys and three keys are used for generic search then the problem will not occur even if the second key is a numeric field.