Showing Parameters passed in the URL in a page – Oracle ADF

In ADF Application we can read the parameters passed in the URL and display them as an output text using a simple EL (Expression language).

#{param.paramName}

Sample Jspx page:

1

Page in browser:

2If your requirement is to show the URL passed in parameter data in the input text field and if there is no parameter data passed then show it as an input text field, then you can use below given EL which is having the ternary condition.

<af:inputText value=”#{param.dept eq null? bindings.DepartmentId.inputValue: param.dept}” />

Browser output when URL: 

http://127.0.0.1:7101/Application2-ViewController-context-root/faces/home.jspx

3.PNG

Browser output when URL: 

http://127.0.0.1:7101/Application2-ViewController-context-root/faces/home.jspx?dept=Marketing

4.PNG

 

This post is intended to show how the requirement can be fulfilled with the help of EL.

Advertisements

Invoke a WebService with the Inserted Rows data of Excel sheet in ADF-DI:

In ADF-DI you don’t have any direct option available to get the control over all the inserted rows to invoke WS (Web Service). So you must implement it with the help of programmatic approach.

First what you need to understand is, If you want to upload 100 records of data from Excel sheet to DB, first you must set a property [InsertBeforeRowActionID : ‘CreateInsert‘] which means before reading each row of data, a new empty row object is created and the data of that row is stored into that row object cache. The same thing happens for all the rows. Once all the rows of Excel sheet are read, the cached data has to be committed to DB. So set the property [CommitBatchActionID: Commit] which inserts the cached data to DB.

The above properties must be set to upload data to DB from Excel sheet. To achieve this your page definition file must contain the bindings [tree binding, Commit, CreateInsert bindings]

To get the Handle over the Excel sheet data, you must set below proprieties.
Property [InsertBeforeRowActionID : ‘eachRowInvokeAM‘] which is being used to Invoke a custom method where current row data can be read and stored to a List. This will repeat for all the rows.
Then set the property [SuccessActionID : afterAllRowsInvokeAM] for a ribbon command ‘Update‘. This triggers the custom method ‘afterAllRowsInvokeAM’ after successful Insert/Update to DB. In this custom method, you can work with the list which has the data of all rows inserted in the Update operation. Using this data you can Invoke a Web Service.

You must write your custom methods in AMImpl class. ADF12c supports only AMImpl class methods, where as ADF11g supports AMImpl & VOImpl class methods to invoke.

Eg: Let’s take an example, where we insert a record to Departments table. And after all the rows got inserted to DB from ADF-DI Excel sheet a webservice should be invoked with the inserted data as input. For this set the below properties.

InsertBeforeRowActionID : ‘CreateInsert’
InsertBeforeRowActionID : ‘eachRowInvokeAM’
InsertRowEnabled: True
CommitBatchActionID: Commit
RibbonCommand- Update -> SelectActionSet -> ActionOptions -> SuccessActionID : afterAllRowsInvokeAM

Custom methods given above are [‘eachRowInvokeAM’, ‘afterAllRowsInvokeAM’] are mehtods created in AMImpl class .
Another class also created ‘Dept’ which is a Pojo class with the column names as variables and having parameterized constructor.
In the AMImpl class, a list is created of type ‘Dept’ pojo class. And the parameterized constructor is being invoked to insert the department type object to the list. Two custom methods are created in it [‘eachRowInvokeAM’, ‘afterAllRowsInvokeAM’] and are exposed and then added to the page definition file as method bindings. During each row insert ‘eachRowInvokeAM’ will be invoked and that row data in inserted to the List. Once the commit operation is susccessful another method ‘afterAllRowsInvokeAM’ will be invoked where the list data is read and used as input to Invoke the webservice.

The page definition file should be:

ADF-DI_1.PNG

Excel properties should be:

ADF-DI_3

ADF-DI_2

 

AmImpl.java:


package model;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;

import java.net.HttpURLConnection;
import java.net.URL;

import java.util.ArrayList;
import java.util.List;

