By using this website, you agree to our Terms of Use (click here)
I'm kicking off an idea for saving SQL Queries in AugSQL.
In order to keep the current AugSQL (QQ900001) screen as clean as possible, I'm proposing that we create a new screen called AugSQL Saved Queries (QQ900003) with some additional functionality.
The AugSQL Saved Queries (QQ900003) screen could be modeled after the first couple of fields in the existing Automation Schedules (SM205020) screen which is pictured here:
Instead of Schedule ID in the previous screenshot, the 1st field could be called Query ID. Instead of Description in the previous screenshot, the 2nd field could be called Query Description.
Then the 3rd field could be called SQL Command, just like on the existing AugSQL (QQ900001) screen.
Below the SQL Command field could be the same results grid area that we have on the AugSQL (QQ900001) screen.
On the top could be an EXEC button, just like we have on the AugSQL (QQ900001) screen.
After the EXEC button, there would be additional buttons on the top. These buttons would mimic the buttons on the top of the Automation Schedules (SM205020) screen as shown in the previous screenshot:
1. Save (Ctrl+S)
2. Cancel (Esc)
3. Add New Record (Ctrl+Ins)
4. Delete (Ctrl+Del)
5. Go to First Record
6. Go to Previous Record (PgUp)
7. Go to Next Record (PgDn)
8. Go to Last Record
The Query ID lookup field would behave like the Schedule ID field on the front-end.
On the backend, the data that gets stored in the database would be similar to the data that gets stored in the database for Automation Schedules. The Query ID would just get stored as an auto-incrementing integer like in the ScheduleID column in the screenshot below. The Query Description would get stored in the same way as the Description column in the screenshot below (same data type, etc.). This means that the Query Description could be changed at anytime without changing the primary key on the query.
This would be created as a separate idea, but I'm mentioning it here to make sure that this idea for saving queries could accommodate this in the future. Would this approach allow us to add parameters? Maybe parameters could simply be defined in the SQL Command window using SQL Variable syntax, then passed to the query in the URL?
put the SQL Command in the URL so you can bookmark certain queries and send links to others
If the core value is to bookmark and share queries, would it be OK in your eyes to store the queries and recall them with an auto-generated ID (ie. kind of how URL-shorteners like Bitly or TinyUrl work)? Just thinking about limits to the length of a Query String (URL) that would limit the size of the query you could share if the whole thing was encoded there.
@timsql I was also thinking that there could be issues with the URL length for long queries.
You'd also probably lose spacing and indents which makes the code easier to read.
I like the idea of being able to store queries and recall them with an auto-generated ID. Passing parameters would be cool to. If I could recall a saved query like this:
https://mycompany.acumatica.com?ScreenId=QQ900001&QueryId=5
Then what if I could pass parameters to it like this:
https://mycompany.acumatica.com?ScreenId=QQ900001&QueryId=5&MyParam1=AA&MyParam2=BB
Then you could save the parameters in the text of the saved query and the query would use the parameters in the URL at run-time. The saved query could look like this:
SELECT *
INTO MyCustomTable
FROM Vendor
WHERE AcctCD LIKE 'MyParam1%' OR AcctCD LIKE 'MyParam2%'