AttachmentSize
Package icon UDFTestHarnessExample.zip1.94 KB

From time to time I’m asked how one might go about writing an UDF or an INMod or some other procedural extension to the database. The question isn’t a “where do I learn C or C++” question; rather it is a how do I go about debugging and testing my extension without the overheads and constraints of running within the database (or utility).


My response is you can use “my Teradata” for which I have the source code (attached). This will allow you to run, test and debug your function outside of Teradata. In short with “my Teradata” all of the features of your IDE will be available to you, including your debugger.


There are a number of documents that describe the structure and interfaces for UDF’s, INMODs and other forms of database extensions. However, I’ve not seen any documents that describe how to leverage powerful debugging features found in all modern IDE’s. The UDF manual suggests “The best practice is to develop and test the function outside the database before you install it. You can use your own debugging tools to verify functionality.”, but provides no examples as to how to go about doing that. In this article I present “my Teradata” to enable you to do this.


My Teradata.c


Unfortunately “my Teradata” doesn’t include all of the features of real Teradata. In fact, it doesn’t include any of the features of real Teradata except the ability to call a UDF (or INMOD etc). That’s right fellow Teradatians; “my Teradata” is simply a standalone test harness.


A UDF, INMOD or any other C or C++ based database extension is a regular C/C++ function that can be called just like any other C function or C++ method. “My Teradata” leverages this by defining a main method which calls the UDF (or INMOD) under development. From here on I will simply call this “extension” or “database extension” unless referring to a specific type of extension.


By doing this (adding a main method) you end up with a program that can run your extension within your IDE (i.e. outside the database). As such the full debugging capabilities of your IDE become available to you.
Structuring the Extension


Whenever I create a database extension, I break it into two parts. These are:

  • Business functions – the function of the extension
  • Interface function(s) – the entry point(s) that Teradata calls to invoke your extension.


Typically I give the main business function a name that reflects the function. In the case of a UDF, I name the interface function using the same name but with the prefix “udf”. For example if my main business function was called xyz, I would name my entry point udfXyz. By doing this, it makes it a little easier to track the top level functions in my extension, especially if the extension is complex. Of course if you are building an extension to a utility (e.g. an INMOD) you are somewhat more limited in your choice of entry point names (it has to be called _dynamn).


In this article, we will build a mind bogglingly useless scalar UDF that add’s two numbers together (the article is about the process, not the extension). The “business function” will be called add and take two parameters (the two numbers to add). It will return the sum of the two numbers. The interface method will thus be called udfAdd.


There are 2 major reasons why I separate the UDF function into business and interface functions. Basically these are because it allows me to reuse existing code and target test cases.


From the reuse angle the interface logic is largely copy and paste from template code. Each interface function will differ according to the number and types of parameters, but the structure is pretty much the same in each case. Similarly the function I want to implement is likely already available from a previous project. Thus it makes sense to copy and paste the business function as is and simply call it from the interface function. Even if the business logic isn’t available from another project and must be built from scratch, it still makes sense to build it as a separate function just in case I want to use it elsewhere and to support testing.


From the testing angle, I can now establish two types of test cases. These are: 

  • Test cases that explore the range of possible inputs and outputs including null values and error conditions (i.e. test the interface) and
  • Test cases that validate the operation of the function itself (i.e. does my extension correctly add the two numbers).
     

The add UDF


First let’s examine the parts of the UDF. The UDF source can be found in the attachment (add.c). This includes both the “business logic” and the “interface logic”.

The business logic


As you might imagine the “business logic” for our mind bogglingly useless UDF is pretty straight forward. So let’s get it out of the way.

    int add (int x, int y) {
        return x + y;
    }

As one would expect, the above miracle of technological prowess takes two numbers, adds them together and returns the result.


The interface logic


The “interface logic” is slightly more interesting. For illustration purposes we will support the handling of null values in our UDF. The alternative is to get Teradata to handle nulls using syntax in the create function query. If we did this, Teradata wouldn’t even bother calling our UDF for null values. Thus this UDF is a “PARAMETER STYLE SQL” UDF. Note that the PARAMETER STYLE clause in our create query influences the signature of the UDF entry point. Refer to the UDF manual for more on this.


