5. Re: Is SQL required to run stored procedures in .NET apps?

[Alan Campin <Alan.Campin@xxxxxxx>]

Absolutely not.

If I understand you questions correctly, you want to know if you can write 
Stored Procedures on the AS/400 and call them from a .Net app. .Net apps just 
use ODBC or OLE and ADO to make calls to the stored procedures. The ADO code 
using the managed provider is the simplest. 

You only need the SQL Development kit if you are writing RPG with embedded SQL 
or Stored Procedure Language(SPL) programs. 

If I understand the second part of your question, stored procedures can be 
written in any high level language. I wrote RPG/ILE stored procedures without 
SQL for years. The only wrinkle that comes up if you want to return result sets 
to the caller. To be able to do that, you have to use embedded SQL in the RPG 
or use the SPL but there are easy ways to get around the problem. I used this 
for years before using embedded SQL. I still think it is easier than using SQL. 

The trick is have the NetServer running on the AS/400 and have a folder 
configured to be seen on the NetServer. You write a regular ILE/RPG program 
receiving parameters, for example, let's say an order number. 

The program takes the order number, looks up the order in the data base using 
regular file I/O. You build a set of data structures that contains the 
information that you want to send back converting all numeric data to 
character. In this example, you would have one structure for the header, one 
for line items and maybe one for a total line. 

You load the header structure and then using IFS file I/O to write the 
structure to a text file in the folder you set up. Then you write each line 
into the text file. Then the total line. 

Techniques for doing this are covered in the RPG list. When you make file 
available, you return the file name to caller. The .Net app just opens the text 
file and reads the line in. As far as the program is concerned, the text file 
is just another text file on a Windows drive and, of course, you can have 
different length records. Just a text file with <CR> <LF> at the end. The .Net 
app only needs to deserialize the data and do its processing. 

The last piece is you have to register the program with the AS/400 SQL Service 
using the "Create Procedure" command. All you are doing is registering the 
program with AS/400 and telling it what parameters to expect and the type of 
program.

The only other part of your question might apply to the Stored Procedure 
Language(SPL) on the AS/400. You can write SPL's on the AS/400 but you do have 
to have the SQL Development Kit. 

Hope this helps. 

>> message: 5
>> date: Fri, 1 Apr 2005 13:53:18 -0500
>> from: rob@xxxxxxxxx
>> subject: Re: Is SQL required to run stored procedures in .NET apps?

>> "Need" is a rather strict term.  Could you write stored procedures without
>> having the SQL product?  Actually I believe so.  Using iSeries Navigator
>> and a slew of other tools.
>> Could you write a stored procedure in RPG?  Again, I believe that you
>> could using SQL CLI instead of imbedded SQL.  However, you will spend more
>>on program maintenance than the cost of the SQL precompiler.

>> Rob Berendt
>> --
>> Group Dekko Services, LLC
>> Dept 01.073
>> PO Box 2000
>> Dock 108
>> 6928N 400E
>> Kendallville, IN 46755
>> http://www.dekko.com