Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SYBASE DATETIME with ODBC

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SYBASE DATETIME with ODBC
 Login/Join
 
Carol Dobson
Guru
posted
Hello FOCUS friends,

I'm trying to run against SYBase tables using an ODBC connection in WebFOCUS 7.6

No matter what I try, the only way I get a returned result set is using A26 in the master to describe the ACTUAL and FORMAT and passing the whole string to an IF statement. It will not work in a WHERE or EDIT of the field or using a wildcard.

IF PHPERIOD EQ '2008-12-30 00:00:00.000';

Found this in the help and it works!
The Data format of HYYMDs is not supported through the SQLODBC synonym for
the Remedy ODBC driver. Change the FORMAT and ACTUAL in the .mas from HYYMDS
to A26. this allows you to extract date and time stamp info from the tables.

That being said, it's not the friendliest solution for the users and buying the Sybase connection may not be an option, times are tough and money is tight.

Any other suggestions out there before I continue down this ugly path to fix this? Perhaps I'm missing something that I have not tried.

Thanks!

This message has been edited. Last edited by: Carol Dobson,



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
susannah
Expert
posted Hide Post
hmmm
do you mean if you change
PHPERIOD/HYYMDS
to
PHPERIOD/A26
it works?
1)Are you willing to change the master?
2) does this work
DEFINE FILE ...
MYPHPERIOD/A26=PHPERIOD;
... what do you get populating that new field, if you just leave PHPERIOD/HYYMDS
3)
What about writing a user-defined function
I'll help you with it, if you answer 1 and 2.
You could put it in the edasprof
so users could
DEFINE FILE ...
MYDATE/YYMD = F_PHPER(PHPERIOD);
which would be less painful.
But we need to know exactly what the content of that field looks like in its native state.



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
Yes, if I change PHPERIOD/HYYMDS to PHPERIOD/A26 in the Master, it works fine.
In the fex, it's no go.

This fails: FIELDNAME=PHPERIOD, ALIAS=phperiod, USAGE=HYYMDs, ACTUAL=HYYMDs, $
This works: FIELDNAME=PHPERIOD, ALIAS=phperiod, USAGE=A26, ACTUAL=A26, $

Yes, I'm willing to change the master, they rarely change.

I can't even run the fex or PRINT data with the format of HYYMDS, I get this error;
(FOC1383) UNSUPPORTED DATETIME FORMAT FOR FIELD : PHPERIOD

Meanwhile all this works in 5.2.8
These tables are huge so I was hoping for a solution that would be passed to the RDBMS for efficiency.

I'm asking the DBA for a data sample right now in the raw format.

Thanks Susannah!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
I still haven't received the raw data yet, however, I did find out that the time stamp is always zeros.
That being said, I was able to prompt for a friendly date, create a define to concatenate the timestamp and the define does gets passed across to SyBase and runs quickly. I think I have a solution!

Thanks Susannah for the offer, you really are the best! Have a good weekend.



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Did you try:
  
FIELDNAME=PHPERIOD, ALIAS=phperiod, USAGE=YYMD, ACTUAL=DATE, $


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Carol Dobson
Guru
posted Hide Post
Hi Danny-SRL

Yes, I did try this below as my first option;
FIELDNAME=PHPERIOD, ALIAS=phperiod, USAGE=YYMD, ACTUAL=DATE, $

Produces the same error, Unsupported datetime format.

Thanks!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
GamP
Virtuoso
posted Hide Post
Carol,

What would happen if you put some defines in the master file to convert the database field to a good old plain yymd field?
Would you then still receive unsupported errors when selecting on the defined field? If not, then you would have to do a little bit of coding for each field, but you'd be quite all right when that is done.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Carol Dobson
Guru
posted Hide Post
Thanks GamP,

When I do that, it doesn't pass the date to the RDBMS so I'm guessing it's doing a TableScan and takes forever!

We're pricing the adapter to see what it will cost right now!

Hope to see you all at Summit!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
Originally posted by Carol Dobson:
. . .
IF PHPERIOD EQ '2008-12-30 00:00:00.000';
. . .


I'd be very surprised if that works with the semicolon appended.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Carol Dobson
Guru
posted Hide Post
Actually, my real code did not have the semicolon, but just for fun, I tried it with the semicolon and it works.

Good news though, we are looking at purchasing the adapter which will solve all my problems. I'm keeping my fingers crossed! Smiler

We opened a case, however, were were told: "The general rule of thumb is that if they have an adapter for a specific type of datasource, using the ODBC against that type of datasource in unsupported."

After I tried to come up with my own solution (and I tried everything under the sun) I turned to ya'll to see if there was anything I missed since there are a million ways to do things with FOCUS. I needed something that would pass the date proccessing over to the SQL because these tables are huge. I'll be very interested to see if there is any performance difference!

Thanks everyone!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
Yippee, we bought the adapter! Big Grin



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Tony A
Expert
posted Hide Post
Carol,

Sorry, but I missed this one. Did you try this syntax to see what SQL you get? 
WHERE PHPERIOD EQ DT(2008-12-30 00:00:00)

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Carol Dobson
Guru
posted Hide Post
Hi Tony,

It didn't work with a WHERE unfortunately but was OK with an IF, makes no sense! The SQL trace did not pass the date processing with the WHERE. We didn't want our business partners to have to code like that or change every report for every date field, they are more GUI oriented so it was better for us to buy the adapter.

But there goes my Summit funds! Frowner



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Tony A
Expert
posted Hide Post
Carol,

The important piece is not WHERE or IF but the DT(........).

It might have saved your Summit trip Frowner

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Carol Dobson
Guru
posted Hide Post
Tony,

DT as in Date Translation?

I'm still not sure about Summit yet, there's always hope! You may find me sleeping on one of the park benches in the atrium! Cool

Have a great weekend!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SYBASE DATETIME with ODBC

Copyright © 1996-2020 Information Builders