Apollo API (Apollo Engine) > xBase Functions Supported

The following xBase functions are supported in index and query expressions passed to Apollo API functions:

ALIAS()

Returns the Alias name of the current workarea as a string.

ALLTRIM( String )

Trims both leading and trailing spaces from a string. The string may be derived from any valid xBase expression.

ALLTRIM(' tester ') returns "tester".

AT( SearchString, TargetString )

Determine whether a search string is contained within a target. If found, the function returns the position of the search string within the target string (relative to 1). If not found, the function returns 0 (zero).

AT('gh', 'defghij') returns 4.

CHR( Val )

Converts a decimal value to its ASCII equivalent.

CHR(83) returns 'S'

CTOD( String )

Converts a character string into an xBase date. The string must be formatted according to the settings of sx_SetDateFormat and sx_SetCentury.

CTOD('12/31/94')

DATE()

Returns the system date (today). Use DTOC(DATE()) to retrieve today's date formatted according to the settings of sx_SetCentury and sx_SetDateFormat . If the date format is American, DTOC(DATE()) returns '03/21/95' if today is March 21, 1995.

DAY( DateField )

Returns the day portion of an xBase date as an integer.

DELETED()

Returns True if the record is deleted and False if not deleted.

DESCEND( String )

An xBase function that inverts a key value using 2's complement arithmetic. The result of the operation is the arithmetic inverse of the key value. When inverted keys are sorted in ascending sequence, the result is in descending order. To create a search key for an index built with this function, you must use the sx_Descend function. An index expression could be

"DESCEND(DTOS(billdate)) + CUSTNO"

If you need to support legacy DOS application and also use the xBase DESCEND() function in the index key, you need to take special care to manage the translate state. For example, when calling sx_Seek, sx_Index, or sx_IndexTag you should first call sx_SetTranslate (FALSE) and afterwards call sx_SetTranslate (TRUE).

DTOC( DateField [, ReturnType] )

Converts an xBase date into a character string formatted according to the settings in sx_SetDateFormat and sx_SetCentury. For example, if the date format was American and sx_SetCentury was True, and the date field contained March 21, 1995, DTOC(datefield) would return '03/21/1995'.

The optional ReturnType parameter has been added for FoxPro compatibility purposes. If not passed, a default ReturnType of 0 is used and returns an xBase date formatted according to the settings of sx_SetDateFormat and sx_SetCentury. A ReturnType of 1 returns the date as a Clipper style DTOS() date ("CCYYMMDD", i.e., November 8th, 1996 would be "19961108").

DTOS( DateField )

Converts an xBase date into a string formatted according to standard xBase storage conventions (CCYYMMDD). For example, December 21, 1993 would be returned as '19931221'. Indexes that contain date elements should use the DTOS() function, which naturally collates into oldest date first.

EMPTY( Field )

Reports the empty status of any xBase field. Character and date fields are empty if they consist entirely of spaces. Numeric fields are empty if they evaluate to zero. Logical fields are empty if they evaluate to False.

Memo fields that contain no reference to a memo block in the associated memo file are empty.

IF( Logical, True Result, False Result )

This is the immediate if function. If the Logical expression is true, return the True result, otherwise return the False result. The types of the True Result and the False Result must be the same (i.e., both numeric, or both strings, etc.) The logical expression must of course evaluate as True or False.

IF(DATE() - CTOD('12/31/93') > 0, 'This Year', 'Last Year')

IIF( Logical, True Result, False Result )

Supported exactly like IF() as noted above.

INDEXKEY()

Returns the current index key as a string. (Same as ORDKEY()).

LEFT( String, Length )

Returns the leftmost characters of the expression for the defined length.

LEFT('xyzabc', 3) returns "xyz".

LEN( Expression )

Returns the length of the expression result as an integer.

LOWER( String )

Converts the string expression into lower case.

Both dBase and Clipper UPPER() and LOWER() case conversion functions limit the characters eligible for case conversion. With UPPER(), only characters a-z are converted to upper case. With LOWER(), only characters A-Z are converted. Characters with diacritical marks ARE NOT CONVERTED when this switch is TRUE if sx_SetTranslate is also set to TRUE. To limit case conversion using this switch, set sx_SetTranslate to TRUE and set the sx_SysProp value on as well.

sx_SetTranslate( True )

