Introduction

This document contains an application development scenario that shows how a simple application (the "CustomerService application") is developed using Informix Object Translator and Microsoft Visual Basic. This example application allows bookstore personnel to track customer orders.

This document contains the following information:

• A description of the database information that the sample application accesses.
• Information about how end users (bookstore personnel) will use the application.
• A list of tools used to develop the application.
• Information about and procedures for the following:
• Launching Object Translator.
• Using Object Translator to create a data model, map application objects, and generate Visual Basic class code for each map object.
• Creating and Deploying the DLL on Microsoft Transaction Server (MTS).
• Running the completed application.

The application accesses data from these database tables: CUSTOMERS, ORDERS, and ORDER_LINES.

CUSTOMERS Table

The CUSTOMERS table contains information about bookstore customers and how to contact them. The table has the following columns:
 
 

CUST_ID	Unique ID for the customer. This column is the primary-key column for the CUSTOMERS table; the primary key is used to identify each customer record in the table.
NAME	Customer name.
SERVICE_REP	Employee ID for the service representative assigned to the customer.
CONTACT	Name of the contact person. This can be the customer name. If the customer is a business, this is the contact person for the business.
POSITION	Position held by the contact person.
PHONE	Customer phone number.
FAX	Customer fax number.
ADDRESS1	First line of the customer’s address.
ADDRESS2	Second line of the customer’s address.
CITY	City of the customer’s address.
STATE	State of the customer’s address.
ZIP	Zip code of the customer’s address.

Note: In a real database design, there would probably be a separate table to store the SERVICE_REP and related information about the service representative, because a customer could have multiple orders with different representatives assigned to each order.

ORDERS Table

The ORDERS table contains information about each order. The information includes when the order was placed and the shipping address for the order. This table has the following columns:
 
 

ORDER_ID	Unique ID for the order. This column is the primary key for the ORDERS table; the primary key is used to identify each record in the table.
CUST_ID	ID for the customer who placed the order. In the database, this column is a foreign-key column that references the primary-key column in the CUSTOMERS table.
DATE_ENTERED	Date when the order was placed.
DATE_SHIPPED	Date when the order was shipped.
DISCOUNT	Discount information for the order, if applicable.
TAX_RATE	Tax rate for the order.
SHIPPING	Shipping cost associated with the order.
S_NAME	Name of the person to whom the order was shipped.
S_ADDRESS1	First line of the shipping address.
S_ADDRESS2	Second line of the shipping address.
S_CITY	City of the shipping address.
S_STATE	State of the shipping address.
S_ZIP	Zip code of the shipping address.
SHIP_VIA	Method of shipment.
NOTES	Any notes about the shipment.

ORDER_LINES Table

The ORDER_LINES table contains information about each item in a particular order. This table has the following columns:
 
 

OL_PK	Unique ID for the item. This column is the primary key for the ORDER_LINES table; the primary key is used to identify each record in the table.
ORDER_ID	ID that uniquely identifies the order associated with this order_line. In the database, this column is a foreign-key column that references the primary-key column in the ORDERS table.
TITLE_ID	ID that identifies the item. In our sample application, this is the title of the book (for example, "MobyDick"). This column is a foreign-key column that uniquely identifies the ordered item.
LINE_NUMBER	Line number of the item.
QTY_ORDERED	Quantity of the item that has been ordered.
QTY_SHIPPED	Quantity of the item that has been shipped.

How Bookstore Personnel Use the Application

Bookstore personnel access the CustomerService application via a client application that triggers a DLL that has been deployed on Microsoft Transaction Server (MTS). From the client application, bookstore personnel can perform the following operations:

• Insert new customers.
• Query the database for information on customers.
• Modify customer information, such as the customer's name, address, or phone number.
• Delete a customer record.
• Insert a new order.
• Query the database for information on an order.
• Modify order information.
• Delete an order.
• Insert a line of information about an ordered item.
• Query the database for information on a line item in the order.
• Modify line-item information, for example, the quantity ordered or shipped.
• Delete a line item from an order.

