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.

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 your MySQL is the datatype actually defined as DATE?

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.

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?

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.

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.

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

In your SQL definition is the datatype TIMESTAMP? If it isn't try changing it there and regenerating your synonym.

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

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.

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.

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.

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.

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

Adelaide --

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