Clarion 12 - Prop:Filter and SQL()

questions
1

After updating to Clarion 12. My browses suddenly showed no records.
I use {Prop:Filter} = ‘SQL(Emp_Number > 100)’
That works fine when only having one Join on the view. But when more than one Join is used something goes wrong.
I have tried ‘SQL(A.Emp_Nunber > 100)’. No change.
If I change the wiev to only have one Join everything works.

If I use normal filter {Prop:Filter} = ‘Emp:Number > 100’ and more than one Join, everything is ok again. Am I missing something or is it a bug in the new Clarion 12?

2

Use Trace.exe to examine the generated SQL.

3

There is a problem with some ODBC windows drivers and Joins. ODBC1 only allowed 1 JOIN, after that it gets handled client side. ODBC3 is better. You will see in the trace a seperate fetch if thats the case.

I’m pretty sure switching on some clarion driver options can mitigate that behaviour.

Try /NESTING = TRUE on all the tables in question

4

What SQL is your database?

5

I am using MSSQL 2022 Express

6

Just to explain what I think is Sean’s point…

If, for whatever reason, Clarion 12 decides, where Clarion 11 didn’t, that the backend cannot handle multiple joined tables, then a) you will see this in the trace – that clarion first asks the database for a set of top level records (based on only the top level table), and then asks for each of the subsidiary records in order, and b) Clarion might do something stupid with your filter. In the case where it submits the full query then everything would be good, but I don’t think Clarion is smart enough to look at your SQL filter and determine which of the queries it submits it should put that filter on. i.e. it knows to translate EMP:Number to A.Number in the query it sends, but it probably balks at trying to figure out what table in the view the Emp_number database column is in (but see below).

Secondly, are you sure your SQL() filter is right. I would expect it to be (Number > 100), unless you really have it set up that the column emp_number in the database is a field called Number in the EMP table.

7

Curious as to whether you’ve tried {PROP:SQLFilter} with its different syntax, instead of SQL()

8

Just to confirm, I am using MSSQL 22 with Clarion 10 and the filters are working OK, but I am using standard Clarion filtering…

BRW1.Setfilter(Local:FilterString)

or, in your case…

BRW1.Setfilter(‘Emp:Emp_Number > 100’)

9

Is this also true for the MS SQL database driver?
And is this change in behaviour supposed to be an improvement or a bug?

10

this happens with other SQL types as well.
The solution I found is :
inside the globals define the cString variable DriverOptions . Its name will be: Glo:DriveOptions
in every table specify the Driver Options as: !Glo:DriverOptions
Initialize this variable with the following string : /ISOLATIONLEVEL=1 /JOINTYPE=‘ODBC 3.0’ /NESTING=TRUE
the jointype is what does the trick,
You can initialize that variable just after the Glo:Owner variable

  • Recompile everything
1 Like