Forum Discussion

angela's avatar
angela
Catalyst II
8 years ago

SQL-Procedures vs. applySQL - what´s less not recommended?

Hi, "AML first" is probably on of the most important things to consider when developing custom code in Aras. And luckily most of all use cases can be covered with AML. But sometimes things have to be done with SQL. I wonder if there is a benefit when using SQL-procedures instead of applySQL? From my current point-of-view, the main benefit for procedures is that they can be reused for different ItemTypes. A typical usecase for SQL-procedures is e.g. the Files flag that we can see at the ItemTypes "Part", "Document", etc. Procedures maybe also have some benefits when it comes to safety and performance topics. But in my case my few applySQL Methods (e.g. for changing the minor_rev property) can just be triggered by a limited amount of people. So I am not sure, if using procedures here would really bring some real benefit. It´s right now not a real problem in our environment, it´s more a question of personal interest. Are there some general recommondations avialable? Angela

1 Reply

  • eli_donahue's avatar
    eli_donahue
    Community Manager
    8 years ago
    Hi Angela, If you absolutely have to use SQL for a task (like minor_rev, as you mentioned), then creating a SQL item in the Aras database is probably the best way to go. You can reuse the same SQL across methods, maintain the SQL in one place, and you can package it up like any other method or configuration item. A word of caution - not just for you, but also anyone else who finds this thread - modifying Aras data via direct SQL can have unintended consequences such as data corruption, data loss, or bypassing the permission model. It should be used only when there is no other option, and it should be tested extensively in dev/test environments before releasing to production. As you mentioned, AML and IOM are the preferred approaches for retrieving and modifying Aras data. Eli
    Eli Donahue Aras Labs Software Engineer