Time format issue with mySQL

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Time format issue with mySQL

Postby Adrian Jones » Thu Jan 24, 2013 4:53 pm

Ffenics as a front-end over a mySQL backend.

I have a time col in a SQL view (just time, not datetime). A value of 13:02:40 is being interpretted as 13:24:00 by Ffenics.

Unforunately, Ff issues a 'where' clause when updating that includes all the original values of the row, so it is looking for a row with the current ID AND a time of 13:24:00, which of course it does not find.

On this particular document, the field is read-only, so fortunately I don't have to face any update issues for the time.

Does anyone have any practical suggestions, such as something to do in the view and/or something to change at the Ffenics form end, say?

BTW, if I change the field type in Ff from time to text 12, the value displays as "000000001324".

And double BTW, my preference would be for Ff to NOT issue this style of where clause in the first place. I do have the update verification field on the Remote Table Info dialog set to 'Unique Fields only', but this option is ignored.

Thanks!
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Graham Smith » Thu Jan 24, 2013 5:54 pm

I could be completely off base here but it's possible that Ff simply doesn't know what to do with a SQL time column.

One of the problems with front-ending an SQL engine is that different engines extend the "standard" data-types differently. And then there is the issue of which standard is being used. In classic SQL the only datatype is DATETIME, and the values are typically stored as a number which the engine then translates into the appropriate formatted value. The Date and Time functions are displayed as a subset of the whole value.

IF MySQL is doing something different than this, it could well be that Ff is being confused by that.

Have you tried formatting the field as a number or as a numeric string to see what it looks like?
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Time format issue with mySQL

Postby Pete Tabord » Fri Jan 25, 2013 7:44 am

MySQL has its own interpretation of datetime. It does also support the standard one as an option, but that is not in use on the particular application that Adrian is addressing.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Fri Jan 25, 2013 11:00 am

Pete, then can you tell me what format Ff is expecting to receive a time value?

I can manipulate what Ff gets, but every guess so far has failed.

BTW, this is just a time column, not datetime.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Fri Jan 25, 2013 5:04 pm

Well, I almost had a solution.

The field in question stores the earliest time we should attempt to call back someone. I'm not interested in the seconds, and it occured to me that it would not be the end of the world if I somehow guaranteed that the minute range would always be 10-59.

So 10:09:00 does not work (displays time as 10:90:00), but 10:10:00 does.

Except that the same issue applies to when the hour is less than 10! A SQL time value of 09:59, for example, gets a Ff time value of 95:90:00...

This is looking more ITCh than ETCh!
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Mon Jan 28, 2013 10:03 am

I could still do with any ideas on how to manipulate time data coming out of a SQL db into Ffenics so that Ff interprets the time properly.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Tue Jan 29, 2013 9:18 am

OK, lacking any useable suggestions, I have resorted to changing the mySQL column from Time to smallint, and will record values in here as minutes since midnight.

I have then created a virtual on my FF form to translate this back into an HH:MM format.

Fortunately I can get away with this because the column is only used by the Ff module I am working on, and actual updates to values in this column, plus corresponding row selection, is handled by triggers and views under my control.

I probably would not have been able to do this if this were a column shared with other modules within the overall system.

At the risk of getting jumped on, can I suggest this is a Ffenics issue, and request some reply that suggests whether or not, at some point, it might be addressed?
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Pete Tabord » Tue Jan 29, 2013 11:13 am

Sorry, I misread your original reply and thought you were talking about datetime.

With an ODBC 'connect', the source database is supposed to advise us of the data types it supports. Obviously they will be very different between say an Excel link and one to MS SQL server. We don't think the data types up ourselves! Nor do we actually know what 'make' the server is at the other end. We only know what it chooses to tell us in the initial negotiation when you create a remote link.

We do of course have to map them to Ffenics data types. It may be something I can sort in the driver, maybe not.

It really is a great pity MS have turned away from OLE DB - it is a much more reliable protocol.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Time format issue with mySQL

Postby Graham Smith » Tue Jan 29, 2013 1:46 pm

Pete Tabord wrote:We do of course have to map them to Ffenics data types.

Just based on the work I have done with DFD/DFW/NetPlus/Ff over SQL, I still find that this is the biggest problem I have had to deal with.

While there is such a thing as standard SQL, the fact is that each vendor and each generation seems to come up with it's own version of DML, DDL, DCL, TCL, and EIEIO. It's like going to Baskin-Robins for ice cream and being presented with all 31 flavors at once.

I really don't understand why all this is necessary. Do we really need an Int, Small Int, and Tiny Int? Perhaps at one time when server space cost an arm and leg per MB but not any more. There must be half a dozen different types of Char and Text out there. Really? Why?

Anyhow, it was a big enough problem when DFD had direct drivers that could be modified to deal with the different data types, but with ODBC and OLEDB, the driver itself is generic and both it and the front end have to be able to recognize and deal with the variations.

If Ff is to be used with any kind of external data sources, then someone is going to have to take the time to document how it works with the various types of data in that back end. AND that is going to have to include the ODBC version and setup as well since that can also change. This has pretty much been left to the end user for the simple reason that there are not enough people using any particular back end to justify the work. This is not meant as a criticism.

From a personal point of view, I would like to be able to use Ff over some flavor of SQL as the primary data storage. BUT having spent time doing that with DFD over Sybase and MSSQL, I can tell you that the two things can be a really bad mix. Routine actions by the end user can cause the SQL engine to rev and spin it's wheels endlessly. And normal reports and procedures can end up being slower than they are with native. It simply comes down to the fact that DQL and SQL are almost entirely unlike each other and translations between the two can be horrible.

