Use PROP:Sql to Fill ABC Browse

While I have used PROP:Sql to fill queues in code and add filter/order conditions, how does one use PROP:Sql to replace the select generated for a View/Queue by the BrowseClass?

My attempt so far has been to put something similar to the following in MyBrowse.Next.
MyBrowseView{PROP:Sql} = ‘select …’

This returns neither an errocode or fileerrorcode and the SQL statement from a log file matches what I have tested directly with Postgres. However, in my app it seems to go into some type of infinite loop within the depths of the BrowseClass.

As for the Clarion docs, they appear to avoid the necessary details.

Thanks you.


Where are you setting the PROP:Sql?
I think the actual Set() for a browse happens way down in the ViewManager.Reset method but can you also deal with the Reset(locate)?

BRWSomething.Reset PROCEDURE

BRWSomething.Reset PROCEDURE(BYTE Locate)
  SELF.View{PROP:Sql}='... WHERE rowsomething > ' & Locate ! Maybe?

I dunno if this will work though.

Generally I can get what I want in a browse through filter and order with SQL() but I can appreciate that this doesn’t cover everything!

Have you seen this in the help?

PROP:Name (SQL Properties)

All of the SQL Accelerator Drivers now support the following syntax:

view{'field_label',PROP:Name} = string

to change the field’s name in the generated SQL. This is normally a name that will be used for a field in a SELECT statement.

Depending on what you are trying to achieve perhaps that could be useful too?


Have not completely tested putting the SQL code in Reset. For my current situation, locate is not a problem, but it certainly would need to be solved for other situations.

I would agree that PROP:Name can be useful for SQL aggregate functions. However, the template implementation appears to me to be limited. Consider the following:

select p.col1, p.col2, sum(c.amount)
from parenttable p
join childtable c on c.parentid = p.parentid
where c.datecol >= myProcDateVar
group by p.col1, p.col2

This query returns 2 cols from p and 1 sum col from c is easy to set use PROP:Name & PROP:GroupBy.

However, if one needs to return MANY parent columns, the PROP:GroupBy string becomes very long and a pain to maintain if display & hot fields in the LIST change. In this situation, I believe a more easily maintained & faster query would use a subquery. How to best handle this in the browse template is the problem. Condsider

select p.col1, ..., p.colx, sub.sumamount)
from parenttable p
(select c.parentid, sum(c.amount) as sumamount
from childtable c
where c.datecol >= myProcDateVar
group by c.parentid) sub on sub.parentid = p.parentid
1 Like

I am curious to hear how you go!

Finally a bit of time to test. An interesting result, but I’m at a loss to explain.

Your suggested code (PROP:Sql and a RETURN prior to the parent call) consistently yields an unopened file error even when Open and UseFile are placed immediately before.

For that type of thing I’d suggest a serverside view and an appropriate table on the Clarion side. It’s way easier to maintain etc, and just works.

1 Like

Yes, I definitely agree with you. However, this situation loops back to the previous discussion in regard to using a PostgreSQL function returned table.

In the example above, the child table or subquery has a date condition that must be passed IF defined on the server.

I actually had a need to do just this today and I think I have a working example.
There are a few gotchas though!

My original code was just a guess and I missed a bit. This will work:

BRWSomething.Reset PROCEDURE(BYTE Locate)
  SELF.View{PROP:Sql}='... WHERE rowsomething > ' & Locate ! Maybe?


(Notice I added the SELF.Open() in there yeah?)

However, the template generated VIEW() declaration will not work. It seems that the template PROJECTs the fields into the view in whatever order it feels like. This is ok if you do a SET(SELF.VIEW) but it is totally NOT OK when you do a PROP:SQL. I guess because there is some connect between the filedriver and the view structure where it is able to bind the fields regardless of sequence, but when you do a PROP:SQL the order of the fields in the view structure must match the order of the fields in the SELECT statement. An additional catch is that the order of the fields in the view must match the order of the fields in the table definition too!

Once you get all that sorted it seems quite happy with the PROP:SQL in ViewManager.Reset !

1 Like

Hmmmmm. I am certainly perplexed. This sounds exactly like what I tested. No idea at the moment what the difference may be, but I will revisit my test code.

Another potential issue to note -
The browse template generates extended SQL code (GroupBy, Having) into the BrowseClass.Init method.
However, if one uses PROP:Alias to identify sql tables in PROP:GroupBy or PROP:Having, the property
will not be correctly applied if set in the Init method.

I don’t know if this will help with the alias problem you’re describing, but in the SQL systems I deal with I use a template to set the alias of every SQL table to the table’s prefix. I do this in the data dll.
This allows you to write sql filters, order by, etc. on Clarion Views and you always know the alias without having to determine the alias by the position of the table in the view structure.



I like the template idea. Can I assume the alias is set in FileManager.Init after the parent call?

95% of the time, I use an sql alias only on a filter. PROP:Alias is therefore easy enough to set in ApplyFilter just prior to the appropriate set filter calls.

I was surprised to notice that setting an Alias the BrowseClass.Init method did not work. I assume it is a thread & timing issue that somehow your data dll template avoids.

That is exactly where I have the code :slight_smile:
Here is the template snippet. It is part of a larger template in my setup.

#! ----------------------------------------------------------------------------------------------------------
#AT(%FileManagerCodeSection,,'Init','()'),PRIORITY(5500),WHERE(%FileDriver = 'MSSQL' AND ~%GlobalExternal )
Self.File{Prop:Alias} = '%FilePrefix'