agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feedSubject: [Pljava-dev] Calling pl/pgsql functions from pl/java?
Date: Mon, 10 Dec 2007 18:31:06 -0800
Message-ID: <475DF66A.4030400@mcsnw.com> (raw)
In-Reply-To: <141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.com>
References: <475DE7E2.3000506@mcsnw.com>
<141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.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
view thread (5+ messages) latest in thread
Message-ID: <475DF66A.4030400@mcsnw.com>
Permalink: ../475DF66A.4030400@mcsnw.com/
Also on: postgresql.org/message-id/475DF66A.4030400@mcsnw.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pljava-dev@postgresql.org
Subject: Re: [Pljava-dev] Calling pl/pgsql functions from pl/java?
In-Reply-To: <475DF66A.4030400@mcsnw.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox