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.
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.
Once these steps have been completed, you are ready to use the Technical Order Entry Example applications.
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:
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.
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:
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.
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.
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.
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.
To start a TechOrderEntryServer, from the OS/400 command line enter the following:
runst '-iimage_name.icx url_name dbName user password'
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.
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.
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.
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.
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.
You can now execute TechOrderEntryPopulateDb using the following command line:
runst '-itoepopdb.icx dbName user password'
See TechOrderEntryPopulateDb application for details.
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:
You can now execute the TechOrderEntrySamples using the following at a command line:
runst /-itoesamp.icx dbName user password'
See TechOrderEntrySamples application for details.
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.
See TechOrderEntrySamples application for details.