Oninit
Talk to an expert →

Informix Error -9751: Cannot prepare the statement because the return type of the routine routine_name is not known.

Cause and resolution

Cannot prepare the statement because the return type of the routine routine_name is not known.

The database server could not prepare the statement because a host variable was specified without a data type in a routine acting on a collection-derived table. You must cast the host variable to a data type that the routine takes as an argument.

For example, the following statement fails to prepare because the argument type of the ST_IsValid routine is not specified: SELECT * FROM table(function ST_IsValid(?));

The following statement prepares successfully because the host variable is cast to a valid data type for the ST_IsValid routine: SELECT * FROM table(function ST_IsValid(?::st_multipolygon));

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-9751 fires when a host variable with no data type is used in a routine acting on a collection-derived table, so the routine's return type can't be resolved at PREPARE time — per the official guidance.

  1. A typeless host variable (?) passed to a routine on a collection-derived table, per the official guidance — the direct, only cause. For example, SELECT * FROM table(function ST_IsValid(?)); fails because ST_IsValid's argument type isn't specified.
Solutions / Resolution
  1. Cast the host variable to a data type the routine accepts as an argument, per the official guidance.
Examples
An untyped host variable in a collection-derived-table routine call
SELECT * FROM table(function ST_IsValid(?));   -- -9751
Corrected
SELECT * FROM table(function ST_IsValid(CAST(? AS ST_Geometry)));
Diagnostic Checks
  1. Check the routine call for untyped host variables (?), and add an explicit CAST to a type the routine accepts.
Related Errors / Related Topics
  • -9750 — "Routine routine_name determined during PREPARE and BIND/EXECUTE return different types." A related PREPARE-time routine-resolution error, on inconsistent resolution rather than the return type being entirely unresolvable.

A host variable had no type, so the routine's return type couldn't be resolved at PREPARE — cast the host variable to a specific type.