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
2 Likes
11

The MSSQL driver is still an ODBC driver, and I guess the driver has to check whether you are using a vintage version of MSSQL.

So far as I know there has never been any good information on exactly what the ODBC driver asks the server to determine its capabilities, so we don’t really know what has changed. If you think it is a bug it will be a difficult one to submit because behaviour will depend on the back end (and possibly how it is set up), and not just on the Clarion side.

Like Sandro says, you can override what the driver thinks using /JOINTYPE

12

While the version of SQL Server will come into play for some functionality, the JOINTYPE is an ODBC driver version thing. And these days i would imagine there are very few ODBC clients that dont support ODBC3.

13

True, But I think Clarion drivers assume V1 unless told otherwise.
This multi-join problem has been around for a very long time

14

Hi Alex,

It is always worth it to make sure and set the PROP:ALIAS for the tables. I use the prefix, like:

MyFile {PROP:Alias} = 'MYF'

Then there is never any ambiguity about what the alias should be in your SQL statements. I created a simple global template for this back in 2014.

3 Likes
15

I agree. Our tests show that the MSSQL Driver fails any time there’s more than two joined tables. Luckily, you can use the C11.1 MSSQL driver and everything runs fine.

1 Like