Creating the database applications

The procedures for building the six database applications are described in these sections:

After building the database applications, you will create an event using the Event wizard to link the SOList application with the SOItemList application as shown in “Creating an event for SOList”.

Building the SOList application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.
On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:
- Connection Cache Name – select sampledb from the drop-down list.
- SQL Query String –
```
select id as SalesOrderID, cust_id as CustomerID, left(order_date, 10) as OrderDate, region as Region from sales_order where sales_rep = @OP["SalesRepID"="299"]
```
- UI XSLT – leave this field empty.
Click Next.
Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels.” Accept the 1 in “Labels are displayed in Record,” then click Next.
In the Filter window, click Next.
In the Configure Parameters window:
1. Under Variable, select the box next to SalesRepID.
2. Under Personalize, select Database Table from the drop-down list.
3. Under Key, select XYZSalesRepID.
4. Click Next.
In the Preview window, in Element Name, enter SOList.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter SOList.

Roles tab Click Add All to add all roles to this application.
Click Finish to save the application, and click OK to confirm.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The SOList application displays in the detail pane.
In Mobile Web Studio, right-click the SOList application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved SOList application in the detail pane.

Building the SOItemList application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.
On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:
- Connection Cache Name – select sampledb from the drop-down list.
- SQL Query String –
```
select id as SalesOrderID, line_id as LineItemID, prod_id as ProductID, quantity as Quantity, left(ship_date, 10) as ShipDate from sales_order_items 
where SalesOrderID = @OP["SalesOrderID"="2001"]
```
- UI XSLT – leave this field empty.
Click Next.
Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels. Accept the 1 in the Labels are displayed in Record option, then click Next.
In the Filter window, click Next.
In the Configure Parameters window, under Variable select the checkbox next to SalesOrderID.
In the Preview window, in Element Name, enter SOItemList.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter SOItemList.

Roles tab Click Add All to add all roles to this application.
Click Finish to save the application, and click OK to confirm.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The SOItemList application displays in the detail pane.
In Mobile Web Studio, right-click the SOItemList application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved SOItemList application in the detail pane.

Building the CustomerList application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.
On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:
- Connection Cache Name – select sampledb from the drop-down list.
- SQL Query String –
```
select id as CustomerID, fname as FirstName, lname as LastName, address as Address, city as City, state as State, zip as PostalCode, phone as Telephone, company_name as CompanyName from customer
```
- UI XSLT – leave this field empty.
Click Next.
Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels.” Enter 1 in “Labels are displayed in Record,” then click Next.
In the Filter window, click Next.
In the Preview window, in Element Name, enter CustomerList.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter CustomerList.

Roles tab Click Add All to add all roles to this application.
Click Finish to save the application, and click OK to confirm.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The CustomerList application displays in the detail pane.
In Mobile Web Studio, right-click the CustomerList application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved CustomerList application in the detail pane.

Building the ProductList application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.
On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:
- Connection Cache Name – select sampledb from the drop-down list.
- SQL Query String –
```
select id as ProductID, name as Name, description as Description, prod_size as Size, color as Color, unit_price as UnitPrice from product
```
- UI XSLT – leave this field empty.
Click Next.
Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels.” Enter 1 in “Labels are displayed in Record,” then click Next.
In the Filter window, click Next.
In the Preview window, in Element Name, enter ProductList.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter ProductList.

Roles tab Click Add All to add all roles to this application.
Click Finish to save the application, and click OK to confirm.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The ProductList application displays in the detail pane.
In Mobile Web Studio, right-click the ProductList application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved ProductList application in the detail pane.

Building the SOCreate application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.

On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:

Connection Cache Name – select sampledb from the drop-down list.

SQL Query String –

begin
declare @SalesOrderID int;
select max("id")+1 into @SalesOrderID from sales_order;
if @SalesOrderID is null then
select 1 into @SalesOrderID;
end if;
insert into sales_order ("id", cust_id, order_date, fin_code_id, region, sales_rep) values (@SalesOrderID, @OP["CustomerID"="101"], '@OP["OrderDate"="2006-01-01"]', 'r1', '@OP["Region"="Eastern"]', @OP["SalesRepID"="299"]);
select @SalesOrderID as SalesOrderID;
end

UI XSLT – leave this field empty.

Click Next.

Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels.” Enter 1 in “Labels are displayed in Record option,” then click Next.
In the Filter window, click Next.
In the Configure Parameters window:
1. Select all the parameters.
2. For the SalesRepID parameter, under Personalize, select Database Table from the drop-down list.
3. Under Key, select XYZSalesRepID.
4. Change the Kind for all parameters to Update.
5. Accept all values in the Default fields.
6. Click Next.
In the Preview window, in Element Name, enter SOCreate.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter SOCreate.

Roles tab Click Add All to add all roles to this application.

Click OK.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The SOCreate application displays in the detail pane.
In Mobile Web Studio, right-click the SOCreate application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved SOCreate application in the detail pane.

Building the SOItemCreate application

From Mobile Web Studio, select Applications in the left pane, select New in the Application Manager Status menu, and click the New button to launch Application Builder.
In the Application Builder, click the down arrow to the right of Add, and select Database Element.

On the Database Element Definition window, make sure the Connection Cache option is selected.

Enter:

Connection Cache Name – select sampledb from the drop-down list.

SQL Query String –

begin
declare @SalesOrderLineItemID int;
select max(line_id)+1 into @SalesOrderLineItemID from sales_order_items where "id" = @OP["SalesOrderID"="2001"];
if @SalesOrderLineItemID is null then
select 1 into @SalesOrderLineItemID;
end if;
insert into sales_order_items ("id", line_id, prod_id, quantity, ship_date) values (@OP["SalesOrderID"="2001"], @SalesOrderLineItemID, @OP["ProductID"="300"], @OP["Quantity"="1"], '@OP["ShipDate"="2006-01-01"]');
select @SalesOrderLineItemID as SalesOrderLineItemID;
end

UI XSLT – leave this field empty.

Click Next.

Use the Define window to identify record 1 as the header row. In the Define Record Layout section, select Rows and click “Records contain labels.” Enter 1 in “Labels are displayed in Record option,” then click Next.
In the Filter window, click Next.
In the Configure Parameters window:
1. Select all the parameters.
2. Change the Kind for all parameters to Update.
3. Click Next.
In the Preview window, in Element Name, enter SOItemCreate.

Click Finish.
In the Application Builder, click Save.
On the Finish window, make these entries (otherwise accept the defaults):

Content tab In Name, enter SOItemCreate.

Roles tab Click Add All to add all roles to this application.
Click Finish to save the application, and click OK to confirm.
Click Close in the upper-right corner to close the Application Builder window.
When you return to the Mobile Web Studio main window, select New from the Application Builder Status menu. The SOItemCreate application displays in the detail pane.
In Mobile Web Studio, right-click the SOItemCreate application in the detail pane, and select Approval Status | Approved.

Click OK.
Select Approved from the Application Manager Status menu. You see the newly approved SOItemCreate application in the detail pane.