An update.
When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.
Matthew
@Matthew M When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.
You can use/run API 1 code in 2019r2+. Maybe try that to make sure it's not a plugin issue.
If that works, then try a Select count(*) in your code above to verify the automatic prepared statement is working correctly.
Jay,
The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).
Markus,
LIKE is my preferred conditional and I generally use the & wildcards. In this situation I was trying to eliminate any variation from the documentation examples in the event said variance was the problem. I did, however, try this but the results were the same.
Thanks to both of you for your suggestions.
@Matthew M The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).
Maybe try it without the TOP limit, so see if that is an issue in API 2.
@Matthew M Derk - The LIMIT parameter throws a syntax error with MSSQL.
Markus - I did try the DatabaseRow format, but had the same problem.
Thanks for both suggestions.
Try your query without () so ... TOP 10 ...
More here:
In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.
https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15
@Matthew M Still no change. It seems that I will have to use 2019r1.1 until such time that this is fixed.
You'll need to submit a Feedback case if it is to be fixed.
Funny you should mention that. I did just try that while preparing my Feedback case and it works fine. It seems to have something to do with the SQL statement preparation so I'm going to look into that a bit more.
Since this is to be a web app I really want to use prepared statements for security reasons.
Thanks.
@Markus R unfortunately it is not possible to see the generated query that SelectSQL made
For future reference, if you want to see the SQL text being sent to SQL Server from local or external applications (and you have admin privileges on the Server) try SQL Profiler . It is part of the MS SQL Server Management Studio install (free).
Of course you'll have to install and run Profiler from a Windows machine.
I hope that helps.