The following tools were used to develop the CustomerService application:

• Microsoft Visual Basic
• A database running on an Informix database server
• Informix Object Translator

Launching and Using Informix Object Translator

Because Object Translator was designed to work seamlessly with Microsoft Visual Basic, you can access Object Translator as a Visual Basic add-in. After you start Visual Basic, launch Object Translator by selecting Informix Object Translator->Object Translator from the Add-Ins menu.

After you launch Object Translator, the Object Translator console appears. Use the menu and toolbar commands on the Object Translator window to start a project, save a project, open an existing project, and launch the Model Viewer and Code Generator components of Object Translator.

In developing the sample CustomerService application, use the Object Translator GUI to perform these tasks:

• Launch the Model Viewer and use the Import Model wizard to create a data model.
• Mapping application objects.
• Launch the Code Generator and generate Visual Basic class code for each map object.

Creating a Data Model

The first step in creating the application is to use the Model Import wizard to reverse-engineer the database schema into an Object Translator data model. The data model is a snapshot of the database structure. Data-model-driven development allows:

• Disconnected development. You do not need to be connected to a database at design time, because the model already contains the database information.

 
• Optimized data access. You can use the information in the model to optimize the data-access process. For example, if two tables are related by a one-to-many (master-detail) relationship, related information can be used to automatically populate foreign-key values in the detail table based on primary key-values in the master table.

To access the Model Import wizard from the Object Translator:

1. Choose File->New Project.

 
2. In the New Project dialog box, click Import Model to display the Model Import wizard and the Model Viewer window.

The wizard guides you through the import process. You can choose the database elements (such as tables, columns, foreign keys, and stored procedures) that you want to import from the database. If the database does not have foreign keys defined or supported, the wizard infers relationships based on primary-key-column names. (These inferred relationships do not enforce referential integrity constraints; they only aid in data access.) See the What's This? help provided with the GUI for information about individual text boxes and buttons.

Because the CustomerService application uses only three tables, de-select all tables except CUSTOMERS, ORDERS, and ORDER_LINES during the import process.

The foreign keys imported from the sample database used for the CustomerService application are as follows:

• A many-to-one (M-1) relationship from CUSTOMER-EMPLOYEES through the SERVICE_REP–EMP_ID columns. (Many customers can have the same employee assigned to them as their support contact.)

 
• An M-1 relationship from ORDERS-CUSTOMER through the CUST_ID column. (Many orders can belong to the same customer.)

 
• An M-1 relationship from ORDER_LINES–ORDERS through the ORDER_ID column. (Many order lines can be part of the same order.)

Note: You can also use the Model Import wizard to refresh an existing data model with changes made in the database after the database schema was imported. You refresh a data model the same way you import one.

Mapping Application Objects

After you create or modify the data model, the data model is again displayed on the Data Model page in the Object Translator window. If you make additional changes to your data model using the Model Viewer, save those changes, and then refresh the data model displayed on the Data Model page. To do this, right-click the data model icon displayed on the Data Model pane and select Refresh from the popup menu.

Now you can use the Object Translator console to map the elements of the data model to the attributes, methods, and events of a language-independent object, called a map object.

In the Object Translator, each map object defines the mapping between an application object and the schema table/columns that are used to hold the application object's state. The only part of the mapping that is visible to you is the mapping between the application object and the map object. (The mapping between the map object and the schema is not visible.)

After you map application objects, you can view and modify the properties of the map object, as well as the properties of its attributes, methods, and events.

To map application objects:

1. Drag tables or columns from the data model displayed on the Data Model page to the Object Viewer pane. Each column becomes an attribute of the map object.

 
2. As necessary, click the object, or one of its attributes, methods, or events to see the properties for that element in the Properties Viewer pane.

For the CustomerService application, use Object Translator to create the following map objects (details provided below):

• Customer object
• Address object
• Orders and OrderLines objects