The entry point for my UDF is defined as follows:

void udfAdd (INTEGER *x, INTEGER * y,
        INTEGER *result,
        int *inputXNullInd, int * inputYNullInd,
        int *resultNullInd,
        char sqlstate [6],
        SQL_TEXT *function_name,
        SQL_TEXT *specific_function_name,
        SQL_TEXT *error_message)
{
   // Function body goes here.
}

If you are unfamiliar with UDF entry point signatures, refer to the appropriate Teradata manual. But briefly, the parameters are: 

  • The arguments supplied to the function (x & y),
  • A place holder for the result (result),  
  • Indicators as to whether or not the inputs and result is/are null (inputXNullInd, inputYNullInd & resultNullInd)
  • Some additional values to tell you about how the function was called and to allow return of success, warning or error information.


Note that all of the input parameters are pointers of some sort. A common mistake is to treat the input parameters (e.g. x & y) as though they have been passed by value – this is not the case. All parameters to the UDF are passed by reference. Making this mistake will generally not result in a desirable outcome!
Note also the “result” of the UDF is not returned through the function; which is declared as void. The result together with any additional information such as an error code or null indicator is passed back from the UDF via one or more of the entry point’s parameters.

Raising an error

Following is the body of the “business logic” for out add UDF. The first part checks the input parameters for null and returns an error if either of both of them is/are null:

if (*inputXNullInd == TD_NULL ||
        *inputYNullInd == TD_NULL) { // Null input?
    strcpy ((char *) sqlstate, "12345");
    strcpy ((char *) error_message, "Null is not allowed");
    *resultNullInd = TD_NULL; // gets Null output
    return;
}

If either of the input parameters is null, then an error is generated. In this case, the error code is “12345” is returned. There are rules about the structure of the sqlstate (i.e. the error code) which you should lookup in the manual (my sqlstate code does not conform to the rules as I simply made up the 12345 value). An error message is also returned from the UDF. The sqlstate and error message will be returned to the end user query tool (e.g. SQL Assistant, bteq etc) and hopefully handled meaningfully (SQL Assistant will display it in the status bar and record it in the history).


Returning a null result

Some people may argue that generating an error on null input may be a bit drastic (especially for this example). If an error condition is generated, it is treated like any other SQL Error condition (e.g. “duplicate row error”, “insufficient perm space in database X” etc). Specifically when an error is returned the entire transaction is rolled back.


One alternative to generating an error is to return a null value as the result. The following block performs this function:

if (*inputXNullInd == TD_NULL ||
        *inputYNullInd == TD_NULL) { // Null input?
    *resultNullInd = TD_NULL; // gets Null output
    return;
}

In the above logic, if either of the input parameters is null, the result Null indicator (resultNullInd) is set and the function returns. In this case the output of the UDF is a NULL value which is returned to the query that invoked the UDF. If the result null indicator is set, any value in the result parameter will be ignored. With this model, the query completes normally (notwithstanding any other problems that may arise). Any NULL inputs would result in the result set generated by your query containing NULL values.

Treatment of null inputs

Obviously the two treatments of null shown above are mutually exclusive. When you build your database extension, you must define how you will treat null input values and build this into the “interface logic”.
I’ve included both treatments to illustrate how you might use both. You may elect to implement the add function in such a way that the first parameter (x) may not be null and generates an error if it is null, but the second (y) may be null in which case the result will be null. This may sound like a silly suggestion; probably because it is. However, remember, the focus of this article is how to go about building and debugging a UDF, it is not about building a sensible UDF!

Invoking the business logic

The final part of the interface function is to invoke the “business function”. This is achieved with the following:

*result = add (*x, *y);

In the above, the “business function” is called and passed the two input values. The value returned from the add function is placed into the de-referenced result pointer.
Remember that the input parameters are pointers to the user values (passed by reference) not the actual user values (passed by value). Put simply you must dereference the input parameters to get the input values.

The test harness

So far we have built the UDF. The final piece to the puzzle is the test harness “my Teradata.c”. The test harness is included in the attachment (addTest.c).

The main function

Following is the main function. Note that there are three different types of test calls. The first two groups test the “business function” the final group tests the “interface function” which also indirectly tests the “business function”.

