ARRAY structure in UDF

mbacay · ‎04-17-2013

Hi. I am trying the built-in C functions for ARRAY handling in Teradata 14. Basically, I copied the code below from the FNC_GetArrayElementCount function in the manual TD 14 External Routine Programming:

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void MyArrayUDF2( 
 ARRAY_HANDLE  *var_ary,
 INTEGER  *result,
 int   *indicator_var_ary,
 int   *indicator_result,
 char   sqlstate[6],
 SQL_TEXT  extname[129],
 SQL_TEXT  specific_name[129],
 SQL_TEXT  error_message[257]
) {
 int presElementsCount;
 int lastElement[1];
 FNC_GetArrayElementCount((*var_ary),&presElementsCount,lastElement);
 *result = presElementsCount;
 return;
}

and created the corresponding SQL function:

REPLACE FUNCTION database1.MyArrayUDF2( 
  ARRAY_1 shot_ary
)
RETURNS INTEGER
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!MyArrayUDF2!MyArrayUDF2.c!F!MyArrayUDF2';

It compiled without warnings, but upon trying the following query:

SELECT MyArrayUDF2(seismic_data.shots);

I get the following error:

*** Failure 7502 A system trap was caused by UDF/XSP/UDM ojt_mnl.MyArrayUDF2 for SIGSEGV

Am I missing any parameter declaration or am I using it incorrectly? I am running this on TD14 in my VM box and I have already checked that ARRAY_HANDLE and FNC_GetArrayElementCount are included on the sqltypes_td.h header file on my vm. Thank you in advance.

Regards,

Maria

mbacay · ‎04-18-2013

Just to add, my ARRAY type is:

CREATE TYPE SYSUDTLIB.shot_ary AS INTEGER ARRAY [1:4][1:4][1:4];

and it is used in a table:

CREATE TABLE seismic_data (id INTEGER,shots shot_ary);

with some sample values:

INSERT INTO seismic_data VALUES (1, shot_ary(1, 2));

INSERT INTO seismic_data VALUES (2, shot_ary(4,5));

INSERT INTO seismic_data VALUES(3,shot_ary(6,7));

INSERT INTO seismic_data VALUES(4,shot_ary(8,9));

INSERT INTO seismic_data VALUES(5,shot_ary(1,2,3,4,5,6));