Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    MySQL extracting dates in Data Mart
Go
New
Search
Notify
Tools
Reply
  
MySQL extracting dates in Data Mart
 Login/Join
 
Member
posted
We are trying to extract dates from a MySQL database.

The dates are stored in HYYMDS like "2007-09-14 09:39:00" for some reason when creating the synonym it does not recognise this as a date field and sets the format to A0, changing this in the mas file does not rectify the problem.

Any help will be greatly appreciated.

We are running iWay Data Management Console 7.6.1 connecting to the MySQL database with MySQL JDBC connector 5.1.5.


Prod: WF 7.6.1 platform Windows, databases: msSQL2000, msSQL2005, IE6
 
Posts: 12 | Registered: June 01, 2007Reply With QuoteReport This Post
Master
posted Hide Post
Are they stored like "2007-09-14 09:39:00" or are they stored as a datetime stamps? "2007-09-14 09:39:00" is a display format for a date, not how a date is stored. For example a datetime stamp in Oracle is stored internally as an 8 byte binary. How is the field defined in the database?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Master
posted Hide Post
In your MySQL is the datatype actually defined as DATE?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Hi,

I've checked the datatype in the mySQL database and it is DATETIME.

I'm not sure what you mean how is the date defined in the database, but the entry is shown as "2007-09-14 09:39:00".

Thanks for any help you can provide.


Prod: WF 7.6.1 platform Windows, databases: msSQL2000, msSQL2005, IE6
 
Posts: 12 | Registered: June 01, 2007Reply With QuoteReport This Post
Master
posted Hide Post
If the field is defined as DATETIME, the in your .mas the USAGE format should be something like HYYMDS and the ACTUAL format should be HYYMDS. Can you post the part of the .mas as the synonym builder generated it?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Member
posted Hide Post
This is the mas file that is created by the synonym builder

FILENAME=PRINTJOBS, SUFFIX=SQLMYSQL, $
SEGMENT=PRINTJOBS, SEGTYPE=S0, $
FIELDNAME=ID, ALIAS=ID, USAGE=P20, ACTUAL=P10, $
FIELDNAME=TRANSACTIONID, ALIAS=TransactionID, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=USERDN, ALIAS=UserDN, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=PRINTQUEUE, ALIAS=PrintQueue, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=PRINTSERVER, ALIAS=PrintServer, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=PRINTER, ALIAS=Printer, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=DELIVERYMODE, ALIAS=DeliveryMode, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=SIZE, ALIAS=Size, USAGE=P11, ACTUAL=P6, $
FIELDNAME=JOBLANGUAGE, ALIAS=JobLanguage, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=PAGES, ALIAS=Pages, USAGE=P11, ACTUAL=P6, $
FIELDNAME=PRINTED, ALIAS=Printed, USAGE=I6, ACTUAL=I2, $
FIELDNAME=CHARGES, ALIAS=Charges, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=TIMESUBMITTED, ALIAS=TimeSubmitted, USAGE=A0, ACTUAL=A0, $
FIELDNAME=TIME, ALIAS=Time, USAGE=A0, ACTUAL=A0, $
FIELDNAME=STATUS, ALIAS=Status, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=BANNERS, ALIAS=Banners, USAGE=P11, ACTUAL=P6, $
FIELDNAME=DUPLEX, ALIAS=Duplex, USAGE=I6, ACTUAL=I2, $
FIELDNAME=PAPERSIZE, ALIAS=PaperSize, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=WORKSTATIONIP, ALIAS=WorkstationIP, USAGE=A255V, ACTUAL=A255V, $
FIELDNAME=INFORMATION, ALIAS=Information, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $
FIELDNAME=COSTCENTRE, ALIAS=CostCentre, USAGE=A255V, ACTUAL=A255V, $


As you can see the TIMESUBMITTED and TIME fields are both A0.

I did try and chenge these to HYYMDS but still received an error.


Prod: WF 7.6.1 platform Windows, databases: msSQL2000, msSQL2005, IE6
 
Posts: 12 | Registered: June 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
FIELDNAME=TIMESUBMITTED, ALIAS=TimeSubmitted, USAGE=A0, ACTUAL=A0, $
FIELDNAME=TIME, ALIAS=Time, USAGE=A0, ACTUAL=A0, $



can you change this to

FIELDNAME=DATESUBMITTED, ALIAS=TimeSubmitted, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=DATEX, ALIAS=Time, USAGE=YYMD, ACTUAL=DATE, $

if that works you only get the date, but then you might be able to add some extra lines like

