Database Guide

Sample DB2-CLI application for OS/400

The configuration map OS/400 Technical Order Entry Examples contains a sample application TechOrderEntryBase which demonstrates the use of the DB2 Call Level Interface (CLI) access classes. This application along with its supporting applications are described below. Packaging instructions for the example applications are also discussed in this section. Packaging instructions for the example applications are contained in the configuration map OS/400 Technical Order Entry Instructions.

Getting Started

The Technical Order Entry Example applications require that a sample database with seed data exist on your OS/400 server. Follow the steps below to set up the sample database and use the Technical Order Entry Example applications.

  1. Load the following configuration maps:
  2. Create the sample database in your existing DB2/400 installation.
  3. Before creating an populating your database tables, you must know the values for several parameters which specify the name of the system where your database will be located as well as, logon and connection information forthat system. These values are set in the class methods for ToeDbSetUp class. See TechOrderEntryDbSetUp application for more information.
  4. Create and populate the sample database tables by executing one of the following:

Once these steps have been completed, you are ready to use the Technical Order Entry Example applications.

TechOrderEntryBase application

This application defines the business objects and their behavior for the Technical Order Entry System. This is an order entry system used for tracking customers, orders and inventory. As customers and orders are created, the respective database tables are updated. In addition, as orders are entered and removed, the inventory of parts for the options being ordered is decremented or incremented accordingly. When a parts inventory runs out, options that require that part can no longer be ordered.

The TechOrderEntryBase classes are called when executing the sample code in the TechOrderEntrySamples application. No modifications are needed to be able to use this application.

The main business objects for this application are as follows:

ToeCustomer
Represents a "real world" customer. A ToeCustomer contains unique ID and address information for a customer. A ToeCustomer can locate an OrderedCollection of its orders.

ToeOrder
Represents a commitment to ship a product. A ToeOrder contains a unique ID and date information for order placement and shipping. A ToeOrder will always contain the customerId for the ToeCustomer placing the order. A ToeOrder also contains an OrderedCollection of its ToeOrderItems.

ToeOrderItems
Represents a holding place for the ToeOptions being ordered. A ToeOrderItem is made up of a ToeOption and a number representing the quantity that is being ordered. A ToeOrderItem is referenced by the orderId of the ToeOrder to which it belongs. A ToeOrderItem will only ever contain 1 ToeOption.

ToeOptions
Represents the actual product that can be ordered. A ToeOption contains a unique ID along with description information for that option. It also contains an OrderedCollection of partId's for all of the ToeParts needed to build that option. A ToeOption can belong to many ToeOrderItems. A ToeOption can only be ordered if there is enough inventory for the ToeParts that make up the ToeOption.

ToeParts
Represents the actual parts in inventory used to build each ToeOption. A ToePart contains a unique ID along with description and inventory information for a part. A ToePart can belong to many ToeOptions. The inventory for a ToePart will be updated as ToeOrders are added and modified.

All database queries and updates are handled by the class methods in ToeDbAccess. These methods are responsible for creating and executing query statements on behalf of the TechOrderEntry business objects. The results of those queries (an AbtIbmCliResultTable or an AbtIbmCliRow) are then returned to the business objects for use in creating and maintaining customer orders and parts inventory. The exception to this is management of database connections and login which is handled by ToeDbSetUp class methods in the application TechOrderEntryDbSetUp.

TechOrderEntryDbSetUp application

The TechOrderEntryDbSetUp application is used to handle database connection and disconnection functions. As part of those functions, this application defines several parameters needed to perform database connections and access tables in the database once connected.

The class methods in ToeDbSetUp are divided into three categories:

Connect
Contains the connect and disconnect methods. These methods also maintain the connection count to the database. On the workstation the example is set up so that connect will return an AbtIbmCliCSDatabaseConnection. On the AS/400 connect will return an AbtIbmCliOS400DatabaseConnection.

Environment variables
Defines values used in the database connection and table access transactions.

dbConnectionSpec
Returns an AbtDatabseConnectionSpec used in connect.

