Returning a value from another database

The value returned from another database by a user-defined function (UDF) is restricted to a smaller set of data types than the return value of a function that accesses only the local database. Similarly, functions that access databases of other server instances are more restricted in the data types of their return values than functions that return values from other databases of the local database server.

For UDFs that access tables or views outside the local database, only the following data types are valid as return values:
Figure 1: Return data types in distributed function calls
(1)
Notes:
  • 1 Not valid in cross-server operations

Element Description Restrictions Syntax
built-in _ non-opaque Atomic built-in data type that is not opaque Type cannot be complex, serial, BYTE, or TEXT Data Type
built-in _opaque Atomic built-in data type that is opaque. Note 1 does not apply to BOOLEAN, BSON, JSON, or LVARCHAR. See built-in opaque data types list in Data Types in Cross-Database Transactions Data Type
distinct DISTINCT type whose base type is another DISTINCT type Root of this hierarchy must be a BSON, BOOLEAN, JSON, LVARCHAR, or an atomic built-in non-opaque data type DISTINCT Types in Distributed Operations
max Maximum size in bytes. Default is 2048. Must be an integer, where 1 < max < 32,739 Literal Number
opaque_UDT A user-defined opaque data type Must be cast explicitly to a built-in type by a cast defined in every participating database Identifier
Important:

The diagram above shows the generalized logical hierarchy of the base types for any DISTINCT data type. Using the DISTINCT OF keywords recursively, however, as in the diagram above, is not valid SQL syntax. The CREATE DISTINCT TYPE statement must specify exactly one base type for the new DISTINCT type. To create a hierarchy of DISTINCT data types, you must issue a separate CREATE DISTINCT TYPE statement for every DISTINCT type in the hierarchy. For the SQL syntax to define a new DISTINCT data type, see the topic CREATE DISTINCT TYPE statement.

Usage

Whether UDFs can return data from other databases depends on
  • the transaction-logging status of the participating databases,
  • and the data type of the returned value or values,
For a function invoked in one database to return a value from another database, both participating databases must be of compatible transaction-logging types:
  • A function invoked in a database that was not created as MODE ANSI cannot retrieve data from an ANSI-compliant database.
  • Conversely, a function called in an ANSI-compliant database cannot retrieve data from a database that was not created as MODE ANSI, whether or not the non-ANSI database supports transaction logging.
  • A function call in a database without transaction logging can retrieve data only from unlogged databases.
  • A function from a database with explicit transaction logging cannot retrieve data from an unlogged database. It can return data only from databases with explicit logging.

In the last case, a UDF called in a database that supports explicit transactions can return a value from another non-ANSI database that uses buffered or unbuffered logging, whether or not both databases use the same buffered or unbuffered logging mode.

If the databases are of incompatible logging types, however, the valid data types of return values is an empty set, rather than any of the types that the syntax diagram identifies.

Sections that follow describe what data types can be returned in distributed function calls.

Return values from cross-database operations

If the Return clause specifies a value (or multiple values, in the case of an SPL function) from another database of the local HCL OneDB™ instance, the following data types are supported as the return data type:
  • Built-in data types that are not opaque or complex
  • Most of the built-in opaque data types, as listed in Data Types in Cross-Database Transactions
  • Any DISTINCT type based on a supported built-in type
  • Any DISTINCT type based on one of those DISTINCT types
  • Any user-defined type (UDT) that is cast explicitly to one of the supported built-in types.

The UDF and all of the DISTINCT types, opaque UDTs, data type hierarchies, and casts must have exactly the same definitions in each of the participating databases. The same data-type restrictions apply to a value that an external function returns from another database of the local HCL OneDB instance.

For more information about data types that are supported in distributed operations across two or more databases of the same database server, see Data Types in Cross-Database Transactions. For the data type hierarchies that are valid for DISTINCT data types in distributed transactions, see DISTINCT Types in Distributed Operations.

Return values from cross-server operations

From databases of other HCL OneDB instances, however, UDFs can specify only the following as a parameter or as a returned data type:
  • Built-in data types that are not opaque
  • BOOLEAN
  • BSON
  • JSON
  • LVARCHAR
  • DISTINCT of built-in types that are not opaque
  • DISTINCT of BOOLEAN
  • DISTINCT of BSON
  • DISTINCT of JSON
  • DISTINCT of LVARCHAR
  • DISTINCT of the DISTINCT types in this list.
The definitions of the UDF and of any data type hierarchies, casts, and DISTINCT types must be exactly the same in each of the participating databases. Except for the BOOLEAN, DISTINCT, BSON, JSON, and LVARCHAR data types that are identified in the previous list, UDFs cannot return any other built-in opaque data type or opaque UDTs in cross-server function calls.

For more information about data types that are supported in distributed operations across two or more HCL OneDB instances, see Data Types in Cross-Server Transactions. For the data type hierarchies that are valid for DISTINCT data types in distributed transactions, see DISTINCT Types in Distributed Operations.