1 2 Previous Next 17 Replies Latest reply: May 22, 2014 7:44 AM by Niharika Nic RSS

    Java Code for adding the attributes

    Niharika Nic

      Hiee,

       

        Can anyone provide the java code for adding the attributes from excel to OSDM. The java code which I'm using is not working!!

        • 1. Re: Java Code for adding the attributes
          Philip Stoyanov-Oracle

          Hi,

          below is a script that will add entities and attributes from excel file. You still need to set data type for attributes

          Excel ODBC driver is 32 bit (at least the one I have) so you need to run 32 bit version of DM

           

          Philip

          var model;
          //
          function getAttribute(entities,ename,aname){
           if(current!=null && current.getName().equals(ename)){
            attr = current.getElementByName(aname);
            if(attr!=null){
                       return attr;
                   }else{
                       attr = current.createAttribute();
                    attr.setName(aname);
                   return attr;
                   }
           }
           for (var i = 0; i < entities.size(); i++) {
            current = entities.get(i);
              if(current.getName().equals(ename)){
                   attr = current.getElementByName(aname);
                   if(attr!=null){
                       return attr;
                   }else{
                       attr = entity.createAttribute();
                    attr.setName(aname);
                 return attr;
                   }
              }
           }
           entity = model.createEntity();
           entity.setName(ename);
           entities.add(entity);
           attr = entity.createAttribute();
           attr.setName(aname);
           current = entity;
           return attr;
          }
          //====
          var current;
          model = model.getDesign().getLogicalDesign();
          entities = new java.util.ArrayList();
          model.getEntitySet().addAllElementsTo(entities);
          java.lang.Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
          // url - jdbc:oracle:thin:@host:port:sid
          //"user","pswd"
          conn = java.sql.DriverManager.getConnection("jdbc:odbc:Excel_imp", "", "");
          stmt = conn.createStatement();
          rset = stmt.executeQuery("select * from [Sheet 1$]");
          if(rset!=null){
           while(rset.next()){
            entity_name = rset.getString(1);
            attr_name = rset.getString(2);
            descr =  rset.getString(3);
            //if(descr!=null){
             attr = getAttribute(entities,entity_name,attr_name);
             if(attr!=null && descr!=null){
              attr.setNotes(descr);
              attr.setDirty(true);
             }
            //}
           }
           rset.close();
          }
          stmt.close;
          
          • 2. Re: Java Code for adding the attributes
            Niharika Nic

            Hi Philip,

             

            The code which you have provided. We added it in our OSDM(For Logical Model). Created the excel with the entities, ename, aname column and applied the changes.

            But it is not making any changes to the .dmd.

             

            How Should I proceed.

             

            My Requirements:

             

            1. I have a LDM .dmd with 2000 entities and 40000 attributes.

            2. The attributes are already available in LDM .dmd but there are few spelling mistakes attribute name correction

            3. I should rename those attributes in the .dmd file.

             

            Please help me with the steps. What should I do.

             

             

            Thanks in Advance,

            Niharika

            • 3. Re: Java Code for adding the attributes
              Philip Stoyanov-Oracle

              Hi Niharika,

               

              your task wasn't defined correctly. Now I see you want to change the name of attributes (and do that in Excel). You don't need a script for that - http://www.thatjeffsmith.com/archive/2013/01/exporting-table-column-comments-to-excel-using-oracle-sql-developer-data-mo…

              i.e. use search, export (to excel file)  and import.

              Provided script uses names for finding entities and attributes - it cannot be used for changing the name of attribute.

               

              Philip

              • 4. Re: Java Code for adding the attributes
                Niharika Nic

                Hi Philip,

                 

                The Link which you provided is for Relational model. I want to make the changes in Logical Model.

                 

                The attributes in my dmd file have so many spelling mistakes. I'm trying to fix it and fixing it manually required so much of time.

                 

                My Task Details:-

                 

                I have a dmd file (Logical) I want to change the attribute name in that. and as there are so many attributes manual work is taking so much time.

                 

                Thanks and Regards,

                Niharika

                • 5. Re: Java Code for adding the attributes
                  Philip Stoyanov-Oracle

                  The example is for relational model but it's the same for logical model. Just do the search in logical model, filter on attributes , create template which includes attribute name (and entity name of course), export to excel file, change it and import into same logical model.

                   

                  Philip

                  • 6. Re: Java Code for adding the attributes
                    Niharika Nic

                    Yep I got it. I'm just working on the import option so that this issue will be fixed. How should we import the .xls once we done with the changes.

                     

                    Thanks,

                    Niharika

                    • 7. Re: Java Code for adding the attributes
                      Philip Stoyanov-Oracle

                      Thee is an option in context menu for model in browser - "Update model with previously exported XLS file"

                       

                      Philip

                      • 8. Re: Java Code for adding the attributes
                        Niharika Nic

                        I'm not gettin the exact location for the context menu. Can you please help me with that?

                         

                        Is it under File, Edit, View, Versioning, Tools. Please help,

                         

                        Thanks in Advance,

                        Niharika

                        • 9. Re: Java Code for adding the attributes
                          user13430807

                          it is in the Browser, if you click on Logical Model, and right mouse click, you will see this option.

                           

                          Joop

                          • 10. Re: Java Code for adding the attributes
                            Dimitar Slavov-Oracle

                            Just right click on the model in the browser and choose "Update model with previously exported XLS". That's it.

                            • 11. Re: Java Code for adding the attributes
                              Niharika Nic

                              Yep Got it. Tested its working fine..

                               

                              Thanks for your great help. That was really very useful

                              • 12. Re: Java Code for adding the attributes
                                Niharika Nic

                                Hi Philip

                                 

                                The above link was helpful to fix the issue.

                                 

                                Can you please help me in the below issue

                                 

                                The requirement is:

                                 

                                I have 1000+ Entities with attributes in the Logical model.

                                Now I need to add 5-7 new attributes for all the 1000+ existing entities.

                                 

                                Regards,

                                Niharika

                                • 13. Re: Java Code for adding the attributes
                                  Philip Stoyanov-Oracle

                                  Hi Niharika,

                                   

                                  thee is a transformation script that does that for relational model and tables - it's called 'table template ...". Here is a quick modification of that script for logical model and entities. You need to create one entity named "entity_template" add attributes to it and script will propagate them to other entities in the model.

                                   

                                  Philip

                                   

                                   

                                  var t_name = "entity_template";
                                  template = model.getEntitySet().getByName(t_name);
                                  if(template!=null){
                                      tcolumns = template.getElements();
                                      tables = model.getEntitySet().toArray();
                                      for (var t = 0; t<tables.length;t++){
                                       table = tables[t];
                                      // compare name ignoring the case
                                       if(!table.getName().equalsIgnoreCase(t_name)){
                                           for (var i = 0; i < tcolumns.length; i++) {
                                              column = tcolumns[i];
                                              col = table.getElementByName(column.getName());
                                              if(col==null){
                                               col = table.createAttribute();
                                              }
                                              column.copy(col);
                                              table.setDirty(true);
                                           }
                                       }
                                      }
                                  }
                                  
                                  • 14. Re: Java Code for adding the attributes
                                    Niharika Nic

                                    Thanks Philip

                                    It is was working

                                     

                                    But can I add different attributes to different entities.. I mean all my entities are different.. I should add columns to the entities.. But all the column name differ from one to another..

                                     

                                    For Example:

                                     

                                    Entity Name is Employee, Job, Department

                                     

                                    Attribute names which I should add are

                                     

                                    Employee I want to add Last Updated date, Last updated by.

                                    Job I want Job Employee Id

                                    Department Department Name

                                     

                                    In the above scenario the attribute names are not matching each other is this possible??

                                     

                                    Regards,

                                    Niharika

                                    1 2 Previous Next