Focal Point
MySQL extracting dates in Data Mart

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/9111094862

January 28, 2008, 05:00 AM
hiten
MySQL extracting dates in Data Mart
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
January 29, 2008, 08:53 AM
jgelona
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.
January 29, 2008, 09:43 AM
PBrightwell
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
January 30, 2008, 04:21 AM
hiten
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
January 30, 2008, 08:36 AM
jgelona
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.
January 30, 2008, 09:36 AM
hiten
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
January 30, 2008, 03:05 PM
FrankDutch
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

February 04, 2008, 11:03 AM
hiten
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
February 05, 2008, 09:22 AM
PBrightwell
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
August 07, 2008, 03:04 PM
jelli4908
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
August 08, 2008, 09:23 AM
jgelona
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.
August 09, 2008, 07:52 PM
OPALTOSH
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.
August 12, 2008, 06:00 PM
j.gross
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
August 13, 2008, 06:40 AM
OPALTOSH
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.
August 13, 2008, 09:09 AM
jelli4908
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
August 13, 2008, 01:41 PM
j.gross
Adelaide --

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


- Jack Gross
WF through 8.1.05