• Embedded object. When you embed an object, you insert the object into other objects that have the same column names as those defined in the embedded object. For example, if a table called Publishers contains columns named address1, address2, city, state, and zip, you can embed the Address object (containing attributes from the Customers table) into the Publishers object without having to add an attribute for each of the address fields. The Address object embedded inside the Customer object refers to the customer’s address and the Address object embedded inside the Publishers object refers to the publisher’s address.

 
• Collection object. A collection object represents the "many" side of a one-to-many relationship among nested objects. In our sample application, Orders and OrderLines objects are collection objects because the Customer object can contain multiple orders, which in turn can contain multiple line items.

Customer Object

To create a Customer object:

1. Click Object_1 in the Object Viewer pane.

 
2. Click in the Name property of the Properties Viewer pane and replace Object_1 with Customer.

 
3. Create the Customer map object by dragging the following columns in the CUSTOMERS table from the Data Model page to the Object Viewer pane:

• Name. Name of customer.
• Cust_id. Customer ID.
• Cserve_rep. ID for the service representative assigned to the customer.
• Contact. Customer's contact person.
• Position. Contact person's position.
• Phone. Phone number for the customer.
• Fax. Fax number for the customer.

Address Object

To create the Address object:

1. Right-click the Customer object on the Objects page of the Workspace pane and choose Create New Object.

 
2. Name the object Address and drag and drop the following columns from the CUSTOMERS table to the object:

• Address1. First line of the customer's address.
• Address2. Second line of the customer's address.
• City. City portion of the address.
• State. State portion of the address.
• Zip. Customer's zip code.

To create the Orders and OrderLines objects:

1. Create two objects and name them Orders and OrderLines.

 
2. Drag the ORDERS table to the Orders object and the ORDER_LINES table to the OrderLines object. Each table column becomes an attribute of the map object.

Defining the Relationships Among the Objects

To define relationships among the application objects:

1. Click the Customer object on the Objects page of the Workspace pane to display that object in the Object Viewer pane.

 
2. Right-click the Customer object in the Object Viewer pane and select Add Contained Object. This displays a dialog box that lists the map objects in the project.

 
3. Click the Address object (the object that  you want to contain) and choose to contain it as an embedded object. The Address object is now added to the list of the Customer object's attributes in the Object Viewer pane. An icon that shows two overlapping pages identifies the Address object as an embedded object.

 
4. Right-click the Customer object in the Object Viewer pane, select Add Contained Object, and add the Orders object as a collection object. The Orders object is now added to the list of the Customer object's attributes in the Object Viewer pane. A tree-structured icon identifies the Orders object as a collection object.

 
5. Click the Orders object on the Objects page of the Workspace pane to display that object in the Object Viewer pane.

 
6. Right-click the Orders object in the Object Viewer pane, select Add Contained Object, and add the OrderLines object as a collection object.

 
7. Click the Address object on the Objects page of the Workspace pane to display that object in the Object Viewer pane.

 
8. In the Properties Viewer pane, change the Embedded property for the Address object to True. The Object Viewer pane now displays the attributes of the Address object without listing the database table from which the attributes first originated. For example, the Customer.Address1 attribute is now displayed as Address1; this is because an embedded object can be embedded into any object that contains attributes of the same name as the attributes of the embedded object.

 
9. The Customer object now contains an Address embedded object and a collection of Orders objects, and each Orders object has a collection of OrderLines objects. Save these map objects as an .xml file by choosing File->Save Project.

After creating and saving the map objects, use Object Translator to generate code for the mapped objects. Do this by launching the Code Generator from the Object Translator console and completing the Code Generation wizard.

To launch the Code Generator, click the Code Generator toolbar button on the Object Translator window. See the What's This? help provided with the wizard for information about individual text boxes and buttons.

After you complete the wizard for the CustomerService scenario, the Code Generator generates the following Visual Basic classes:

• Address. bas
• Customer. bas
• Customer.cls
• CustomerCollection.cls
• CustomerOID.cls
• Orders.bas
• Orders.cls
• OrdersCollection.cls
• OrdersOID.cls
• OrderLines.bas
• OrderLines.cls
• OrderLinesCollection.cls
• OrderLinesOID.cls