import model.common.AppModule;

import oracle.jbo.Row;
import oracle.jbo.server.ApplicationModuleImpl;
import oracle.jbo.server.ViewLinkImpl;
import oracle.jbo.server.ViewObjectImpl;

public class AppModuleImpl extends ApplicationModuleImpl implements AppModule {

/* Custom Code */
List dept = new ArrayList();
public void eachRowInvokeAM(){
System.out.println("Entered before Upload..");
System.out.println("row count:"+getDepartmentsView1().getRowCount());
Row r = getDepartmentsView1().getCurrentRow();
System.out.println(r.getAttribute("DepartmentId"));
System.out.println(r.getAttribute("DepartmentName"));
System.out.println(r.getAttribute("LocationId"));
System.out.println(r.getAttribute("ManagerId"));

int deptId = Integer.valueOf(r.getAttribute("DepartmentId").toString());
String deptName = r.getAttribute("DepartmentName").toString();
int locId = Integer.valueOf(r.getAttribute("LocationId").toString());
int mgrId = Integer.valueOf(r.getAttribute("ManagerId").toString());

//adding current row to list
dept.add(new Dept(deptId, deptName, locId, mgrId));
}

public void afterAllRowsInvokeAM() {
System.out.println("after Insert success..");
System.out.println("rows inserted:"+dept.size());
for(int i=0; i< dept.size(); i++){
System.out.println(dept.get(i).getDepartmentId());
System.out.println(dept.get(i).getDepartmentName());
System.out.println(dept.get(i).getLocationId());
System.out.println(dept.get(i).getManagerId());
}
System.out.println("Now Invoking WebService");
try {
http_client();  // Invoke Webserive method
} catch (Exception e) {
}
}

public static void http_client() throws Exception {
System.out.println("Invoke service using direct HTTP call with Basic Auth");
String payload =
"\n" +
" \n" +
" \n" +
" \n" +
" INR\n" +
" USD\n" +
" \n" +
" \n" +
"";

httpPost("http://www.webservicex.net/CurrencyConvertor.asmx" + "?invoke=", payload);
}
private static String httpPost(String destUrl, String postData) throws Exception {
URL url = new URL(destUrl);
HttpURLConnection conn = (HttpURLConnection)url.openConnection();
if (conn == null) {
return null;
}
conn.setRequestProperty("Content-Type", "text/xml; charset=UTF-8");
conn.setDoOutput(true);
conn.setDoInput(true);
conn.setUseCaches(false);
conn.setFollowRedirects(true);
conn.setAllowUserInteraction(false);
conn.setRequestMethod("POST");

//byte[] authBytes = authStr.getBytes("UTF-8");
//String auth = com.sun.org.apache.xml.internal.security.utils.Base64.encode(authBytes);
// conn.setRequestProperty("Authorization", "Basic " + auth);

System.out.println("post data size:" + postData.length());

OutputStream out = conn.getOutputStream();
OutputStreamWriter writer = new OutputStreamWriter(out, "UTF-8");
writer.write(postData);
writer.close();
out.close();

System.out.println("connection status: " + conn.getResponseCode() +
"; connection response: " +
conn.getResponseMessage());

InputStream in = conn.getInputStream();
InputStreamReader iReader = new InputStreamReader(in);
BufferedReader bReader = new BufferedReader(iReader);

String line;
String response = "";
System.out.println("==================Service response: ================ ");
while ((line = bReader.readLine()) != null) {
System.out.println(line);
response += line;
}
iReader.close();
bReader.close();
in.close();
conn.disconnect();

return response;
}

Dept.java:

</pre>
package model; public class Dept { int DepartmentId; String DepartmentName; int LocationId; int ManagerId; public Dept(int DeptId, String DeptName, int LocId, int MgrId) { //super(); this.DepartmentId = DeptId; this.DepartmentName = DeptName; this.LocationId = LocId; this.ManagerId = MgrId; } public void setDepartmentId(int DepartmentId) { this.DepartmentId = DepartmentId; } public int getDepartmentId() { return DepartmentId; } public void setDepartmentName(String DepartmentName) { this.DepartmentName = DepartmentName; } public String getDepartmentName() { return DepartmentName; } public void setLocationId(int LocationId) { this.LocationId = LocationId; } public int getLocationId() { return LocationId; } public void setManagerId(int ManagerId) { this.ManagerId = ManagerId; } public int getManagerId() { return ManagerId; } } 

Your Excel in Design Time:

ADF-DI_4.PNG

Your Excel at run time:

ADF-DI_5


After Click on Ribbon button ‘Upload’, all the rows inserted to DB and also a web service is invoked at back end.

ADF-DI_6

Hope the post is Informative!

ADF Desktop Integration – showing table data in Excel workbook

This post explains how to to display table data in the Excel sheet using ADF Desktop Integration.

We’ll be discussing below items

  • Installing ADF- DI extension
  • Creating ADF Application
  • Working with Excel workbook

Installing ADF- DI extension:

Prerequisites: Before proceeding with ADF-DI extension installation first your system should have below soft wares installed, otherwise you end up getting an error when you try to install ADF-DI extension.

  • Microsoft .NET Framework 4
  • Microsoft Visual Studio 2010 Tools for Office Runtime
  • MS Office [I’ve used 2013 version]

Now go to JDeveloper -> ‘Tools’ -> Install ADF Desktop Integration.

Creating ADF Application:

  • Create Fusion Web Application (ADF). Application Name: ADFApp2, project names (keep default as model and viewcontroller)
  • Right click on Model project and create ‘Business Components from Tables’, crete DB connection and select ‘Departments’ table. Drag this table to right pane for updatableVO.
  • Right click on ViewController project and create JSPX page (dept.jspx)
  • Drag and drop the Deaprtments datacontrols from datacontrol section on to the dept.jspx page as table. (which will creates deptPagDef.xml file and along with tree binding in it)
  • Right click on viewcontroller project and create Microsoft Excel Workbook from client tier. (give Name: dept-DT)

6

7

8

Working with Excel workbook:

  • Double click on the excel file and it’ll prompt to select a page definition file from the available all page definition files in the project. Select view_deptPageDef file and click OK.

9

  • You can see a tree binding (DepartmentsView1) under Binidngs section of the Excel workbook.

10

  • Drag and drop the tree binding onto the cell and select ‘ADF Read-only Table’ -> OK.

11

  • It’ll open workbook properties window, there just click on OK. (we’ll configure it later)

12

  • Now you can see all the tree bindings in the Excel file.

13

  • in Jdev Right click on ViewController project -> project properties -> Java EE Application -> set ‘Java EE WebContext Root’ as ‘departments’ -> ok.

14

15

  • In excel work book click on worksheet properties and configure there to display data.

Worksheet properties -> Events […] -> Add -> StartUp -> ActionSet -> Actions […] -> Add button (Component Action) -> select Download -> OK -> OK -> OK.

16

17

18

19

20

21

  • In Jdev Right click on dept.jspx page and click on Run.
  • In excel workbook under oracle ADF menu tab, click on Run.

22

  • You can see the table data in the excel sheet as below. After review click on Stop and close the excel.

23

Hope You enjoyed the post!

In future will come up with more posts related to ADF-DI ‘update records in excel and upload to DB’ , ‘WebService Integration’ etc.

Disable few items in Select One Choice LOV in Oracle ADF

This post covers how to disable certain items in selectOneChoice LOV based on certain condition.

Existing code:

<af:selectOneChoice value="#{bindings.DepartmentId.inputValue}" 
label="#{bindings.DepartmentId.label}"
required="#{bindings.DepartmentId.hints.mandatory}"
shortDesc="#{bindings.DepartmentId.hints.tooltip}" id="soc1">
<f:selectItems value="#{bindings.DepartmentId.items}" id="si1"/>
</af:selectOneChoice>

Replace with:

<af:selectOneChoice value="#{bindings.DepartmentId.inputValue}" 
label="#{bindings.DepartmentId.label}"
required="#{bindings.DepartmentId.hints.mandatory}"
shortDesc="#{bindings.DepartmentId.hints.tooltip}" id="soc1">
<af:forEach items="#{bindings.DepartmentsView1.rangeSet}" var="list">
<af:selectItem label="#{list.DepartmentName}" id="si1" value="#{list.DepartmentId}"
disabled="#{list.DepartmentId eq 10 or list.DepartmentId eq 20 or list.DepartmentId eq 30}"/>
</af:forEach>
</af:selectOneChoice>

 

Reference:

https://mjabr.wordpress.com/2012/01/23/how-to-make-some-lov-items-non-selectable/

 

 

Using Partial Triggers in Oracle Applications Cloud, Oracle Fusion Applications

This post explains how can we refresh a component when another component value is changed. You must be already aware that this can be achieved easily in ADF code by (a) setting the partial Triggers property of target component to the ID of source component & (b) setting the autoSubmit property of source component to ‘true‘.

But in Oracle Applications Cloud, Oracle Fusion Applications we won’t be having access to the source code and we need to implement it from the browser itself using customization feature.

partial triggers property is available for customization, but we need to find the ID of the source component which is not displayed anywhere to us. So to get the ID of the source component click on F12 (browser Inspect Element) then navigate to the source component and click on it. You can find the ID of that component over there.

What ever discussed above, let’s see step by step.

  • Login to Oracle Applications Cloud

1

  • You’ll be taken to the home page.

2

  • Select the sandbox if not selected any from the ‘Manage Sandboxes’ link (you can create a new sandbox also and its mandatory to select one sandbox before we proceed doing the customization to the application)

3

  • After selecting sandbox, click on ‘Set as Active’

4

  • Now navigate to the page where you wanted to do customization (eg: like in Order Management -> Orders on Hold page). Here let’s take a requirement that on selecting some value in ‘Hold Name’ LOV, the ‘Release comments’ component should display some custom message.

5

  • Now click on Inspect element (F12) and click on source attribute to find the ID of it. Copy the ID  [if source and destination components present inside same parent component then the last part if ID will be sufficient].

6

  • Click on Customize Pages link which you can get from user drop down.

7

  • Go to the Structure tab  and click on required component to customize

8

Note: If component is present inside a popup then click on button which brings the popup and in the component structure scroll down little where you can see multiple popups. Click on each popup and edit and read the ‘popup fetch listener’ property to understand why that popup is being used and check it is the correct one that we need to edit. After you find required popup if the content inside that is not visible, then set the ‘Content Delivery’ property from ‘lazyUncached’ to ‘Immediate’, then the content inside popup will be available to edit.

  • Click on source component -> If any popup comes select Edit -> Click Edit icon or right click on component at source code and click on Edit -> Set the ‘autoSubmit’ property to ‘true’

9

  • Click on Destination component and click on Edit and set the ‘partialTriggers’ to the ID which we copied earlier. [don’t set the entire path of ID, if source and destination components present inside same parent component then the last part if ID will be sufficient]. For the Value property set the EL logic as [#{bindings.HoldNameHeader.inputValue eq ‘CUSTOM HOLD’? ‘This is a custom Hold.’: bindings.HoldReleaseComments.inputValue] which means when ever ‘CUSTOM HOLD’ is selected in the LOV then below target component shows the message as ‘This is a custom Hold.’, and whenever other value is selected it won’t show any message and user can enter text over there. 

10

  • Now close the customization and you are ready to test. Select ‘CUSTOM HOLD’ from Hold Name LOV, then Release Comments shows the message as ‘This is a custom Hold‘. If you select other value then input text will be empty and editable. Now exit from the sandbox.

11

Hope you enjoyed the post 🙂

 

BPEL Creation with DB Adapter usage and consuming it in ADF application as webservice

Story of Project:  We are going to pass the employee id from the ADF web application to SOA BPEL, which fetches more details of an employee from the database and returns them to ADF which are then shown on the ADF page. Tool used for this project is JDev 12.1.3

This Post includes the following

  • SOA Application creation
  • DB Adapter Configuration (for connecting to Database)
  • BPEL Creation (WSDL generation)
  • ADF Application Creation
  • Consuming the WSDL and create data controls
  • Test from the ADF Application

SOA Application Cretion:

Create a SOA Application with name ‘SOA_fetchDBdataApp’

Snap2

Create a Project ‘FetchEmpDataProj’ and select the Composite With BPEL Process while project creation

Snap3

Create a BPEL with the name ‘fetchEmpDetailsBPEL’ and select Template as Synchronous BPEL Process and then click OK

Snap4

The BPEL will be created with receiveInput and replyOutput as default schema components.

Snap17

Navigate to Databases tab and create a new Database Connection with the name with which we created in console

Snap21

Now Navigate the Database Adapter to the bpel flow

Note: Before proceeding from this step you should configure your DB Adapter in the console first. Please go through the DB Adapter configuration steps given at the end of the post.

Snap22

Give the dbreference name and click on Next

Snap23

Click on Search Icon and select the created Database Connection and click on Copy Connection

Snap24

Check only Select check box as we are doing only select operation in this example and click on Next

Snap25

Click on Query button to select the existing table in DB and move it to the selected area by clicking on right single row.

Snap26

Now remove the unneccessary  relationships by selecting each and clicking on remove button

Snap27

Click on Add button for creating a bind variable to the query which basically used to pass a variable dynamically. Generally to apply where condition its been used

Snap28

Click on Edit button beside to query click on Add button and then Check the Parameter option and select the created parameter ’empId’ from dropdown

Snap29

You can observer the modified SQL query

Snap30

Click on Next and finally Click on Finish

Snap31

Drag and drop the Invoke BPEL Constructor below to input and map to the dbreference

Snap34

Configure the Input and Output for this DB adapter. Click on + symbol and give names to the input and output variables.

Snap36

Now drag and drop the Assign Constructor below to input

Snap37

Double click on Assign which opens a Edit Assign popup in which map the input variable value to input of database adapter and click on Apply and OK

Snap38

Now assign the output of DB Adapter to the reply output of BPEL.  For that use Assign constructor.  Here the output of DB adapter has many fields so create as many outputs in the schema also to map from DB Output to BPEL output.

Open the schema file (.xsd file) and add the the required inputs and outputs in its source view

Snap35

Now drag and drop the Assign Constructor to below to DB adapter to  map the output of DB Adapter to BPEL output.

Snap40

And then double click on it for mapping. a popup window opens where map the output of DB adapter to output variable of BPEL. Map all fields directly. There is field named ‘EmpName’ at the output section for which the input should be from two fields (firstname, lastname) so drag and drop the Expression icon to the output field ‘EmpName’

Snap41

On Drag of Expression onto field a popup opens where click on concat and then click on first name and last name. The format should be concat(val1, val2) which combines the output of these two fields  and stores in EmpName field.

Snap42

Finally click on Apply and OK

Snap43

Deployment:

Save the project and right click on project-> Deploy

Snap44

Click on Deploy to Application Server.

Check the box Overwrite any existing composites with the same revision ID. which basically useful for generating same wsdl url for every deployment. Click on Next

Snap46

Click on Integrated weblogic server. Here you can add any server and deploy to that.

Select the domain (Default Server) and click on Finish which finishes the deployment.

Snap47

Open Enterprise Manager on browser. Select the project and click on Test

Snap51

Copy the WSDL to clipboard. Give the input as 102 and click on Test Web Service

Snap52

It opens the Response tab with the result. you can Launch Flow Trace to view the complete step by step response

Snap63

ADF Application Creation:

Create ADF Fusion Web Application ‘ADF_SOA_App’ keep the default names for the project.

Snap64

Create a custom project ‘consumeEmpWS’

Snap65

Right click on the created project -> New -> Select Web Service Client and Proxy

Snap66

Paste the copied WSDL URL in the WSDL Document URL field and click on Next and proceed by keeping default settings and Finish.

Right click on the WSDL and click on Test Web Service which opens a Http Analyzer.

Snap69

Give the input and click on Send Request which returns Response at right side.

Snap70

Now right click on the WSDL file and click on Generate Data Controls which generates the data controls which can be used on in the ADF page.

Snap72

You can see the updated data Controls Section

Snap73

Now Create a jspx page under View Controller Project.

Snap74

Check the JSP XML and name it ‘getDetails.jspx’

Snap79

Go to Source code of the page and then drag and drop the web service data controls on to the center facet of the page and select the option as ADF Parameter Form.

Snap76

Now right click on the page and click on Run

Snap77

In the page pass the Employee Id and you can see the more details which are fetched through BPEL and displayed on the page.

Snap78

DB Adapter Configuration:

This includes creation of Data Source, DB Adapter Outbound Connection Creation, mapping created Outbound Connection Creation with created data source.

Open Console and Create Data Source for connecting to the database.

Snap11

Create a New Generic Data Source

Snap13

Give the connection name as ‘HRConn’ and JNDI name as ‘jdbc/HRConnDS’ for the data source

Snap14

Give the Database Connection details and click on Next

Snap15

Click on Test Configuration which shows a success message for the successful connection. Click on Finish.

Snap16

Click on DbAdapter from the deployments

Snap5

Click on Configuration -> Outbound Connection pools -> New and then create an outbound connection

Snap7

Click on Next

Snap8

Give the JNDI name for the Outbound connection as eis/DB/HRConn

Snap20

Click on the Created Outbound Connection Pool

Snap9

Give the property  value for XA Data Source name as with earlier created DataSource jndi name ‘jdbc/HRConnDS’ and click on save

Snap61

Now again go to deployments and select DbAdapter and click on update which ends the DB Adapter Configuration.

Snap62

Database Modeling in JDeveloper

In this post am gonna discuss about online and offline database modelling.

well database modelling means managing/modifying the database objects (tables, views, etc).

Online Database Modelling: For doing this generally we create a Database diagram to visualize, create, or inspect tables, views, synonyms, or sequences. After creating the database diagram we can add the database objects to it and then we can edit them, which will directly reflect in the database. No need to execute any commands to do changes in database, we can do the changes that we want.. in the database diagram only. which provides UI to do many operations (rename, data type changes, size changes etc).

Offline Database Modelling: This is similar to online database modelling but the only difference is the changes won’t reflect in the database until we synchronize the changes we made with the current object in the database. well this is very useful as we can do the modelling as like we want and modify at any time before synchronizing with the database.

Lets discuss each of them in detail below

In detail – Online Database Modelling :

Am gonna explain it with an example.

1) Create a Fusion Web Application (ADF ) or Generic Application

2) Create a project

3) Create a database diagram under the project

Snap1

4) Create a New Database Connection

