Calling a UDF in informix from a Java App
I'm having a small problem calling a UDF, in a informix DB. I can call SP's
fine, but I get a "java.sql.SQLException: Routine (sp_test) can not be
resolved" when I attempt to call the UDF.
Code as follows
Java app code
---------
CallableStatement s = connection.prepareCall("{call sp_search(?,?)}");
s.setString(1, "text parm1");
s.setString(2, "text parm2");
ResultSet rs = s.executeQuery();
informix Reg
--------------
CREATE FUNCTION sp_test(lvarchar, lvarchar)RETURNS lvarchar
WITH (iterator, STACK = 64)
EXTERNAL NAME '/u/dwainc/java/crystal/iiug/bin/exec.bld(run_Test)'
LANGUAGE C not variant;
GRANT EXECUTE ON FUNCTION sp_test(lvarchar, lvarchar) TO PUBLIC;
If I change it to a SP it is found, it only dosen't find the procedure when
it's a UDF. I can run this UDF from within DBACCESS.
It only doesn't find it from my Java app, using JDBC.
What's the magic to making this call work?
Thank you for your help,
Dwain
"Dwain" <Clever_glove@hotmail.com> wrote in message news:<vdpj2oa0u0it0e@corp.supernews.com>...
> I'm having a small problem calling a UDF, in a informix DB. I can call SP's
> fine, but I get a "java.sql.SQLException: Routine (sp_test) can not be
> resolved" when I attempt to call the UDF.
>
> Code as follows
>
> Java app code
> ---------
> CallableStatement s = connection.prepareCall("{call sp_search(?,?)}");
Try:
CallableStatement s = connection.prepareCall("{call sp_search(CAST(?
AS LVARCHAR),CAST ( ? AS LVARCHAR))}");
I'm gonna bet (no machine to test this on) that this is a signature
resolution issue.
[ snip ]
> informix Reg
> --------------
> CREATE FUNCTION sp_test(lvarchar, lvarchar)> RETURNS lvarchar
> WITH (iterator, STACK = 64)
> EXTERNAL NAME '/u/dwainc/java/crystal/iiug/bin/exec.bld(run_Test)'
> LANGUAGE C not variant;
>
Err. .
You do know that your Java code is calling sp_search(), while your
UDF declaration here is for sp_test()?