mrcuser
Moderator
Registered: 11/29/05
Posts: 67
|
| | 12/19/07 at 12:16 PM | Reply with quote | #1 |
|
Not enough people really utilize the power of SQL in their m-Power generated servlets. That may be because they don't understand how m-Power's external object interface interacts with SQL. I hope to clarify that understanding through this post.
But first, let's talk about some of the things we might be able to do with SQL:
- Maintenance program edit checks,
- Logging application use or type of use,
- Updating additional files based on user actions,
- Mass updates based on simple inputs,
- Overcoming some design features of current templates much more simply than writing your own templates,
- Generating report totals at report initialization so that the totals can be used in percent of total calculations on each line.
Also, let's talk a little about why we might want to use SQL. Any of the items on the above mentioned list can be accomplished with any procedural language and external objects. The beauty of accomplishing them through SQL is that it is far more universal than languages like RPG, CL, COBOL, etc. - existing on almost any platform and data-base you might wish to work on while being far easier to learn than Java.
Here is a simple example to get you started. This example was built on a System i, but can easily be extended to other platforms.
First, we create our SQL external object. On the System i we do that in a source member of a source phyical file of type 'txt'. It looks like this:
Code: Columns . . . : 1 100 Edit CROWLEY/QSQLSRC
SEU==> TESTPROC2
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
*************** Beginning of data ************************************************** *************************************
0001.00 create procedure testproc2
0002.00 (INOUT i_error character (99),
0003.00 INOUT i_cusnbr decimal (6,0))
0004.00 language SQL
0005.00 case
0006.00 when i_cusnbr = 100100 then
0007.00 set i_error='This is an error for customer 100100.';
0008.00 when i_cusnbr > 500000 then
0009.00 set i_error='The customer is over 500000.';
0010.00 else
0011.00 set i_error=' ';
0012.00 end case;
0013.00
****************** End of data ************************************************** ****************************************
This SQL statement lets us pass it two parameters; a 99 character error message, and a 6,0 numeric Customer number. It then flags an error on two conditions; if the customer number = 100100 or if the customer number is greater than 500000.
Once we have save the SQL source in the text member, we run the following command to create an SQL stored procedure.
RUNSQLSTM SRCFILE(CROWLEY/QSQLSRC) SRCMBR(TESTPROC) DBGVIEW(*LIST)
The program created will be called TESTPROC and will live in library QGPL.
Next we simply register this program as an external object in our data dictionary, along with the two parms. The only entries needed on the object definition are the description, program name, library name, where available ='O' and object type = 'R'. You do not need to create a stored procedure as it was already done above.
That's really all there is to bringing the power of SQL to your servlets. Try it out. Let us know what kinds of things you are doing with SQL and servlets.
__________________
User Forum Moderator
mrc
555 Waters Edge, Ste 120
Lombard, IL 60148
mrc@mrc-productivity.com
http://www.mrc-productivity.com |
| Loading... | |