lRetVal = sx_SysProp( SDE_SP_SETLIMITCASECONV, 1 )

MONTH( DateField )

Returns the month portion of an xBase date as an integer.

ORDER()

Returns the current index order as an integer.

ORDKEY()

Returns the current index key as a string. (Same as INDEXKEY())

PADC( String, Length, Character )

Centers the passed string between a number of the passed character to make the string the specified length.

"[" + PADC('Scott', 9,'-') + "]" returns "[--Scott--]".

PADL( String, Length, Character )

Pads the passed string to the specified length with the specified characters. If the string is longer than the value specified by Length, the string is truncated to this length.

"[" + PADL('Scott', 8, '*' ) + "]" returns "[***Scott]".

"[" + PADL('Loren Scott', 8, ' ' ) + "]" returns "[Loren Sc]".

PADR( String, Length, Character )

Pads the passed string to the specified length using the specified character. If the string is longer than the value specified by Length, the string is truncated to this length.

"[" + PADR('Scott', 8, ' ' ) + "]" returns "[Scott ]".

"[" + PADR('Loren Scott', 8, ' ' ) + "]" returns "[Loren Sc]".

QKEYVAL

Returns the current key value for the passed IndexTagName as a string. See sx_Query for more information.

RAT( SearchString, TargetString )

Determine whether a search string is contained within a target, starting from the right side of the target string. If found, the function returns the position of the search string within the target string (relative to 1). If not found, the function returns 0 (zero).

RAT( 'ab', 'abzaba' ) returns 4.

RECCOUNT()

Returns the number of records in the table as a long integer.

RECNO()

Returns the current physical record number as a long integer.

RIGHT( String, Length )

Returns the rightmost characters of the expression for the defined length.

RIGHT('xyzabc', 3) returns "abc".

SELECT()

Returns the workarea number for the current workarea as a long integer.

SOUNDEX( String )

Returns a 4-byte string consisting of the first letter of the passed string and three numbers representing a 'sound' value for the specified string.

SPACE( Length )

Returns a string consisting entirely of spaces for the defined length.

STOD( String )

The inverse of DTOS(). STOD() converts a string formatted according to standard xBase storage conventions (CCYYMMDD) to an xBase Date formatted according to the settings of sx_SetDateFormat and sx_SetCentury.

STR( Number, Length, Decimals )

Converts a number into a right justified string with decimals digits following the decimal point. The total length of the string is defined by the length parameter. STR(RECNO(), 5, 0) is a common indexing element that ensures creation of unique keys if appended to another field element.

An index key using this expression could be built with NAME + STR(RECNO(),5,0)

Default Width Behavior

If the decimals parameter is omitted, the STR() function defaults to zero decimals.

If the length parameter is omitted and number is passed as a literal, the function will default to a width of 10. For instance:

STR( 9 ) // defaults to width of 10

However, if number is passed in the form of the fieldname of a numeric field, the default behavior depends on the RDE that was used to open the table:

If the table was opened with SDEFOX, the function will default to a width of 10.

If the table was opened with SDENSX or SDENTX, the function will default to a width equal to the length of the field.

Note: SDEFOX and SDENTX use different rules for determining the default length if the length parameter is missing from the xBase STR() function.

The SDEFOX engine mimics Microsoft FoxPro's behavior. Indexes created in a FoxPro application with the expression STR(field) will therefore be compatible with SDEFOX engine.

The SDENTX and SDENSX engines mimic the behavior of CA-Clipper. Indexes created in a Clipper application with the expression STR(field) will therefore be compatible with SDENTX and SDENSX engines.

Please note, however, if you created a FoxPro (CDX) index in a CA-Clipper application, it is likely that the Clipper data driver used the CA-Clipper default rule which uses the field width. In this case, you must rebuild any indexes built with STR() that did not use the length parameter.

The best thing to do is to avoid this confusion altogether and always include the length parameter when using xBase STR().

STRZERO( Number, Length, Decimals )

Converts a number into a, zero-padded right justified string with decimals digits following the decimal point. The total length of the string is defined by the length parameter.

STRZERO( 1234, 10, 2 ) returns "0001234.00"

If the decimals parameter is omitted, the function defaults to zero decimals. If the length parameter is omitted as well, the length of the result is 10 characters under SDEFOX. Under SDENSX and SDENTX the default is the length of the field.

