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

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