Attempt To 'Own' A MySQL Table

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Thu Aug 02, 2012 4:25 pm

OK. How to I explain what I am trying to do without people misunderstanding.

I'm currently exploring issues to do with building a Ff app over a MySQL database, in this case via ODBC.

I can create the link and build forms over the backend tables and view, which I note creates a TDF, FRM and presumably unneeded DBM file.

What I need to now explore is how to get the front-end functionality such as field derivations, etc.

I can, by going to Doc Props --> Remote Table, change the top option Local Form defines Remote Table to yes and save it, which seems to enable the field definition dialog elements.

I can add a field derivation, but I can't then save the form. I get an error, and at the same time, Ff zero-bytes the TDF and creates an FR$ file as well.

I stress that I am not trying to do data definition via Ffenics. The table exists in MySQL, and I am not changing the structure in any way.

I'm sure this was possible in DfW, and is really what NetPlus is doing.

Does anyone have any experience of attempting the same thing?

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: Attempt To 'Own' A MySQL Table

Postby Graham Smith » Thu Aug 02, 2012 4:56 pm

I have tried and failed to do the same sort of thing. I'm pretty sure it was reported but that was a long time back.
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Thu Aug 02, 2012 5:06 pm

A small addition to my post -- I just tried exactly this in DfW 6.5, and had no problem.

Thanks, Graham -- you understand what I'm trying to do.

I wonder is there a way to cheat Ffenics here?
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Thu Aug 02, 2012 5:15 pm