Snap2

5)  Connect to the HR Schema

Snap4

6) Right click on the database diagram that we created and select Add to Diagram and then select the required database object on which you wanted to do the changes.

Snap6

Snap7

7)  You can directly open the Database Navigator and then drag and drop the required objects to the diagram. while dragging to the diagram select the Leave objects the in the Database Connection check box

Snap8

Snap5

8)  Similarly you can do it for other db objects (Employees table) also. After adding the required DB objects to the diagram optimize the height and width of the objects to get a clear view. To do this select a DB Object, right click on it -> Optimize Shape Size -> Height and Width

Snap9

Snap10

9) You can change the header color of an object from the Inspect Element to get a clear view when there are more objects in the diagram. (you can also use different colors for views, tables to differentiate them clearly)

Snap11

10)  Now lets start modelling the database objects. Select a DB object Departments and double click on it which opens a popup.

Snap12

11) Change the Size of the Department_name column from 30 to 38 and click on ok

Snap13

12) The change we made here will reflect in the database, so this procedure is called Online database modelling. To check the changes in the database select Tools -> Database -> SQL Worksheet -> Select the created Connection name.

Snap14

13) Execute the command desc departments;  and observe the size of the department_name column which is changed to 38.

Snap15

14) Similarly you can rename the table, change the properties of the column etc..

