I’m using SQL Server 2019, and Clarion 11.1, but I’ve had this issue with earlier versinos of both.
When I look at my file in the dictionary, there is a “Driver Options:” field, with an “expand” button that launches a window containing the “MS SQL Driver String Builder”.
One of the options is “Extra WHERE Clause”, with the tooltip hint “Anything entered here will be added to every WHERE clause for every SELECT statement generated for this table” and I want to use this to filter data coming back from the table.
My dict fieldname is “Type”, NAME “AssessmentType”; I’ve tried adding AssessmentType=‘GY’, which works in SQL Server Managmeent Studio:
AssessmentType=‘GY’ - which adds /WHERE=‘AssessmentType=’‘GY’’’ to the driver string
Generated made ran, and a browse on this file returned all records ie The additional “where” Was ignored. I ran Trace.exe, and the “Select” Statement sent to the server does not contain this extra “where”.
In case - unlikely! - I needed to use the Clarion name, not database name, I also tried
GWY:Type=‘GY’ (adds /WHERE=‘GWY:Type=’‘GY’’’), but again, no effect. Not even an error message saying that the server had choked with the invalid “where”.
What am I missing? Is it, perhaps, that this feature has never worked? If so, is there a way of embedding this logic in global embeds or similar?
ie get MS SQL server to log what is being sent to it so see what is coming from your clarion app?
At least this way you should be able to see if the SQL builder window is adding the extra where clause.
ie get MS SQL server to log what is being sent to it so see what is coming from your clarion app?
I’ve had Trace.exe capturing what the driver’s sending, and can see that the “Extra WHERE clause” isn’t being added to the Select statement that fills the browse.
ie I can see that Clarion isn’t sending it!
The Help does say that using that as a SEND “must be executed after the SET statement and before the NEXT or PREVIOUS statement.”
Agreed - and, the “help” is very sketchy about filling in that option for the driver!
I’d been about to set off preparing the views and then recalled that I’d never got that Extra WHERE clause feature to work, so thought, this time, that I’d ask!
What if there is a problem in Trace.exe? The SQL profiler is MS, much like using debugview is using a MS program instead of using the clarion debugger.
This is the line recorded in my trace log:
0664H(2) 15:59:44.800 Preparing Statement 04A17050H : SELECT A.GuidAssessment, A.DESIGNATOR, A.AssessmentDescription, A.AssessmentType, A.NOYR01BDS, A.NOYR02BDS, A.NOYR03BDS, A.NOYR04BDS, A.NOYR05BDS FROM dbo.Assessment A ORDER BY A.DESIGNATOR Time Taken:0.00 secs
Hmmm… THat gets me thinking: there’s no “Where” clause in that select.
I’ll put a filter on the browse, to ensure that there should be a “where” clause, check what happens, then re-introduce the Extra WHERE clause, while also having a go with the Profiler.
Is a memory address, you might be able to lookup in the debugger. That may also help you track back where the statement is being built.
Another thought, is can you find in the debugger where the SQL Builder String Where clause is stored in memory?
Its a pity the debugger doesnt have a search facility because it would make life so much quicker, because you could then put a unique string in the where clause, and then search for it in the debugger, but hey, I didnt design MS Windows or set the international standards.
This is getting well into “X-Y Problem” territory! Or, “When you’re up to your ears in alligators, it can be hard to remember that you originally set out to drain the swamp.” Clearly, this alleged out-of-the-box feature of Clarion does not work as straightforwardly as implied, if at all, and certainly does not do what I require.
I’ve created views, as Jane suggested, which, because the server can determine unambiguously which table/row is being updated, are updateable, so I get the behaviour that I want, and in an area that works as expected!
No I’m not talking about the Q Viewposition mod, I’m talking about I have found an instance where the clarion debugger is giving me the wrong info. Thats the point, Trace.exe could also be doing the same thing, hence why I suggested using the profiler.
I take the view that I’m not trying to debug stuff written by SV!
On the face of it, “extra WHERE clause” doesn’t do what it claims. If, in addition, Trace.exe ALSO doesn’t work as claimed - even thoguh every single time I’ve used it previously, “Select” and “Update” statemetns have been represented accurately - by somehow knowing to omit that bit of the “Where” clause that was added by “extra WHERE clause”, then that provides even more reason to avoid that feature and to use one of the many different options available to achiveve the same end.
I’m trying to apply a filter to a base table before it gets to my application layer to make it easier for me to present what, in the data structure, is one thing, but that the user thinks of as three different things. As in ways of skinning a cat, there is a number of possibilities. If this one - the “obvious” one - doesn’t work, then I’ll use a different, totally legitimate, mechanism instead.
Well when its stopping your own programs from working, you get forced down a road you dont want to go or time to give up programming and fight the criminals running the country. There is way to much surveillance in the world today, it makes me feel like a lab rat, so other people with an ego must love it.
I think that two threads might have got conflated, here, or does the “Q Viewposition mod” have a bearing on the “extra WHERE clause” not doing what its tooltip claims?
I’m talking about I have found an instance where the clarion debugger is giving me the wrong info. Thats the point, Trace.exe could also be doing the same thing, hence why I suggested using the profiler.
Yes, I appreciate that, but what I am saying in response is that I am not going to start delving into the possible incorrect functioning of an SV utility when my requirement - to present a filtered subset of rows from a table to the user - can be achieved, very easily and properly, on the server instead. I do not NEED to get this purported, but apparently-defective, feature in the dictionary to work to achieve my end; using updatable views is very nearly exactly the same, functionally, and could even turn out to be preferable, downstream, when it comes to reporting.
Well when its stopping your own programs from working
But it isn’t! I tried using the “extra WHERE clause” a long time ago and it didn’t work for me then, so I did something different. Hoping that, maybe, it’d now work, I tried it again, with the same outcome, and this time, I thought I’d ask if anyone else had had any joy with this feature that, it looks to me, has never done what is claimed. Tellingly, I found no posts here, or in the sv forums, so suspected that it was a feature that nobody had ever used, and that is looking like it’s the case. Not being a particularly good programmer, I went down the path that uses totally valid, supported, techniques familiar to me, that I know and trust, and that are likely to be understood immediately by my co-developer. I can achieve EXACTLY THE SAME outcome by using views on the single table in SQL Server. There’s even potentially a future benefit in doing so: when it comes to analytical reporting, I’ll be doing something like populating pivot-table reports in Excel from a data query, and that process could be much easier for the end-user if they don’t have to apply a filter to a generically-named table, rather than using a precisely-named view.
you get forced down a road you dont want to go or time to give up programming
That’s not the situation I’m in: the feature I tried was but one of a number of ways to achieve the desired outcome. No need to go delving into areas that I don’t understand, when I can go down a different path that’s simpler and better-maintained.
Everything is an area we dont understand until we explore it and then we understand it. Thats life.
This aint the matrix where we can download an app and have full knowledge at the press of a button but search engines are making that possible.
So I’m guessing you have done something to the sql views which filter server side instead?
You see I need some of this stuff in Clarion to work in order to achieve where I want to go, which is apps that work but also give me the feedback I need to make sure notone is the pulling the wool over my eyes. Thats our differences.
Oh, was that that the topic under discussion? I thought we were discussing whether anyone knew if – and if so, how - the ‘extra WHERE clause’ feature works. Turns out that nobody has ever used it (or won’t admit to it here!) so I saw little to be gained in flogging that particular equine corpse & said that I’d use a different, familiar, approach that’d take me to the outcome I want instead.
So I’m guessing you have done something to the sql views which filter server side instead?
No need to guess! I did say that that was what I was doing instead, and I think I might also have said that, because the server can identify unambiguously what row is to be updated/deleted, that the views are updateable.
But this particular deviation is not one that I wished to go down, since it seems to be largely about me being criticised for choosing the particular path to achieve the outcome I desire.
Talking of which, can you remember the wallpaper on your laptop at Kings Langley?
No. Is it relevant?
I might have missed that bit then, perhaps you werent specific enough.
I wrote “I can achieve EXACTLY THE SAME outcome by using views on the single table in SQL Server.”
I don’t understand why this topic has become what feels to me like a personal attack. Have I done something that’s caused you offense? Given your reference to Kings Langley, did I perhaps do something to offend you a decade or two ago?
How do I know its you and not a govt sock puppet? One of the reasons for not using SV’s unencrypted news groups is that I’ve noticed posts have been edited when I’ve download the ngs onto a new machine and those edited posts would have caused bugs, so I’m wondering why would someone want to introduce bugs into code? Maybe a spook or hacker perhaps?
