Qs about new 8.4 exec_sql in queries

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Qs about new 8.4 exec_sql in queries

Post by 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
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Qs about new 8.4 exec_sql in queries

Post by aware_support »

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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.
Aware IM Support Team
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Qs about new 8.4 exec_sql in queries

Post by Jaymer »

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

Code: Select all

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:

Code: Select all

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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

Re: Qs about new 8.4 exec_sql in queries

Post by 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
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Qs about new 8.4 exec_sql in queries

Post by 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Qs about new 8.4 exec_sql in queries

Post by 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply