Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
19
Erster Beitrag:
vor 6 Jahren, 4 Monaten
Letzter Beitrag:
vor 6 Jahren, 3 Monaten
Beteiligte Autoren:
DarrenF, Piet van Zanten, DerekT , Arie, Arnaud B., Al

[WD16] How a Query works with the Analysis

Startbeitrag von DarrenF am 18.04.2012 11:48

Hi guys,

I'm not new to Queries & SQL per say, but I am new to Queries & SQL in WD and I'm sure it's just a matter of it being explained to me in English... :confused: but...

Q1 - I've modelled several files as 1 to many, i.e. (0,n) and (0,1). Now, I was under the impression that the 0 (zero) bit made it an optional relationship?

Q2 - I've modelled it so that for example; an Operator is related to (can have many) Stock_History, and also that an Operator is related to (can have many) PO_Header (PO = Purchase Order). Now, when I write my query using the Query Editor (not written manually), and only reference the Operator and Stock_History fields, why does the Query Editor want to include Operator, Stock_History AND PO_Header, even though I've not referenced the PO_Header file anywhere in my Query within the Query Editor? :confused:

Is it something I'm doing wrong, a problem with my Analysis, just something I have to work-around manually or an aspect of all of these??

I hope this makes sense...?

Antworten:

Hi Darren,

This sometimes happens, to fix it you can edit the joins manually.
(Right click and choose edit the joins)

Regards,
Piet

von Piet van Zanten - am 18.04.2012 12:32
Hi Piet,

Looks like a little bugget to me?! :eek:

Yes, I tried editing the joins... I'll try again [[5]]

Another question - when I was "playing" with the query and edited it manually to see what was happening in different scenarios (I'm aware I can't use the editor any longer), but how do I run the query with the Go button? I started getting an error saying I need a connector blah, blah - what?!

von DarrenF - am 18.04.2012 12:54
Darren
This happens if you have set up links in the analysis itself.

Seems that the query generator is a bit over zealous and assumes you require all previously defined links.
Also you may get a window displayed showing all of the possible 'linking' options.

Personally I do not set links in the analysis for this reason

von DerekT - am 18.04.2012 13:35
Hi Derek,

Thanks for the info...

Sorry - I'm a bit "old school" in this area - by links, do you mean relationships between files (tables) or some other kind of link?

I haven't knowingly defined any other types of links... or have I? :confused:

Cheers...

von DarrenF - am 18.04.2012 14:14
Hi Darren,

If you remove any reference to the PO_Header from the join, Windev will ask you if you want to remove the file from the query.
Also, you need to define a connection in the project initialization code to be able to test the query. The project initialization code will be executed before each and every test you do (also to test a single window)

Regards,
Piet

von Piet van Zanten - am 18.04.2012 16:32
Hi Piet,

Sorry for being a bit stupid Piet, I don't understand what you mean by define a connection :confused:

Do you mean HOpenConnection or HChangeConnection? If so, I'm not using client/server - I'm using HF classic, or am I on the wrong "track" alltogether?

My WIndows run in Go/Test mode no problem, so I'm not sure what you mean by this.

Is it documented in the Help anywhere...?

von DarrenF - am 18.04.2012 17:55
Hi Darren,