main () {
    test (1, 2); // Test 1 + 2
    test (-1, -2); // Test -1 + -2

        // Assert that 2 + 1 = 3
    testAssert (3, 2, 1);
        // Assert that 2 + 1 = 2 (produces an error)
    testAssert (2, 2, 1);

        // Test 4 + 5
    tdTest (4, TD_NOT_NULL, 5, TD_NOT_NULL);
        // Test NULL + 5
    tdTest (4, TD_NULL, 5, TD_NOT_NULL);
    exit (0);
}

For the first two groups of test cases, we are interested in testing the business logic. In this case the business logic is trivial, so separating the “business tests” and the “interface tests” may seem pointless. However, for larger more complex UDF’s there will likely be many more “business logic” test cases than “interface logic” test cases. Separating the two allows you to concentrate on just the “business logic” without having to wade through the handling of null input values. Hopefully this statement will become clearer once we view the two test functions. Of course this will become your test harness, and you are free to test your extension anyway you choose.


Business logic test cases

The following function simply invokes the business function (add) and prints the result.

void test (int x, int y) {
    int result;
    result = add(x, y);
    printf ("add(%d, %d)=%d\n", x, y, add (x, y));
}

It will (hopefully) produce output similar to the following:

add (1, 2)=3

How you code this function is entirely up to you. If I have a lot of test cases, I will also code it to accept an extra parameter being the expected result. The idea here is for the test function to assert the expected result against the actual result and if there is a difference highlight this with an appropriate message. In this case the test function looks like this:

void testAssert (int expected, int x, int y) {
    int result;
    result = add(x, y);
    printf ("add(%d, %d)=%d (expect %d) %s\n", x, y, result, expected,
        result == expected ? "" :
                             "******* Error, result not expected"
        );
}

The enhanced test method shown above would be invoked using something like the following:

    testAssert (3, 1, 2);
    testAssert (3, 1, 1);

The second invocation would trigger the message “******* Error, result not expected” because the expected value (3) does not equal the actual result (1 + 1 = 2).


It probably doesn’t make much sense to use both types of test functions; your choice will depend upon the type of extension you are building. For example testing a table function or an INMOD, the first type (test without the assertion) will likely be easier and more efficient. For scalar and aggregate UDF’s, the second type (testAssert) would be more efficient.


Interface logic test cases


The interface logic test cases are oriented to test the handling of nulls, error conditions are raised correctly and a few other things relating to the Teradata to UDF interface.


This function is much larger than the others so I’ve omitted it for brevity. You can find the interface logic test function in the attachment (addTest.c).


Since this function involves testing null inputs, it requires extra parameters when called. An example of invoking the interface logic follows.

    tdTest (4, TD_NOT_NULL, 5, TD_NOT_NULL);
    tdTest (4, TD_NULL, 5, TD_NOT_NULL);


In the first of the above cases, I’m testing 4 + 5 and neither parameter is null. In the second case, I’m testing NULL + 5. For this second test case, the first parameter (4) is irrelevant, but the ‘C’ language rules require that it be present.


Basically the interface function test cases will output the parameters and results with null indicators. In addition to this, it will detect if the UDF has attempted to alter any of the input parameters. If it has, then it will output a warning message.

Conclusions

Anyone who has attempted to track down a dangling pointer, out of bounds index or any other subtle bug will appreciate the ability to use a debugger over embedded print statements. Within the database debugging is essentially limited to embedded print statements that wind up in a trace table (refer to the UDF manual for details).

Use of the simple test harness presented here easily enables the use of a fully fledged debugger and should help you track down problems more quickly. Even if I am not tracking down a particular problem, but merely wanting to test that my function works, I’ve found clicking “run” in my IDE and viewing the results is vastly more productive compared to the alternative. The alternative is of course a matter of creating the UDF in the database, running some queries to exercise it then querying the log table to see what happened. The exercise is repeated until testing is complete, while all the time hoping that there isn’t some sort of exception encoded in the UDF which will bring the system to its knees (you do test your UDF’s in protected mode don’t you?!).

Obviously the standard disclaimer applies; specifically you shouldn’t just use this test harness and decide that your function works because it passes all your tests. You must test your extension in the database or database utility, ideally with the same test cases used in the test harness. This test harness merely enables you to simplify testing and leverage the powerful debugging features in your IDE.

  

