How to Use the Informix Object Translator Visual Basic Runtime Component

Informix Object Translator provides a persistence layer between an object-oriented application and the database. This means that application developers do not need to access the database directly using SQL. The Code Generator component of Object Translator generates classes, including methods to retrieve, insert, update, and destroy database data for those classes.

This document provides examples of how to use the Object Translator Visual Basic runtime component to perform the following tasks:

Work with simple objects
Work with collection objects
Work with embedded objects

Working with Simple Objects

This section provides examples that show how to work with simple objects in the following ways:

Instantiate an object that can connect to the database
Manipulate the database data associated with the object
Perform manual transactions

The examples in this section use the following classes, which were generated using the Object Translator GUI:

Customer. This class represents an individual instance of the object and contains database-access methods for the object. This class has getter/setter functions for all the attributes (database columns) in the object, plus Restore, Store and Destroy methods for the object as a whole.
CustomerOID. This class encapsulates all of the object's attributes with an OID property of True. This class has getter/setter functions for primary keys and a ToString function, which returns the condition to be used in an SQL WHERE clause.
CustomerCollection. This class represents a collection of Customer. This class contains functions that let you navigate among the objects in the collection.

For information on using the Object Translator GUI, see the online help provided with the GUI.

Instantiating a Data Object That Can Connect to the Database

To instantiate a data object that can connect to the database, follow these steps:

Instantiate a connection object
Connect to a database
Instantiate a data object
Assign the connection object to the object

Instantiating a Connection Object

Use the ORMapperConnection class to declare and instantiate a connection object, conn, as follows:

Dim conn As New ORMapperConnection

Connecting to a Database

Connect to a database using either ODBC or OLEDB.

To connect using ODBC, use this code:

    conn.DBMS = "ODBC"
    conn.DataSource = "your_datasource_name"
    conn.Database = "your_database_name"
    conn.UID = "your_user_ID"
    conn.Password = "your_password"
    conn.AutoCommit = True       ' transaction type
    conn.Connect

To connect using OLEDB Provider for Informix, use this code:

    conn.Provider = "Ifxoledbc.2"
    conn.DataSource = your_database@your_server
    conn.UID = "your_user_ID"
    conn.Password = "your_password"
    conn.AutoCommit = True       ' transaction type
    conn.connect

Instantiating a Data Object

Use the following code to instantiate a Customer data object, oCust, including instantiating its OID object, setting its primary key, and assigning its OID object to its OID property:

' Instantiate object of type Customer
Dim oCust as new Customer

'Instantiate object of type CustomerOID
Dim oCustOID as new CustomerOID

' Set the CustomerOID object's customer_num property to 100
oCustOID.customer_num = 100

' Assign the CustomerOID object to the Customer object's OID property
oCust.oid = oCustOID

Assigning the Connection Object to the Data Object

Use this code to assign the ORMapperConnection object to the Customer object's connection property:

oCust.connection = conn

Manipulating the Database Data Associated with the Data Object

After you complete the steps in the previous section to instantiate a data object that can connect to the database, you can manipulate the database data associated with that object in the following ways:

Restore (retrieve) the object's data from the database
Store (insert and update) data from the object to the database
Destroy (delete) the object's data from the database

Restoring an Object's Data

This section describes how to retrieve data for a single data object and for a collection of objects.

Single Objects

For the Customer object with a customer_num of 100, use that object's Restore method to retrieve all of the object's data from the database:

    oCustOID.customer_num = 100
    oCust.oid = oCustOID
    oCust.Restore

To retrieve the data for individual properties (for example, to populate user-interface items), use the Customer object's getter functions:

Text1.text = oCust.customer_num
Text2.text = oCust.name

Collection of Objects

To retrieve the data for a collection of objects from the database, create an instance of the CustomerCollection object (oCustColl) and call its Restore method with the selection criteria for the collection, as follows:

Dim oCustColl as CustomerCollection
oCustColl.Restore(conn, "customer_num > 100")

Use the FirstObject, NextObject, PreviousObject, and LastObject functions to navigate among the individual Customer objects in the CustomerCollection object.

Storing an Object's Data

This section describes how to use a data object's Store method to insert a new row into the database and to update an existing row.

Inserting Data

To insert a new Customer object's customer_num and name data into the database, set the appropriate object properties and then call the object's Store method. Note that for an insert, you do not use an instance of the CustomerOID object.

    oCust.customer_num = 100
    oCust.name = "Robert"
    oCust.Store

Updating Data

To update an object's database data, you must first retrieve the current data, set the appropriate properties to their new values, and then call the object's Store method, as follows:

    oCustOID.customer_num = 100
    oCust.oid = oCustOID
    oCust.Restore
    oCust.name = "Sam"
    oCust.Store

You can achieve the same result by calling the MarkChanged function with a value of True. Note that when you use the MarkChanged function, you do not use an instance of the CustomerOID object.

    oCust.customer_num = 100
    oCust.MarkChanged(True)
    oCust.name = "Sam"
    oCust.Store

Destroying an Object's Data

Use the Customer object's Destroy method to delete the database data associated with that object:

oCust.destroy

Performing Manual Transactions

A transaction is a collection of SQL statements that is treated as a single unit of work by the database server. You can instantiate a connection object in which transactions are managed by the Object Translator runtime library by setting the connection object's AutoCommit property to True (see the Instantiating a Connection Object section).

If you prefer to perform manual transactions, then you must set the connection object's AutoCommit property to False and provide the database-server transaction logic, as shown in the following example:

' Instantiate a transaction object
Dim oTrans As ORMapperTransaction

' Instantiate a connection object
Dim conn As New ORMapperConnection

' Connect to the database using ODBC
    conn.DBMS = "ODBC"
    conn.DataSource = "books"
    conn.Database = "books"
    conn.UID = "informix"
    conn.Password = "informix"
' Set the transaction type to False, for manual
    conn.AutoCommit = False
    conn.Connect

' Begin the manual transaction
Set oTrans = conn.CreateTransaction
oTrans.Begin
oCust.Connection = conn

' Insert data into the database
oCust.customer_num = 100
oCust.name = "Robert"
oCust.store

' Commit the transaction
oTrans.Commit

' Disconnect from the database
conn.Disconnect
Set conn = Nothing

Working with Collection Objects

Collection objects represent the "many" side of a one-to-many relationship among nested objects. In this section, for example, the Customer object can contain multiple Order objects, which in turn can contain multiple OrderLine objects, meaning that the Order objects and the OrderLine objects are collection objects.

You use the Object Translator GUI to generate the objects, and to specify that they are in a collection relationship. While defining each parent (Customer and Order), you can set their RestoreType, StoreType, and DestroyType properties to Deep or Shallow. If you choose Deep for both parent objects, then when your application calls the Customer object's Restore, Store, or Destroy methods, Object Translator also performs those operations on the Order and OrderLine objects. (For information on using the Object Translator GUI, see the online help provided with the GUI.)

The examples in this section show that you can override the GUI property settings (RestoreType, and so on) to fit your application's needs, by setting the DeepRestore, DeepStore, and DeepDestroy properties to True or False.

This section describes how to work with collection objects in the following ways:

Instantiate the objects needed for the examples in this section
Restore (retrieve) the data for the objects from the database
Store (insert and update) data from the object to the database
Destroy (delete) the data from the database associated with all the objects

Instantiating the Objects

Use this code to instantiate the objects needed for the examples in this section, and to assign the connection object to the Customer object:

    Dim oCust As new Customer
    Dim oCustOID As New CustomerOID
    Dim oOrders as new Orders
    Dim oOrdColl as new OrdersCollection
    Dim oOrdLns as new OrderLines
    Dim oOrdLnsColl as new OrderLinesCollection
    oCust.connection = conn

Restoring the Data for the Objects

After you instantiate the collection objects as shown in the previous section, you can restore data for the objects.

In the following code, setting the Customer object's DeepRestore property to True means that data is also retrieved for the Order objects when you call the Customer object's Restore method. If you set the property to False, then data is retrieved for a particular object only when you call the getter function of that object.

    oCustOID.customer_num = 26
    oCust.oid = oCustOID
    oCust.DeepRestore = True
    oCust.Restore

To retrieve the data for individual properties (for example, to populate user-interface items), use the getter functions:

Text1.text = oCust.name

This code retrieves an OrdersCollection object:

Set oOrdColl = oCust.Orders ' Returns OrdersCollection object

Use the FirstObject, NextObject, PreviousObject, and LastObject functions to navigate among the individual objects contained in the Orders collection object. For example, to retrieve the first Orders object, use this code:

Set oOrders = oOrdColl.FirstObject

Then you can retrieve data from that Orders object using a getter function:

Text1.text = oOrders.order_num

Storing the Data to the Database

After you have instantiated the collection objects, you can insert new data into the database and update existing data for those objects.

Inserting Data

The following code sets properties for instances of a Customer object, an Orders object, and an OrderLines object, in preparation for inserting new data into the database:

    oCust.Customer_num = 100
    oCust.name = "Robert"
    oOrders.Order_num = 1000
    oOrders.customer_num = 100
    oOrders.order_date = "03-03-1999"
    oOrders.paid_date = "04-04-1999"

    oOrdLns.ol_pk = 2000
    oOrdLns.Order_num = 1000
    oOrdLns.Qty_Ordered = 89
    oOrdLns.Qty_Shipped = 78

You can use the AddObject function to add any number of OrderLines objects to an OrderLinesCollection object, as follows:

oOrdLnsColl.AddObject(oOrdLns)