In detail – Offline Database Modelling :

1) Create a project and then drag and drop the DB Objects (Departments, Employees) to the diagram.  Select the Copy Objects to Project and click on OK

Snap18

2) You can observe the structure of the project now…which creates a folder and keeps the selected DB objects under it.

Snap19

3) After adding the required DB objects to the diagram optimize the height and width of the objects to get a clear view. To do this select a DB Object, right click on it -> Optimize Shape Size -> Height and Width

Snap9

Snap10

4) You can change the header color of an object from the Inspect Element to get a clear view when there are more objects in the diagram. (you can also use different colors for views, tables to differentiate them clearly)

Snap11

5) Now lets start modelling the database objects offline. Select a DB object Departments and double click on it which opens a popup.

Snap12

6) Change the Size of the Department_name column from 45 and click on ok

Snap20

7) The change we made here won’t reflect in the database as we are doing it offline. To check the changes in the database select Tools -> Database -> SQL Worksheet -> Select the created Connection name.

Snap14

8) Execute the command desc departments;  and observe the size of the department_name column which is  still showing old value…not changed to the new value 45.

Snap155

9) In order to reflect the changes  in the database that we  made in diagram, we have to synchronize the changes with DB object. Select the required DB object in the diagram right click and select Synchronize with Database -> Generate To -> SQL script which opens a popup with few radio buttons to select what type of query to generate which is explained below.