FIELDNAME=TIMESUBMITTED, ALIAS=TimeSubmitted, USAGE=HYYMDs, ACTUAL=HYYMDs, $
FIELDNAME=TIMEX, ALIAS=Time, USAGE=HYYMDs, ACTUAL=HYYMDs, $

As you can see these fields points to the same fields in the database, but the results you get are different.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Hi Frank,

I have tried the mothod you deailed, but this bring up the following message
(FOC207) ERROR IN THE FORMAT DEFINITION OF FIELD: DATESUBMITTED
(FOC207) ERROR IN THE FORMAT DEFINITION OF FIELD: DATEX
(FOC207) ERROR IN THE FORMAT DEFINITION OF FIELD: TIMESUBMITTED
(FOC207) ERROR IN THE FORMAT DEFINITION OF FIELD: TIME
(FOC1720) SYNONYM GPAS/PRINTJOBS REFRESH COMPLETED

Thanks
Hiten


Prod: WF 7.6.1 platform Windows, databases: msSQL2000, msSQL2005, IE6
 
Posts: 12 | Registered: June 01, 2007Reply With QuoteReport This Post
Master
posted Hide Post
In your SQL definition is the datatype TIMESTAMP? If it isn't try changing it there and regenerating your synonym.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Has there been a resolution to this issue? I am working with MySQL and receiving a similar error.

The error is "(FOC1383) UNSUPPORTED DATETIME FORMAT FOR FIELD : CLIENT_LAST_ACTIVITY "

The master file generated by the synonym wizard contains:
FIELDNAME=CLIENT_LAST_ACTIVITY, ALIAS=client_last_activity, USAGE=HYYMDS, ACTUAL=HYYMDS, MISSING=ON, $

I have changed the USAGE and ACTUAL to YYMD and receive weird data (1901/05/24, 1901/05/28, etc.), and the dates are suppose to be 2007-12-06 14:53:01 and 2007-12-06 15:38:00.

According to MySQL the data is stored as a DATETIME.

Thanks for the help!
Josh


WebFOCUS 8.0.09
Windows, DB2 iSeries, ODBC
Output: Excel, HTML, PDF, AHTML
 
Posts: 37 | Registered: October 26, 2007Reply With QuoteReport This Post
Master
posted Hide Post
I have never worked with MySQL, but in Oracle all date fields are date/time. If time is not stored, it is set to the default time of midnight (00:00:00.00000). Just because a field is define as date/time does not mean the time is being stored. In our database, we have hundreds of fields where the time portion is not relevant. For these, I set USAGE=YYMD and the ACTUAL=DATE. For those fields where time is relevant I will do this:
FIELD=DATEONLY,ALIAS=DATETIME,USAGE=YYMD,ACTUAL=DATE,$
FIELD=DATETIME,ALIAS=DATETIME,USAGE=HYYMDS,ACTUAL=HYYMDS,$

This lets me treat the field as a date only or date time.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 905 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
The problem with MYSQL dates is that internally they are stored as the number of milliseconds since 01011970, not since 01011901 like FOCUS internal dates.
So you have to add the internal MYSQL value to 101011970 get a value that WebFOCUS can interpret as a date.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I'd say that's not a "problem with MySQL", it's a problem with the iWay adapter.

The ACTUAL specifies the layout of the value that the adapter places in Focus's retrieval buffer (and accepts for an input value in the transaction buffer) -- not nec. identical to either the database's internal storage format or its interface format for the field.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Of course it's not a problem with MYSQL per se, but it is a problem for anyone wanting to access these dates and convert them to the FOCUS internal format such as hiten.

This is how I did it.
Define the MYSQL date field in the MAS file as I11 in this case PH_DATE

TEMP1/D11 = PH_DATE;
ICASTERBASE/I8YYMD WITH PH_ID = 19700101;
CASTERBASE/YYMD = ICASTERBASE;
CASTERBASEDTM/HYYMDS = HDTTM(CASTERBASE,8,'HYYMDS');

Hope it helps.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks OPALTOSH for the good ideas.

It wouldn't work for me as I11. I have it working with setting the date as an alpha and then using HINPUT to convert it to a FOCUS date-time. Hopefully I can work with this.

Thanks again for the ideas,
Josh


WebFOCUS 8.0.09
Windows, DB2 iSeries, ODBC
Output: Excel, HTML, PDF, AHTML
 
Posts: 37 | Registered: October 26, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Adelaide --

Of course - work around it - but also open a case.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    MySQL extracting dates in Data Mart

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.