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:[email protected]] Sent: Thursday, March 31, 2016 9:55 AM To: [email protected] 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 <[email protected]> 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:[email protected]] > Sent: Thursday, March 31, 2016 2:36 AM > To: [email protected] > 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 > <[email protected]> > 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:[email protected]] > > Sent: Thursday, March 24, 2016 12:06 AM > > To: dev <[email protected]> > > 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 > > <[email protected]> > > 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 > > > <[email protected]> > > > 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 > > > > <[email protected]> > > > > 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 > > > > > > > > > > > > > > >
