[WD16] Hexecute Queries - Setting up with multiple parameters

Startbeitrag von Al am 05.05.2012 00:17

Hello All

I am getting more into the concept of queries as opposed to file read loops and I would like to confirm that one of the methods that I am using is correct.

I can go to the query editor and create a simple query on a file with multiple parameters and then choose which parameter(s) I use at the time the query is run by HExecuteQuery() and remove the conditions I don't want by making their parameters Null ?

Invoice_Query has the following conditions and paramters
InvoiceCustomerId = pCustID
InvoiceDate = pDate
InvoiceFinPeriod = pFinPeriod

For a query on the customer ID I can run
pCustId = LCustID
pDate = null
pFinPeriod = null
HExecuteQuery(Invoice_Query)

For a query on the customer ID and the date I can run
pCustId = LCustID
pDate = LTransDatel
pFinPeriod = null
HExecuteQuery(Invoice_Query)

So I can setup a single query with multiple conditions and just use the most appropriate conditions each time to return the data I want.

I am actually doing this and it all seems to work, but after getting burned on the HexecuteQuery() behavour the other day I want to make sure.

Regards
Al

Antworten:

Hi Al,

I'm doing this all the time and it works fine.

Regards,
Piet.

von Piet van Zanten - am 05.05.2012 07:46
Hi Al,

You dnon't even need to explicitely set the unwanted params to null. Just not initialising them is enough.

hExecuteQuery will clear the parameters set anyway.

Cheers,

P.

von Peter Holemans - am 05.05.2012 08:01
Hello Piet and Peter

Thanks for the replies, with those votes of confidence, I shall keep going with that query method

Peter, I take the point about not setting the unwanted parameters but I think I will continue to set them as it makes my intententions clear when I look at the code later on and also I like the surety of knowing that I have explicitly cleared them.

Until I get an answer from PCSoft tech support regarding queries running in background mode, I have reservations about using the HNoRefresh option as I think that using HNoRefresh in an HreadSeekFirst() that runs immediately after HExecuteQuery() means it can then access an unfinished query.

Regards
Al

von Al - am 05.05.2012 08:39
Hi Al,

My recent thread was useful in solving my issue - Arie's reply worked great for me.
http://27130.foren.mysnip.de/read.php?27131,128550

I thought as you do... I like my code to be self-documenting, so I too was setting params to Null, but the query wasn't working as expected!

They are subtle differences, but I found that by issuing a HFreeQuery followed by just setting the params I knew that I needed and then issuing the HExecuteQuery gave me the results I was expecting...

von DarrenF - am 05.05.2012 10:57
Hello Darren

Thanks for the follow up on this thread, I will try as you suggest. There must be an underlying issue with setting a parameter to Null that does not happen with a HFreeQuery reset of the parameters.

One of the things that has kept me from using queries is that there is no trace mechanism.
You issue commands and data appears in a cursor with no way to see how it got there and there is this black box engine that decides the best way to extract the data and how it will interpret your commands and on top of that, it appears that there are differences in interpretation of the rules between different flavours of SQL

With a read loop you can track it every step of the way and see the data building and the interconnections. So yes it's slower but so much more trustworthy for me.

Regards
Al

von Al - am 05.05.2012 11:29
Al,

When you have the Query description open, take a look at the

Selection Condtions > Existing Condtions (AND/OR)

How you set the AND / OR condtions and whether you put them in parenthesis makes a difference in your output.

If these are not correct you can have some irregular results

Here is an example ... (1 through 11 are different selection conditions)

1 OR 2 OR ( 3 AND 4 ) OR 5 OR ( 6 AND 7 ) OR 8 OR 9 OR 10 OR 11

Then you do not have to put Null in any selection conditions you are not using

Dan

von DanM - am 06.05.2012 19:24
Hi Al,

Yes, that's right, there's definitely a difference in the initialisation of the params and how the HFreeQuery does things - as you can see from my thread, it's a very subtle difference in the code and one way worked and the other didn't!

Yes, it's a bit difficult to trace as well... especially when there are inconsistences in the processing of parameters, which is a bit of a shame, as any serious performant code needs to be (at some point) written in SQL. HReads... and HFilters... are okay but for large systems, I don't think you can "live" without SQL at some point?

von DarrenF - am 06.05.2012 20:03
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.