Sorry, I was assuming you were using HFCS. In that case you need hOpenConnection.
For HFclassic, in most cases you need to use hChangeDir in the Project Initialization code to point to the right location of the files (unless you're using the exe directory for the data, which is not good practice)
If you have your data definition outside the project init code, you can use IF OnTestMode() THEN
However, if you can test a single window with data, a query should work in testmode without any adaptations too.

Regards,
Piet

von Piet van Zanten - am 18.04.2012 18:22
Darren
Yes, sorry I am referring to relationships (Links in WD speak)

von DerekT - am 18.04.2012 19:27
Hi Darren,

Discard my posts about the connection stuff. After reading your post more carefully, I could see that this is not the issue.
About editing the SQL code:
The error message when testing is not always clear. Perhaps there's an typo in your code that Windev interprets as a file, check if you see any red underlining that shows an error tooltip.
BTW: although Windev warns that the graphical editor will not work anymore, I found that sofar all code-based queries could be reverse engineered to a graphical query.

Reghards,
Piet

von Piet van Zanten - am 19.04.2012 08:40
Piet, After reading your posts, I must admit that the "tail-spin" I was in did start to get faster!:confused: ...so thanks for taking the time to slow it down and I'm now managing to pulling myself out of it... slowly ;)

Yes, it may be a typo or some obvious error in the SQL - I'll take a closer look... I'll also try reverse engineering bit as well. [[5]]

Derek, your post certainly raises a few interesting points about links between files in the Analysis and maybe taking the "path of least resistence" - How many people actually bother implementing the links between files, especially if they seem to cause more work?

That in turn raises more questions; If links aren't implemented, do you use SQL at all? ...and if you do, how do you write it - in the Query Editor and add the links there OR do you write the code by hand?

Soooo many questions :eek:

von DarrenF - am 19.04.2012 09:29
Darren
I use queries extensively with all but the most complex created using the generator.
I do not define any links in the analysis and accept the overhead of defining the ones I want when generating the query.

The questions I would ask.......

Do you use RAD.
RAD uses the defined links to determine the relationships of the windows created
Do you require cascading deletions.
Yes requires a link - I personally would not use this option as I like to be in control
Do you require cardinality (limit the number of child records).
Yes requires a link - my preference is to handle by code (control thing again)
Do you check in your code for errors when writing records.
If yes then most of what the links offer can be handled by code, If no then you really should start
Do you use transactions when writing to multiple tables.
If yes then data integrity will be maintained if your error checking is in place

IMHO if you return NO, NO,NO, YES and YES then links are not required.

Only a personal view of course but being a bit of a dinosaur I tend to stick with what I know

von DerekT - am 19.04.2012 12:04
Hello Darren

I am old school like Derek and I have no links in the analysis of any of my projects. I simply don't trust them to work every time and the integrity checking is an overhead that slows things down a little. I run all my deletes through a central process and the thought of allowing cascading deletes by the data engine is quite unnerving.

Links can be useful in the design phase as the integrity checker can pick up errors in code but then I remove them all and every project has the following line in the top of the project code, in case I missed removing any links

HSetIntegrity("*","*", hCardinality+hOnUpdate+hOnDelete,False)


Regards
Al

von Al - am 19.04.2012 13:02
Derek,

The answers to your checklist are:

Do you use RAD.
RAD uses the defined links to determine the relationships of the windows created
NO

Do you require cascading deletions.
Yes requires a link - I personally would not use this option as I like to be in control
YES - but only because I tried to embrace a "Brave New World" and I'm too far down the road to turn back!

Do you require cardinality (limit the number of child records).
Yes requires a link - my preference is to handle by code (control thing again)
YES - same as answer to "Do you require cascading deletions."

Do you check in your code for errors when writing records.
If yes then most of what the links offer can be handled by code, If no then you really should start
NO - Only because of the answer to "Do you require cascading deletions."

Do you use transactions when writing to multiple tables.
If yes then data integrity will be maintained if your error checking is in place
NO

The question for me now is - how long is this d@mn road and do I have time to turn back?!! :eek:

von DarrenF - am 19.04.2012 15:42
Hi Al,

I only trusted the Analysis becuase it's very similar to the Data Modeller I use in my day job, and I now know the difference - the one in my day job works!!!

It doesn't start adding spurious links to files/tables that aren't mentioned in the definition.

...and as you say, does the DB integrity actually work? if a (seemingly) simple thing like adding references to unreferenced tables doesn't work?

I feel a bit like I'm cornered - (H)Filters are slow and there's a bigger learning/development curve than I could have imagined - I need a drink... and a strong one! :drink:

von DarrenF - am 19.04.2012 15:49
Hi,

I don't really understand what is your problem.
I'm ten years experienced with WD and HF, and I'm really surprised to read "links don't have to be created in the Analysis"

Links (referential integrity) are absolutely necessary in a database design.

How can you be sure of the integrity of your data if you don't have integrity check ?? :eek:
It's crazy...

Referentiel integrity actually work well in HF. There are other bugs (e.g. on outer joins) but not on referentiel integrity

The only bug I ever ran into about referential integrity, was when a trigger delete a record : the cascade delete does'nt work in this case (at least with WD14, I didn't check it again on next versions).

von Arnaud B. - am 19.04.2012 22:24
Arnaud,

I absolutely agree and I don't have a problem with what you are saying - all these features are there for a reason, but please read on...

I really, really, really (really), want to use it WD, the Anaysis with all it "bells and whistles", but it's causing me problems, because I don't think it fully works!

Please could you give me a little help and re-assurance that the Query Editor can do what I'm trying to do? Please could you tell me how you use the Analysis and Query Editor to it's full extent?

I have no confidence that it can even create a Query based on my selected files and fields - it seems to select random files! That certainly doesn't give me much confidence.

I'm wasting a lot of time trying to get WD do what I think should be lightniing fast development... I'm a little disappouinted to say the least.

von DarrenF - am 19.04.2012 22:46
Darren,

based on your links between files, WD is trying to figure out the best joins for your query. Of course it can only do that if you design your database (links) well. Nice feature in a lot of cases.
On the other hand: only you know what kind of data you are looking for. Will it return just 1 record of 50000 records. You probably know beforehand. WD can not. So there are always situations when WD comes up with the wrong query-plan. You need to overrule WD at this point.

But indeed: sometimes the query editor keeps "searching for joins", even if you use "edit the links/joins" to do it manually.

atm I don't know when this happens. Sometimes I can perfectly change the joins by hand. Sometimes WD ignores my changes when pressing the green OK button and creates them again. Very annoying.

Anyone know if this "feature" can be turned off?

The only workaround I know is to switch to the SQL code, change the joins there and "reverse engineer" it (to get a nice visual presetation).

von Arie - am 20.04.2012 12:33
Hi Arie et al,

I appologies if I'm sounding a little frustrated... but I am! :(

I thought I'd been very careful... but I go back to my 1st post, why oh why if I have 3 files modelled with optional links and only ever reference 2 of the files, does it insist on creating the WRONG query... pretty much every time - sometimes with all 3 files and sometimes with 2 files with 1 being the WRONG one? It's very rare for a file with 3 or more links to it, to create the CORRECT query! :rolleyes:

I can see why people HAVE taken the path of least resistence and unfortunately taken the route that Arnaud seems to so despise (don't get me wrong - he's correct), but when your backed into a corner... what else can you do?

Arnaud, I'd love you to write a real Query Editor user guide based on your 10 years of expereince? [[5]] :spos:

von DarrenF - am 20.04.2012 12:54
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.