Unanswered: Retrieve datetime fraction from Tandem NON-Stop SQL DB using ADO
I'm implementing VB clients to access Tandem NON-Stop SQL DB via ODBC for nearly 10 years now. In the past we used a self developed "data access object" that encapsulated ODBC to access the DB.
For this reason I'm sure it's not the ODBC drivers fault - well, it was responsibe for some major headaches over the last few years but unfortunately this time I can't blame it on that thing.
Now we have to use ADO to access our Tandem NON-Stop SQL DB - don't ask me why, there's "no budget to rewrite the thing" for the latest ODBC version - and that's where the trouble starts:
ADO cuts of the fractional part of a datetime column defined as DATETIME YEAR TO FRACTION(6) which equals TIMESTAMP (or adDBTimeStamp as type of the returned field in the ADO recordset).
DB: 2004-02-19 14:43:33.123456
RS: #2004-02-19 14:43:33#
That's a major problem as you can easily see, because the primary key of the table is this one datetime column and the time fraction is vital as lots of records are logged there within one second.
I checked a lot of ressources on the net concerning similar problems so far but since it's also an urgent task I hope to find someone within here who can help me with this problem faster than I might be able to find a solution - if there is one.
Originally posted by Gulinborsti
C'mon, not even the smallest suggestion out there?
Which database are you using ? The latest tandem sql/mx or is it sql/mp..
This problem could have 2 reasons.Actually the datetime field could have set the precision with 19 ..the max length..thats the reason i guess the data is being truncated.Please check out what is the precision set for the particular TIMESTAMP datatype.
As per the ODBC 3.5 specs the total length should be set to 26 which would include the fraction part too..
Please let me know if this helps.
I don't know which version we are using, but I'm sure it's not the latest ;-). And also our Tandem OBDC drivers may not be up to the latest standards, it doesn't support very much ODBC and SQL stuff.
But ADO gets the corret precision, it's 26 as it should be. But the value of the returned columns only contains year to second.
Maybe it's a visual basic problem?
I think the VB date datatype doesn't support fractions and therefore truncates the value.
I have still no idea how to solve this, and since our tandem ODBC driver is ... well, it's not the best money can buy I guess, I can't even use functions in an SQL statement to seperate the date time into to columns for year to second and the fraction in a seperate column.
btw, IF a statement would finally work for the seperate columns idea, how should it look syntactically? I tried everything in the docu but nothing worked. Maybe I can present a statement to our DB group and tell them: "Make this work, however!" =) But I lack the experience and information ...