Examples: EXTFNAPIV4_DESCRIBE_PARM_CAN_BE

Example procedure definitions, _describe_extfn API function code fragment, and SQL queries for getting EXTFNAPIV4_DESCRIBE_PARM_CAN_BE_NULL values.

Procedure Definition

Sample procedure definition used by the example queries in this topic:

CREATE PROCEDURE my_udf(IN p INT)
RESULT (x INT)
EXTERNAL NAME ‘my_udf@myudflib’;

API Function Code Fragment

Sample _describe_extfn API function code fragment used by the example queries in this topic:

my_udf_describe(a_v4_extfn_proc_context *cntxt)
{

	if( cntxt->current_state > EXTFNAPIV4_STATE_OPTIMIZATION ) {
	a_sql_byte can_be_null = 0;
	a_sql_int32 ret = 0;

	ret = cntxt->describe_parameter_get( cntxt, 1, 
	EXTFNAPIV4_DESCRIBE_PARM_CAN_BE_NULL,
	&can_be_null,
	sizeof(a_sql_byte) );

	if( ret <= 0 ) {
	    // Handle the error.
	}
}
}

Example 1: Without NOT NULL

This example creates a table with a single integer column without the NOT NULL modifier specified. The correlated subquery passes in column c1 from the table has_nulls. When the procedure my_udf_describe is called during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 1.

CREATE TABLE has_nulls ( c1 INT );
INSERT INTO has_nulls VALUES(1);
INSERT INTO has_nulls VALUES(NULL);
SELECT * from has_nulls WHERE (SELECT sum(my_udf.x) FROM my_udf(has_nulls.c1)) > 0;

Example 2: With NOT NULL

This example creates a table with a single integer column with the NOT NULL modifier specified. The correlated subquery passes in column c1 from the table no_nulls. When the procedure my_udf_describe is called during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 0.

CREATE TABLE no_nulls ( c1 INT NOT NULL);
INSERT INTO no_nulls VALUES(1);
INSERT INTO no_nulls VALUES(2);
SELECT * from no_nulls WHERE (SELECT sum(my_udf.x) FROM my_udf(no_nulls.c1)) > 0;

Example 3: With a Constant

This example calls the procedure my_udf with a constant. When the procedure my_udf_describe is called, during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 0.

SELECT * from my_udf(5);

Example 4: With a NULL

This example calls the procedure my_udf with a NULL. When the procedure my_udf_describe is called, during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 1.