Discussion
FAQ 6 comments Joined 05/09
02 Jun 2009

For scalar UDFs one simple way could be to just write a complete C program with the UDF defined as a function; and call the UDF from the main function, this way you can even write a loop around the UDF to stress test the code for different possible values.

FAQ 6 comments Joined 05/09
02 Jun 2009

What can be a simple way to test an aggregate function?

tvanlint 2 comments Joined 05/09
12 Jun 2009

I have a UDF TO_TIMESTAMP (basicly the TO_DATE UDF that is available on ths site in the download package "Teradata UDFs for popular Oracle functions" ).
It returns a TIMESTAMP datatype, and it works very well in queries, thank you.
But when I want to insert the result into a timestamp(0) field, I run into problems:
I get a "5404: Datetime field overflow" error.

OK, I've seen this before: a cast to timestamp(0) seems in order.
But then I get error "7454: DateTime field overflow" ...

I tried the ANSI cast and even the Teradata cast syntax - same result..

I even modified my UDF creation statement, using "RETURNS TIMESTAMP(0) CAST FROM TIMESTAMP".
Now a every call of my UDF returns error "7454: DateTime field overflow".

I'm out of inspiration now.
The only thing that seems to work is keeping the max precision of timestam (6) everywere : in the UDF's return datatype and the target database column ...

Is there a solution for working with TIMESTAMP(0) ?

najm 1 comment Joined 11/07
27 Jun 2009

Hello Everyone,

I am using the demo version and encoutering the following error:

*** Failure 5600 Error creating UDF/XSP/UDM/UDT: no compiler available.
Statement# 1, Info =0

I have installed the VC++ 2008 version but still facing the same error. Please help me in fixing the issue.

Thanks-

neilotoole 23 comments Joined 11/08
30 Jun 2009

Very shortly (coming weeks), the Developer Exchange discussion forums will be coming online. Until then, these questions are best asked at the existing Teradata "Drivers and UDFs" discussion forum (url below). Bear in mind, these will be migrated to DevX shortly.

http://www.teradata.com/teradataforum/Forum11-1.aspx

DevX Platform Architect

dbc1012 31 comments Joined 03/09
30 Jun 2009

You should double check that your version of Teradata recognises your compiler.
THere is a registry update you could try.

However, I CAUTION YOU WITH MAXIMUM CAUTIONING, not to update your registry without first:
A) Taking a backup
ii) Understanding what this change will do
3) writing down what the original settings in the registry were (so you can just restore just this part later) and
$) Reverting immediately if this doesn't work.
Remember if you update your registry and mess it up, you at risk of breaking your computer.
Also, I have no idea if this will work, as my compiler is obviously working correctly. So there are absolutely no guarantees in this.

You will also need to understand what this update is doing and modify it to match your compiler paths.
Another approach is to try to download the Microsoft 2003 redistributable compiler (I believe you can get this from Microsoft, but I'm not entirely sure about that).
You could also post a message to the forum and/or if you have a support arrangement log a service request.

To try the registry update, save the following to a .reg file (e.g. tdcompiler.reg)
Modify the paths (InstallDir= andProductDir=) so that they point to your compiler directories. Be carefull not to break the quotation marks and not the double \ for the path seperator.
ProductDir contains the compiler (e.g. cl.exe). InstallDir contains miscelaneous files (e.g. msenv.dll, vssln.dll and other dlls).
Remember there are no guarantees and be very very careful. The first line of the file is "Windows Registry Editer Version 5.00". Here is the registry file:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\7.1]
"InstallDir"="C:\\Program Files\\Microsoft Visual Studio .NET 2003\\Common7\\IDE\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\7.1\Setup\VC]
"ProductDir"="C:\\Program Files\\Microsoft Visual Studio .NET 2003\\Vc7\\"

dbc1012 31 comments Joined 03/09
30 Jun 2009

In relation to the aggregate function question, the principle is the same, although you make several calls, one for AGR_INIT, multiple AGR_DETAIL calls etc.

I'm currently working on Part 3 of working with Large Objects. Once I'm done with that and assuming my day job doesn't get in the way I will post another article on Aggregate functions and Table Functions (if there is interest).

