Data Source Best Practice

Startbeitrag von André Labuschagné am 03.02.2018 12:53

Hi All

I think I may have found what may be an issue with data sources. The issue is most likely rather with me than with WX. I am needing to get my head into this black box.

I have a data source [say test] declared. I then step through an array and for each element in the array I am calling say:

HExecuteSQLQuery(test, "select * from customer where customer code = whatever")

Whatever is a field in the array. I am getting intermittent results where the data source is not being cleared and filled with the new data.

I did a bit of reading and came across:


What does this actually do? Does it dispose of the data in test? Is it a good idea to invoke this after dealing with the data source and before getting ready to execute the next HExecuteSQLQuery?

I hope that makes sense.


Hi André

YES? you need to do a hcanceldeclaration after each query, or the result will stay in memory till the program ends, which is a good way of causing a memory leak

However, this is necessary for QUERIES... it has nothing to do with data sources...

And that is because you can execute queries without ever declaring a data source and you can use data sources that are files, not queries...

I'm not sure I made things really clearer here, but as you are mixing different stuff under the data source name, it's already a mess :-)

Best regards

von Fabrice Harari - am 03.02.2018 14:26
Hi Fabrice

Thanks a ton for that. Then I think I may just have found my problem. Consider the code below:

MyDataSource is Data Source

sSQLStatement is string

FOR EACH Rec OF arrCommunicationStructure

MyTable.PhoneNumber = ""
sSQLStatement = "select * from telephonetable where customernumber = " + Charact(39) + ...
StringFormat(Rec.CustomerNumber,ccIgnoreSpace) + Charact(39) + " ;"
IF HExecuteSQLQuery(MyDataSource,MyConnection,hQueryWithoutCorrection,sSQLStatement) = False THEN
ReturnValue = Error(ErrorInfo())

IF HNbRec(MyDataSource) > 0 THEN

FOR EACH MyDataSource

IF StringFormat(MyDataSource.TelTypeCode,ccIgnoreSpace) = "P" THEN
MyTable.PhoneNumber = StringFormat(MyDataSource.TelCode,ccIgnoreSpace) + StringFormat(MyDataSource.Phone,ccIgnoreSpace)


IF StringFormat(MyTable.PhoneNumber) ""



I was getting weird results with this logic without the HCancelDeclration call. Sometimes the HExecuteSQLQuery would return the correct result and sometimes it would only collect the first result, never return an error and the same customers number would be returned for all customers. Does that make sense? So I am really trying to confirm that the HCancelDeclaration actually clears the data source or frees up the data source so that the next HExecuteSQLQuery call can function correctly and return the new data. Am I on track with this logic or way off the mark?


von André Labuschagné - am 03.02.2018 14:44
Cancelling the query result is always important as is reseting any variables that have been used to insure that no 'historic' data is lurking when the procedure is next run.
I do things slightly differently but I guess the result would be the same......
STR_Phone is structure
Phone is string
TelTypeCode is string
arrPhoneNum is array of STR_Phone

//Run your query
sSQLStatement = "select * from telephonetable where customernumber = " + Charact(39) + ...
StringFormat(Rec.CustomerNumber,ccIgnoreSpace) + Charact(39) + " ;"
IF HExecuteSQLQuery(MyDataSource,MyConnection,hQueryWithoutCorrection,sSQLStatement) = False THEN
ReturnValue = Error(ErrorInfo())
//If a result is returned - Loop thru the array
IF arrPhoneNum..Occurance > 0 THEN
IF StringFormat(TYPECODE.TelTypeCode,ccIgnoreSpace) = "P" THEN
//Do your thing here
//Free the query and reset the varaibles to their original values
VariableReset(STR_Phone )

Only thing to note here is that the name of the structure members have to be the same as the column names returned by the query.
Does not matter how many columns the query returns only those 'matched' will be added to the array.

