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.
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)?
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
join
(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
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.
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)
CODE
SELF.Open()
SELF.View{PROP:Sql}='... WHERE rowsomething > ' & Locate ! Maybe?
IF ERRORCODE()
SELF.Primary.Me.Throw(Msg:ViewOpenFailed)
END
RETURN
PARENT.Reset(Locate)
(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 !
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.
Douglas,
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.
