You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Weiqing Xu <xu...@gmail.com> on 2016/03/24 07:27:17 UTC
How to get date from sqlci in binary format
Hi All,
As default, sqlcli return DATE value in *String format* in JDBC T2 Driver.
I want to get the DATE in Binary format since MT-DCS need use JDBC t2
Driver.
Does anyone know how to do it ?
By the way, mxosrvr get the DATE value as Binary from SQLCLI , so I think
it's possible as jdbc t2 too.
Best Regards,
Weiqing
Re: How to get date from sqlci in binary format
Posted by Weiqing Xu <xu...@gmail.com>.
Thanks Dave,
I also think the format was convert in ExpDatetime. ex_function_dateformat
has member variable call dateformat_ which used to decide it call
convDatetimeToASCII or not. Qifan gave me some suggestions on debugging the
code about ExpDatatime last week. I am following these suggestions and
trying to find weather it has relation with my problem .
Re: How to get date from sqlci in binary format
Posted by Weiqing Xu <xu...@gmail.com>.
Thanks Dave,
I also think the format was convert in ExpDatetime. ex_function_dateformat
has member variable call dateformat_ which used to decide it call
convDatetimeToASCII or not. Qifan gave me some suggestions on debugging
the code about ExpDatatime last week. I am following these suggestions and
trying to find the relation with my problem.
RE: How to get date from sqlci in binary format
Posted by Dave Birdsall <da...@esgyn.com>.
Hi Weiqing,
I see. I'm guessing (but I don't know) that mxosrvr wants binary because it
wants to optimize its wire protocol to the T4 client to use fewer bytes.
I don't know though if this binary value is the same as what is internal to
the SQL engine.
I took a look at the SQL engine code. From ExpDatetime::convDatetimeToASCII
in core/sql/exp/exp_datetime.cpp (and with some debugging), I found that the
internal representation of a DATE datatype value is 4 bytes, as follows:
Byte 0-1: The year in binary (I see this in little endian in the debugger)
Byte 2: The month in binary (1-based; for March, for example, I see x03 in
the debugger)
Byte 3: The day in binary
I hope this helps.
Dave
-----Original Message-----
From: Weiqing Xu [mailto:xuweiqingchn@gmail.com]
Sent: Thursday, March 31, 2016 9:55 AM
To: dev@trafodion.incubator.apache.org
Subject: Re: How to get date from sqlci in binary format
Hi Dave,
Yes, they get different results.
For T2 , it gets String like "2016-03-32". As the code, in T2 code , we
need treat date type as VARCHAR_WITH_LENGTH. It looks t2 use varchar to
store date type since some history reason which I don't know.
For mxosrvr , it gets the binary 4 bytes as it stored.
My problem is in MT-DCS. MTDCS need to give the date value to client as the
format which mxosrvr giving to client now. So I don't want the varchar
type.
I will continue to compare the logic of T2 and mxosrvr to find the root
cause of the difference . I think it will be very useful if somebody can
give me some hints or some history information.
Best Regards,
Weiqing
On Fri, Apr 1, 2016 at 12:36 AM, Dave Birdsall <da...@esgyn.com>
wrote:
> Hi Weiqing,
>
> It might help if I could understand what problem you are trying to solve.
>
> I imagine that both the T2 driver and mxosrvr would be calling the
> same sqlcli functions to retrieve dates. Is there some problem where
> the two code paths are getting different results?
>
> Dave
>
> -----Original Message-----
> From: Weiqing Xu [mailto:xuweiqingchn@gmail.com]
> Sent: Thursday, March 31, 2016 2:36 AM
> To: dev@trafodion.incubator.apache.org
> Subject: Re: How to get date from sqlci in binary format
>
> Hi Dave,
> I have spent some time to add some debug information in mxosrvr. I
> want to get the format of the date as the format stored in the databsae.
>
> For date: " DATE specifies a datetime column that contains a date in
> the external form yyyy-mm-dd and stored in four bytes."
>
> But in T2, I can only the the string like "2016-03-31".
>
> I am compare the code of T2 and mxosrvr now. I think I need a lot of
> time to find the root cause. It will reduce the time if someone can
> give me some information.
>
> Best Regards,
> Weiqing
>
> Best Regards,
> Weiqing
>
> On Thu, Mar 24, 2016 at 11:42 PM, Dave Birdsall
> <da...@esgyn.com>
> wrote:
>
> > Hi Weiqing,
> >
> > Can you say more about what you mean by "binary format"? For
> > example, are you looking for a value such as the Unix/Linux time()
> > function returns?
> >
> > Dave
> >
> > -----Original Message-----
> > From: Hans Zeller [mailto:hans.zeller@esgyn.com]
> > Sent: Thursday, March 24, 2016 12:06 AM
> > To: dev <de...@trafodion.incubator.apache.org>
> > Subject: Re: How to get date from sqlci in binary format
> >
> > Hi again,
> >
> > Sorry, no, I don't know how to prevent that conversion to a string
> > from happening. As far as I know, the CLI will always transfer
> > datetime values as a string.
> >
> > Hans
> >
> > On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu
> > <xu...@gmail.com>
> > wrote:
> >
> > > H Hans,
> > >
> > > Thanks for your quickly response.
> > >
> > > But I want to get the Binary datetime from the sqlcli directly
> > > rather than converting it in jdbc driver layer.
> > >
> > > In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli.
> > > When give the parameter "SQLDESC_VAR_PTR", it will get the value
> > > of the item.
> > > Now , JDBC T2 will get a string format datetime through this function
> > > .
> > > What I want to do is getting the binary datetime from this function.
> > > Do you have any idea?
> > >
> > > Best Regards,
> > > Weiqing
> > >
> > > On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller
> > > <ha...@esgyn.com>
> > > wrote:
> > >
> > > > Hi Weiqing,
> > > >
> > > > Here is a code fragment from the UDF code that converts a string
> > > > to the Java "Date" type, taken from
> > > >
> > > >
> > > https://github.com/apache/incubator-trafodion/blob/master/core/sql
> > > /s rc /main/java/org/trafodion/sql/udr/TupleInfo.java
> > > >
> > > > Date resultDate;
> > > >
> > > > String val = getString(colNum);
> > > > if (wasNull_)
> > > > return new Date(0);
> > > > DateFormat df;
> > > >
> > > > try {
> > > > switch (t.getSQLType())
> > > > {
> > > > case DATE:
> > > > // yyyy-mm-dd
> > > > df = new SimpleDateFormat ("yyyy-MM-dd");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > case TIME:
> > > > df = new SimpleDateFormat ("HH:mm:ss");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > case TIMESTAMP:
> > > > df = new SimpleDateFormat ("yyyy-MM-dd
> HH:mm:ss");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > default:
> > > > throw new UDRException(38900,
> > > > "getTime() not
> > > > supported for
> > > SQL
> > > > type %d",
> > > >
> > > > t.getSQLType().ordinal());
> > > > }
> > > > }
> > > > catch (java.text.ParseException e1) {
> > > > throw new UDRException(
> > > > 38900,
> > > > "Unable to parse datetime
> > > > string
> > > %s
> > > > for conversion to Date",
> > > > val);
> > > > }
> > > >
> > > >
> > > >
> > > > Note that some of the enums, Exceptions and variables won't
> > > > match your case, but I hope the general idea is useful.
> > > >
> > > > Hans
> > > >
> > > > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu
> > > > <xu...@gmail.com>
> > > > wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > As default, sqlcli return DATE value in *String format* in
> > > > > JDBC
> > > > > T2
> > > > Driver.
> > > > > I want to get the DATE in Binary format since MT-DCS need use
> > > > > JDBC
> > > > > t2 Driver.
> > > > >
> > > > > Does anyone know how to do it ?
> > > > >
> > > > > By the way, mxosrvr get the DATE value as Binary from SQLCLI ,
> > > > > so I
> > > think
> > > > > it's possible as jdbc t2 too.
> > > > >
> > > > > Best Regards,
> > > > > Weiqing
> > > > >
> > > >
> > >
> >
>
Re: How to get date from sqlci in binary format
Posted by Weiqing Xu <xu...@gmail.com>.
Hi Dave,
Yes, they get different results.
For T2 , it gets String like "2016-03-32". As the code, in T2 code , we
need treat date type as VARCHAR_WITH_LENGTH. It looks t2 use varchar to
store date type since some history reason which I don't know.
For mxosrvr , it gets the binary 4 bytes as it stored.
My problem is in MT-DCS. MTDCS need to give the date value to client as the
format which mxosrvr giving to client now. So I don't want the varchar
type.
I will continue to compare the logic of T2 and mxosrvr to find the root
cause of the difference . I think it will be very useful if somebody can
give me some hints or some history information.
Best Regards,
Weiqing
On Fri, Apr 1, 2016 at 12:36 AM, Dave Birdsall <da...@esgyn.com>
wrote:
> Hi Weiqing,
>
> It might help if I could understand what problem you are trying to solve.
>
> I imagine that both the T2 driver and mxosrvr would be calling the same
> sqlcli functions to retrieve dates. Is there some problem where the two
> code
> paths are getting different results?
>
> Dave
>
> -----Original Message-----
> From: Weiqing Xu [mailto:xuweiqingchn@gmail.com]
> Sent: Thursday, March 31, 2016 2:36 AM
> To: dev@trafodion.incubator.apache.org
> Subject: Re: How to get date from sqlci in binary format
>
> Hi Dave,
> I have spent some time to add some debug information in mxosrvr. I want to
> get the format of the date as the format stored in the databsae.
>
> For date: " DATE specifies a datetime column that contains a date in the
> external form yyyy-mm-dd and stored in four bytes."
>
> But in T2, I can only the the string like "2016-03-31".
>
> I am compare the code of T2 and mxosrvr now. I think I need a lot of time
> to find the root cause. It will reduce the time if someone can give me some
> information.
>
> Best Regards,
> Weiqing
>
> Best Regards,
> Weiqing
>
> On Thu, Mar 24, 2016 at 11:42 PM, Dave Birdsall <da...@esgyn.com>
> wrote:
>
> > Hi Weiqing,
> >
> > Can you say more about what you mean by "binary format"? For example,
> > are you looking for a value such as the Unix/Linux time() function
> > returns?
> >
> > Dave
> >
> > -----Original Message-----
> > From: Hans Zeller [mailto:hans.zeller@esgyn.com]
> > Sent: Thursday, March 24, 2016 12:06 AM
> > To: dev <de...@trafodion.incubator.apache.org>
> > Subject: Re: How to get date from sqlci in binary format
> >
> > Hi again,
> >
> > Sorry, no, I don't know how to prevent that conversion to a string
> > from happening. As far as I know, the CLI will always transfer
> > datetime values as a string.
> >
> > Hans
> >
> > On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu <xu...@gmail.com>
> > wrote:
> >
> > > H Hans,
> > >
> > > Thanks for your quickly response.
> > >
> > > But I want to get the Binary datetime from the sqlcli directly
> > > rather than converting it in jdbc driver layer.
> > >
> > > In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli.
> > > When give the parameter "SQLDESC_VAR_PTR", it will get the value of
> > > the item.
> > > Now , JDBC T2 will get a string format datetime through this function .
> > > What I want to do is getting the binary datetime from this function.
> > > Do you have any idea?
> > >
> > > Best Regards,
> > > Weiqing
> > >
> > > On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com>
> > > wrote:
> > >
> > > > Hi Weiqing,
> > > >
> > > > Here is a code fragment from the UDF code that converts a string
> > > > to the Java "Date" type, taken from
> > > >
> > > >
> > > https://github.com/apache/incubator-trafodion/blob/master/core/sql/s
> > > rc /main/java/org/trafodion/sql/udr/TupleInfo.java
> > > >
> > > > Date resultDate;
> > > >
> > > > String val = getString(colNum);
> > > > if (wasNull_)
> > > > return new Date(0);
> > > > DateFormat df;
> > > >
> > > > try {
> > > > switch (t.getSQLType())
> > > > {
> > > > case DATE:
> > > > // yyyy-mm-dd
> > > > df = new SimpleDateFormat ("yyyy-MM-dd");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > case TIME:
> > > > df = new SimpleDateFormat ("HH:mm:ss");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > case TIMESTAMP:
> > > > df = new SimpleDateFormat ("yyyy-MM-dd
> HH:mm:ss");
> > > > resultDate = df.parse(val);
> > > > break;
> > > >
> > > > default:
> > > > throw new UDRException(38900,
> > > > "getTime() not
> > > > supported for
> > > SQL
> > > > type %d",
> > > > t.getSQLType().ordinal());
> > > > }
> > > > }
> > > > catch (java.text.ParseException e1) {
> > > > throw new UDRException(
> > > > 38900,
> > > > "Unable to parse datetime
> > > > string
> > > %s
> > > > for conversion to Date",
> > > > val);
> > > > }
> > > >
> > > >
> > > >
> > > > Note that some of the enums, Exceptions and variables won't match
> > > > your case, but I hope the general idea is useful.
> > > >
> > > > Hans
> > > >
> > > > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu
> > > > <xu...@gmail.com>
> > > > wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > As default, sqlcli return DATE value in *String format* in JDBC
> > > > > T2
> > > > Driver.
> > > > > I want to get the DATE in Binary format since MT-DCS need use
> > > > > JDBC
> > > > > t2 Driver.
> > > > >
> > > > > Does anyone know how to do it ?
> > > > >
> > > > > By the way, mxosrvr get the DATE value as Binary from SQLCLI ,
> > > > > so I
> > > think
> > > > > it's possible as jdbc t2 too.
> > > > >
> > > > > Best Regards,
> > > > > Weiqing
> > > > >
> > > >
> > >
> >
>
RE: How to get date from sqlci in binary format
Posted by Dave Birdsall <da...@esgyn.com>.
Hi Weiqing,
It might help if I could understand what problem you are trying to solve.
I imagine that both the T2 driver and mxosrvr would be calling the same
sqlcli functions to retrieve dates. Is there some problem where the two code
paths are getting different results?
Dave
-----Original Message-----
From: Weiqing Xu [mailto:xuweiqingchn@gmail.com]
Sent: Thursday, March 31, 2016 2:36 AM
To: dev@trafodion.incubator.apache.org
Subject: Re: How to get date from sqlci in binary format
Hi Dave,
I have spent some time to add some debug information in mxosrvr. I want to
get the format of the date as the format stored in the databsae.
For date: " DATE specifies a datetime column that contains a date in the
external form yyyy-mm-dd and stored in four bytes."
But in T2, I can only the the string like "2016-03-31".
I am compare the code of T2 and mxosrvr now. I think I need a lot of time
to find the root cause. It will reduce the time if someone can give me some
information.
Best Regards,
Weiqing
Best Regards,
Weiqing
On Thu, Mar 24, 2016 at 11:42 PM, Dave Birdsall <da...@esgyn.com>
wrote:
> Hi Weiqing,
>
> Can you say more about what you mean by "binary format"? For example,
> are you looking for a value such as the Unix/Linux time() function
> returns?
>
> Dave
>
> -----Original Message-----
> From: Hans Zeller [mailto:hans.zeller@esgyn.com]
> Sent: Thursday, March 24, 2016 12:06 AM
> To: dev <de...@trafodion.incubator.apache.org>
> Subject: Re: How to get date from sqlci in binary format
>
> Hi again,
>
> Sorry, no, I don't know how to prevent that conversion to a string
> from happening. As far as I know, the CLI will always transfer
> datetime values as a string.
>
> Hans
>
> On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu <xu...@gmail.com>
> wrote:
>
> > H Hans,
> >
> > Thanks for your quickly response.
> >
> > But I want to get the Binary datetime from the sqlcli directly
> > rather than converting it in jdbc driver layer.
> >
> > In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli.
> > When give the parameter "SQLDESC_VAR_PTR", it will get the value of
> > the item.
> > Now , JDBC T2 will get a string format datetime through this function .
> > What I want to do is getting the binary datetime from this function.
> > Do you have any idea?
> >
> > Best Regards,
> > Weiqing
> >
> > On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com>
> > wrote:
> >
> > > Hi Weiqing,
> > >
> > > Here is a code fragment from the UDF code that converts a string
> > > to the Java "Date" type, taken from
> > >
> > >
> > https://github.com/apache/incubator-trafodion/blob/master/core/sql/s
> > rc /main/java/org/trafodion/sql/udr/TupleInfo.java
> > >
> > > Date resultDate;
> > >
> > > String val = getString(colNum);
> > > if (wasNull_)
> > > return new Date(0);
> > > DateFormat df;
> > >
> > > try {
> > > switch (t.getSQLType())
> > > {
> > > case DATE:
> > > // yyyy-mm-dd
> > > df = new SimpleDateFormat ("yyyy-MM-dd");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > case TIME:
> > > df = new SimpleDateFormat ("HH:mm:ss");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > case TIMESTAMP:
> > > df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > default:
> > > throw new UDRException(38900,
> > > "getTime() not
> > > supported for
> > SQL
> > > type %d",
> > > t.getSQLType().ordinal());
> > > }
> > > }
> > > catch (java.text.ParseException e1) {
> > > throw new UDRException(
> > > 38900,
> > > "Unable to parse datetime
> > > string
> > %s
> > > for conversion to Date",
> > > val);
> > > }
> > >
> > >
> > >
> > > Note that some of the enums, Exceptions and variables won't match
> > > your case, but I hope the general idea is useful.
> > >
> > > Hans
> > >
> > > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu
> > > <xu...@gmail.com>
> > > wrote:
> > >
> > > > Hi All,
> > > >
> > > > As default, sqlcli return DATE value in *String format* in JDBC
> > > > T2
> > > Driver.
> > > > I want to get the DATE in Binary format since MT-DCS need use
> > > > JDBC
> > > > t2 Driver.
> > > >
> > > > Does anyone know how to do it ?
> > > >
> > > > By the way, mxosrvr get the DATE value as Binary from SQLCLI ,
> > > > so I
> > think
> > > > it's possible as jdbc t2 too.
> > > >
> > > > Best Regards,
> > > > Weiqing
> > > >
> > >
> >
>
Re: How to get date from sqlci in binary format
Posted by Weiqing Xu <xu...@gmail.com>.
Hi Dave,
I have spent some time to add some debug information in mxosrvr. I want to
get the format of the date as the format stored in the databsae.
For date: " DATE specifies a datetime column that contains a date in the
external form yyyy-mm-dd and stored in four bytes."
But in T2, I can only the the string like "2016-03-31".
I am compare the code of T2 and mxosrvr now. I think I need a lot of time
to find the root cause. It will reduce the time if someone can give me some
information.
Best Regards,
Weiqing
Best Regards,
Weiqing
On Thu, Mar 24, 2016 at 11:42 PM, Dave Birdsall <da...@esgyn.com>
wrote:
> Hi Weiqing,
>
> Can you say more about what you mean by "binary format"? For example, are
> you looking for a value such as the Unix/Linux time() function returns?
>
> Dave
>
> -----Original Message-----
> From: Hans Zeller [mailto:hans.zeller@esgyn.com]
> Sent: Thursday, March 24, 2016 12:06 AM
> To: dev <de...@trafodion.incubator.apache.org>
> Subject: Re: How to get date from sqlci in binary format
>
> Hi again,
>
> Sorry, no, I don't know how to prevent that conversion to a string from
> happening. As far as I know, the CLI will always transfer datetime values
> as
> a string.
>
> Hans
>
> On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu <xu...@gmail.com>
> wrote:
>
> > H Hans,
> >
> > Thanks for your quickly response.
> >
> > But I want to get the Binary datetime from the sqlcli directly rather
> > than converting it in jdbc driver layer.
> >
> > In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli.
> > When give the parameter "SQLDESC_VAR_PTR", it will get the value of the
> > item.
> > Now , JDBC T2 will get a string format datetime through this function .
> > What I want to do is getting the binary datetime from this function.
> > Do you have any idea?
> >
> > Best Regards,
> > Weiqing
> >
> > On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com>
> > wrote:
> >
> > > Hi Weiqing,
> > >
> > > Here is a code fragment from the UDF code that converts a string to
> > > the Java "Date" type, taken from
> > >
> > >
> > https://github.com/apache/incubator-trafodion/blob/master/core/sql/src
> > /main/java/org/trafodion/sql/udr/TupleInfo.java
> > >
> > > Date resultDate;
> > >
> > > String val = getString(colNum);
> > > if (wasNull_)
> > > return new Date(0);
> > > DateFormat df;
> > >
> > > try {
> > > switch (t.getSQLType())
> > > {
> > > case DATE:
> > > // yyyy-mm-dd
> > > df = new SimpleDateFormat ("yyyy-MM-dd");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > case TIME:
> > > df = new SimpleDateFormat ("HH:mm:ss");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > case TIMESTAMP:
> > > df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> > > resultDate = df.parse(val);
> > > break;
> > >
> > > default:
> > > throw new UDRException(38900,
> > > "getTime() not supported
> > > for
> > SQL
> > > type %d",
> > > t.getSQLType().ordinal());
> > > }
> > > }
> > > catch (java.text.ParseException e1) {
> > > throw new UDRException(
> > > 38900,
> > > "Unable to parse datetime
> > > string
> > %s
> > > for conversion to Date",
> > > val);
> > > }
> > >
> > >
> > >
> > > Note that some of the enums, Exceptions and variables won't match
> > > your case, but I hope the general idea is useful.
> > >
> > > Hans
> > >
> > > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu
> > > <xu...@gmail.com>
> > > wrote:
> > >
> > > > Hi All,
> > > >
> > > > As default, sqlcli return DATE value in *String format* in JDBC T2
> > > Driver.
> > > > I want to get the DATE in Binary format since MT-DCS need use JDBC
> > > > t2 Driver.
> > > >
> > > > Does anyone know how to do it ?
> > > >
> > > > By the way, mxosrvr get the DATE value as Binary from SQLCLI , so
> > > > I
> > think
> > > > it's possible as jdbc t2 too.
> > > >
> > > > Best Regards,
> > > > Weiqing
> > > >
> > >
> >
>
RE: How to get date from sqlci in binary format
Posted by Dave Birdsall <da...@esgyn.com>.
Hi Weiqing,
Can you say more about what you mean by "binary format"? For example, are
you looking for a value such as the Unix/Linux time() function returns?
Dave
-----Original Message-----
From: Hans Zeller [mailto:hans.zeller@esgyn.com]
Sent: Thursday, March 24, 2016 12:06 AM
To: dev <de...@trafodion.incubator.apache.org>
Subject: Re: How to get date from sqlci in binary format
Hi again,
Sorry, no, I don't know how to prevent that conversion to a string from
happening. As far as I know, the CLI will always transfer datetime values as
a string.
Hans
On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu <xu...@gmail.com> wrote:
> H Hans,
>
> Thanks for your quickly response.
>
> But I want to get the Binary datetime from the sqlcli directly rather
> than converting it in jdbc driver layer.
>
> In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli.
> When give the parameter "SQLDESC_VAR_PTR", it will get the value of the
> item.
> Now , JDBC T2 will get a string format datetime through this function .
> What I want to do is getting the binary datetime from this function.
> Do you have any idea?
>
> Best Regards,
> Weiqing
>
> On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com>
> wrote:
>
> > Hi Weiqing,
> >
> > Here is a code fragment from the UDF code that converts a string to
> > the Java "Date" type, taken from
> >
> >
> https://github.com/apache/incubator-trafodion/blob/master/core/sql/src
> /main/java/org/trafodion/sql/udr/TupleInfo.java
> >
> > Date resultDate;
> >
> > String val = getString(colNum);
> > if (wasNull_)
> > return new Date(0);
> > DateFormat df;
> >
> > try {
> > switch (t.getSQLType())
> > {
> > case DATE:
> > // yyyy-mm-dd
> > df = new SimpleDateFormat ("yyyy-MM-dd");
> > resultDate = df.parse(val);
> > break;
> >
> > case TIME:
> > df = new SimpleDateFormat ("HH:mm:ss");
> > resultDate = df.parse(val);
> > break;
> >
> > case TIMESTAMP:
> > df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> > resultDate = df.parse(val);
> > break;
> >
> > default:
> > throw new UDRException(38900,
> > "getTime() not supported
> > for
> SQL
> > type %d",
> > t.getSQLType().ordinal());
> > }
> > }
> > catch (java.text.ParseException e1) {
> > throw new UDRException(
> > 38900,
> > "Unable to parse datetime
> > string
> %s
> > for conversion to Date",
> > val);
> > }
> >
> >
> >
> > Note that some of the enums, Exceptions and variables won't match
> > your case, but I hope the general idea is useful.
> >
> > Hans
> >
> > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu
> > <xu...@gmail.com>
> > wrote:
> >
> > > Hi All,
> > >
> > > As default, sqlcli return DATE value in *String format* in JDBC T2
> > Driver.
> > > I want to get the DATE in Binary format since MT-DCS need use JDBC
> > > t2 Driver.
> > >
> > > Does anyone know how to do it ?
> > >
> > > By the way, mxosrvr get the DATE value as Binary from SQLCLI , so
> > > I
> think
> > > it's possible as jdbc t2 too.
> > >
> > > Best Regards,
> > > Weiqing
> > >
> >
>
Re: How to get date from sqlci in binary format
Posted by Hans Zeller <ha...@esgyn.com>.
Hi again,
Sorry, no, I don't know how to prevent that conversion to a string from
happening. As far as I know, the CLI will always transfer datetime values
as a string.
Hans
On Wed, Mar 23, 2016 at 11:56 PM, Weiqing Xu <xu...@gmail.com> wrote:
> H Hans,
>
> Thanks for your quickly response.
>
> But I want to get the Binary datetime from the sqlcli directly rather than
> converting it in jdbc driver layer.
>
> In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli. When
> give the parameter "SQLDESC_VAR_PTR", it will get the value of the item.
> Now , JDBC T2 will get a string format datetime through this function .
> What I want to do is getting the binary datetime from this function. Do you
> have any idea?
>
> Best Regards,
> Weiqing
>
> On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com>
> wrote:
>
> > Hi Weiqing,
> >
> > Here is a code fragment from the UDF code that converts a string to the
> > Java "Date" type, taken from
> >
> >
> https://github.com/apache/incubator-trafodion/blob/master/core/sql/src/main/java/org/trafodion/sql/udr/TupleInfo.java
> >
> > Date resultDate;
> >
> > String val = getString(colNum);
> > if (wasNull_)
> > return new Date(0);
> > DateFormat df;
> >
> > try {
> > switch (t.getSQLType())
> > {
> > case DATE:
> > // yyyy-mm-dd
> > df = new SimpleDateFormat ("yyyy-MM-dd");
> > resultDate = df.parse(val);
> > break;
> >
> > case TIME:
> > df = new SimpleDateFormat ("HH:mm:ss");
> > resultDate = df.parse(val);
> > break;
> >
> > case TIMESTAMP:
> > df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> > resultDate = df.parse(val);
> > break;
> >
> > default:
> > throw new UDRException(38900,
> > "getTime() not supported for
> SQL
> > type %d",
> > t.getSQLType().ordinal());
> > }
> > }
> > catch (java.text.ParseException e1) {
> > throw new UDRException(
> > 38900,
> > "Unable to parse datetime string
> %s
> > for conversion to Date",
> > val);
> > }
> >
> >
> >
> > Note that some of the enums, Exceptions and variables won't match your
> > case, but I hope the general idea is useful.
> >
> > Hans
> >
> > On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu <xu...@gmail.com>
> > wrote:
> >
> > > Hi All,
> > >
> > > As default, sqlcli return DATE value in *String format* in JDBC T2
> > Driver.
> > > I want to get the DATE in Binary format since MT-DCS need use JDBC t2
> > > Driver.
> > >
> > > Does anyone know how to do it ?
> > >
> > > By the way, mxosrvr get the DATE value as Binary from SQLCLI , so I
> think
> > > it's possible as jdbc t2 too.
> > >
> > > Best Regards,
> > > Weiqing
> > >
> >
>
Re: How to get date from sqlci in binary format
Posted by Weiqing Xu <xu...@gmail.com>.
H Hans,
Thanks for your quickly response.
But I want to get the Binary datetime from the sqlcli directly rather than
converting it in jdbc driver layer.
In JDBC T2 , it use CLI_SetDescItem() to get the data from sqlcli. When
give the parameter "SQLDESC_VAR_PTR", it will get the value of the item.
Now , JDBC T2 will get a string format datetime through this function .
What I want to do is getting the binary datetime from this function. Do you
have any idea?
Best Regards,
Weiqing
On Thu, Mar 24, 2016 at 2:35 PM, Hans Zeller <ha...@esgyn.com> wrote:
> Hi Weiqing,
>
> Here is a code fragment from the UDF code that converts a string to the
> Java "Date" type, taken from
>
> https://github.com/apache/incubator-trafodion/blob/master/core/sql/src/main/java/org/trafodion/sql/udr/TupleInfo.java
>
> Date resultDate;
>
> String val = getString(colNum);
> if (wasNull_)
> return new Date(0);
> DateFormat df;
>
> try {
> switch (t.getSQLType())
> {
> case DATE:
> // yyyy-mm-dd
> df = new SimpleDateFormat ("yyyy-MM-dd");
> resultDate = df.parse(val);
> break;
>
> case TIME:
> df = new SimpleDateFormat ("HH:mm:ss");
> resultDate = df.parse(val);
> break;
>
> case TIMESTAMP:
> df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
> resultDate = df.parse(val);
> break;
>
> default:
> throw new UDRException(38900,
> "getTime() not supported for SQL
> type %d",
> t.getSQLType().ordinal());
> }
> }
> catch (java.text.ParseException e1) {
> throw new UDRException(
> 38900,
> "Unable to parse datetime string %s
> for conversion to Date",
> val);
> }
>
>
>
> Note that some of the enums, Exceptions and variables won't match your
> case, but I hope the general idea is useful.
>
> Hans
>
> On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu <xu...@gmail.com>
> wrote:
>
> > Hi All,
> >
> > As default, sqlcli return DATE value in *String format* in JDBC T2
> Driver.
> > I want to get the DATE in Binary format since MT-DCS need use JDBC t2
> > Driver.
> >
> > Does anyone know how to do it ?
> >
> > By the way, mxosrvr get the DATE value as Binary from SQLCLI , so I think
> > it's possible as jdbc t2 too.
> >
> > Best Regards,
> > Weiqing
> >
>
Re: How to get date from sqlci in binary format
Posted by Hans Zeller <ha...@esgyn.com>.
Hi Weiqing,
Here is a code fragment from the UDF code that converts a string to the
Java "Date" type, taken from
https://github.com/apache/incubator-trafodion/blob/master/core/sql/src/main/java/org/trafodion/sql/udr/TupleInfo.java
Date resultDate;
String val = getString(colNum);
if (wasNull_)
return new Date(0);
DateFormat df;
try {
switch (t.getSQLType())
{
case DATE:
// yyyy-mm-dd
df = new SimpleDateFormat ("yyyy-MM-dd");
resultDate = df.parse(val);
break;
case TIME:
df = new SimpleDateFormat ("HH:mm:ss");
resultDate = df.parse(val);
break;
case TIMESTAMP:
df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss");
resultDate = df.parse(val);
break;
default:
throw new UDRException(38900,
"getTime() not supported for SQL
type %d",
t.getSQLType().ordinal());
}
}
catch (java.text.ParseException e1) {
throw new UDRException(
38900,
"Unable to parse datetime string %s
for conversion to Date",
val);
}
Note that some of the enums, Exceptions and variables won't match your
case, but I hope the general idea is useful.
Hans
On Wed, Mar 23, 2016 at 11:27 PM, Weiqing Xu <xu...@gmail.com> wrote:
> Hi All,
>
> As default, sqlcli return DATE value in *String format* in JDBC T2 Driver.
> I want to get the DATE in Binary format since MT-DCS need use JDBC t2
> Driver.
>
> Does anyone know how to do it ?
>
> By the way, mxosrvr get the DATE value as Binary from SQLCLI , so I think
> it's possible as jdbc t2 too.
>
> Best Regards,
> Weiqing
>