• State Verified Answer
  • Replies 4 replies
  • Subscribers 12 subscribers
  • Views 10165 views
  • Users 0 members are here
Options
Related

using the SQL item type as a view

paul.sheehy@bd.com
Offline

Querying a SQL view I built in SSMS from an Aras server method:

string SQL_View = "innovator.my_custom_sql_view";

string SQL = "select * from " + SQL_View;
Item SQL_qry = inn.applySQL(SQL);

This is quick for me because I do a lot in SQL, but I know it is bad practice. So, I want to replace this SQL injection using IOM:

I created a new SQL item, type=View, with the contents from the custom SSMS view.

What is the correct C# code to get the view results?

Item SQL_qry = inn.newItem("SQL", "get");
SQL_qry.setProperty("keyed_name", SQL_View);

SQL_qry = SQL_qry.apply();

This code just gets the SQL item, not the contents of the View. 

I couldn't find anything in the Programmers guide for this, and the Blog posts were only about Stored Procedures, not Views.

I'm thinking it should work the same way, but I haven't figured out the correct IOM syntax.

Thanks,

Paul Sheehy

  • 0 Offline

    Hi Paul

    Can you post the innovator.my_custom_sql_view SQL query  here so that we can have a look

    You need to convert your SQL query to AML query and use it in method. ("SQL" in your method will get the SQL item types but you need to use the correct item type which you are querying in you SQL view).

  • 0 Offline in reply to Gopikrishnan

    my view query is fairly simple:

    select
    id
    ,ITEM_NUMBER

    from innovator.part
    where [state] = 'Released'
    and IS_CURRENT = 1
    and id not in (select NCMR_PART from innovator.NCMR_MANAGEMENT)

    if i have to convert the sql query to AML, then what is the point of a SQL item with type=view? how is it used?

  • +1 Offline in reply to [email protected]

    Hi Paul

    You can use below method to get the required view. I used item_number of NCMR as ID is unique for each item type

    Innovator inn = this.getInnovator();
    //Verify the Item type name in system
    var ncmrItem = inn.newItem("NCMR MANAGEMENT", "get");
    ncmrItem.setAttribute("select", "id,item_number");
    ncmrItem = ncmrItem.apply();
    if (ncmrItem.isError())
    {
    return inn.newError("No NCMR Item Found");
    }
    else
    {
    var ncmrIdList = new List < string > ();
    for (int i = 0; i < ncmrItem.getItemCount(); i++)
    {
    var ncmrOneItem = ncmrItem.getItemByIndex(i);
    //I'm using item_number here as ID is unique for each item type and we might not have same ID used for Part and NCMR. If you want to use ID, then change item_number to id
    ncmrIdList.Add("'" + ncmrOneItem.getProperty("item_number", string.Empty) + "'");
    }
    var partView = inn.newItem("Part", "get");
    partView.setAttribute("select", "id,item_number");
    partView.setProperty("is_current", "1");
    partView.setProperty("state", "Released");
    partView.setProperty("item_number", string.Join(",", ncmrIdList)); // Change it to ID if you want to use ID instead item_number
    partView.setPropertyAttribute("item_number", "condition", "not in"); // Change it to ID if you want to use ID instead item_number
    partView = partView.apply();
    if (partView.isError())
    {
    return inn.newError("No Part item found using this criteria");
    }
    else
    {
    return partView;
    }
    }

    We can SQL also but it is good practice to use AML as SQL directly interacts with tables and other server events will not be triggered.

    Views in database is created for Poly Item and are system created. If you want to use SQL for any complex get query, you can use stored procedure and call the SQL Process in your method