Weird SQL for initial foundset on one table

Classic Servoy
1

Hi all,

Running Servoy v4.1.4 with Sybase v10. I have trouble showing data from a line items table. I thought at first that it was a problem with my TableFilterParam on fk_tenant_id and fk_market_id, but that is not the case, because when I check the box to use a separate foundset on my line items forms, the data is being shown.

Using databasManager.getSQL, I get the following result:

"select line_item_id from line_items where 1 = 2 and fk_tenant_id = ? and fk_market_id = ? order by line_item_id asc"

It is completely normal that none of my forms based on the line items foundset would not show anything, since 1 will never equal 2 (even if some have tried to prove otherwise - Mathematical fallacy - Wikipedia). I have searched in my workspace for this impossible ‘where’ condition, but I am unable to find a location for this part of the statement. There are no form methods either. Is there a way to pinpoint where this SQL gets generated by Servoy so I can modify the parameters somehow and eliminate the offending part of the SQL statement?

Thanks in advance,

Ben

2

I have seen this condition before, there was also a sample to illustrate it ( it was quite complex with multiple level nested tab panels). As far as I remember there was a flaw in the logic of the application that led to generation of this weird condition. If you are sure it is a bug please add a case in our system (with a sample). Also maybe try another approach ?

3

I thought of that also - I looked at the relations to and from the line items table and at the tabpanels used to show the records, but I can’t see anything out of the ordinary. Which brings me back to my initial question: What does Servoy use to generate the SQL statement for the initial foundset on a particular table? Where/when does this happen exactly? Do we have any control over this (aside from table filters)?

Thanks for looking into this, Laurian.

Ben

4

LOGIsoft:
I thought of that also - I looked at the relations to and from the line items table and at the tabpanels used to show the records, but I can’t see anything out of the ordinary. Which brings me back to my initial question: What does Servoy use to generate the SQL statement for the initial foundset on a particular table? Where/when does this happen exactly? Do we have any control over this (aside from table filters)?

Thanks for looking into this, Laurian.

Ben

I guess you could use loadRecords method to specfiy our own query. For related foundset, we use the conditions defined in the relation, of course.

5

Ben,

Servoy sets the condition ‘where 1=2’ to an empty foundset to make sure no data is returned when a query would be done.

A foundset can be made empty using foundset.clear() or is made empty when created via databasemanager.getFoundSet().
A call to load[All]Records() should replace that condition.

Rob