I do have a question---
Why are you not filtering the query on the TelTypeCode 'P' value

von DerekT - am 03.02.2018 18:55
Hi DerekT

Cool. Never knew about HFreeQuery nor VariableReset. I am sure that this has been the issue all along.

The 'P' is really just an example. It is not restricted to one condition so there may be an array of possibilities that can be tested against.

Really hoping this is going to do the trick.

Thanks a ton for your help.


von André Labuschagné - am 03.02.2018 22:08
Thanks to Fabrice and DerekT the problem appears to be sorted. It turns out that HFreeQuery uses HCancelDeclaration.

Still testing but so far so good.


von André Labuschagné - am 05.02.2018 11:40
It has not worked. Still get intermittent behaviour.

Will have to take another route.


von Andre Labuschagne - am 08.02.2018 08:39
Is there a specific set of circumstances where your error occurs?

Do you run this consecutively without closing the window in which the call is made?

In your example you are only testing for the presence of content in the data source
This would of course be true if the query had failed to return data and HFreeQuery() had not cleared the previous result.

Try testing the content - HNbrRec - of the data source following HFreeQuery()
If this is always zero then the issue may be with the params that the query is using.

Obvious stuff I know but experience tells me that is is always the obvious that we tend to overlook.

von DerekT - am 08.02.2018 09:42
Hi DerekT

Specific in that the iteration calls this query for each record in this specific procedure.

It does run consecutively. There is no window. It is a procedure in a service.

I have not used HNbrRec after HFreeQuery() - good point. When I get time I will have a look at that.

For the moment I have just moved the query outside of the loop and include a larger data set that would cover the specifics of that data set filter inside the loop. That works as it is only called once when entering the procedure and then disposed of.

I cannot experiment too much as the procedure handles sms creation with instruction coming from one of our mobile apps. It is a mission critical app where failure to communicate via sms, push notification or email can result in death - literally. It is a Sports Clinic app for coaches. The problem has been tested with sms but the same logic is applied to the push notification and email procedures.


von André Labuschagné - am 08.02.2018 18:24
Hi André

as you seem to think that your problem comes from the data source, did you try to run your code without using one?

There is NO OBLIGATION to use a data source to run a query. Older syntaxes are still available and works very well.

Best regards

von Fabrice Harari - am 09.02.2018 11:30
Hi Fabrice

I am 100% convinced now that the problem is with the data source. I have moved the query outside of the iteration and then loop through the data source and the problem persists but it is intermittent. I can reproduce the problem at all. The problem is deep inside this black box. I am going to move the contents of the data source into an array and loop through that instead.


von André Labuschagné - am 09.02.2018 19:23
Hi André,

that is NOT what I was advising... Your do NOT need an array... just run the query WITHOUT declaring a data source...

That has worked for me without a hitch for many years.

Best regards

von Fabrice Harari - am 10.02.2018 12:59
Andre, let me take a stab at this. I am no expert, but I think I see where Fabrice is coming from.

