Usage of SQLJ Iterator in portlets?

0 comments



One of the important features of SQL is the functionality to perform a transaction among different rows using CURSORS.

Cursors are mainly used to loop through the series of rows and perform the same transaction for each row.

Like Cursors, ITERATORS are the SQLJ version of CURSORS.

SQLJ is an extension of embedding SQL statements in JAVA programming language.

Iterators are used to move across the contents of the result data set produced in response to the query.

Types of Iterators

There are 2 types of iterators used –

1: Named iterator
2: Positional Iterator

1: Named Iterators:
  • As the name suggest, the named iterator are declared with the name and the data type of the columns of the database that are to be returned as the output of SQL query.
  • Since the name of the column is ALWAYS defined along with the data type, the order of the columns defined in table and one defined in Iterator DOES NOT matter.
Pre-requisite:

The iterator class should be generated with the type of the result data set expected as the output for the SQL query.

Syntax:

#sql iterator IteratorName (Name and data type of Col1, Name and data type of Col2 …)

Example:

#sql iterator EmpDetails (string Emp-Name, int Emp-id)

The iterator EmpDetails is generated with the data type String and Integer which is expected as the output of the query.

Usage of iterator along with the SQL query:

When the SQL command is issued to loop around the rows, the result set should be passed to the instance of the iterator class.

Example:

EmpDetails NameIter;

#sql [connCtx] NameItr = {Select Emp-Name, Emp-id from Employee-Info)

Where:

Employee-Info : database to be accessed
ConnCtx : Will establish a connection with the Employee-Info database.
Instance NameItr is created to hold the result data set.

Looping using named iterator:
  • The method IteratorName.next () is used to go to the next row before each loop cycle.
  • To retrieve column values from the named iterator, the iterator method name can also be used after the column name in the result set.
Example of Looping using named Iterator

Consider the database Employee-Info having the information of all the employees working.

Employee-Info Database
Emp–Id Emp Name

10 John
20 Sandy
30 Ronnie

To display the list of employees and their employee number using named iterator

Do
{
System.out.println(“Good Morning” + NameItr.Name() + “. Your Employee id is” + NameItr.Emp-id());
}while (NameItr.next());
NameItr.close();

Note: - Iterator needs to be closed once the end of the table is reached.

Output of the above looping is:

Good Morning John. Your Employee id is 10
Good Morning Sandy. Your Employee id is 20
Good Morning Ronnie. Your Employee id is 30

2: Positional Iterator:
  • The positional iterators are defined only with the data types of the columns of the database that are to be return as part of the output of SQL query.
  • The names of the columns are NOT required.
  • As the name suggests and also since the name of the column is not defined, the order of the columns IS IMPORTANT.
Pre-requisite:

The iterator class should be generated with the type of the result data set expected as the output for the SQL query.

Syntax:

#sql iterator IteratorName (data type, data type,.…)

Example:

#sql iterator EmpDetails (int, string)

Usage of iterator along with the SQL query:

When the SQL command is issued to loop around the rows, the result set should be passed to the instance of the iterator class.

Example:

EmpDetails PosnIter;

#sql [connCtx] PosnItr = { Select Emp-id, Name from Employee_Info)

Where:

Employee-Info: database to be accessed
ConnCtx: Will establish a connection with the Employee-Info database.

Looping using Positional iterator:
  •  To retrieve column values from the positional iterator, the retrieved data is stored in Host Variables.
  •  The method iterator.endFetch() is used to determine of there are any more rows in the database to fetch.

Example of Looping using positional Iterator

Consider the database Employee_Info having the information of all the employees working.

Employee-Info Database:

Emp–Id Emp Name
40 Jeane
50 Donna
60 Reene

To display the list of employees and their employee number using positioned iterator

#sql {Fetch: PosnItr INTO: ename, :eid};

While (!PosnIter.endfetch())
{
System.out.println(“Good Morning” + ename + “. Your Employee id is” + eid);
#sql {Fetch :PosnItr INTO :ename, :eid};
}

PosnIter.close();

Note: - Iterator needs to be closed once the end of the table is reached.

Output of the above looping is:

Good Morning Jeane. Your Employee id is 40
Good Morning Donna. Your Employee id is 50
Good Morning Reene. Your Employee id is 60

No comments:

Post a Comment

Recent Posts

Popular Posts

© 2011-2019 Web Portal Club