Qs about new 8.4 exec_sql in queries

Jaymer

Dear support:

Changelog says: Queries can now be defined using EXEC_SQL statement

This sounds great.
Just trying to get my head around this.

So instead of “find all customer” in the text tab of a query, it would be
EXEC_SQL ’select * from customer’ ? (I assume no)

Or do I need to explicitly specify field names? (I assume yes)
As in EXEC_SQL ’select name,city,state from customer’ ?

Does this allow tag replacements using << >> notation?
As in EXEC_SQL ’select name,city,state from customer WHERE ’ + <<LoggedInRegularUser.myWhere>>
(Or maybe there is no need if a field is used directly)

Can a field be used for the entire parameter?
As in EXEC_SQL LoggedInRegularUser.myQuery ?

Can you share your thoughts on why this was implemented?
There must be someone specific who had a need for this.
What business case does this solve?

Thx

aware_support

So instead of “find all customer” in the text tab of a query, it would be
EXEC_SQL ’select * from customer’ ? (I assume no)

Yes, except that the name of the table will be businessSpace_customer or BASTestDomainBusinessSpace_customer

Or do I need to explicitly specify field names? (I assume yes)
As in EXEC_SQL ’select name,city,state from customer’ ?

You can specify field names, but you need to be careful - make sure you select ID, BASTIMESTAMP and BASVERSION among other fields. Also keep in mind that those fields you do NOT select will not be available to various rules

Does this allow tag replacements using << >> notation?
As in EXEC_SQL ’select name,city,state from customer WHERE ’ + <<LoggedInRegularUser.myWhere>>
(Or maybe there is no need if a field is used directly)

You do not use tag expressions here - you can just refer to objects and attributes as you do in normal rules (think of FIND or EXEC_SP), for example
EXEC_SQL 'select * from bas_customer WHERE ' + LoggedInRegularUser.SomeAttribute

Can a field be used for the entire parameter?
As in EXEC_SQL LoggedInRegularUser.myQuery ?

Yes

Can you share your thoughts on why this was implemented?

This was requested by former Magic developers - apparently Magic has this feature and it's very popular.

Jaymer

ok, in MSSQL, I had a Stored Procedure defined as

SELECT      ROW_NUMBER() OVER (ORDER BY Count(*)) AS ID,
			1 AS BASVERSION, 
			GETDATE() AS BASTIMESTAMP, 
			ROStatus as Status,
			ST.StaOrigCaption as Caption,
			ST.Color ,
			COUNT(*) AS Cnt
FROM        RO
INNER JOIN STATUS ST ON RO.ROStatus = ST.ID
GROUP BY	ROStatus, ST.StaOrigCaption, ST.Color
Having Count(*) < 300
Order by Cnt DESC

So I changed it to use this new method:

EXEC_SQL 'SELECT      ROW_NUMBER() OVER (ORDER BY Count(*)) AS ID,
			1 AS BASVERSION, 
			GETDATE() AS BASTIMESTAMP, 
			ROStatus as Status,
			ST.StaOrigCaption as Caption,
			ST.Color ,
			COUNT(*) AS Cnt
FROM        RO
INNER JOIN STATUS ST ON RO.ROStatus = ST.ID
GROUP BY	ROStatus, ST.StaOrigCaption, ST.Color
Having Count(*) < 300
Order by Cnt DESC' 
RETURN DASH_RO_Status_Counts

I'm not doing any field replacement here, but even if I was, I'm not sure of the advantage of this over using a SP.
A SP gets pre-compiled/analyzed by MSSQL and is supposed to perform better than an ad-hoc query.
And IF I NEEDED TO substitute some values in a WHERE clause, then I would do that in the SP anyway and I'd still have the benefits of a pre-compiled SP.

You know, traditionally Magic wasn't a SQL tool - for many years, many large systems were written using Btrieve.
I certainly would like to know a benefit to a Magic Programmer-now-turned-Aware Programmer of using this.
Just because Magic could do it (and now you have an influx of Magic architects investing into Aware in Europe who have expressed interest and motivated Aware to implement this) doesn't mean there's a real reason to do it.
Maybe the "magic guys" (and I'm one of them) who wanted this weren't that strong on doing Stored Procedures (example: Mark Bailey who does almost nothing in SQL) and this enables them to get their hands dirty a little bit. But by doing it this way, you're not writing portable code - the Syntax is going to be diff for MSSQL, MYSQL, Oracle, for example. And now you've got SQL code littered throughout Aware instead of all in one place in the back end.
I dunno, there's pros & cons, I'm sure.

But it still would be nice to know a real "business case" or Programming Problem this solves that already wasn't doable with Aware... rather than "the ex-Magicians-turned-Aware-Investors" requested it.

ddumas

I am wondering if it might be better from an application maintenance perspective (so you do not have "one-off" embedded queries all over the place) , and better performance, that you instead call a stored procedure, Ex: sp_qryGetCustomers, passing in any desired parameters, which returns the output. You could also have different flavors of that with different parameters passed in.

However, as an AwareIM newbie, I am not familiar with how filters work in terms of: Do they filter on the data already returned to the application, or is another database select called when a filter is applied?

Dave

Jaymer

ddumas wrote
However, as an AwareIM newbie, I am not familiar with how filters work in terms of: Do they filter on the data already returned to the application, or is another database select called when a filter is applied?
Dave

Use the SQL Profiler and find out !

... and, a grid only returns the Grid PageSize # of Rows - 1M rows in the DB, but u can filter on CustID and get back a page immediately.
Set # Rows returned to 100 and you'll see it takes longer to step thru pages.
Its the "next page" queries that start to take longer/more back end DB work - but that may be neglibible - again, watch the profiler as you step through a table and you'll see what its having to do.

Jaymer

ddumas wrote
I am wondering if it might be better from an application maintenance perspective (so you do not have "one-off" embedded queries all over the place) , and better performance, that you instead call a stored procedure, Ex: sp_qryGetCustomers, passing in any desired parameters, which returns the output. You could also have different flavors of that with different parameters passed in. Dave

You see this at the end of my exec-sql example:
RETURN DASH_RO_Status_Counts

Problem with this method (confirmed by Support) is that it doesn't really work. Sure, it shows data on the screen and it DID return the desired columns, but you'd like to click on a row to drill down or something.
Since the result set isn't "really" in that BO "DASH_RO_Status_Counts", there's no record ID to latch onto to pass to the drill down process.
So its nice window dressing if you needed some static data that you can't manipulate.

The correct way to do it is, as you said, to create a SP - i pass in the LIRU.ID (LoggedInRegularUser.ID) to the SP. It deletes old data for that user only. Inserts top(10) customers.
Now the Grid on the screen IS ON A REAL TABLE (where DASH_RO_Status_Counts.theUserID = LoggedInRegularUser.ID) and I can click on a row.
(Remember, on tables you create in a SP, you need the std 1st 3 columns in every aware table. Ver & Time are not that imp. But you must have an ID #.)

Like I said in this thread, I'm trying to find a reason to use this new function - haven't found it yet.