History is repeating itself, back in version 6 we could edit the query statement. I back then would use this to specify an index on a table join but that a long time ago, and progress not SQL.
1 Like
Wonder how concerns with SQL injection will be handled.
1 Like
Thank you this is very useful. Is anyone working on an option to convert a BAQ to SQL?
The sql code is displayed in BAQ studio, do you mean a .sql file export?
the importer for SQL does do some validation and doesnât allow invalid SQL injection. Remember that when it imports the SQL, it converts it to a valid BAQ, which is what is actually executed from that point forward.
3 Likes
since the SQL code is already displayed, you can simply go to that display, and select it, and copy it to your clipboard.
3 Likes
As noted in that display, this isnât the exact SQL that is run. A BAQ dynamically inserts code for company, plant, field, and territory restrictions. So, you may get more records than the BAQ. Just a warning in case you see that.
2 Likes
It does open up a new concern and the damage could be significant. Wonder if some validation using a list of tables or even a cheaper AI models, specifically try to identify the tables and check against access rights could be and idea worth looking intoâŚ
2 Likes
@deepak, It doesnât just blindly run the SQL you feed it. It attempts (key word here) to create the BAQ from the SQL you give it. Then from the BAQ that it generates, runs it like any other BAQ. So if you canât do SQL injection with a BAQ now, you canât do it with this new tool.
5 Likes
Thanks for the clarification. So it is really a faster way for power users(someone who knows how to write raw SQL) to create a BAQ faster
3 Likes
correct!
for example, if you write a pure SQL statement in SQL, and run it, SQL doesnât know about our security at the data table and field levels⌠it also doesnât know about our CRM rules for viewing data. It doesnât know about whether you are allowed to see data in company 02 vs Company 01âŚ
BUT⌠when you import your SQL into the BAQ, then the BAQ will apply all the additional rules when it actually executes the BAQ. Like someone already pointed out, the SQL you see in the BAQ is not the actual final SQL statement that is run. instead we apply additional filters to hide companies, tables, fields, and even specific records based on YOUR Kinetic Security.
4 Likes
Extending on the same idea⌠is there a CLI/API option in the works? Iâm thinking of version control or CI/CD pipeline. If there is a way to see the execution plans, would really help with ideas for optimizations.
Of course youâve already been doing this hahaha, youâre insane my friend. Crazy cool!
1 Like
2 Likes
Great news, Tim! This will be very helpful for the EpiCare support folks too⌠Instead of sending a 5 page word file with steps on how to create a BAQ to get some data, they could provide the SQL and weâd paste it in⌠Edit whatever fields need changing (like Company = XYZ, InvcHead=âŚ). Or even just grab the current company⌠Canât wait to see this.
Any chance itâll be in 2024.2? <crossing fingers!>
4 Likes
Thatâs a great use case here @askulte !
1 Like
How is this going to handle UD columns? When querying directly against an Epicor db or readonly you always have to manually join the UD table by hand. Part and Part_UD on SysRowId for example. When working in a BAQ this is kind of wrapped behind the scenes together into one so it just displays the UD columns in the table itself. Which format will the input SQL take?
1 Like
Hmm you should be able to query by using the view. Dbo.Part
5 Likes
Currently⌠It doesnât. Iâm assuming that itâs on the roadmap to address, but it just ignores the fact that there are UD fields.
3 Likes
I use a handful of commonly used or exceedingly complex table/scalar stored procedures stashed in a Custom DB on the same instance as ERP. There is a performance hit when I use them. Itâs usually not noticeable. There are 1 or 2 complex, high row count reports where I had to architect it around the issue. I donât think users noticed the hit (it was a couple seconds), but I did.