Create: On selection of this option tool provides the queries to create the current DB Object with the changes we made

Replace: On selection of this option tool provides sql commands to delete the current DB object and also to create it again with the changes we made

Alter:  On selection of this option tool provides sql commands to alter the changes we made to the existing DB object.

10) Select the Alter check box and check the Manual Reconcile option under ALTER check box which helps us to view what changes we made and show the differences between diagram DB object and Database DB object which helps us to cross verify before updating changes to database.

Snap25

11) In the next screen you can see the changes we made to the DB Objects.

Snap26

12) Click on the top right image to view the only differences between DB objects

Snap27

13) After verifying the changes go Back and un check Manual Reconcile and click Next and FinishSnap28

14) Now you can see the generated alter sql command. Execute it to reflect the changes in the database

Snap29

This is how Offline and Online database modelling works.

15) while working with offline db objects at any point of time you can get the latest DB object from the database by simply right clicking on DB object -> Synchronize with Database -> Select Refresh from DB connection name.

Snap30

Let’s discuss about creating a new database object (new table) offline and then Reconcile with the database

Creating New Database objects:

1) Open component palette and drag n drop the table to the database diagram

Snap32

2) Give the table name and create columns by specifying their names and datatypes. double click on the fields allows to create a new column.

Snap33

3)Double click on the column to make it as primary key or to set as not null, which opens a popup. Select the Primary Key in the side menu and move the required column to selected section to make it as primary key.

Snap34

4) After setting a column as primary key a record will be displayed in key relationship section of the DB object in the diagram

Snap36

5) Now Synchronize with the database. Right click and select synchronize with database -> Generate to-> SQL Script which opens a popup -> select Create check box -> Next -> FinishSnap37

6) Click on Finish generates an SQL query for creating the table in database

Snap38

7) Execute the query in by connecting to the database which creates a table in the database.

In this post till now we discussed about

  • Database modelling
  • Creating a schema model (adding existing schema objects to the database diagram)
  • Modify the schema model
  • Reconcile the modifications to the database (save to database)
  • Import database object definitions to an offline database
  • Reconcile the offline database objects with the database (save to database)
  • Creating New offline database objects and Reconcile with the database