Informix UDR Problem
This is a multi-part message in MIME format.
------=_NextPart_000_00B1_01C344A4.1309F1D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Following is a problem with returning Collection DataType from Informix UDRs
written in Java .
A UDR is written in Java ( in Informix Database ) .
The function returns a java.util.Vector . External function declaration is
written in Informix for it ( returning a List ,
as List in Informix maps to Vector type in Java ) .
This External function of Informix is called from a stored procedure , which
executes the function and
stores the value to a List ( local ) variable . Using a foreach cursor the
result from the list is returned from the procedure
as a normal result set .
The problem with the above approach is that on executing the stored
procedure second time , the Java UDR returns a null value .
Please suggest is this approach valid , to return a Collection Object ( or
ResultSet )
from a UDR into a Informix Procedure .If yes , then why the function returns
a null upon executing it multiple times from a stored procedure .
Here is the source code for the function and Stored Procedure .
Java UDR .
_____________
public class TestRow{
public static Vector getNames() throws SQLException
{
Vector v = new Vector();
v.add("ABC");
v.add("DEF");
return v;
}
}
Informix External Function ( The above class is installed in the DB with a
jar ID 'tRow_jar ) .
________________
CREATE FUNCTION getNames()
RETURNING list( char(10) not null )
WITH (class='jvp')EXTERNAL NAME 'tRow_jar:TestRow.getNames()'
LANGUAGE JAVA;
___________________
Stored Procedure to Call the above function
_____________________
create procedure sp_getNames()
returning char(10)define l list( char(10) not null ) ;
define n char(10);
let l = getNames();
if l is NULL then ------ The function returns null when the procedure is
Invoked Second Time .
return "" ;
end if
foreach cursor1 for
select * into n
from table(l)
return n with resume;
end foreach ;
end procedure;
______________________________
------=_NextPart_000_00B1_01C344A4.1309F1D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><U>Following is a problem with =
returning=20
<STRONG>Collection DataType</STRONG> from Informix UDRs written in Java =
.=20
</U></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>A UDR is written in Java ( in Informix =
Database ) .=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The function returns a java.util.Vector =
. External=20
function declaration is written in Informix for it ( returning a List=20
,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>as List in Informix maps to Vector type =
in Java ) .=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This External function of Informix is =
called from a=20
stored procedure , which executes the function and</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>stores the value to a List ( local ) =
variable .=20
Using a foreach cursor the result from the list is returned from the =
procedure=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>as a normal result set . </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><EM>The problem with the above approach =
is that on=20
executing the stored procedure second time , the Java UDR returns a null =
value .=20
</EM></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Please suggest is this approach valid , =
to return=20
a Collection Object ( or ResultSet )</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>from a UDR into a Informix Procedure =
.If yes , then=20
why the function returns a null upon executing it multiple times from a =
stored=20
procedure . </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Here is the source code for the =
function and Stored=20
Procedure . </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Java UDR . </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>_____________</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>public class TestRow{</FONT></DIV>
<DIV><FONT face=3DArial size=3D2> public static Vector getNames() =
throws=20
SQLException<BR> {<BR> Vector v =3D new=20
Vector();<BR> v.add("ABC");<BR> v.add("DEF");<BR>&n=
bsp; return=20
v;<BR> }</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>}</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Informix External Function ( The =
above class=20
is installed in the DB with a jar ID 'tRow_jar ) . </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>________________</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>CREATE FUNCTION getNames()<BR>RETURNING =
list(=20
char(10) not null )<BR>WITH (class=3D'jvp')<BR>EXTERNAL NAME=20
'tRow_jar:TestRow.getNames()'<BR>LANGUAGE=20
JAVA;<BR>___________________</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Stored Procedure to Call the above =
function=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>_____________________</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>create procedure =
sp_getNames()<BR>returning=20
char(10)<BR>define l list( char(10) not null ) ;<BR>define n=20
char(10);</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>let l =3D getNames();</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>if l is NULL then ------ The =
function returns=20
null when the procedure is Invoked Second Time . <BR> return "" =
;<BR>end=20
if</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>foreach cursor1 for<BR>select * into =
n<BR>from=20
table(l)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>return n with resume;<BR>end foreach =
;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>end=20
procedure;<BR>______________________________</FONT></DIV></BODY></HTML>
------=_NextPart_000_00B1_01C344A4.1309F1D0--
sending to informix-list