Catching values returned by a java program by a DB2 function/stored procedure

[satish mullapudi]

Hi,
I am using DB2 v8.2 & jdk 1.4.
1. I have a java program which returns an array which contains 1 to 5
numbers.
The code:
public class ArrayTest
{
public static final int ARRAY_SIZE = 5;
public static int[] array()
{
int array[] = new int[ARRAY_SIZE];
for(int i = 0; i < ARRAY_SIZE ; i++)
{
array[i] = i+1;
}
return array;
}
public static void main(String params[])
{
int a[] = array();
for(int i = 0; i < a.length ; i++)
{
System.out.println(a[i]);
}
}
}
2. Now, I need to catch that values returned by java using a function /
stored procedure in DB2
3. Using the values caught, I need to return a result set like :
select firstname,salary from employee where empno='x' where 'x' is
the value returned by the
java program & caught by DB2 function/stored procedure
So, the logic:
for (i=1;i<x;i++)
{
select firstname,salary from employee where empno='x'
}
should be implemented in the function/stored procedure.
4. This result set should be displayed on the standard output

Please help me out, as this is very urgent for me.

Thanks in advance...........

satish

[Knut Stolze]

satish mullapudi wrote:

Hi,
I am using DB2 v8.2 & jdk 1.4.
1. I have a java program which returns an array which contains 1 to 5
numbers.
The code:
public class ArrayTest
{
public static final int ARRAY_SIZE = 5;
public static int[] array()
{
int array[] = new int[ARRAY_SIZE];
for(int i = 0; i < ARRAY_SIZE ; i++)
{
array[i] = i+1;
}
return array;
}
public static void main(String params[])
{
int a[] = array();
for(int i = 0; i < a.length ; i++)
{
System.out.println(a[i]);
}
}
}
2. Now, I need to catch that values returned by java using a function /
stored procedure in DB2
3. Using the values caught, I need to return a result set like :
select firstname,salary from employee where empno='x' where 'x' is
the value returned by the
java program & caught by DB2 function/stored procedure
So, the logic:
for (i=1;i<x;i++)
{
select firstname,salary from employee where empno='x'
}
should be implemented in the function/stored procedure.

What exactly have you done so far? (show us the code)

You could simply create the array, then construct a SQL statement like this:

SELECT firstname, salary
FROM employee
WHERE empno IN ( 1, 2, 3, 10 )

where the elements in the IN-clause stem from your array. Finally, execute
the query and open a cursor. The cursor is then returned by the procedure
and could be processed by an application that invoked the procedure.

Another alternative are table functions. You write a table function that
returns your array as table (we are talking SQL here, so you only have
tables!) Then you can join the EMPLOYEE table with the results of your
table function:

SELECT e.firstname, e.salary
FROM TABLE ( your-function() ) AS t(id) JOIN
employee AS e ON ( t.id = e.empno )

Have a look at the DB2 samples to see how table functions and stored
procedures can be implemented.

4. This result set should be displayed on the standard output

I tried to explain the other day that there is no standard output with SQL.
Maybe you want to return the rows as a regular result set? Such a result
set can be processed by an application or you could use the DB2 command
line as application (which has its own standard output).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany