trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Birdsall <dave.birds...@esgyn.com>
Subject RE: How to get date from sqlci in binary format
Date Thu, 31 Mar 2016 17:29:15 GMT
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 <dave.birdsall@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
> <dave.birdsall@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 <dev@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
> > <xuweiqingchn@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
> > > <hans.zeller@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
> > > > <xuweiqingchn@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
> > > > >
> > > >
> > >
> >
>

Mime
View raw message