ODBC - Sort order¶
Sculptor keyed files sort positive integer key values first, followed by negative key values. SQL databases, however, sort negative values first.
Provided that the SQL database supports binary fields, flags can be used to make main index integer key fields (type i2, i4 or i8) sort positive values first, so that the file is sorted in the same order as a Sculptor keyed file.
To make a specific main index key field sort positive values first, assign the “U” flag (must be upper case) to the field at run time before creating the table:
field_id->flags = “U”
DBFL_UNSIGNED_KEYS has no effect on secondary index key fields, so the “U” flag must be set for these if required.
When Sculptor creates the table, it makes the i2 or i4 field a binary (b4) field in the SQL data dictionary. Sculptor then handles all translations between integer and binary automatically. Once the table has been created, the sort sequence is fixed, so the flags are not needed when opening an existing table.
To make it easy to use a non-Sculptor reporting tool, Sculptor creates a second, computed SQL field for each such binary field (but only if the SQL database supports computed fields). These fields are given the same name as their corresponding Sculptor fields but have the prefix “int_”. When these fields are retrieved in a reporting tool, their values are returned as normal integers. Computed fields are read-only and cannot be updated.
RELATED TOPICS |