Tuen 24 comments Joined 07/05
19 Feb 2010

Ok, first off let me start by saying that I haven't touched C code in probably 10 years (and even then I wasn't what I would call a stellar coder). I am taking the addtest.c code and trying to compile it, but it is complaining that the reference to add is undefined. I am assuming that in the addtest.c that I need to put in the actual logic and function for add, correct? Or am I missing something (which is probably the case). I figure if I can get the harness working then I can at least start playing around with the C code to refresh my memories (probably of how much I hate coding) and right some rudamentry udf's to learn by.

dbc1012 31 comments Joined 03/09
22 Feb 2010

One of the beauties (and some might argue the negatives) of C is that there are a million and one ways to do even the simplest thing.

Since you didn't supply the command that you used, I'm going to guess that you entered a command like this:
cc [various options] addTest.c

When I do that I get two unresolved references (_add and _udfAdd).

As you summise this doesn't work because addTest.c doesn't contain the add function (nor udfAdd). Both of these functions are in add.c
So what you need to do is (only) compile each file (not compile and link which the above command does), then link the intermediate files (add.o and addTest.o) into addTest[.exe]
Usually the easiest way to do this is to specify both source files on the command line as in:
cc addTest.c add.c

Different platforms and different compilers take different options, so I can't give you the exact commands.
If you are using visual studio (or some other IDE - I use NetBeans for Java, C and other stuff) it might be just as simple as adding both of the .c files to your project (certainly this is true for NetBeans).

I hope the above helps. If not, post another note including what you have tried and we will see where we can go from there.

Tuen 24 comments Joined 07/05
22 Feb 2010

Ahh! I got it now, I was trying to figure out how the add.c fit in with the addtest.c and was missing it. Ok, now I have it compling and working. Thanks for the help.

dlieske 1 comment Joined 08/10
08 Nov 2010

Great Article, But I have a couple of follow up questions.

1. Does it matter what we call the *result variable. is the "*result" a reserved word/pointer/existing variable or is it simply any pointer after the two variables (X and Y) defined in the function.

Does the Name of the Null variables (*inputXNullind and *inputYNullind) matter or is it the order that converts teradata input parameters into C variables?

So, Is it the variable order that drives how you set up a Parameter style SQL UDF, or do the names matter.

CREATE FUNCTION udfAdd(
X INTEGER,
Y INTEGER
)RETURNS INTEGER
LANGUAGE C
NO SQL
EXTERNAL
PARAMETER STYLE SQL;

void udfAdd (INTEGER *x,
INTEGER *y,
INTEGER *result,
int *inputXNullInd,
int * inputYNullInd,
int *resultNullInd,
char sqlstate [6],
SQL_TEXT *function_name,
SQL_TEXT *specific_function_name,
SQL_TEXT *error_message)

Thanks

pranilrp 1 comment Joined 07/10
27 Mar 2011

how to handle null in case of function overloading?
mean:-
created a function fn_abc, that can work with varchar and integer datatype.
eg. fn_abc('xyz'); --returns 1
fn_abc(123); --returns 1
now i have overloaded function.
but in case of query,
sel fn_abc(null); --cant find best fit function
is there any solution??

dbc1012 31 comments Joined 03/09
28 Mar 2011

I would have expected that when using the null (e.g. sel fn_abc(null)), Teradata would have used the Integer variant of the function. This is because null's are by default integer (try: sel type(null) ).
*However*, I've also encountered a situation where I've had to cast the parameter for the function to be invoked. If memory serves this related to prepared queries e.g. sel fn_abc(?) had to be submitted as sel fn_abc (cast (? as integer)). But that is a different story.

I suspect that casting the null to the datatype you want it to be will solve your problem for example select fn_abc(cast (null as integer)) or sel fn_abc(cast (null as varchar(10))).

dbc1012 31 comments Joined 03/09
28 Mar 2011

Hi Dirk (dlieske), sorry I missed your comment.