SUBSTR( String, Start, Length )

Returns a portion of the string expression starting at the defined start location for the defined length..

SUBSTR("xyzabcd", 3, 4) returns "zabc".

sxChar( Length )

sxChar( Length ) is a special indexing function that allows the creation of Roll-Your-Own (RYO) indexes with each key being a character string of the defined sxChar length. An sxChar index does not rely on any given field value for a key. The maintenance of the index and the contents of each key is entirely in the hands of the applications programmer. See sx_RYOKeyAdd and sx_RYOKeyDrop.

When an sxChar index is created it is initially empty. Reindexing an sxChar index will also leave it in an empty state.

sxChar may only be used as an index expression in sx_Index or sx_IndexTag and it may only be used with IDX/CDX or NSX index types. It is recommended that single tag indexes be used for sxChar RYO indexes. To create an sxChar index, pass the option parameter in sx_Index or sx_IndexTag as IDX_EMPTY.

Reindexing an sxChar RYO index empties the index.

sx_IndexTag( "", "CODE", "sxChar( 8 )",

IDX_EMPTY, False, "" )

SX_VFGET( VFieldName, Length )

This function is only used for creating an index on a weakly-typed 'V' field, and returns a fixed length string value. When processing each record for index creation, if the current value of the specified 'V' field is shorter than Length, it is padded with spaces to the right. If the value in VFieldName is longer than Length, it is truncated.

sx_IndexTag( "", "CITY", "SX_VFGET( 'CITY', 30 )",

Ä IDX_NONE, False, "" )

TIME()

Returns the system time as a string in the form HH:MM:SS.

TRANSFORM( Expression, Picture )

Transform converts strings and numeric values into formatted character strings. The function transforms the result of the first expression in accordance with the second picture string.

The picture string is made up of two parts. The first part is the Function string and it is optional for both strings and numeric values (as long as the second Template string is present).

A character string transformation picture may consist of only a Function string or only a Template or both.

A numeric picture must contain a Template string; the Function string is optional.

A logical value must contain only a Template string with Template characters L or Y.

The Function string consists of a leading @ character followed by one or more formatting characters. If the Function string is present, the @ character must be the first character in the picture string with its formatting characters immediately following and it may not contain spaces.

If a Template string exists as well, it follows the Function string. A single space separates the Function string and the Template string.

Function string characters allowed for numeric values are:

B left justify;

C display CR after positive numbers;

X display DR after negative numbers;

Z blank a zero value;

( encloses negative numbers in parentheses.

Function string characters allowed for strings are:

R inserts unassigned template characters;

! converts all alpha characters to uppercase.

The @R Function requires a Template; the ! Function does not.

The Template string describes the format on a character by character basis. The Template string is made up of special characters which have specific results and optional unassigned characters which either replace characters or are inserted in the formatted string depending upon the absence or presence of the @R Function string.

Template assigned characters are as follows:

A,N,X,(,# are place holders and are interchangeable;

L displays logical values as T or F;

Y displays logical values as Y or N;

! converts the corresponding character to upper case;

, (comma) or a space (in Europe) in a numeric template separate

the elements of a number;

. (period) or, (comma - in Europe) in a numeric template specify

the decimal position;

* fills leading spaces with asterisks in a numeric template;

$ as the leading character in a numeric template results in a

floating dollar sign being placed in front of the

formatted number.

Example: Where "phone" is a character field holding a phone number with no formatting characters.

sx_EvalString( "transform(phone, '@R (###) ###-####')")

returns '(555) 123-1234'. If the formatting characters were actually present in the field, the "@R" function would be omitted

For numeric fields, sx_EvalString ("transform(123456.78, '$9,999,999.99')") returns ' $123,456.78'.

TRIM( String )

Removes trailing spaces from the string expression.

UPPER( String )

Converts the string expression into upper case. Character fields used in index expressions should always be converted to upper case to insure correct collating sequence.

sx_SetTranslate( True )

lRetVal = sx_SysProp( SDE_SP_SETLIMITCASECONV, 1 )

VAL( String )

Converts a string of numeric characters into its equivalent numeric value. The conversion stops at the first non-numeric character encountered (or the end of the string).

VAL('123ABC') returns a value of 123.

YEAR( Datesx_Field )

Returns the year portion of an xBase date as an integer.