Whoops ... and it looks like I can do it in DFW 7.2 as well.. :(
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: Attempt To 'Own' A MySQL Table

Postby Graham Smith » Fri Aug 03, 2012 12:00 pm

Pete can jump in here but I believe that this stems from the removal of the old interoperability functionality.
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Fri Aug 03, 2012 1:43 pm

I'm starting to work out how I can cheat this, but I'll convince myself I have a workaround before I give any details.
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: Attempt To 'Own' A MySQL Table

Postby Pete Tabord » Tue Aug 21, 2012 9:52 am

It's nothing to do with interoperability.

I made extensive changes to ODBC just so it would work with MySQL - unfortunatly this was about 2008 and I can't remember anything about it now. I seem to recall you no longer do things through forms, you create Aspects, but I could be quite wrong. I'm pretty sure it didn't involve making the forms 'own' the tables.
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Tue Aug 21, 2012 10:07 am

I think part of the problem in discussing this is that the option on that remote table dialog is labelled something like "own remote table", but it actually does more than that.

In DfW, it let you make some limited changes to the TDF, ones that are not about data definition (from a mySQL perspective), but perhaps more about data interpretation/presentation -- e.g. 4 digit years, integer and decimals, etc.

More importantly, it lets you add field derivations, clearly something that belongs at the app-wide level in DE-Ff, and again nothing to do with the backend base table.

The downside, as I found out, is that the option also does mean "own remote", and will, for example, delete the mySQL table if one deletes the 'owning' form.

To make Ff into a SQL front-end, as opposed to putting Ff in the bin and working with something else, it does need to let us define what I've loosely called 'data interpretation'.

I have found a workaround, I think, and am currently trying to assess if I can get Ff to work fully as a mySQL front-end, maybe with a few compromises when it comes to the back-end.

this is for HBC, btw.
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: Attempt To 'Own' A MySQL Table

Postby Pete Tabord » Tue Aug 21, 2012 10:16 am

I guessed.

Trina was dead set against having the ff form 'own' the table precisely because it may cause inadvertant damage and in the modern model of things it would not be 'our' SQL stuff to do damage to. So I'm sure you could make the sort of changes you discuss without owning the table at one time.

It may be that some change since 2008 has inadvertantly caused the derivation etc. stuff to be accidentally locked out when you don't own the table - I seem to recall something to do with LOV's that may be responsible. I'll go and check.
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: Attempt To 'Own' A MySQL Table

Postby Pete Tabord » Tue Aug 21, 2012 10:41 am

Quick look suggests there is a lot of confusion between "USES/OWNS_TABLE" and "USE/DEFINE_TABLE" (the first refers to the TDF, the second to the remote table). It looks like I need a few hours with this and some careful notes to make sure we are changing the right one - the dialog in "remote table" in doc props is setting the wrong one, for example. Another fix for 1.6, I guess!

Yes, I did change it previously so you could do the derivations etc without changing to 'defines', but I've subsequently commented it out because it didn't work properly and I didn't - at the time - notice the above confusion. The date (9/2009) probabaly gives the clue to why - I wasn't thinking very well at the time.

I think it always did create an empty DBM.

I seem to remember also you had to turn DDL on somewhere for the ODBC driver maybe in the .ini file? That may be why it isn't working whan you do define the remote table. But would still be better not to have to switch them to 'define'.
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Tue Aug 21, 2012 10:46 am

[written before the post above]

If it were possible to do the -- shall we call them repository changes? -- without doing any DDL, that would be great!

Of course, once you start looking at this, it does open a small minefield.

For example, I know that in the background, Ff stores, say, integers as one or more byte numbers depending on whatever is the size that will be need for a four-digit integer, and that probably increasing the size to five digits should not make a difference to the actual storage (and hence not need any DDL).

So it should be right to alter the interpretation of the number of digits -- which after all is a presentational thing really -- but how should you handle what happens when the size change requires a change in the type of back-end data type?

Obviously, Ff should not be changing the data type. The issue I see, though, is how to present this meaningfully to the Ff user. Unless the field def dialog is modified to only allow changes to ints and other numbers according to the range the back-end type will allow. Or numbers are not allowed to be tweaked in this way in the first place.
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: Attempt To 'Own' A MySQL Table

Postby Adrian Jones » Tue Aug 21, 2012 10:47 am

Pete Tabord wrote:
I seem to remember also you had to turn DDL on somewhere for the ODBC driver maybe in the .ini file? That may be why it isn't working whan you do define the remote table (which is a seperate issue from 'owning' the TDF - in FF, forms always own the TDF). But it would still be better not to have to switch them to 'define'.


I'll have a look. Didn't occur to me that ODBC might control the use of DDL...
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: Attempt To 'Own' A MySQL Table

Postby Pete Tabord » Tue Aug 21, 2012 11:09 am

I was editing and you've posted more :-) I've added some more detail above.

One of the things about making it possible to edit stuff without changing to 'DEFINES' is that it guarantees that any changes - such as length of number field - remain cosmetic.
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: Attempt To 'Own' A MySQL Table

Postby Graham Smith » Tue Aug 21, 2012 12:32 pm

Jumping in here with a few foggy recollections - on the theory that it sometimes helps to know how you got to a particular place...

Doesn't this all get back to Arun's concept for middleware?

I remember for a long time there was the debate about whether DFD and DFW and NetPlus should only be used as a front end or if they should be used as SQL development tools. They could do both but a number of people (myself and Trina included) felt that they should only be used to front SQL tables - that all development should be done with proper SQL tables. And I spent a couple years working with DFD over Sybase at Gore that way and never had an issue (once I learned how to write a propper DQL).

Ff has a form property that allows it to "Define Remote Table" (a rather inaccurate description). This allows you to do things like add derivations and make a number into a numeric string. You can also add virtual fields. Basically it behaves the same as DFD and DFW with the exception that the other two had two properties. One allowed the same as Ff but the other actually allowed you to OWN the table - make changes to it.

So far, so good. The problem, as Adrian notes, is that you can't save a Form in Ff if you do flip the Define switch. I think that if that were fixed, everything would be OK. BUT it all depends on what you can and cannot do with that enabled. IMnsHO, any changes made should only be made at the TDF level.
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: Attempt To 'Own' A MySQL Table

Postby Pete Tabord » Tue Aug 21, 2012 2:14 pm

Well, Trina moved things on a bit, along with some of the stuff done with OLEDB.

As you mention many people were of the opinion that it was too much to ask of both Ffenics and the typical Ffenics developer to build an SQL database entirely from within Ffenics. The feeling was that exchanging data with or reporting data on a SQL server was fine, as was data warehousing for BI/EIS purposes. Or even building a new database in some server software and developing the entire front end in ff. But not developing databases from scratch entirely with ff.

So the idea of building tables from within ff was largely dropped, as it had also been from the OLEDB consumer that had been written earlier. This change of strategy meant that there was little reason to actually 'define a SQL table' from within ff and the only situation in which ff can now define or change the actual SQL table is if it is an ODBC link and DDL has been enabled in the driver - I can't recall if it is by default. This is what the remaining option is for, as in ff the form always owns what in DFW was called the table (The stuff in the TDF).

There is no question of enabling DDL in the OLEDB consumer, no code was written to support it. The 'defines' option should really be greyed out except in the specific case above.

This however also meant that if you wanted to add some 'front end' logic to the tables we needed an alternative, and the idea was that you would be able to add derivations, change display lengths, visual controls etc from any form (not aspect) over a remote table.

I actually implemented this to Trina's requirements, but it had some problems and at a time of stress I just commented it out. I can now see the problems were due to the confusion in the code mentioned above between owning the ff data definition and defining the SQL table, which are two quite seperate things. So I intend to fix it before 1.6 goes out.
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