Firstly, thanks for the feedback, it was fun writing the article :-) - I wish I had time to do all my others :-(
The names of the variables in the C function declaration are irrelevant. The positions and datatypes are however are critical. The are determined by the create function declaration.
The C function names must the templates as defined in the UDF manuals. This is template is:
void someFunctionName (
Pointers to the parameters (in my case X & Y),
A pointer to the result,
Pointers to the null indicators (in my case Null Indicator for X and Null Indicator for Y),
Pointer to indicate whether the result will be null,
char sqlstate [6],
SQL_TEXT *function_name,
SQL_TEXT *specific_function_name,
SQL_TEXT *error_message)

The last 4 are fixed for this type of function (Scalar & Parameter Type SQL).
Different templates are required for different function types. For example an aggregation function adds a "stage" parameter to the paramter list. If the Parameter Type was TD_General, then the null indicators for the inputs are not present.
The UDF Programming Manual has examples of all of the different function templates.

me-hp 2 comments Joined 04/11
25 Apr 2011

Hi, dbc1012.

Where can I find the attachement "addTest.c" mentioned above?
I've searched for quite a while, but still can't find it.

Would you please tell me the specific location, or mail a copy to my email address(me-hp@hotmail.com)?

Thanks.

dbc1012 31 comments Joined 03/09
26 Apr 2011

I do not know where the attachements went. However I've added them again, you will find them in the zip file.
Enjoy :-)

me-hp 2 comments Joined 04/11
26 Apr 2011

Hi, dbc1012 (Mr.McCall).

I've read your addTest.c, and found it of helpful.
But still have 2 questions, would you please give me some advice?
1. How to write a test harness for a table function for debugging outside TD?
Such as the example named "extract_field()" on P291 of <>.
I've had a try, but misssing of functions define like "FNC_*" is a challenge.
2. I've also tried debugging within TD using "trace table", but could not make it work.
I didn't get any trace table record after createing and executing a function with the following code.

I can't find a place to add an attachement, so have to paste the long code here.

Thanks a lot : )

**************************************************
extract_field.test.dml
**************************************************
SELECT *
FROM TABLE (extract_field('25,2:9005,7896,9004,7839;36,1:737,9387;',
25)) AS t1;

**************************************************
extract_field.ddl
**************************************************
replace FUNCTION extract_field(Text VARCHAR(32000),
From_Store INTEGER)
RETURNS TABLE (Customer_ID INTEGER,
Store_ID INTEGER,
Item_ID INTEGER)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME extract_field;

**************************************************
extract_field.c
**************************************************
#define SQL_TEXT Latin_Text
#include
#include
/*************************************/
/* The definition of the scratch pad */
/*************************************/
typedef struct
{
int custid;
int itemid;
} item_t;
typedef struct {
int Num_Items;
int Cur_Item;
INTEGER store_num;
item_t *Item_List;
} local_ctx;

/********************************************************************/
/* This copy to SQL_TEXT fields will work to copy ASCII strings to */
/* SQL_TEXT strings (in this case error_message) for any character */
/* set mode. In other words if the SQL_TEXT is defined as */
/* Unicode_Text it will still work to give the proper error message */
/********************************************************************/
static void unicpy(SQL_TEXT *dest,
char *src)
{
while (*src)
*dest++ = *src++;
}
/********************************************************************/
/* A simple function to scan to the next break in the text based on */
/* the delimiter passed in */
/********************************************************************/
static VARCHAR_LATIN *next(VARCHAR_LATIN find,
VARCHAR_LATIN *data)
{
while (*data != '\0')
{
if (*data == find)
break;
data++;
}
return data;
}
/*********************************************************************/
/* The text data that this function processes is in a very simple */
/* format: */
/* */
/* ,:,, ... ; */
/* ,: ... */
/*********************************************************************/
/*********************************************************************/
/* Do a pre-scan of the text and save the data. Note: This pre-scan */
/* routine actually extracts all needed data out of the text field */
/* and saves it in allocated memory via FNC_malloc. With this logic */
/* when it gets to the TBL_BUILD phase the data will simply be taken */
/* from the saved area. There is no need to look at the original */
/* string again during the TBL_BUILD phase. This is just one way to */
/* design it. The alternative is to just do the scanning on the fly */
/* for each time TBL_BUILD is called from the text data field that */
/* is passed in to the table function at all times. It is a choice */
/* that the designer must make when developing the application. */
/*********************************************************************/
static int Prescan(local_ctx *info,
VARCHAR_LATIN *Text,
INTEGER *frmstore)
{
INTEGER storenum;
int i;
int num_items = 0;
VARCHAR_LATIN *Tscan = Text;

/* find the data for the store we are interested in */
while (*Tscan )
{
sscanf((char *) Tscan, "%d", &storenum);
if (*frmstore == storenum)
{
/* found the entry of interest - get the information */
/* on how many items there are */
Tscan = next(',', Tscan)+1;
sscanf((char *) Tscan, "%d", &num_items);
break;
}
/* find next store */
Tscan = next(';',Tscan);
if (*Tscan == '\0')
break;
Tscan++;
}
/* let's malloc some worst case memory to keep track of the items */
/* we collect */
if (num_items)
{
info->Item_List = FNC_malloc(sizeof(item_t)*num_items);
if (info->Item_List == NULL)
/* not good - should have been able to get the memory */
return -1;
}
else
{
info->Num_Items = 0;
return 0;
}
/* now let's find all the entries for the store that we are */
/* interested in */
/* skip to first item */
Tscan = next(':', Tscan)+1;
for (i=0; iItem_List[i].custid,
&info->Item_List[i].itemid);
Tscan = next(',', Tscan)+1;
Tscan = next(',', Tscan)+1;
}
info->Num_Items = num_items;
info->store_num = *frmstore;
return num_items;
}

