FILL-WHERE-STRING questions

ge1971 wrote:
Having some problems using FILL-WHERE-STRING with data sources; can't seem to get this to work at all.
- must be something stupid I've overlooked, but I've searched and not found the answers so hoping someone might be able to help...
I have some code that populates a dataset.
It uses logical-physical field maps on the data source works perfectly well.
Originally I included the WHERE criteria in the QUERY-PREPARE statement and this was fine; but I started looking at the FILL-WHERE-STRING, hoping it would address some issues, but so far I've not been able to get it to do anything - my dataset is being filled with all records, regardless of the FILL-WHERE-STRING.
e.g.
hQuery:QUERY-PREPARE('FOR EACH ' + cTable + ' ' + cWhere) 
hData:FILL().
- works fine.  The cirteria is used and the mapped fields are populated.
hQuery:QUERY-PREPARE('FOR EACH ' + cTable).
hSource:FILL-WHERE-STRING = cWhere.
hData:FILL().
- all records populated during the fill, the cWhere criteria are ignored.
I've tried variations on this, like:
hSource:FILL-WHERE-STRING = 'WHERE Account BEGINS "A"'. (using logical field name)
hSource:FILL-WHERE-STRING = 'WHERE acc-code BEGINS "A"' (using physical DB field name)
..even hSource:FILL-WHERE-STRING = 'complete garbage'
...same results every time, all data filled, no criteria applied.
*) I'm obviously missing something. I've not tried using FILL-WHERE-STRING before, could anyone tell me what (if necessary I can post the full code, but hopefully thats not necessary - everything works fine with the criteria in the QUERY-PREPARE, I just can't it to work woth FILL-WHERE-STRING.

Your data-source probably still has a non default query, as the FILL-WHERE-STRING is ignored in that case.

The second example should work and the last example should give an error. (The first example probably gives no error, but returns no data).

*) Can you use FILL-WHERE-STRING when your logical and physical field names differ (mapped in the data source) ? 
If so, do you use the physical or logical names ?

You use physical names, except if you join to the parent in a child relation query. (this is typically done automatically).

The initial reason for setting out down this path was to remove physical field names from areas of the system they shouldn't be used.
I have found some documented examples of this issue being resolved by processing the cWhere and translating logical to physical fields by parsing the data sources field-map attribute.  But these were old examples and that solution seems rather unsatisfactory; so I was hoping to resolve it by specifing logical names in the data source's  FILL-WHERE-STRING  - is this possible or is manual translation using the field-map string the only way to achieve this ?

The  fill-where-string is not any different than a regular query in this aspect. It is mainly providing an easy way to add an expression for simple cases. (The fill-where-string does not work in more complex cases where the data-source need to access multiple tables.)

You will have to do some form of manual transformation to make a logical (temp-table) query into a physical query.

You will have to do some form of manual transformation to make a logical (temp-table) query into a physical query.

I'm using Harvard's QueryString and DsQueryString classes as a base for this purpose. These classes have been released as part of the sample code/whitepapers for the DataView SmartObject. They are cryptic, but do a great job

Thank you very much for the responses, I think they clear up the problem.

It's a great shame that there isn't a native method of using logical names, particularly as the information is available and has already been setup in the data-source maps.

I'm just doing a little prototyping here and there; with a view to making some proposals for modernizing an ADM-based application.

The development team is small, but long-established and I would anticipate that the step of separating logical and physical names by creating data maps would be met with a great deal of antipathy - we would need in the region of 400 business entities and everyone is comfortable working with 'acc-addr[6]'; rather than 'CustomerCountry'; it's what most people have worked with for many years.

I suspect that having to introduce an additional step to 'fiddle' each query is likely to be viewed as a step too far; and increase the likelihood of any potential OERA development making use of 'DEFINE ... LIKE ...' code in the majority of places.

This would of course 'break' several paradigms and lead to many potential problems in the future; but there isn't a great deal of momentum towards adopting OO/OERA development at the moment; and this is the sort of thing that's not going to help!

Anyway, thanks again for the replies; I'll plow on and see where it takes me.

I suspect that having to introduce an additional step to 'fiddle' each query is likely to be viewed as a step too far; and increase the likelihood of any potential OERA development making use of 'DEFINE ... LIKE ...' code in the majority of places. 

The classes from Harvard I've mentioned in my earlier post here are capable of translating queries based on a field and table name mapping as well as joined data-source queries.

Thanks Mike - I'll check them out.

Thanks Mike - I'll check them out.

I don't have the link to the download at hand, but I'm sure Harvard can help (as soon as Bedford awakes).

I've worked around it for the time-being by writing my own (very basic) 'Unmap' method.

It's by no-means certain that the company will even head in this direction; and the prototyping is at a very early stage so that should be OK for now.

But I've added code comments about those classes and a link back here so I know of their existence; and can follow them up if things go any further.

mikefe wrote:
Thanks Mike - I'll check them out.
I don't have the link to the download at hand, but I'm sure Harvard can help (as soon as Bedford awakes).

I provide demo code to illustrate usage of a querymapper/builder in any OERA like code I write. (I also typically throw in a set of collections).  I believe these are essential components in any ABL framework.

http://communities.progress.com/pcom/docs/DOC-36538

http://communities.progress.com/pcom/docs/DOC-102833

I believe the latter has some more additional constructors to support general query building with no mapping, but the first one has been used in real applications.

As pointed out they are very cryptic (the QueryString class is the first OOABL code I ever wrote in 2006). I do not have the time to describe the details now, so you will have to dig in the source. The QueryString is the actual query builder. You implement IQueryMap to provide the BaseQuery and mapping.

Keep in mind that the query mapping is only one part of this. The part that I have found most useful is the ability to extract a query for some (not all) tables in any order. I believe this is a must for any DataAccess layer.  This allows me to optimize the query across multiple data-source tables (assuming I have a properly implemented data source object that can add and remove tables and change the order of the tables). If you can extract the query for one table then you can test which of the tables has the best index usage and then use this information to build the full query in the optimal order.

Another important part of this is to ensure that the query can be inserted without compromising the BaseQuery, which is a data-source query that kind off defines the DataAccess layer.

There will be refined version of this in the next AutoEdge sample (which will be made public soon I've heard). This is a complete rewrite with better APIs, but it does not have a query parser as the assumption is that you use it to also build the query and thus never need to parse it.

Thanks again, that's all very useful information and a greap help