Each generated Visual Basic class contains the methods and attributes needed to run on MTS. For each attribute of the map object, the Code Generator adds a corresponding attribute with the same name. The attributes are private by default.

For each map object, the Code Generator automatically adds getter/setter methods for accessing attribute values. In general, the data is returned in the native type (for example, the Cust_ID call returns the Cust_Id column as an integer).

Object Translator map objects have Restore, Store and, Destroy methods. During a restore, the object’s state is initialized from the database. At store time, the object’s state is inserted or updated in the database. During a destroy, the object is deleted from the database.

Note: Each object generated will have methods to get/set the values of its own attributes. However, each object, by default, has Restore, Store, and Destroy methods.

Creating and Deploying the DLL on Microsoft Transaction Server (MTS)

After you have used the Code Generator, you must create a DLL and deploy it on the computer running MTS. Any client computer running the CustomerService application can access the DLL; the DLL interacts with the database on behalf of the clients via the Object Translator-generated code.

To create the DLL, follow these steps:

1. Choose File->projectName.dll from the Microsoft Visual Basic menu.

 
2. Change the DLL's MTSTransactionmode property to any value except 1(NotAnMtsObject).

• Create a package for the DLL
• Register the DLL
• Add the DLL to your list of components
• Create the package for the client computer that allows the client application to access the DLL

1. On the computer hosting MTS, open Microsoft Transaction Server and go to Packages.

 
2. Right-click Packages and select New->Package.

 
3. Click the Create an Empty Package icon.

 
4. Type the name of the package (OrmPack) and click Next.

 
5. Click the Interactive User button and click Finish.

1. In MS-DOS, go to the directory that contains the DLL.

 
2. Invoke this command:

where filename is the name of the DLL, such as OrmMtsDll.dll.

1. Open Microsoft Transaction Server and go to Components.

 
2. Right-click Components and select New->Component.

 
3. Click the Import Components That Already Exist icon. All the DLLs in your Windows registry are displayed.

 
4. Select the DLL you want and click OK.

1. Open Microsoft Transaction Server and go to Packages.

 
2. Right-click OrmPack and select Export.

 
3. Choose the destination folder and type the name of the package (OrmClientPack).

 
4. Click Export. A package named OrmClientPack.exe is generated on the MTS computer.

 
5. Copy the OrmClientPack.exe package to your client computer and run the package. This makes an entry for the MTS DLL in the client computer's Windows registry.

Running the Created Application

Now the user can run the CustomerService application from Microsoft Visual Basic on the client computer. The user starts the application by clicking the Start button or pressing the F5 key.

The user uses the client application by clicking the following buttons:

• Query, to query the database for customer data.
• Save, to insert new customer data into the database.
• Update, to modify existing customer data.
• Delete, to delete a customer record.
• ReviewOrders, to display the first order from the OrdersCollection object for the customer so the customer can begin to review order records.

The DLL deployed on MTS has methods to store (insert and update), restore, and delete Customer, Orders, and OrderLines objects. Each of these methods access the corresponding methods in the Object Translator-generated code. See the source code for implementation details.

The user can perform these additional operations using the CustomerService application:

1. To insert a new customer record into the database, type the customer data (such as the customer ID, name, and phone number) into the various text fields and click Save.

 
2. To update a customer record, edit the text fields and click Update.

 
3. To delete a customer record, provide the ID for that customer and click Delete.

 
4. When you click the different buttons, the associated methods in the DLL deployed on MTS are executed and the form displayed on the screen changes accordingly.

 
5. To review all orders for a particular customer, click the ReviewOrders button to display the first order from the OrdersCollection object for the customer. Then use the navigation buttons to go to other orders, or click the ReviewOrderLines button to display the OrderLines form with the first OrderLines record from the OrderLinesCollection for the current Order object.

 
6. The Orders and the OrderLines forms have similar buttons, so you can query, save, update, and delete Orders and OrderLines records. You can also navigate through the collections of Orders and OrderLines.

Last updated June 23, 2000.