PDA

View Full Version : Problems with using the overlap field.



Edgard
21-Mar-2022, 01:21 PM
Hi,

I know that the recommendation is not to use it anymore, especially in SQL.


As I'm dealing with an embedded and legacy base I come across, with the following scenario (also in df20):
15271

15272

The visual object is confusing, especially when selecting the fields:

15273




The field size is being registered in a different way to what was used with the embedded.


That's fine by me, if this is what was expected since since the Dataflex x SQL migration, everything works fine.


Now I need to create a new base.


Using DBBuilder for the first time in DF20.1, I chose to create the embedded database (this seems to be a more dynamic way of building).
I started typing the fields and after selecting the first relationship the program aborted without issuing any error.
The filelist had been updated, but all data was lost.
Delete the filelist information and repeat the process.
I was saving the screen step by step, for evidence of the abortion, which did not happen.
So life goes on...

15274
When I got to the point of creating another overlap relationship, I can't since the selection combo must be coming across, with the difference in the size of the overlap field, which in the embedded bank: 4 bytes instead of 18.

15275
It was no use deselecting the object:

15276

(*) I decided to delete the embedded base and create it in SQL, and the size of the overlap field occupies another position: 5 bytes.

15277

I think, that something needs to be adjusted or is it time to abandon the overlap?

Regards,

Edgard Luis Paneque

(*) Modification, including testing of creation in SQL.

Stephen W. Meeley
21-Mar-2022, 04:15 PM
Edgard,

We have been strongly advising against using overlaps since multi-segment relationships was introduced in Visual DataFlex 11.0 (that was in 2005).

The only time I'd consider keeping them (and I'd never actually create new ones) is if I have to continue to use a version of the product that didn't support them (VDF 10.x and earlier or 3.2 character mode) and that forced me to (and even then I'd never touch them).

In all other cases they should be replaced with the techniques defined in the doc (mostly just using multi-segment relationships).

So yes, abandon the use of overlaps!

Edgard
22-Mar-2022, 04:20 AM
OK Stephen.


I really don't need to use it on versions older than DF20.


When I migrated to SQL (DF19 to DF20) , I tried to remove this overlap, (using dbBuilder), but strangely the record relationship search didn't work and I ended up going back in time.


I'll try to do that again in DF20.1 and if it's successful (I hope so), it's fine, otherwise I'll bother you a little more.

Regards,

Edgard

Edgard
22-Mar-2022, 05:19 AM
Unfortunately dbBuilder aborts when I try to change.


Steps:


1) Studio closed and open.
2) Calling the dbBuilder, and opening the bases involved (exclusive mode).
3) Current structure:
15279
4) Modified structure, which I preserved and changed the name: Key -> Field_Free, Overlap->Ascii, size from 18 to 1 (This to preserve the same number of fields in the file)
15278
5) I clicked on Relationships and everything was ok.

6) I clicked on Index and the system recognized the index fields, I clicked on the Toogling button, which requests the recording:
15280
7) I activated the .DEF creation option.
15281
8) Bang! abort ! . . . Opens the frame that is modifying the table and closes without warning. (I tried to capture the screen, but the operation is fast).


I can repeat the same cycle, and the result is always the same: dbBuilder Abort !

raveens
23-Mar-2022, 06:07 PM
Hi Stephen

I beg to differ - overlaps still has its usage in DF19.x and DF20.x

Reason: Diamond relationships

When we migrated to DF19.1 - the first thing we wanted to do was to get rid of all the overlaps and use the multi-segment relationship technique (yayy!), but soon found issues with diamond relationships. At the end of project, we got rid of most OVERLAP fields but only retained a handful to govern diamond relationships, as DF does not support relationships to multiple parents from a single child field.

Let me give you a real world scenario….



An Invoice is related to an Account (ACC) with is a related to a Customer

Invoice.Cust#, Invoice.Acc# –n:1-> ACC.Cust#, ACC.Acc#
ACC.Cust# –n:1-> Customer.Cust#
ACC.Type –n:1-> AccountType.Id


1 Customer can have multiple Accounts.
1 Account can have multiple Invoices.
Now for the real world scenario….

An invoice can have a NULL Account (ACC) Parent and therefore a NULL Customer, when you’re creating the invoice you might not know the customer's details (yet) – imagine buying a TV and and salesperson wants ALL your details before even being able to produce a quote or even talk about prices and options !? You would walk out wouldn’t you ? Or you’re on a website and you can’t add items to a cart before you create a login ?
All the items for the sale is first entered against a NULL ACC and therefore a NULL Customer, once the deal is OK and customer is happy with the prices and whatnots – the sale is finalised or on a web you want to ‘checkout’ the cart.
At this point, the salesperson gets the customer’s details…he/she could be an existing customer, so for this work in the DataFlex VIEW level the invoice needs a DDRELATE from Invoice.Cust# to Customer.Cust# together with the above file based relationships.
Even if he/she is a existing customer, the salesperson may have to create a new Account (ACC) for the customer for this invoice, so therefore ACC needs to have a Allow_Foreign_New_Save_State=True and the appropriate AccountType selected (Cash, Business, Hire Purchase, etc.)
Or, select an existing account, by first selecting the customer.