/********************************************************************/
/* Extract all of the data now. Actually this routine just takes */
/* the items that Prescan built and transfers the data out one item */
/* at a time. Notice that it does not build the output column if it */
/* is not being asked for. For this simple example it probably */
/* makes no difference, but if there is a lot of complexity in the */
/* application to build some columns then it could when noticing */
/* that a field is null not go through the computation to build it */
/* at all. In fact the Prescan function could have been smarter and */
/* not built the list of fields that are not being asked for */
/********************************************************************/
static int Extract(local_ctx *info,
INTEGER *custid,
INTEGER *store,
INTEGER *itemid,
int custid_i,
int store_i,
int item_i)
{
/* check to see if there is something left to extract */
if (info->Cur_Item == info->Num_Items)
return 0;
/* okay let's set the output data only if they want it */
if (custid_i == 0)
*custid = info->Item_List[info->Cur_Item].custid;
if (store_i == 0)
*store = info->store_num;
if (item_i == 0)
*itemid = info->Item_List[info->Cur_Item].itemid;
/* set up for next item the next time */
info->Cur_Item++;
return 1;
}
/***********************************/
/* Do a reset of the context block */
/***********************************/
static void Reset(local_ctx *info)
{
info->Num_Items = 0;
info->Cur_Item = 0;
info->Item_List = NULL;
}
/*********************************************************************/
/* Clean up upon error or when done. Needs to free up any memory */
/* that was allocated or it will return an error message. Note that */
/* the memory was allocated outside of the general scratch pad. But */
/* the address must be retained in the scratch pad or you have no */
/* means of referencing the data or freeing it for subsequent calls. */
/*********************************************************************/
static void Clean_Up(local_ctx *info)
{
if (info->Item_List)
FNC_free(info->Item_List);
}