dbLogonSpec
Returns an AbtDabaseLogonSec used in connect.

dbmClass
Defines the database manager class name for the AbtDatabaseConnectionSpec (CLI, ODBC, and so on). The current value is set to AbtIbmCliDatabaseManager. However, on OS400 this class is ignored and AbtIbmCliOS400DatabaseManager is always used.

dbmName
Defines the name of the database to which you will be connecting. On OS/400, this will usually be the system name for the local database. You define this name in the relational database directory using ADDRDBDIRE or WRKRDBDIRE.

password:
Defines the password for the user logging in.

schemaName
Defines the name of your database schema. If not specified, this defaults to the user name.

server:
Defines the name of the server where the database is located.

user:
Defines the user name logging in to the database server.

Before using the TechOrderEntryPopulateDb and TechOrderEntrySamples applications on the workstation, you must initialize values for dbName:, user:, and password: to values specific to your database installation. This can be accomplished by executing the following in a workspace substituting your values:

ToeDbSetUp
   dbName: 'your_db_name';
   user: 'your_user_name';
   password: 'your_password'

When using main method in ToePopulateDB on your OS/400 system after packaging, these values will be entered as parameters from the command line.

DB Set UP contains methods that return the names of the database tables as they are known to the system on which database resides. These names are a combination of the database schemaName and hard coded table name known to the TechOrderEntryDbSetUp application. All queries use values returned from these methods to determine the correct table name to use in database queries.

TechOrderEntryPopulateDb application

You use the TechOrderEntryPopulateDb application to create and populate tables in the sample database on your OS/400 system. The sample database must exist on your OS/400 server before you can successfully use this application. You can create it using the ADDRDBDIRE command if the local database on the AS/400 has not yet been defined.

The class method runDbSetUp in ToePopulateDb is the starting point for the create and populate process. Executing the class method runDbSetUp in ToePopulate causes an AbtIbmCliCSDatabaseConnection to be established with the sample database. After the connection has been established, the class method create in ToePopulateDb and the class method populate in ToePopulateDb are called in succession.

The create method creates tables in the sample database required by the Technical Order Entry Example applications. Table names created are based on the names specified in ToeDbSetUp class methods in the DB Set Up category. Table creation is accomplished using the instance method createTableNamed:definition:ifError: in AbtDatabaseConnection. The following code sample illustrates the creation of the customer table in the sample database.

createCustomerTable
   | db t result |
   db := ToeDbSetUp connect.
   db isAbtError
      ifFalse: [ | def|
         result := db
            deleteTableNamed: self customerTableName
               ifError: [:error | ^error].
   def := WriteStream on: (String new: 100).
   def
      nextPutAll: ' (custid integer not null primary key,';
      nextPutAll: ' custname char(36) not null,';
      nextPutAll: ' address1 char(36) not null,';
      nextPutAll: ' address2 char(36),';
      nextPutAll: 'city char(20) not null,';
      nextPutall: 'state char(2) not null,';
      nextPutall: 'zip char(12) not null,'.
   result := db
      createTableNamed: self customerTableName
         definition: def contents
            ifError: [:error | ^error].
   result isAbtError ifTrue: [^result]].
   (db := ToeDbSetUp disconnect) isAbtError ifTrue: [db display]

After all tables have been created, the class method populate in ToePopulateDb is called to populate each AbtTable with its seed data. To add data to each table, an emptyRow is retrieved from the desired table. The columns for the AbtIbmCliRow are populated and the row is then added to the table. The following code sample illustrates adding a row to the customer table in the sample database.

| db table row rc |
(db := ToeDbSetUp connect) isAbtError ifTrue: [^db].
table := db
   openTableNamed: self customerTablename.
table isAbtError ifTrue: [^table].
(row := table emptyRow)
   at: 'CUSTID' put: (self nextCustId);
   at: 'CUSTNAME' put: 'John Smith';
   at: 'ADDRESS1' put '1 First Street';
   at: 'ADDRESS2' put '';
   at: 'CITY' put: 'Phoenix';
   at: 'STATE' put: 'AZ';
   at: 'ZIP' put: '85022';
   yourself.
