Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
Description
Showddl was fixed to address the launch bug report https://bugs.launchpad.net/trafodion/+bug/1421438 ‘UDF: showddl does not work for UDFs’. It now supports both UDF and TMUDF.
However, the library field in the showddl output is wrong. It is showing the function name, instead of the library name. As shown in the following example, the library field shows TRAFODION.MYTEST.MYUDF which is really the function name. The real library name should be TRAFODION.MYTEST.QA_UDF_LIB
This is seen on the v0331 build installed on a workstation.
(1) Download the attached tar file and untar it to get the 3 files in there. Put the files in any directory <mydir>.
(2) Make sure that you have run ./sqenv.sh of your Trafodion instance first as building UDF needs $MY_SQROOT for the header files.
(3) Change the line “create library qa_udf_lib file '<mydir>/myudf.so';”; in mytest.sql and fill in <mydir>
(4) From sqlci, obey mytest.sql
---------------------------------------------------------------
Here is the execution output:
>>create schema mytest;
— SQL operation complete.
>>set schema mytest;
— SQL operation complete.
>>
>>create library qa_udf_lib file '<mydir>/myudf.so';
— SQL operation complete.
>>
>>create function MYUDF
+>(INVAL int)
+>returns (OUTVAL int)
+>language c
+>parameter style sql
+>external name 'myudf'
+>library qa_udf_lib
+>deterministic
+>state area size 1024
+>allow any parallelism
+>no sql;
— SQL operation complete.
>>
>>create table mytable (a int, b int);
— SQL operation complete.
>>insert into mytable values (1,1);
— 1 row(s) inserted.
>>
>>select myudf(a) from mytable;
OUTVAL
-----------
1
— 1 row(s) selected.
>>
>>get libraries;
Libraries in Schema TRAFODION.MYTEST
====================================
QA_UDF_LIB
— SQL operation complete.
>>showddl function myudf;
CREATE FUNCTION TRAFODION.MYTEST.MYUDF
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT OUTVAL INTEGER SIGNED
)
EXTERNAL NAME 'myudf'
LIBRARY TRAFODION.MYTEST.MYUDF
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;
— SQL operation complete.
>>showddl procedure myudf;
CREATE FUNCTION TRAFODION.MYTEST.MYUDF
(
IN INVAL INTEGER SIGNED
)
RETURNS
(
OUT OUTVAL INTEGER SIGNED
)
EXTERNAL NAME 'myudf'
LIBRARY TRAFODION.MYTEST.MYUDF
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
STATE AREA SIZE 1024
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;
— SQL operation complete.
>>
>>
>>drop schema mytest cascade;
— SQL operation complete.