SQL Stored Procedures

Hi Guys,

Today I am writing about Stored Procedures. What are stored Procedures, How to write them, How to compile and how to call them?

Stored Procedures

Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG.

So how do you write SQL Procedure? It is simple. You just need to write SQLRPGLE with “CREATE PROCEDURE” SQL Statement

Example 1.

C/Exec SQL

C+CREATE PROCEDURE GetName 

C+ (IN ProgLang CHARACTER(10), 

C+IN Comp CHARACTER(20), 

C+INOUT FOUND INTEGER(4)) 

C+LANGUAGE SQL 

C+SELECT COUNT(*) INTO FOUND FROM TestLib/NameData 

C+WHERE TechLang = ProgLang AND Employeer = Comp

C/End-Exec

How to compile the Procedure?

You can compile the above code like any other SQLRPGLE code

How to call above procedure?

Create another RPGLE code as below

D GetName        PI

     D  ProgLang                     10A

     D  COMP                         20A

     D  Found                         4 

.

.

.

.

    Call GetName( :EMPID, :Comp, :Found);

Example 2.

c/EXEC SQL                                                

c+ Create procedure OrderSumm(                            

c+   in    partnerID            NUMERIC(5,0),             

c+   in    storeNumber          NUMERIC(7,0) )            

C+ LANGUAGE RPGLE NOT DETERMINISTIC                       

c+ READS SQL DATA                                         

c+ EXTERNAL NAME EXTPGM01                                 

c+ PARAMETER STYLE GENERAL                                

c/END-EXEC                      

Above code will be called as below

Create another RPGLE code as below

D OrderSumm        PI

     D  PartnerID                     5

     D  StoreNumber                   7

.

.

.

.

    Call OrderSumm( :PartnerId, :StoreNumber);

In example 2 when OrderSumm is called, it in returns call further a new program “EXTPGM01”

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

XML Parsing using XML-INTO

Something on XML-INTO

IBM has introduce a new OP-Code XML-INTO, which has make XML parsing as smooth as butter..:)

Steps to use XML-INTO

1.        Define a Data structure or array of data structures that matches the layout of XML document.

2.        Run XML-INTO (This loads the values from XML to Data Structure

Below sample code will help you write simple XML Parsing code.

D qualName        DS                   QUALIFIED

D   FrstName                    10A

D   LastName                    10A

D EmpInfo        DS                   QUALIFIED

D* Employee Name

D   EMPNme                             LIKEDS(qualName)

D* Manager Name

D   MName                              LIKEDS(qualName)

D* Manager Last Name

D MLName          S             10A    VARYING

 //  Assume file Emplpoyee.xml contains the following lines:

 //<empinfo>

 // <EMPNAME><Frstname>Scott</Frstname><LastName>Sherbert</LastName></EMPNAME>

 // <MNANE Frstname="Ken" LastName="Mahajan"></MNAME>

 //</empinfo>

 /free

    xml-into empInfo %XML('Employee.xml' : 'doc=file');

 // EMPInfo.EMPNme.Frstname='Scott' EMPInfo.EMPNme.Lastname = 'Sherbert'

 // EMPInfo.MName.Frstname='Ken' EMPInfo.MName.Lastname = 'Mahajan'

 // Parse the "empinfo/Mname/Lastname" information into variable

 // "MLName".  Use the "path" option to specify the location

 // of this information in the XML document.

  xml-into MLName %XML('Employee.xml'

                   : 'doc=file path=empinfo/MName/lastname';

 // MLName = 'Mahajan'

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

Advantages and Disadvantages of AS400

Hi Guys, 

One of my friend over Linkedin want to know the advantages and disadvantages of AS400. So thought of sharing the same.

Advantage of AS400

The main advantages of AS400 over other are

 1. Stability

You would have sometime re-booted your desktop twice or thrice a day. But can you afford to do the same with your Servers. I know few organizations where IT admin are not aware of AS/400 Machine reboot date

2.        Integration

Using AS400 you need not worry about integration of database, Programing languages to use

Will the security system work with the database and will the database work with the operating system, etc are questions that do not apply to AS/400. It is amazing how much time can be invested in making these things work together, but on iSeries (as with AS/400 and S/38), the question is simply irrelevant.

3.        Security

OS/400 is an object orientated operating system it is virtually immune to viruses.

Disadvantage of AS400

To count disadvantages of AS400, there would be less. Still to count, below are the few disadvantages of AS400

1.       Cost - 

          The hardware costs for NEW machines are significantly higher than other platforms.

2.       Developer Resource – 

        Nowadays RPG Developer are a rare breed of developers which comes with high Dollar value as compare to other development languages like JAVA

3.       Mindset - 

        The biggest problem is mindset of people. An AS400 is seen as a clumsy box capable of only green screen applications and suitable only for large businesses having large cash transactions. This is simply untrue. The box needs almost no supervision. All you ever have to do once it's loaded up is change the backup tapes. BUT, it is perceived as old technology when in fact it is probably more robust and cutting edge than most of it's competitors.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

Few SQL Questions

Hi Guy, 

This time I am back with Few SQL Questions....

What will runsqlstm will do?

If we want to execute set of SQL statement then we can write all the SQL statement to be including with the source.

Type as SQL

Only insert, update and delete and no select is allowed.

Then use STRSQLSTM to execute the program

RUNSQLSTM   SRCFILE (LIB/TEST) SRCMBR (SQL01) COMMIT (*NONE)

Embedded SQL in RPGLE (SQLRPGLE)

Hi Guys,

Today I am here with Embedded SQL Program.

How to write embedded SQL Program.

SQLRPGLE is very simple if we can understand the flow of program. SQLRPGLE basically consist of below keyword

1.        Declare Cursor

2.        Open Cursor

3.        Fetch Cursor

4.        Close Cursor

SQL400 for Beginners Part 4

Hi Guys,

I am back with Joins in SQL400. I hope till now you would have basic understanding of SQl400. After this blog, you would be able to know more on Joins.

SQL JOIN Statements

There are five types of SQL Joins

1.Inner Join

2.Left Outer Join

3. Exception Join

4. Cross Join

5. Multiple Join types in one Statement

SQL400 for Beginners Part 3

Hi Guys,

I am back with 3rd tutorial of SQL400. After going through this blog, I hope all of you would be having basic understanding of SQL400

SQL ORDER BY

‘ORDER BY’ is used to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value.

The syntax for an ORDER BY statement is as follows:

SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY"column_name" [ASC, DESC]

It is possible to order by more than one column.

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

SELECT * FROM TBLSTR ORDER BY STRNAM

The above statement will select all records from file TBLSTR and sort them based upon the ascending values of column STRNAM