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
Qs about new 8.4 exec_sql in queries
Qs about new 8.4 exec_sql in queries
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
Aware Programming & Consulting - Tampa FL
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Qs about new 8.4 exec_sql in queries
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)
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’ ?
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)
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 ?
Code: Select all
Can you share your thoughts on why this was implemented?
Aware IM Support Team
Re: Qs about new 8.4 exec_sql in queries
ok, in MSSQL, I had a Stored Procedure defined as
So I changed it to use this new method:
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.
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
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
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
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Qs about new 8.4 exec_sql in queries
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
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
Re: Qs about new 8.4 exec_sql in queries
Use the SQL Profiler and find out !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
... 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
Aware Programming & Consulting - Tampa FL
Re: Qs about new 8.4 exec_sql in queries
You see this at the end of my exec-sql example: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
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
Jaymer
Aware Programming & Consulting - Tampa FL