Without OVERLAPS this scenario will not work in DataFlex, why !?

DF will complain that Invoice.Cust# is already linked to Acc.Cust#, and it can’t be also linked to the Customer.
The only way around this is to create an OVERLAP field Invoice.ACCKEY which is (CUST#, ACC#), therefore allowing for this ‘diamond relationship’ to work

Invoice.ACCKEY (Cust#, Acc#) –n:1-> ACC.ACCKEY (Cust#, Acc#)
Invoice.Cust# –n:1-> Customer.Cust#
ACC.Cust# –n:1-> Customer.Cust#
ACC.Type –n:1-> AccountType.Id





Although saying that, one has to be very careful with OVERLAPS
1. Should only be used for relationships, never ever accessed directly.
2. Even if you're priming the file buffer, always use the 'Actual' fields, never prime the overlap field.

Clive Richmond
23-Mar-2022, 09:09 PM
+1

I reported a similar issue (https://support.dataaccess.com/Forums/showthread.php?63831-Database-Builder-Overlap-Column-Selection-Dialog&highlight=overlap) described by Edgard back in 2019. Two releases later and it's still not fixed.:confused:

We refactored many of our overlap columns when we made our source code 'sql ready'. Things we changed included:



replacing any physical relationship from an overlap column to relate via the individual columns,
replaced any overlap columns in index segments with their individual columns, and
refactored any underlap column usage.

Vincent Oorsprong
24-Mar-2022, 03:22 AM
Clive,

I cannot find a support or JIRA ticket about this. Please start a HDE ticket with a sample workspace that can be compiled and chased and we look into that from there.

Edgard
25-Mar-2022, 09:15 AM
Thanks to Ravens and Clive for knowing I'm not alone on the boat.


In a way I can live without the overlap.


I performed the test on the embedded (my application works, using db Dataflex or SQL or mixed form) but I reinforce that dbBuilder contains a bug not being able to remove the overlap of the SQL base, which was converted under such condition.

I know I can do this via SQL command and this will require manually tweaking (.FD, .Tag, .Int, .cch and ???), so I would at least appreciate that this process can be performed by dbBuilder.


Anyway as a plan "B", if anyone has an example of this command, and what is involved in manual changes, thanks because apparently this may not be fixed in RC3 or another official release.

Or "C" plan, in which DAW can guide me on how to do it by dbBuilder.

Regards,

Edgard Luis Paneque

Clive Richmond
27-Mar-2022, 08:52 PM
I cannot find a support or JIRA ticket about this. Please start a HDE ticket with a sample workspace that can be compiled and chased and we look into that from there.

There you go!

15299

Edgard
28-Mar-2022, 11:23 AM
The problem of visually overlapping objects, while inconvenient, is the least of the problems.


The biggest one ends up being, that even using Studio's table edit and when trying to remove an overlap field from a SQL base, an abortion occurs without warning!


15302

Simple steps:


1) Change the overlap->ascii field
2) Tab
3) Save

15303


Note, that during the screenshot, it was possible to see, that the value that was 18, turned into zero.


In another capture I had changed the value to 1 where it was probably passed to zero.

15304


In both cases, abort occurs.

Regards,

Edgard

Vincent Oorsprong
29-Mar-2022, 02:24 AM
Edgard,

What do you mean with an SQL Base? Is this that you already converted the table to an SQL database table? And if so what database? What driver?

Then; I don't think you should change an overlap type column to an ASCII (or other) type column. Overlap is a number of bytes from a start offset. I think you should add a column (not insert) then after restructure copy data from the original overlap column to the newly added column and finally remove the overlap column.

Edgard
29-Mar-2022, 05:50 AM
Big Vincent !


Surely you provided the way of the stones.


It's a longer loop, but at least it worked, that is, I performed the steps:

15310

15311

15312


Answering your questions:


Yes, about 1 year ago I converted the embedded dataflex base, to SQL:
15313


Migration and everything works great, even with the overlap field.


As the need arose to include a base and following Stephen recommendation, I wanted to get rid of the overlap, keeping the position of the other fields (avoiding major problems).


As the use of dbBuilder over the embedded in the overlap field, it is not necessary to go around the stones, commenting this in the help, can help the elderly. . .
(must be few: overlap field in SQL base, converted and legacy of an embedded) . . .
(if I ask, how many have a license, to park in an elderly spot, I will be +1 soon :rolleyes:).

Regards,

Edgard Luis Paneque