I think you are mixing concepts of what you need to do to get and manage data. In our old Clarion days we would declare a QueueType and create a Queue. Then we would loop through some data file by key and fill the Queue, then parse the Queue plucking out data and putting it somewhere. Then we had to cancel and free the queue to reclaim memory. (Damn, I am really rusty at this Clarion, haven't used it in ages, hope I got that right...).

I think that Fabrice is saying you don't need no stinking Queue... or DataSource. Instead, just run the query. The query returns all the data and you can read, slice and dice from the query itself.

// Run the query
ResExecute = HExecuteQuery(test, "select * from customer where customer code = whatever";)
// Error while running the query?
IF ResExecute = True THEN
// Browse the result of the query
IF StringFormat(test.TelTypeCode,ccIgnoreSpace) = "P" THEN
MyTable.PhoneNumber = StringFormat(test.TelCode,ccIgnoreSpace) +
IF StringFormat(MyTable.PhoneNumber) ""
// Display the error message
Error("The query was not run.")

After you are done processing the Query, clear the memory. Then you can run another query.

Only declare a DS or MemoryTable or Structure or Class if you need to SAVE the data for further use.

Givin all that... I could be wrong.

von Art Bonds - am 10.02.2018 15:50
Also I have an issue with your code, or maybe I just don't understand what you are trying to do. In this part of the code:

IF HNbRec(MyDataSource) > 0 THEN
...FOR EACH MyDataSource
......IF StringFormat(MyDataSource.TelTypeCode,ccIgnoreSpace) = "P" THEN
............// your code to format phone number
......END // End If StringFormat
...END // End For Each MyDataSource

// To me this means you have processed all the records in the DataSource. Done with it. If so, what does the following code do???

...IF StringFormat(MyTable.PhoneNumber) ""
...END // End If StringFormat

// Shouldn't this code be inside the FOR EACH loop?

von Art Bonds - am 10.02.2018 16:00
I understand what both you and Fabrice are saying but.....

What is 'test' in your example?
My take on this has always been that it is indeed a 'data source' by any other name, just without the actual declaration.
The query has to store the result somewhere.

Of course I may be wrong, not for the first time, here and if so then I am sure one of you will enlighten me.

von DerekT - am 10.02.2018 16:33

Art - I missed not declaring a DS - but I thought that was implicit if not declared.

Fabrice - I did miss that but I do not see the difference as the variable storing the result set is surely a DS. What is the difference between explicitly declaring one or implicitly ending up with one. Is there a fundamental structural and runtime difference in behaviour between explicitly declaring a target for the result set or not? I have not tried that but it would be a new one to me.

DerekT - I am on the same page as you.

What I am doing now is declaring a data source, reading result into it, building an array from the data source and loop through the array for each record to find the specific phone number(s). First runs are working. Holding thumbs.


von André Labuschagné - am 10.02.2018 17:13
DerekT - "test" is the name Andre gave to his query. I would have called it QryPhones or some other such silly name.

Andre - Some smart dude with a bad haircut once said keep things as simple as possible, but no simpler. But I think he's dead now so it might not be applicable... Declaring a DS/array is yet another thing you have to keep track of and hope you don't forget or don't know something (like hFreeQuery, hCancelDeclaration, etc, etc). Why complicate things if there is no compelling reason to do so.

And how about the second post about where you are accumulating data from and when/where you are saving it? Not saying it is wrong, I just never have seen that construct before and it could be because that code snippet is out of the overall context.

von Art Bonds - am 10.02.2018 18:43

Difference between data source and no data source:

at the beginning, data sources did not exist in wlanguage...

So when we started using queries, we just used queries... Then they added data source that can be queries or files... So in my understanding, a data source is a layer ON TOP of a query or a file...

So there is an internal difference... Which one I don't know...

What I'm saying is: AS YOU ARE SURE that your problem is coming from a data source, DON'T USE ONE!!! Because the one and only thing I can tell you is that I have been using queries for YEARS, WITHOUT data source, and that I NEVER EVER had a problem similar to yours...

If you still have the problem, then it's not coming from a data source.

Best regards

von Fabrice Harari - am 11.02.2018 13:44

I always call hcanceldeclaration() before hexecutequery() . for HExecuteSQLQuery() no need.

this is because early days of windev if you don't call hcanceldeclaration() before hexecutequery() , you will get error if no error the content of query not refresh .

it became habit since then

von ccc2 - am 11.02.2018 17:16
ccc2 - thanks for that.

Well, this turned out to be a false alarm. But I have learnt a lot in the process. Hopefully others have too. Had no idea about hcanceldeclaration and the clearing query thing.

I traced it back to a buggy mobile app on iOS. It was sending the duplicates. All sorted now and working like a charm.


von André Labuschagné - am 13.02.2018 10:14
Zur Information: 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.