UDFs using C which needs to get data from a Teradata Table
I have an intersting dilema. We need to create some UDFs which call a C program. This C program will perform some processing on the incoming data and then perform a lookup into a Teradata table. The data will come back and the UDF will in turn go back to the SQL and then perform the same function for the next row.
My concern is that as this is processing for each row, opening and closing the database connection to get a lookup value is going to be very slow. Is there any way this UDF can keep a connection open?
select my_udf_c_call(first_name) from my_multi_million_row_table
In the SQL above, the udf is called my_udf_c_call. The column "first_name" from the table "my_multi_million_row_table" is passed into the C code. This C code in turns does some processing in C, but then also need to make a call to Teradata to pull back some information. Is there any way to keep the db connection open as the 2nd, 3rd, and subsequent rows are processed?