Remedy - Server - Behavior Attachments in AR System

Version 4
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    AR System


    APPLIES TO:

    BMC Remedy AR System Server



    PROBLEM:

     

    1. How the attachments are stored in the database?
    2. How those can be extracted from the database?
    3. How the attachments are extracted in case the same attachments are attached multiple time?

      

     

     


    CAUSE:

    N/A


    SOLUTION:

     

    Attachments in Remedy are attached to attachment fields.

    In an attachment pool if you create a attachment field, it will create 3 columns in the B<schemaId>
    Example:
    column C<attachmentfield_Id1>
    column CO<attachmentfield_Id1>
    column CC<attachmentfield_Id1>


    When an attachment is attached an entry is created for the attachment.
    when adding multiple attachment then multiple fields need to be added.

    Adding 2nd attachment field will add 3 more columns in the B<schemaId> table.
    Example:
    column C<attachmentfield_Id1>
    column CO<attachmentfield_Id1>
    column CC<attachmentfield_Id1>

    column C<attachmentfield_Id2>
    column CO<attachmentfield_Id2>
    column CC<attachmentfield_Id2>


    Attaching the same attachment multiple times, the attachment will be added in the column format at database level.

      

    Test scenario
    Steps:
    1- Create a regular form -> Add an attachment pool -> Add 3 attachment fields -> save the form.
    2- Open the form using the Midtier or User tool.
    3- Add the same attachment in the 3 attachment fields and save the request.
    4- Open the database -> Open the [B<SchemaId>] table and perform below query.
          select * from B<SchemaId>;


    For the 3 attachment fields we have below columns created.
    .....................
    C1,
    C<attachmentfieldId1> |
    CO<attachmentfieldId1> |
    CC<attachmentfieldId1>
    |
    C<attachmentfieldId2> |
    CO<attachmentfieldId2> |
    CC<attachmentfieldId2>
     |
    C<attachmentfieldId3> |
    CO<attachmentfieldId3> |
    CC<attachmentfieldId3>
    |
    .....................
    Thus for same attachments those will get added to above in column format.

      

    Below is the Snippet of logs which shows how the attachments are getting saved in the database.
    .......................
    */+CE     ARCreateEntry -- schema Test_attachment from Mid-tier (protocol 20) at IP address {IP_Address}  // :q:0.0s
    */     Start filter processing (phase 1) -- Operation - CREATE on Test_attachment - <NULL>
    */     End of filter processing (phase 1) -- Operation - CREATE on Test_attachment - <NULL>
    */BEGIN TRANSACTION
    */OK

    */INSERT INTO B358 (C1) VALUES ('000000000000004')
    */OK
    */UPDATE B358 SET CO536870914=1086,CC536870914=625,C536870914='mail.txt' WHERE C1 = '000000000000004'
    */OK
    */INSERT INTO B358C536870914 (entryId) VALUES ('000000000000004')
    */OK
    */SELECT C536870914 FROM B358C536870914 WHERE entryId = '000000000000004'
    */OK
    */Setting LOB into the above row ...
    */OK

    */UPDATE B358 SET CO536870918=1086,CC536870918=625,C536870918='mail.txt' WHERE C1 = '000000000000004'
    */OK
    */INSERT INTO B358C536870918 (entryId) VALUES ('000000000000004')
    */OK
    */SELECT C536870918 FROM B358C536870918 WHERE entryId = '000000000000004'
    */OK
    */Setting LOB into the above row ...
    */OK

    */UPDATE B358 SET CO536870919=1086,CC536870919=625,C536870919='mail.txt' WHERE C1 = '000000000000004'
    */OK
    */INSERT INTO B358C536870919 (entryId) VALUES ('000000000000004')
    */OK
    */SELECT C536870919 FROM B358C536870919 WHERE entryId = '000000000000004'
    */OK
    */Setting LOB into the above row ...
    */OK

    */INSERT INTO T358 (C2,C7,C8,C3,C5,C6,C1) VALUES ('Demo',0,'Demo W-RRODGE-37.adprod.bmc.com SQL -- SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)',1428073291,'Demo',1428073291,'000000000000004')
    */OK
    */INSERT INTO H358 (entryId,T0,U0) VALUES ('000000000000004',1428073291,'Demo')
    */OK
    */COMMIT TRANSACTION
    */OK
    */-CE               OK -- New ID 000000000000004
    .......................


    Additional Information:

    BMC Software use a proprietary algorithm to compress and decompress the attachmen at the database level.
    The only way to get access to the attachment and decompress it is via the GetEntryBlob  API call. 
    You can use Remedy's Java API to do this .This is documented in the Java doc file ardoc81_build001.jar

    Syntax: getEntryBlob:

    public void getEntryBlob(String formName, String entryID, int fieldID, String filePath) throws ARException

    Returns the attachment that is stored in the indicated IDs on the specified server.
    The returned attachment will be placed into a given file path. The system returns information based on the access privileges of the user you specify in this ARServerUser object.
    User permissions are verified for the specified field. If the user cannot access the field, the system returns an error message.

      

    Parameters:
    formName - the name of the form that containing the entry
    entryID - the id of the entry that contains the attachment
    fieldID - the id of the attachment field
    filePath - the path to a file that stores the return attachment
    Throws:
    ARException - if blob attachment is not returned


    Note: exists a small utility not developed by BMC Software that can help to extract the data of an attachment without the need to have AR System installed.
    You can check this here:
    http://remedylegacy.com/tools/db-attachments/
    Be advised that this tool and any other utilities hosted in the above url are not official tools developed by BMC Software and using them falls strictly under your responsibility and BMC Support will not address any questions, queries or issues using any of them.


    Related Documentation:
    The attachment tables for a form

     


    Article Number:

    000096795


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles