You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2016/03/18 04:43:31 UTC

How to implement a TO_DATE UDF in Trafodion

Hi, all,

I just learned the support of TO_DATE will be avialbe in Trafodion in R2.0 from Anoop, that is great! At present, due to the urgent requirement of current migration project, we cannot wait, so I want to write a UDF to do the TO_DATE.
When I try to write it, I found it seems the UDF can only return simple data type like INT, VARCHAR, I cannot find the definition of returning a TIMESTAMP/DATE, could anyone help here?

In sqludr.h , I cannot find corresponding data type for DATE/TIMESTAM.
So I don't know how to define the UDF's entry function, example as below:

SQLUDR_LIBFUNC SQLUDR_INT32 to_date(SQLUDR_VC_STRUCT *srcStr,    //input string
                                SQLUDR_CHAR *pattern,     //date format
                                ??? *out1,     //the output
                                SQLUDR_INT16 *inInd1,
                                SQLUDR_INT16 *inInd2,
                                SQLUDR_INT16 *outInd1,
                                SQLUDR_TRAIL_ARGS)

How can I specify the out1 type to be a DATE or TIMESTAMP? I marked with '???' .

Thanks in advance,
Ming

答复: How to implement a TO_DATE UDF in Trafodion

Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.

Thanks Hans,

Yes, SQLUDR_CHAR can support input/output values as TIMESTAMP. This solves my problem.
So Trafodion UDF can support TIMESTAMP as both input parameter and output , this is great!

Thanks,
Ming

发件人: Hans Zeller [mailto:hans.zeller@esgyn.com]
发送时间: 2016年3月24日 1:34
收件人: user@trafodion.incubator.apache.org
主题: Re: How to implement a TO_DATE UDF in Trafodion

Hi Ming, I think the C data type corresponding to datetime values is SQLUDR_CHAR. You create a string that looks like "2016-03-23 01:23:45" (this example is for a TIMESTAMP(0)) and return that. Trafodion will convert it to the datetime type.

Sorry, I have not tested it but hope it will work.

Hans

On Thu, Mar 17, 2016 at 8:43 PM, Liu, Ming (Ming) <mi...@esgyn.cn>> wrote:
Hi, all,

I just learned the support of TO_DATE will be avialbe in Trafodion in R2.0 from Anoop, that is great! At present, due to the urgent requirement of current migration project, we cannot wait, so I want to write a UDF to do the TO_DATE.
When I try to write it, I found it seems the UDF can only return simple data type like INT, VARCHAR, I cannot find the definition of returning a TIMESTAMP/DATE, could anyone help here?

In sqludr.h , I cannot find corresponding data type for DATE/TIMESTAM.
So I don’t know how to define the UDF’s entry function, example as below:

SQLUDR_LIBFUNC SQLUDR_INT32 to_date(SQLUDR_VC_STRUCT *srcStr,    //input string
                                SQLUDR_CHAR *pattern,     //date format
                                ??? *out1,     //the output
                                SQLUDR_INT16 *inInd1,
                                SQLUDR_INT16 *inInd2,
                                SQLUDR_INT16 *outInd1,
                                SQLUDR_TRAIL_ARGS)

How can I specify the out1 type to be a DATE or TIMESTAMP? I marked with ‘???’ .

Thanks in advance,
Ming

Re: How to implement a TO_DATE UDF in Trafodion

Posted by Hans Zeller <ha...@esgyn.com>.

Hi Ming, I think the C data type corresponding to datetime values is
SQLUDR_CHAR. You create a string that looks like "2016-03-23 01:23:45"
(this example is for a TIMESTAMP(0)) and return that. Trafodion will
convert it to the datetime type.

Sorry, I have not tested it but hope it will work.

Hans

On Thu, Mar 17, 2016 at 8:43 PM, Liu, Ming (Ming) <mi...@esgyn.cn> wrote:

> Hi, all,
>
>
>
> I just learned the support of TO_DATE will be avialbe in Trafodion in R2.0
> from Anoop, that is great! At present, due to the urgent requirement of
> current migration project, we cannot wait, so I want to write a UDF to do
> the TO_DATE.
>
> When I try to write it, I found it seems the UDF can only return simple
> data type like INT, VARCHAR, I cannot find the definition of returning a
> TIMESTAMP/DATE, could anyone help here?
>
>
>
> In sqludr.h , I cannot find corresponding data type for DATE/TIMESTAM.
>
> So I don’t know how to define the UDF’s entry function, example as below:
>
>
>
> SQLUDR_LIBFUNC SQLUDR_INT32 to_date(SQLUDR_VC_STRUCT *srcStr,    //input
> string
>
>                                 SQLUDR_CHAR *pattern,     //date format
>
>                                 ??? *out1,     //the output
>
>                                 SQLUDR_INT16 *inInd1,
>
>                                 SQLUDR_INT16 *inInd2,
>
>                                 SQLUDR_INT16 *outInd1,
>
>                                 SQLUDR_TRAIL_ARGS)
>
>
>
> How can I specify the out1 type to be a DATE or TIMESTAMP? I marked with
> ‘???’ .
>
>
>
> Thanks in advance,
>
> Ming
>