Hi Folks,
What’s the most appropriate/efficient/wisest method of adding parameters (@val) to a PROP:SQL command (MSSQL)?
Cheers,
Stu
1 Like
Do you mean like “Parameterizing in T-SQL with sp_executesql” as described here?
My understanding is that Clarion calls pretty much everything via sp_executesql already though you would want to trace that on the SQL server side to be sure for PROP:SQL.
If you are calling stored procedures then you can bind parameters on the clarion side for use in the CALL statement. I think they are parametized automatically but again, double check.
One thing that I often do is use a string class of some kind and to a replace. e.g.
Sql.Str('SELECT col1, col2 FROM myTable WHERE col42 = @myParameter')
Sql.Replace('@myParamter', Quote('the value'))
Temp{PROP:SQL} = Sql.Str()
You would of course want to refine it more than that and of course the Quote() function is less that ideal but hopefully this gives a starting point for people to rip apart my techniques and give you better options 
Hey Stu,
Check out the help topic titled Using Embedded SQL. It discuss BINDing a variable and using it as a parameter in a SQL statement.
I’ve only used it for OUT parameter or return parameters from a stored procedure, but from reading the help I don’t see anything saying it won’t work for input parameters.
Basically:
BIND('MyVar', MyVar)
{Prop:SQL} = 'select Field1 From Table where Field2 = &MyVar'
Give it a try and let us know the results.
Rick
Thanks Brahn! Looks great, will have a crack.
Thanks Rick, much appreciated! Will investigate this.
Please report back, I am curious to know if this Bind works. I thought is was only for CALL and NORESULTCALL.
Well. yesterday i was working with something similar and making the BIND to set the input parameter at least does not work for me.
BIND('LocValue',LocValue)
LocValue = 'some_value'
MYSQL_Table{PROP:SQL} = 'SELECT * FROM [dbo].[MYSQL_Table] WHERE [dbo].[MYSQL_Table].Field LIKE <39>%<39>+&LocValue+<39>%<39>'
With this code it works fine no need to BIND in this case
MYSQL_Table{PROP:SQL} = 'SELECT * FROM [dbo].[MYSQL_Table] WHERE [dbo].[MYSQL_Table].Field LIKE <39>%<39>+<39>' & LocLabLicense & '<39>+<39>%<39>'
Hope this helps
1 Like
Wow. Very frustrating. I’ve posted to the newsgroups (database drivers group) to see if there’s anyone there who can shed some light.
Basically.
- Using SQL Profiler was able to get a bit further into what PROP:SQL does.
- What profiler says PROP:SQL does, if you copy and paste it into a new query window in sql management studio and run it, gives back an error.
- I’ve tried a bunch of different things, using multiple PROP:SQL calls, using a single one, using with different files.
Trying to get PROP:SQL to allow for DECLARE and SET and then SELECT seems, at this point, not possible.
On a PROP:SQL Profiler says the following is being run:
declare @p1 int
set @p1=1073741897
declare @p2 int
set @p2=0
declare @p5 int
set @p5=16388
declare @p6 int
set @p6=8196
declare @p7 int
set @p7=1
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT TOP 5 * FROM Contact WHERE DisplayName LIKE ''%Stu%'' ORDER BY DisplayName',@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, `@p6, @p7
If you copy and paste and then run this in a query window, you get the following message/error:
Msg 8179, Level 16, State 6, Procedure sp_cursorprepexec, Line 1
Could not find prepared statement with handle 1073741897.
(1 row(s) affected)
–
So I’ve spent a couple of hours on a Sat arvo delving into this and not really getting anywhere, except to get a better understanding of how little I know about sql 
Will report back if anything comes back from the newsgroups.
1 Like
I had a quick look and here are some comments that hopefully help!
I believe that would be the event identified as RPC:Completed in the SQL Profiler yeah? Look a few steps further back for RPC:Starting. The “completed” one is showing you the values of the parameters after the exec has completed. At least I think that is what it means.
Here is a simple example that might give you the flow better:
declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT 42'
select @p1, @p2
exec sp_cursorfetch @p2
Paste that into SSMS and it will come out with the answer in the end.
Notice that I have removed the additional parameters since they are optional and this example works fine without them (plus, I would have to go and learn what they are actually for which is not a Sat night task, at least not this week).
Looks like a nice explanation here: http://www.davewentzel.com/content/prepareexecute-model
And for some calls coming from Clarion you will see the sp_cursorprepare and sp_cursorexecute then the sp_cursorfetch. I guess it depends on what your originating statement is? Needs more investigation I think.
But regardless, this is just what the SQL Profiler sees happening and all Clarion is likely saying is shove this “SQL” through a cursor and give me stuff back. It doesn’t get you anywhere closer to your original question about adding parameters to a PROP:SQL statement.
This makes me want to dig into the reasons for your question. What is your motivation for wanting to do this? Perhaps there is a more appropriate solution that will solve your actual problem here?
Thanks Brahn.
A wonderful clarion dev very kindly let me know that a lot of what I was putting under the PROP:SQL banner was in fact the work of the ODBC driver. So there’s that.
So I’ve been investigating how to make our sql server and code run better, faster, safer etc. Came across this old post (of mine, which I definitely don’t remember, ha) on Nettalkcentral, http://www.nettalkcentral.com/index.php?option=com_smf&Itemid=36&topic=4981.0;prev_next=next, where Larry helpfully pointed me to an excellent article about the benefits of parameterizing your queries in Sql.
I’ve got some C# code that uses them, and understand how important it is especially security-wise, but was interested to see the speed increases.
So I figured, can this be done with PROP:SQL?
Hence me coming here. If it can, it’d make a great addition to the growing store of helpful information on clarionhub
Doesn’t this depend on the file driver you are using though? Well, sort of. The “MSSQL” file driver is I think just an extra abstraction over the ODBC driver for Clarion and the advantages/differences seem to be a little hard to define. I thought it was related mostly to SQL Native Client but I seem to recall this was disproved some time back. See also this related topic.
Yeah, so that is about making sure what you are doing is helpful to the query optimizer but the advantage might depend on the kind of workload you are throwing at the server. e.g. If you have one query that takes 10 minutes to complete then no amount of parametization will help but if you have a query that gets run a zillion times then the more you can do to help the server optimize for the execution of that query the better.
However, I am pretty sure that anything you run via the Clarion file drivers is already being done via prepared statements and you don’t have much (any?) control over how that happens. On the other hand, I think you gain a bit of safety against SQL Injection attacks (don’t quote me, still test for this!).
If he is around @Dennis has lots of good tips on this kind of thing. He might also be able to point you in the direction of interacting more directly with the server.
Probably the speed problem with Clarion is not so much related to the parametization but the shoving of everything through cursors, or more specifically matching the odbc cursor type to your workload requirements). I would think the best thing you can do is optimize the heck out of your SQL code and do as much as possible on the server. For example, in your NetTalkCentral post it seems like you are pulling a bunch of data back from the server into a clarion memory table. Unless you absolutely need that data client side you would be better to up your sql-fu and get all your processing done in a stored procedure or something and only return the results. Plus, you can then, within your stored procedures, start to consider optimizations like parametization of queries 
Documentation on the Clarion File Drivers would be nice or even better, being able to poke around at their code and actually know what they are doing.
Absolutely, I love it!! 
Thanks Brahn.
The nettalkcentral post is way old, that’s not what this particular adventure was about. Since then I think my sql-fu has levelled up a couple of times!
There are some queries I do that are on the expensive side of things, LIKE searches and wotnot on large files. Even if just those being parameterised gave an increase, I’d consider it a win.
Cheers for all the help and pointers.
It depends on the details but I don’t think parameterization would make a difference.
Have you tried isolating the query and running it in management studio? From there you could try different strategies to improve performance. The advantage then is you have a better chance of knowing if the problem is Clarion or SQL.
Plus, from SSMS you can see the execution plans, both estimated and actual. It will give you hints for missing indexes and you can also see where you are hitting SEEK or SCAN and such.
Specifically on the point of LIKE, hit up a few of the results from this google search and you should get some ideas.
Okay, I don’t have enough knowledge to argue that, but this article (Performance Implications of Parameterized Queries - Simple Talk) seems to say it will have a difference.
Yup, do most things through management studio. Did try the query, yes, but didn’t look at execution plans (CTRL L from memory I think). Will do so.
Yeah for sure, it totally will but it’s relative to the cost of the query itself. (and how often the query is executed, etc).
Are you optimising the right thing is what I am wondering
Let’s say I have a search function where the user types in one or more search terms.
This goes and searches through a couple of main files and then for each of those found, searches through a whole heap of related files.
Let’s say one of the main files is a PERSON file. It contains ~40,000 records. Not heaps, but enough.
The PERSON file has a DISPLAYNAME field, which is 512 chars.
Doing a LIKE ‘%Stu%’ or even a LIKE ‘Stu%’ on that is expensive. If I can keep the LIKE and get a performance increase out of it by parameterizing the query, then super stuff.
My search function is a lot deeper than what I’ve described, does a lot more logic and picks which fields to search etc based on certain truths about the search terms, but above is something that can happen.
So that’s one reason why I’m interested in parameterizing PROP:SQL.
I think you said it here yourself. The LIKE is expensive. Parametization won’t help that (significantly enough to bother with).
You might find an improvement by limiting it to 'STU%' instead of '%STU%'. The difference being (potentially) an index scan vs an index seek which could make a massive difference.
You could have a switch on your Clarion window that lets the user choose between a “contains” filter and a “starts with” and a message that says “contains is slower”.
Perhaps consider full text search using Contains() instead of LIKE
New term for the day which might also help out:
Yeah, as said, the search logic is a lot deeper, and does pull out the % and use = and wotnot as intelligently as it can.
I’m not going to add any complexity for the user at this stage. They need to be able to type in a name, a number, an email and hit enter and get back results.
If you say that parameterization won’t give the LIKE any significant improvement, then okay. I guess that’s the door closing on that particular avenue.
Ha, now you are going to hate me. From what you have said, I don’t think it will but maybe it will be enough for your situation. I would certainly be very interested to hear your results! Anything that improves performance in a useful way is great.
That is of course if you can even do that via the Clarion–>ODBC drivers. Or if someone who knows can share a good way to do this via a more direct way.
Perhaps it is something you can pre calculate? (use triggers to prime a “search” table on UPDATE/INSERT)
You add complexity to the data but that would be invisible to the user.
SQL is fun 
Yeah what I’d love to do is some kind of funky Memory file index / SQL almalgum for searching, split all the possible fields into components themselves and equals instead of like etc.
But that would take some time and also more brains than I currently have allocated! So it’s on the Trello todo pile