From: jeffreylyon at mac.com (Jeffrey Lyon)
Date: Mon, 10 Dec 2007 21:17:43 -0500
Subject: [Pljava-dev] Calling pl/pgsql functions from pl/java?
In-Reply-To: <475DE7E2.3000506@mcsnw.com>
References: <475DE7E2.3000506@mcsnw.com>
Message-ID: <141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.com>

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