I think this speaks for itself. Just let us quickly edit/copy into a BAQ the correct SQL. Many of us edit/test SQL in another editor because it is so much easier and then we have to reproduce it graphically.
I’ll even allow the E9 "can’t represent the query graphically’ error for some of the SQL commands…
I’m gonna negative vote this one… is that an option? NOPE… LoL Don’t want anyone doing direct SQL… ever… nope nope.
But you have to admit a copy paste in the BAQ editor would be pretty nice sometimes…
Nope… I do not want any single one of my customers to have this power… EVER… too much at stake. Would I personally like it? sure… but its a terrible idea for 99.99999999% of the customers. IMO
Why is it any more dangerous than copying it out, and testing it using toad or similar program? Just asking.
People can write really bad SQL. The BAQ tool mitigates this to some extent, it prevents you from shooting yourself in the foot (as much as it can)
Plus there’s SQL injection and security issues.
ok, ok - I definitely see your point… How about we make it another security option. The “Jose-approved” checkbox (for that 0.000000001%) for users to get this function? LOL
Remember their SAAS guys all share a single db server too. Even in Azure from my understanding. They would have to move their whole security model to Row Level Security inside of SQL Server instead of just having their own security layer in the app.
I think what you are looking for is some kind of extension or app where you can write SQL and it will turn it into a BAQ file that you could import.
Perhaps that is the best solution @John_Mitchell. I’d be more than happy with that.
However, to Jose’s point, would that not have the same effect as pasting SQL into the current editor? This BAQ tool would also have to recognize bad code, injections, and all manner of foul deeds perpetrated by Jose’s user base (couldn’t help it Jose!)
An external program could take advantage of the myriad of SQL ‘check’ code out there…
That’s very true. Also, I just looked at a E10 .baq file and it looks like they are compiled now.
I’ll also add that BAQ does inject additional stuff into the queries to limit results. Territory, Tenancy, Payroll, etc as appropriate.
Is there a way to bypass this without going to a External BAQ? I know the additional stuff can really degrade the query performance(meaning running the BAQ generated query phrase syntax on the server vs running the BAQ in Epicor is significantly different) and we end up using the External. I’d rather maintain the query in Epicor.
No. Breaking security rules in the product probably will not be a feature we add 
I think we should all give a measure of thanks that the BAQ Editor gives us a glimpse of the generated SQL at all - whether it is well formed or not. When trying to clean up a mess from a well-intended user’s BAQ or from the “Expert” work delivered by another consultant I have often used that to quickly identify the parts to hack away.
Now THAT is an interesting direction. Execution plans, the generated SQL, etc is an interesting conversation to educate people that want to look at what they are doing to the system.
A reader for the execution plan would be handy so you didn’t have to bring it into another program.
perhaps, access to in-line table functions in BAQ designer? External BAQ designer, enables this functionality though.
We are on version 10.0.7.4 and off maintenance. We have the Epicor education module and the official Epicor training material that shipped with our version includes exercises where direct connections via visual studio in the form of SQL queries are actively promoted.
Version information below
ED907905
90521-905-1606-583702
9.05.702
Revision: March 14, 2013 2:07 a.m.
Total pages: 66
course.ditaval
Epicor previously promoted this, is it purely the SAAS implication or some other reason that brought about the change of position by Epicor - we are off maintenance and as we have a strained relationship with Epicor so don’t get invited to anything so most likely missed any communication.
I get the security issues and the sql injection issues, but I think dropping tables, delete data, select * from erp.parttran etc could easily be prevented by having a list of allowable commands within a direct sql editor with a default limit on rows returned and execution time that you have to specifically override.
really? ugh…