[Pljava-dev] Calling pl/pgsql functions from pl/java?

[Jeffrey Lyon]

Lucas,

You need to position the ResultSet:
>                ResultSet res = stmt.getResultSet();
                    res.next();
>
>                ret = res.getString(1);
>                res.close();
>                stmt.close();
>                sql.close();

J


On Dec 10, 2007, at 8:29 PM, Lucas Madar wrote:

> Hello,
>
> I'm trying to call preexisting plpgsql functions from pljava. I've  
> been
> beating my head against a wall with them all day, and I can't seem to
> figure out what I'm doing wrong. Apologies if this is too much junk,  
> but
> this is a low-traffic list ;)
>
> Thanks,
> Lucas Madar
>
>
>
> Given the following setup, why does this happen? I can't seem to call
> *any* plpgsql functions from within pl/java.
>
> create or replace function pljava_test_internal(integer) returns  
> varchar
> as 'stupid.test.Dummy.test_internal' LANGUAGE 'javaU' VOLATILE;
> create or replace function pljava_test_external(integer) returns  
> varchar
> as 'stupid.test.Dummy.test_external' LANGUAGE 'javaU' VOLATILE;
>
> CREATE OR REPLACE FUNCTION plpgsql_test_external(varchar) RETURNS
> varchar AS $$
> BEGIN
>   return $1 || ' from plpgsql!';
> END;
> $$ LANGUAGE plpgsql VOLATILE;
>
> package stupid.test;
> import java.sql.*;
> public class Dummy {
>
>        public static String test_internal(int number) {
>                return "internal test la la la " + number;
>        }
>
>        public static String test_external(int number) throws  
> SQLException {
>                String text = "internal test la la la " + number;
>                Connection sql =
> DriverManager.getConnection("jdbc:default:connection");
>                String ret;
>
>                Statement stmt = sql.createStatement();
>                if(stmt.execute("SELECT plpgsql_test_external('" + text
> + "');"))
>                        throw new SQLException("No results from  
> query?");
>                ResultSet res = stmt.getResultSet();
>                ret = res.getString(1);
>                res.close();
>                stmt.close();
>                sql.close();
>
>                return ret;
>        }
> }
>
>
> # select pljava_test_internal(1);
>   pljava_test_internal
> --------------------------
> internal test la la la 1
> (1 row)
>
> # select plpgsql_test_external('test');
> plpgsql_test_external
> -----------------------
> test from plpgsql!
> (1 row)
>
> # select pljava_test_external(1);
> ERROR:  java.sql.SQLException: ResultSet is not positioned on a  
> valid row
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pljava-dev