agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] Calling pl/pgsql functions from pl/java?
5+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] Calling pl/pgsql functions from pl/java?
@ 2007-12-11 01:29
2007-12-11 02:17 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
0 siblings, 1 reply; 5+ messages in thread
From: @ 2007-12-11 01:29 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 01:29 [Pljava-dev] Calling pl/pgsql functions from pl/java?
@ 2007-12-11 02:17 `
2007-12-11 02:31 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
0 siblings, 1 reply; 5+ messages in thread
From: @ 2007-12-11 02:17 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 01:29 [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:17 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
@ 2007-12-11 02:31 `
2007-12-11 03:01 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
0 siblings, 1 reply; 5+ messages in thread
From: @ 2007-12-11 02:31 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 01:29 [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:17 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:31 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
@ 2007-12-11 03:01 `
2007-12-20 08:52 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
0 siblings, 1 reply; 5+ messages in thread
From: @ 2007-12-11 03:01 UTC (permalink / raw)
That's probably a question for the PostgreSQL guys, but it looks like
the function isn't actually being called until the moment that the
value is needed.
J
On Dec 10, 2007, at 9:31 PM, Lucas Madar wrote:
> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 01:29 [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:17 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:31 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 03:01 ` [Pljava-dev] Calling pl/pgsql functions from pl/java?
@ 2007-12-20 08:52 `
0 siblings, 0 replies; 5+ messages in thread
From: @ 2007-12-20 08:52 UTC (permalink / raw)
I finally had time to go back and look at this, and it appears that
this is unique to PL/Java.
When Statement.execute(query) is called, the statement is not actually
executed by that command if it is a select query. This behavior is in
SPIStatement.executePlan... If isCursorPlan() returns true, a cursor
is opened for the query instead of executing it; nothing is done until
the resultset is acquired and *used*.
This works, it's just kind of kludgy, especially for functions that
don't actually return a useful value.
stmt.execute("SELECT mangle_user(5)"); // works with jdbc postgresql
driver
ResultSet res = stmt.getResultSet(); res.next(); res.close(); //
kludge to get calls to work with plpgsql
Perhaps this should be documented somewhere?
Thanks,
Lucas Madar
On Dec 10, 2007, at 7:01 PM, Jeffrey Lyon wrote:
> That's probably a question for the PostgreSQL guys, but it looks
> like the function isn't actually being called until the moment that
> the value is needed.
>
> J
>
> On Dec 10, 2007, at 9:31 PM, Lucas Madar wrote:
>
>> 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.
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-12-20 08:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-11 01:29 [Pljava-dev] Calling pl/pgsql functions from pl/java?
2007-12-11 02:17 `
2007-12-11 02:31 `
2007-12-11 03:01 `
2007-12-20 08:52 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox