From: lucas at mcsnw.com (Lucas Madar)
Date: Mon, 10 Dec 2007 18:31:06 -0800
Subject: [Pljava-dev] Calling pl/pgsql functions from pl/java?
In-Reply-To: <141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.com>
References: <475DE7E2.3000506@mcsnw.com>
	<141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.com>
Message-ID: <475DF66A.4030400@mcsnw.com>

Oops. That was a stupid mistake in my dummy code. In the much more 
complicated code, I've run into an interesting observation.

If I call a function with select function(arg1, arg2) and then ignore 
any returned result set, the function doesn't even get executed.
If the function doesn't have a return (a null), there's nothing I can do.

If the function, however, returns something and I process its resultset, 
the function is executed.

ie..

                        Statement b = dbq.getConnection().createStatement();
                        ResultSet res = b.executeQuery("SELECT 
qappVMeasurementResultReadingOpSum('asdf', 'asdf')");
                        res.close();
 
Does not call the function.

                        Statement b = dbq.getConnection().createStatement();
                        res = b.executeQuery("SELECT 
qappVMeasurementResultReadingOpSum('asdf', 'asdf')");
                        res.next();
                        System.out.println(res.getString(1));
                        res.close();

*DOES* call the function.

What's up with that?

- Lucas

Jeffrey Lyon wrote:
> 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
>

-- 

*Lucas Madar*
Madar Consulting Services, Inc
lucas at mcsnw.com
607.592.1518