void extract_field(VARCHAR_LATIN *Text, /* field decode */
INTEGER *frmStore, /* data to extract */
INTEGER *custid, /* 1st output column for row */
INTEGER *store, /* 2nd output column */
INTEGER *item,
int *Text_i, /* in parameter indicator */
int *frmstore_i, /* if no store, return no row */
int *custid_i, /* 1st output indicator for */
int *store_i, /* row, and so on */
int *item_i,
char sqlstate[6],
SQL_TEXT fncname[129],
SQL_TEXT sfncname[129],
SQL_TEXT error_message[257] )
{
local_ctx *state_info;
FNC_Phase Phase;
int status;
/* make sure the function is called in the supported context */
switch (FNC_GetPhase(&Phase))
{
/***********************************************************/
/* Process the constant expression case. Only one AMP will */
/* participate for this example */
/***********************************************************/
case TBL_MODE_CONST:
/* depending on the phase decide what to do */
switch(Phase)
{
case TBL_PRE_INIT:
/* let the system know that I want to be the participant */
switch (FNC_TblFirstParticipant() )
{
case 1: /* participant */
return;
case 0: /* not participant */
/* don't participate */
if (FNC_TblOptOut())
{
strcpy(sqlstate, "U0006"); /* an error return */
unicpy(error_message, "Opt-out failed.");
return;
}
break;
default: /* -1 or other error */
strcpy(sqlstate, "U0007");
unicpy(error_message,
"First Participant logic did not work");
return;
}
case TBL_INIT:
/* get scratch memory to keep track of things */
state_info = FNC_TblAllocCtx(sizeof(local_ctx));
Reset(state_info);

/* Pre-process the Text */
status = Prescan(state_info, Text, frmStore );
if (status == -1)
{
Clean_Up(state_info);
strcpy(sqlstate, "U0008");
unicpy(error_message, "Text had pre-scan errors");
return;
}
break;
case TBL_BUILD:
state_info = FNC_TblGetCtx();
status = Extract(state_info,
custid,
store,
item,
*custid_i,
*store_i,
*item_i);
if (status == 0)
/* Have no more data, return no data sqlstate */
strcpy(sqlstate, "02000");
else if (status == -1)
{
Clean_Up(state_info);
strcpy(sqlstate, "U0009");
unicpy(error_message, "Text had extract error");
return;
}
break;
case TBL_END:
/* everyone done */
state_info = FNC_TblGetCtx();
Clean_Up(state_info);
break;
}
break;
/**********************************/
/* Process the varying expression */
/**********************************/
case TBL_MODE_VARY:
switch(Phase)
{
case TBL_PRE_INIT:
/* get scratch memory to use from now on */
state_info = FNC_TblAllocCtx(sizeof(local_ctx));
Reset(state_info);
break;
case TBL_INIT:
/* Pre-process the Text */
state_info = FNC_TblGetCtx();
status = Prescan(state_info, Text, frmStore );
if (status == -1)
{
status = FNC_TblAbort();

if (status == 1)
{
Clean_Up(state_info);
strcpy(sqlstate, "U0008");
unicpy(error_message, "Text had pre-scan errors");
return;
}
}
break;
case TBL_BUILD:
state_info = FNC_TblGetCtx();
status = Extract(state_info,
custid,
store,
item,
*custid_i,
*store_i,
*item_i);
if (status == 0)
/* Have no more data return no data sqlstate */
strcpy(sqlstate, "02000");
else if (status == -1)
{
status = FNC_TblAbort();
/* if I was the first then let's report the error */
if (status = 1)
{
Clean_Up(state_info);
strcpy(sqlstate, "U0009");
unicpy(error_message, "Text had extract error");
}
return;
}
break;
case TBL_FINI:
/* initialize for the next set of data */
state_info = FNC_TblGetCtx();
Clean_Up(state_info);
Reset(state_info);
break;
case TBL_END:
/* everyone done */
state_info = FNC_TblGetCtx();
Clean_Up(state_info);
break;
case TBL_ABORT:
state_info = FNC_TblGetCtx();
Clean_Up(state_info);
break;
}
}
}

rayedmond 3 comments Joined 12/09
03 Aug 2011

Thanks for the article on developing UDFs.
I am having an issue trying to install the XML services hopefully you can help.
My system is Win 7 64-bit. I am running the MS Virtual-PC XP mode with Teradata Demo 13. I tried the Teradata on VMware first but could not get any of the other utilities.
Now I am trying to install the XML services on it and it keeps telling me:
*** Failure 5862 C/C++ compiler is not installed.
I installed the MS VC++ Express
I have used cufconfig -f to add the CompilerPath and LinkerPath and they show correctly when I do cufconfig -o
I have made the registry changes, in XP.
And I still get the compiler is not installed message. What am I doing wrong?
Also if I restart Teradata the cufconfig changes go away. Is there a way to make them stick?

rayedmond 3 comments Joined 12/09
04 Aug 2011

Update on my previous post. Note Teradata Express 13 will not work with the MS VC++ Express 2010. You have to load 2008 or earlier. I got it to install fine after that.

inah 2 comments Joined 09/12
11 Sep 2012

Hi, is there any way we can add SELECT statements inside a UDF?
Thanks.

You must sign in to leave a comment.