For that reason, I cannot see that Ff over SQL is a positive direction to be moving in. And if it is going to be done, then the user is going to have to invest a whole boatload of time into optimizing everything that is done by the front end and they are going to have to know that SQL engine inside-out and backwards.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Time format issue with mySQL

Postby Adrian Jones » Tue Jan 29, 2013 2:24 pm

you may be right, Graham, that ultimately FF is not a good SQL front-end.

However, the company I'm doing this for wanted it, and so I've been on a journey of discovery to see if it is possible.

Pretty much it is, although there are plenty of things to consider.

Like NOT doing any or much processing on the front-end (use the back-end instead).

FF could do with quite a bit of revision in this mix as well. Some things have been turned off (probably accidentally), such as the ability to update the forms that get built over views and tables (I've worked out how to turn that back on, btw).

Or how the update verification setting is ignored, resulting in auto-generated 'where' clauses that check every original field value (and is why I am forced to find workarounds to the bits that generated SQL just gets wrong).

I don't agree that you need to know the backend back to front. Like most things in computing, you just need to be prepared to tackle issues as they arise, as long as there's a modest amount of knowledge. I don't know C++, C# or Javascript inside out, for example, but I think I know enough about general programming (culled from a LOT of reading and not just based on the limited window that DE-Ff gives you), backed up with internet searches, to find decent solutions.

But you're right to imply that you can't approach a Ff-SQL situation as though it is anything like native.

Still, having done all this work, I have a fair bit of knowledge about how to make Ff work for SQL. A combination of some documentation/training/workshop plus some improvements in Ff itself might make it a viable option for more than a select few.

If not, so what! It becomes my (and maybe your) specialism for those situations and clients that insist on it or need it.

Given that I got no useable answer regarding the time format, I've had to work around it. If I could find a way to stop Ff generating this 'where' in the first place, I'd save myself a lot of grief.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Graham Smith » Tue Feb 05, 2013 1:36 pm

The most useful thing I have found is to create a SQL table with one column for each data-type used in the database. Populate this with some data, then put the front end over it and experiment. The big advantage to doing this is that you can tinker all you want without harming anything and you can potentially find all your problems in advance.

Tied with the above is to put some kind of monitor in place so that you can see the SQL being generated by the front end. Use the table mentioned above to see exactly what happens when you add/delete/modify data. It seems that you may already be doing this. Part and parcel with this is to dig around in the preferences, ini files, form settings, etc, and flip every switch you can find one at a time and see what if any change that has. Document that 100% as you go. I spent nearly two days doing this with a DFD/Sybase installation and found some really important stuff - which is of 0 value today for obvious reasons.

And then, once you figure out what won't work, get creative. I won't say that Ff cannot be used over SQL, that's obviously not true because I'm using it over an old SQLA application for something myself. But, you cannot us it as if it were native. With the DFD/Sybase app, it was very hard to convince the users that they could no longer open a form and do a Sh-F3 to see the "last record". Once I gave them an alternative (and subjected them to a sufficient amount of beating and ridicule), they finally stopped. Same goes for how searches are done.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Time format issue with mySQL

Postby Pete Tabord » Wed Feb 06, 2013 12:14 pm

Part of the issue is that what effort that has been put into FF SQL support in recent years has mainly been focussed on the OLEDB/Oracle combination because that is the combination that has been most commonly used by our customers since Ff was launched.

That needs to change, mainly because MS has changed its mind about moving everything to OLEDB.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Wed Feb 06, 2013 3:03 pm

Hi Graham,

I've been controlling just how records the user gets to see (often only one!) for so long I'd forgotten about/eliminated the Sh-F3 issues.

And my experience with DQL processing is that it is basically better to get the backend to do it.

(In this app I've been trying out posting a record to a table that then triggers further updates, etc. IOW, a single 'enter a record in' at the Ff end results in more processing-type things happening via the backend.)

So Ff is restricted to bascially UI and navigation. With specialised views on the backend, for example to return a single line of summary data, its proving quite good, really! Null/datetime/time/AnythingElseIveForgtten caveats aside.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Adrian Jones » Thu Feb 07, 2013 9:25 am

I should add that I found a practical issue with having an aspect that allowed access to more than one record at a time.

Imagine the user adds a new record and saves. On or after the save, the dataset is refreshed (IOW, another SQL select goes out) which returns data in PK order, and then seems to jump to the last record in the set.

So it could be the case that suddenly the record you just entered disappears and is replaced by another one.

For this and other reasons I need to control how much data such aspects access at a time -- basically, one 'main' record ('main' in inverted commas because in order to achieve this, I need to use a parenting form to act as a filter onto the subform/in fact true data).

Really, one cannot approach FF over SQL using 'native' thought processes. Issues aside (which can either be fixed in time, or worked around), it does make one begin to question whether FF over SQL is really that worthwhile, and I did ask the client whether they wanted to use some other tool than Ff for the frontend.

Of course, my experiences to date have been based around the requirements of this one project. Ironically, for example, despite my issues with dates and times, for this module the user never does any actual date or time entry directly; these values are all worked out at the backend.

I do try to think outside the box and consider implications of things I come across in a more general fashion, but there are times when simply having to get something resolved overrules idler speculation.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Time format issue with mySQL

Postby Pete Tabord » Thu Feb 07, 2013 4:18 pm

The thing is, I know of several apps out there that do use Ff as a front end to SQL, most of which, as I have said, use OLEDB to Oracle. This includes our largest single customer in the UK.

They seem reasonably happy with it, although there are certainly things that could be improved - there always are. But no-one until now has attempted to use ODBC to MySQL, and clearly it has thrown up some problems that do not occur in other combinations, although it is not clear whether they are to do with the ODBC support or to do with MySQL.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 
Next

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron