This is the last lab for this course, and in this lab, we will outline and create the beginning process to tie all of the pieces for the project together. As has been discussed in the lecture material, middleware is the glue that holds applications in an integrated system together. In this lab, we will create a simple middleware batch application using the Windows OS to execute all of our Oracle and C#.NET applications. For the lab, you will only need to set up processing for the first half of the application processes, but you will need to build on the lab deliverable to finish the project.

A Windows OS batch file application can be a very powerful thing, yet it is very simple to construct and execute. The key things that will need to be dealt with in this process are making sure that data files are being sent to the correct place and that application executables are where they should be and referenced correctly in the batch file. You will be using the user input form screen to create your test data for the lab. Try to create data that will test both valid and invalid order data so that you will get errors reported in the error_audit table. Make sure that as you put the pieces together for the lab and testing that you are using the correct file names to test with.

Be sure to check each of the applications and script files to ensure they are saving and reading files from the correct locations and with the correct names. For example, in iLab 2, the order entry input file was named “Lab2OE.txt” and in iLab 6 it was named “Lab6OE.txt.“For the final project, all applications should refer to this file the same way, such as by the name “Lab7TestOE.txt.” You should verify the location and naming of all other input and output files used by the applications.

For this lab, you will be required to submit the batch file application that you will be creating and the resulting files from the TRANS_APP and INV_GRAB C#.NET applications. You will be provided with test files to run through the applications. Place the batch file along with the two output files in a single ZIP file and submit to the Dropbox for Lab 7.

Before you can proceed any further, you need to determine the processing order for all of the applications you are going to use in the system. The Oracle Form is used to create the initial order information files that will be used by the other applications at the front. What you need to be concerned with is what applications will need to be executed and in what order to be able to complete the order transaction process successfully.

For this lab, we will only set up the scenario for the first set of processes, it will be your task then to continue forward and complete the set of processes for the project. For the lab, you will need to set up the correct process order in the middleware application to execute the user input form, XML_PROC and SEQ_PROC procedures and the TRANS_APP and INV_GRAB C#.NET applications.

To simplify things with the Oracle XML_PROC and SEQ_PROC, it might be easier to create a SQL file that will log you into Oracle and then execute both the procedures and then exit SQLPlus. By doing this, you will only need to execute one statement verses several. Your statements inside the file might look similar to the following:

conn sai430_001/[email protected]

execute XML_PROC(‘SAI430_###_DIR, ‘LAB7TEST.XML’);
execute SEQ_PROC(‘SAI430_###_DIR, ‘LAB7TESTOE.TXT’, ‘LAB7TESTOL.TXT’);
EXIT

You will need to include your login information of course for the connection string. You could save the file with a name like ProcessScript.SQL and then simply reference the one file in your batch listing.

The process of setting up a Windows OS batch file is very simple and can be done using Notepad as the editor. There are only a few basic commands that you will need, and most of the coding is simply telling the OS where to find things.

To execute the user input form you simply need to provide the path to the form. Be sure that you have set the paths in the form for the output files to go to the Q: drive directory you have been assigned as they will be read by the Oracle procedures and must be on that drive. Your command to execute this application should look similar to the following.

Q:SAI430_###SAI430_FORM.EXE

To communicate with Oracle, you will need to invoke SQL*Plus which can be done with a simple reference to SQLPLUS. Since your login credentials are setup in the file that will be called you do not need to login at the time you invoke SQL*Plus. For example, to invoke SQL*Plus and then execute the SQL file you created in Step 1 you would use the following syntax line:

SQLPLUSW /nolog @Q:SAI430_###ProcessScript.sql

This will start up the SQL*Plus editor in the Windows OS and the log into Oracle and then immediately execute the SQL file you have provided the path to. You will be able to see the process take place in the command window. By adding the EXIT statement in the SQL file then SQL*Plus will close as soon as the two procedures have completed. Now we need to be able to call the C#.NET applications.

To simplify the process, or at least the path used for the C# calls you can take the executable file found in the bindebug directory under the project name and move it to the SAI430_### root directory. It will still execute the same but it will simplify the full path you have to use in the call. This is only a suggestion and in no way will affect the way your application executes. To call the application you simply list the path to the executable. For example, to execute the TRANS_APP application you would list the following in the batch file.

Q:SAI430_###TRANS_APP.EXE

Or if you did not want to move the executable then the path would be –

Q:SAI430_###TRANS_APPBINDEBUGTRANS_APP.EXE

During development it is often easier to leave the executables in their debug directories so that you don’t need to move them every time you have to fix an error.

One additional consideration you might want to think about would be to include the word PAUSE between each line that calls an application. This will cause the processing to pause between each application until you press the enter key, at which time the process will continue to the next command.

Now you have the syntax for the two PL/SQL procedures and one of the C#.NET applications, all you need to do now is add the second C# application call for this lab. When finished, you want to make sure that you save your file with a .BAT extension (be sure it does not save it with a .bat.txt type extension).

VERY IMPORTANT – Once saved, this file is now an executable. DO NOT double click on the file if you need to edit it. Doing so will cause the file to execute! If you need to edit the file, then right click on it and select Edit from the list. This will open the file in Notepad.

To test your batch file, you will need to make sure you have everything in the correct directory on Citrix and that you have used the correct file names in the batch file that you created. You will have three input files created by the user input form for the PL/SQL procedures and the two C#.NET applications should each produce a single output file. Also, you will need to start a session in Oracle and delete the data from the ERROR_AUDIT, ODER_LINE, ORDER_ENTRY, and CUST_ENTRY tables before running your batch program. Not doing this will cause integrity violations in the tables and cause the process to stop.

To test your middleware application, you simply need to double click on it. You should first be presented with the user input form which you can use to create the files to go to Oracle. Once you exit out of the form, you should see the OS command window appear and then the processes start to run. If the process is successful then check the TRANS_FILE.TXT and INV_GRAB.TXT files that were generated by the C# applications to make sure there is data in them. You will be submitting these for grading of the lab. If there were errors in the processing, then work on correcting these and rerun the batch file.