Informix UDR Problem

Informix UDR Problem

Post by Sunil Incha » Wed, 09 Jul 2003 06:23:18



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;
 }

Quote:}

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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Please suggest is this approach valid , =
to return=20
a&nbsp; Collection Object ( or &nbsp;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>&nbsp;</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>&nbsp;public static Vector getNames() =
throws=20
SQLException<BR>&nbsp;{<BR>&nbsp;&nbsp;Vector v =3D&nbsp; new=20
Vector();<BR>&nbsp;&nbsp;v.add("ABC");<BR>&nbsp;&nbsp;v.add("DEF");<BR>&n=
bsp;&nbsp;return=20
v;<BR>&nbsp;}</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>}</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Informix External Function&nbsp; ( 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>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>let l =3D getNames();</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>if l is NULL then&nbsp; ------ The =
function returns=20
null when the procedure is Invoked Second Time . <BR>&nbsp;return "" =
;<BR>end=20
if</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</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>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>return n with resume;<BR>end foreach =
;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>end=20
procedure;<BR>______________________________</FONT></DIV></BODY></HTML>

------=_NextPart_000_00B1_01C344A4.1309F1D0--

sending to informix-list

 
 
 

1. UDR Support in Informix Dynamic Server.

Can someone tell me what exact version
of Informix Dynamic Server that first
supported C User Defined Routines.

I've tried searching for this on their
website with no joy. (their advanced
search doesnt work at all !)

Rgds,
Peter.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. English vs. German Installation

3. HELP !!! Problem with registration of Java UDR

4. passwords in SQL server

5. UDR-Problem, shared libs

6. univ ingres front-end wanted?

7. Java UDR Problem

8. Changing indexes in tables with relations

9. Java UDR problems.