The following code adds Orders objects to an OrdersCollection object and assigns an OrdersCollection object to a Customer object. Because the DeepStore properties of the Customer object and the Orders object are set to True by default, calling the Customer object's Store method inserts the data for the Customer object, its Orders objects, and their OrderLines objects into the database:

    oOrdColl.addObject(oOrders)
    oCust.orders = oOrdColl
    oCust.store

To insert the data into only the Customer object (and not any collection objects it contains), use this code:

oCust.deepStore = False
oCust.store

Updating Data

To update data in the database, you must first retrieve the existing data, set the appropriate properties to their new values, and then store the data to the database. The following code shows how to retrieve the data for the Customer object (and its Orders objects) with a customer_num property of 26, and set the name property to Pete:

    oCustOID.customer_num = 26
    oCust.DeepRestore = True
    oCust.Restore
    oCust.name = "Pete"

The following code shows how to retrieve an OrdersCollection object, update the first Orders object, update the first OrderLines object, and write all the updated data for these objects to the database:

Set oOrdColl = oCust.Orders

    Set oOrders = oOrdColl.FirstObject
    If Not oOrders Is Nothing Then
        oOrders.order_date = "04-09-1999"
        oOrders.paid_date = "05-10-1999"
    End If

    Set oOrdLnsColl = oOrders.OrderLines
    Set oOrdLns = oOrdLnsColl.FirstObject
    If Not oOrdLns Is Nothing Then
        oOrdLns.Order_num = 1000
        oOrdLns.Qty_Ordered = 89
        oOrdLns.Qty_Shipped = 78
    End if

oCust.store ' update the database

Destroying Data from the Database

After you have instantiated the collection objects, you can destroy their data from the database. You must restore all the objects, set the DeepDestroy property to True for each object that is the parent of a collection object (for example, for the Customer object, because it is the parent of the Orders object), and then call the Destroy method, as follows:

    oCustOID.customer_num = 26
    oCust.oid = oCustOID
    oCust.DeepRestore = True
    oCust.Restore

    Set oOrdColl = oCust.Orders
    Set oOrders = oOrdColl.FirstObject
    While Not oOrders Is Nothing
        oOrders.DeepDestroy = True
        Set oOrdLnsColl = oOrders.Items
        Set oOrdLns = oOrdLnsColl.FirstObject
        While Not oOrdLns Is Nothing
             Set oOrdLns = oOrdLnsColl.NextObject
        Wend
        Set oOrders = oOrdColl.NextObject
    Wend

oCust.DeepDestroy = True
oCust.Destroy

Working with Embedded Objects

Embedded objects have an aggregate relationship with each other. For example, both an Employee table and a Customer table might include address information. Using the Object Translator GUI, you can generate an Address object using the street, city, state, and zip fields from the Employee object, and then both the Employee object and the Customer object can contain the Address object. (For information on the Object Translator GUI, see the online help provided with the GUI.)

This section describes how to perform the following tasks:

Instantiate the objects needed for the examples in this section
Restore (retrieve) the data for the objects from the database
Store (insert and update) data from the object to the database
Destroy (delete) the data from the database associated with the objects

Instantiating the Objects

Use this code to instantiate the objects needed for the examples in this section, and to assign the connection object to the Customer object:

    Dim oCust As new Customer
    Dim oCustOID As New CustomerOID
    Dim oAddress as Address    ' Embedded object of oCust

oCust.connection = conn 'Assigning the connection object

Restoring the Data for the Objects

After you have instantiated the objects, you can retrieve their data from the database.

The following code shows how to retrieve the data for the Customer object with a customer_num property of 26:

    oCustOID.customer_num = 26
    oCust.oid = oCustOID
    oCust.Restore

To retrieve the data for individual properties (for example, to populate user-interface items), use the object's getter functions:

text1.text = oCust.customer_num

Use this code to retrieve the embedded object's data:

set oAddress = oCust.Address

text2.text = oAddress.city
text3.text = oAddress.state

Storing the Data

After you have instantiated the objects, you can insert and update data in the database.

Inserting Data

Use this code to insert data for a new customer, including his address, into the database:

    If Address is Nothing Then
        set oAddress = new Address
        oCust.Customer_num = 100
        oCust.name = "Robert"
        oAddress.city = "Oakland"
        oAddress.state = "CA"
        oCust.Address = oAddress
        oCust.store
    End If

Updating Data

Use this code to retrieve existing data, set the appropriate properties to new values, and store those new values to the database:

    oCustOID.customer_num = 26
    oCust.oid = oCustOID
    oCust.Restore

    oCust.name = "Pete"
    oAddress = oCust.Address
    oAddress.city = "New York"
    oAddress.state = "NY"
    oCust.store

Destroying the Data

After you have instantiated the objects, you can delete their data from the database. The following code shows how; notice that this is similar to deleting a simple object, because there is no separate table in the database for the embedded object:

    oCustOID.customer_num = 26
    oCust.oid = oCustOID
    oCust.destroy

Last updated June 19, 2000.