DEVELOPERS FORUM - Item added through SQL query is not accessible

ergr - Tuesday, May 26, 2015 10:04 PM:

Hi,

I am designing a small Windows Forms application, which will allow users to add/delete/view an item to/from Innovator database directly.

As we are not giving direct access to communicate with Innovator instance I am not using IOM.dll in my project. So, I am doing things through SQL queries. To add an item I did this,

To generate Ids:

   public static string GetUniqueID(int Size)
    {
        char[] validChars = new char[36];
        validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890".ToCharArray();
        byte[] dataToAttr = new byte[1];
        using (RNGCryptoServiceProvider RNGCSP = new RNGCryptoServiceProvider())
        {
            RNGCSP.GetNonZeroBytes(dataToAttr);
            dataToAttr = new byte[Size];
            RNGCSP.GetNonZeroBytes(dataToAttr);
        }
        StringBuilder IDstring = new StringBuilder(Size);
        foreach (byte b in dataToAttr)
        {
            IDstring.Append(validChars[b % (validChars.Length)]);
        }
        return IDstring.ToString();
    }

This creates ids like : OB7NFUVH2SSGTANCCSY058EV8JOAU070, 2V35DB7328ULFYWMM43FMNCIQJZ5N9X0

To add an item:

string newID = GetUniqueID(32);

SqlCommand addItem = new SqlCommand("insert into [innovator].[NIS_ASSET](NAME, CONFIG_ID, ID, CREATED_BY_ID, CREATED_ON,PERMISSION_ID,KEYED_NAME,IS_CURRENT,GENERATION,MAJOR_REV,IS_RELEASED,NOT_LOCKABLE) values('" + NameTxt.Text + "','" + newID + "','" + newID + "','30B991F927274FA3829655F50C99472E', '" + dtNow + "', 0 , '9122CD065CF04141B8EFE263FC80BEA4','" + newID + "',1,1,'A',0,0)", connMIF);
            addItem.ExecuteNonQuery();

Here for PERMISSION_ID , I gave 'Default Access' item's id as value, for CREATED_BY_ID 'Innovator Admin' Item's id a s value and the same generated Id(newID) for KEYED_NAME.

Executes successfully. But when I try to open this in Innovator, recieving this "2V35DB7328ULFYWMM43FMNCIQJZ5N9X0 is not valid". After clicking OK form opens and displays data as well. But I am unable to edit and delete that item. Seeing the same error if I try to Lock/Delete the item.

Is this because of the ID that I am creating? or anything wrong with the query? Please give me some suggestions over this and help me.

Thank you,

Erg R

Andy - Wednesday, May 27, 2015 12:05 PM:

I would not do this via SQL at all.  One line of AML can represent many lines of SQL.

However, I suspect the problem is related o the fact that you are not creating the Item properly.

You have left out several required system properties in the SQL.

kentonv - Thursday, June 11, 2015 2:45 PM:

I wouldn't recommend adding items direct in SQL, but if you must, the IDs need to be a 32 character HEX string. So letters greater than 'F' will not function. Effectively aras uses a GUID item for IDs. SQL server has a NEWID() function that returns a valid GUID, but includes dashes, so you would need to replace those.

REPLACE(NEWID(),'-','') should work.

Again, I wouldn't recommend the basic plan to use SQL instead of the IOM, since it opens up potentially other little missed pieces of the puzzle in how Aras is setup.

Jeroen Bosch - Thursday, May 28, 2015 6:24 AM:

Hi Erg,

Andy is 100% correct.

Working directly with the database objects is not recommended at all because it bypasses Innovator's logic but also is not "resilient" as the database schema might change for any reason.

AML should have all you need for building great applications with Innovator.

Jeroen

AbhishekSrivastava - Wednesday, April 6, 2016 7:59 AM:

Guys,

Can I edit created on Property for an Item.By default it was not editable.

Thanks

Abhishek Srivastava

kentonv - Thursday, April 7, 2016 9:18 AM:

Typically, the created_on property should only be updated when importing data, and when that date is important to the item history. 

To update properties that are who/when props, I would use a SQL update. Unless you create the entire item through SQL, you will need to create the items using the import tool, or some AML, then perform an update of the created_on/created_by.

This can be done in a method using something like:

this.GetInnovator().ApplySQL("UPDATE INNOVATOR.ITEM_TYPE_TABLE_NAME SET CREATED_ON=innovator.ConvertFromLocal('2000-01-01T00:00:00','Eastern Standard Time') WHERE ID='**ARAS_ID_VALUE**';");

If you are updating as part of some data migration script, you could simply run a series of these updates in a SQL server management studio session.

AbhishekSrivastava - Friday, April 8, 2016 4:03 AM:

Thank Kenton,

One more query,  Is that possible to view Gantt Chart in Hours like Days and weeks.?

Thanks

Abhishek Srivastava