(rc := table addRow: row) isAbtError ifTrue: [^rc].

Both the create and the populate methods handle transaction commits (commitUnitOfWork) and rollback transactions (rollbackUnitOfWork) if any errors are encountered. All errors are reported back to the user through runDbSetUp. See the individual ToePopulate class method comments for more information on the transactions being performed.

This process can be executed on an OS/400 system by packaging the main class method in ToePopulateDb and running the resulting image on an OS/400 system. The transaction flow will be the same as described above. See Packaging Instructions for more details.

TechOrderEntrySamples application

You use this application to execute sample database transactions against the database that you have created and populated with seed data. The sample database must exist on you OS/400 server or local copy of DB2 before you can successfully use this application. See Getting Started for more details.

The ToeSamples class methods are meant to be used in conjunction with the TechOrderEntryPopulatedDb, TechOrderEntryDbSetUp and TechOrderEntryBase applications. All applications can be found in the configuration map OS/400 Techincal Order Entry Example.

The sample code contained in ToeSamples class methods can be run on the workstation simply by executing the doit code in each method comment. Explanation of the sample code can be found in ToeSamples class method comments.

The printSampleTables class method in ToeSamples connects to the sample database and prints out the contents of each of the tables. This transaction is accomplished by building a query statement that returns all rows for each of the tables in the sample database. These rows are then used to create the business objects for the Technical Order Entry System and output their contents to the System Transcript. The contents of the ToeCustomer, ToeOrder, ToeOrderItems, and ToeParts tables are updated during the execution of sample1.

The following code sample from class method allRowsFromTable: in ToeDbAccess illustrates retrieving a result table that contains all of the rows from the customer table. Each row in the result table is then added to a collection and returned to the calling method.

| db dbAccess resultCollection table result |
db := AbtDbmSystem activateDatabaseConnection.
db isAbtError ifTrue: [^db].
resultCollection := OrderedCollection new.
table := db
   openTableNamed: tableString
      ifError: [:error | ^error].
(result := (db resultTableFromQuerySpec:
   (AbtQuerySpec forEntireTableNamed: tableString)))
      isAbtError ifTrue: [^result].
result do: [:eachRow | resultColletion add: eachRow].
^resultCollection

Ultimately, each row in the collection is used to create an instance of ToeCustomer. See the createFromRow: class method in ToeCustomer for more details. This process is repeated for each database table.

The sample1 class method in ToeSamples connects to the sample database creating a new customer and an order for that customer. The order is then deleted from the database causing some table rows to be deleted and others to be updated. Database table contents are displayed throughout the processing so that you may see the effects of the transactions on the contents of the tables.

These transactions are accomplished by creating a new customer and then building a query statement that uses the instance of ToeCustomer to populate the column values for a row in the customer table. That row is then added to the customer table. This process is similar to that used for populate described earlier. Similarly, an order and orderItems are created for the new customer and rows are added to their respective tables. The process of adding the order to the database causes rows in the parts table to be updated to reflect a decrease in inventory. See the class methods addOrder and addItem: in ToeOrder for more details.

Next, the order and orderItems are deleted. This is accomplished by querying the database for the appropriate table rows for the order and its orderItems.These rows are then deleted from their respective tables. The following code sample from the rowsFromTable:column:id: class method in ToeDbAccess illustrates how to run a query statement that returns rows from specified table based on a selection criteria (in this case the orderId for a ToeOrder).

rowsFromTable: tableString column: columnString id: anId
| db id querySpec result resultCollection |
db := AbtDbmSystem activeDatabaseConnection.
db isAbtError ifTrue: [^db].
resultCollection := OrderedCollection new.
querySpec :=
   AbtQuerySpec new
      statement: (
         'SELECT * FROM %1 WHERE %2 = %3'
            bindWith: tableString
            with: columnString
            with: anId printString);
         yourself.
result := db
   resultTableFromQuerySpec:  querySpec
      withValues: nil
      ifError: [:error | ^error].
result do: [:eachRow | resultCollection add: eachRow].
^resultCollection

After finding the ToeOrder by its orderId, the order can then be deleted from the order table. The following code sample from the deleteOrder: class method in ToeDbAccess illustrates deleting an AbtRow from an AbtTable. The class method rowFromOrderId in ToeDbAccess uses the previous code sample to obtain the correct AbtRow from the order table.

deleteOrder: anOrder
   |db table id row result |
   db := AbtDbmSystem activeDatabaseConnection.
   db isAbtError ifTrue: [^db].
   id := anOrder orderId.
   table := db
      openTableNamed: self orderTableName
      ifError: [:error | ^error].
   row := (self rowFromOrderId: id).
   row is AbtError
      ifTrue: [
         ToeDbSetUp disconnect.
         ^row].
   result := table
      deleteRow: row
         ifError: [:error | ^error].
   ^result

The process of deleting the order causes rows in the parts table to be updated to reflect an increase in inventory for each part. Table contents are displayed throughout the processing to show that table rows have been deleted or updated. See the removeOrder method in ToeOrder for more details.

See Deleting a row from a table for a code sample that demonstrates this task. More information is available in the VisualAge Smalltalk Database Guide.

Once you are familiar with the use of printSampleTables and sample1, you can use the order class methods in ToeSamples to perform other transactions on the sample data.

The main class method in ToeSamples, which incorporates these methods, can be packaged and run from an OS/400 system. The resulting image when run on OS/400 will demonstrate the same functionality as the printSampleTables and sample1 methods described above. See Packaging Instructions for more details.

TechOrderEntryServer application

This application is used to execute sample database transactions against the database that you have created and populated with seed data. the sample database must exist on your OS/400 server or local copy of DB2 before you can successfully use this application. The TechOrderEntryServer application is meant to be used in conjunction with the TechOrderEntryPopulateDb, TechOrderEntryDbSetUp and TechOrderEntryBaseapplications. All applications can be found in the configuration map OS/400 Technical Order Entry Example. See Getting Started for more details.

Unlike the TechOrderEntrySamples application, the TechOrderEntryServerapplication uses functionality provided by Server Smalltalk to allow the user to make database queries to the Technical Order Entry System and display the results of those queries in a web browser.

The TechOrderEntryServer application uses the same DB2 CLI access services used by TechOrderEntrySamples and implemented in TechOrderEntryBase. However, the results of the database transactions are directed to your web browser instead of the transcript or command line. This is accomplished by using the servlet-based server architecture provided by Server Smalltalk. This architecture allows you to write the equivalent of a Java servlet which, in the case of the TechOrderEntryServer application, can then be packaged and run from an OS/400 server. See Packaging Instructions for more details.

A simple explanation of how the TechOrderEntryServer application uses this servlet-based architecture is as follows. In the Technical Order Entry System, each page displayed in your browser has a corresponding subclass of SstHttpCgiServlet in the TechOrderEntryServer application. Each of these subclasses knows how to call the DB2 CLI Smalltalk code needed to execute database transactions, and present it as formatted HTML code. Keeping track of orders that are added or deleted for a customer is handled in ToeShoppingCartSession a subclass of SstHttpSession. Refer to the ToeShoppingCartSession method comments for implementation details.

Starting the server

From an OS/400 command line

To start a TechOrderEntryServer, from the OS/400 command line enter the following:

runst '-iimage_name.icx url_name dbName user password'
image_name
The name of the image created by packaging the TechOrderEntryServer application.
url_name
URL designating the HTTP Port number that the server will run from.
dbName
On OS/400, this will be the name of the server where the packaged image and database reside. See TechOrderEntryDbSetUp application.
user
User name used to access the database server. See TechOrderEntryDbSetUp application.

An example of a correct OS/400 command line is:

runst '-itoeserv.icx htt://:4400 starbase Bob BobsPassword'

This will cause the TechOrderEntryServer to start running on port 4400 on your OS/400 systems.

From a development image

To start the TechOrderEntryServer from your development image, you must have a local copy of DB2 running. In a workspace type the following:

ToeDbSetUp
   dbName: 'your_dbName';
   user: 'your_user_name';
   password: 'your_password'

To start the server, create a singleton server in the development image:

ToeServer startAt: 'http://4400' 

This will cause the TechOrderEntryServer to start running on port 4400 on local system.

After you have started the server either on your OS/400 or local system, you can connect to the Technical Order Entry System simply by typing:

http://yourServerName:4400

The first page of the technical Order Entry System should now be displayed in your browser.

Stopping the server

From your web browser

To shut down the server from your web browser, add '/Admin' to the end of the URL for any page of the Technical Order Entry System you may be on. An example of this is:

http://yourServerName:4400/Admin

The Administration page of the Technical Order Entry System should not be displayed in your browser. Click Stop Server to stop the server on which every system it is running.

Packaging Instructions

If you have not done so already, read and carefully follow the instructions in Getting Started.

This section describes how to package and run the main class method in ToePopulateDb, the main class method in ToeSamples, and the main class method in ToeServlet. The packaged images, when run on an OS/400 system, allow you to do the following:

All packaging instructions are contained in the configuration map OS/400 Technical Order Entry Instructions.

Packaging the main class method of ToePopulateDb

Instructions for packaging the main class method in ToePopulateDb are contained in the class ToePopulateDbInstructions in the application TechOrderEntryInstructions. This application should be loaded in your development image and thus included in your OS/400 XD image when it is built.

  1. From the XD Packager Control Panel select the Instructions in Database tab. A list of applications that include packaging instructions will be displayed.
  2. Select the instance method ToePopulateDbInstructions in TechOrderEntryInstructions.
  3. Select Modify Instructions. TechOrderEntryPopulateDb, TechOrderEntryBase, and TechOrderEntryDbSetUp should be included in the list of applications listed in the center pane of the XD Packager Control Panel.
  4. Continue through the packaging steps, ending with Output Image. The output file toesamp.ixc is created. Copy the resulting file to the OS/400 system.

You can now execute TechOrderEntryPopulateDb using the following command line:

runst '-itoepopdb.icx dbName user password'

See TechOrderEntryPopulateDb application for details.

Packaging the main class method of ToeSamples

Instructions for packaging the TechOrderEntrySamples are contained in the class ToeSampleInstructions in the application TechOrderEntryInstructions. This application should be loaded in your development image and thus included in your OS/400 XD image when it is built. Take the following steps to complete the packaging process from an XD OS/400 image:

  1. From the XD Packager Control Panel select the Instructions in Database tab.
  2. Select the ToeSampleInstructions instance method in TechOrderEntryInstructions
  3. Select Modify Instructions. TechOrderEntrySamples, TechOrderEntryBase, and TechOrderEntryDbSetUp should be included in the list of applications listed in the center pane of the XD Packager Control Panel.
  4. Continue through the packaging steps, ending with Output Image. The output file toesamp.ixc will be created. Copy the resulting file to the OS/400 system.

You can now execute the TechOrderEntrySamples using the following at a command line:

runst /-itoesamp.icx dbName user password'

See TechOrderEntrySamples application for details.

Packaging the main class method of ToeServlet

Instructions for packaging the TechOrderEntryServer are contained in the class ToeServerInstructions in the application TechOrderEntryInstructions. This application should be loaded in your development image and thus included in your OS/400 XD image when it is built. Take the following steps to complete the packaging process from an XD OS/400 image.

  1. From the XD Packager Control Panel select the Instructions in Database tab.
  2. Select the ToeServerInstructions instance method in TechOrderEntryInstructions.
  3. Select Modify Instructions. TechOrderEntryServer, TechOrderEntryBase, and TechOrderEntryDbSetUp should be included in the list of applications listed in the center pane of the XD Packager Control Panel.
  4. Continue through the packaging steps. The output file toeserv.ixc is created. Copy the resulting file to the OS/400 system. You can now execute the TechOrderEntryServer. Follow the instructions in Starting the server.

See TechOrderEntrySamples application for details.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]