agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] stuck with procedures having out parameters
4+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] stuck with procedures having out parameters
@ 2012-10-23 21:00
2012-10-23 21:30 ` [Pljava-dev] stuck with procedures having out parameters
0 siblings, 1 reply; 4+ messages in thread
From: @ 2012-10-23 21:00 UTC (permalink / raw)
Hi,
coming from Apache Derby and having implemented some stored procedures
using Java code there I am currently looking at plJava.
I successfully ported/re-used a method not taking any parameter and only
returning a String in PostgreSQ, i.e. the following works as expected:
public static String CURRENT_CLIENTID() throws SQLException {
String vcFKClientID = "000";
return vcFKClientID;
}
CREATE OR REPLACE FUNCTION rte."CURRENT_CLIENTID"()
RETURNS character varying AS
'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte."CURRENT_CLIENTID"()
OWNER TO postgres;
=> select rte."CURRENT_CLIENTID"() returns '000'
However I am not able to figure out why I am getting error message
"Unable to find static method allDatabasesPk.Procedures.SP_getNextID
with signature (Ljava/lang/String;)I" when doing the following:
(exactely the same Java code which works fine in my Apache Derby
environment)
public static void SP_getNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String cSQL = "SELECT \"LastValue\" \n" + "FROM rte.\"TBL_IDs\" \n"
+ "WHERE \"IDName\" = '" + vcIDName + "'\n";
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
iNextVal[0] = rs.getInt(1) + 1;
rs.updateInt("LastValue", iNextVal[0]);
rs.updateRow();
}
rs.close();
stmt.close();
return;
}
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte."SP_getNextID"(character varying)
OWNER TO postgres;
Can someone please point me in the right direction. I have consulted the
user guide and searched the internet but didn't actually find an example
showing the use of OUT parameters or a combination of IN and OUT parameters.
Thanks a lot in advance
Thomas
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] stuck with procedures having out parameters
2012-10-23 21:00 [Pljava-dev] stuck with procedures having out parameters
@ 2012-10-23 21:30 `
0 siblings, 0 replies; 4+ messages in thread
From: @ 2012-10-23 21:30 UTC (permalink / raw)
My guess is that you haven't set the class path - e.g. SELECT sqlj.set_classpath('public', 'lib1; lib2');
If you haven't done this, that's undoubtedly an issue you need to resolve first.
Note you can browse the tables on the sqlj schema to see what has been installed, what the class path is set to, etc.
Sent from my Tricorder
On Oct 23, 2012, at 2:00 PM, Thomas Hill <Thomas.K.Hill at t-online.de> wrote:
> Hi,
>
> coming from Apache Derby and having implemented some stored procedures using Java code there I am currently looking at plJava.
>
> I successfully ported/re-used a method not taking any parameter and only returning a String in PostgreSQ, i.e. the following works as expected:
>
> public static String CURRENT_CLIENTID() throws SQLException {
> String vcFKClientID = "000";
>
> return vcFKClientID;
> }
>
> CREATE OR REPLACE FUNCTION rte."CURRENT_CLIENTID"()
> RETURNS character varying AS
> 'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
> LANGUAGE java VOLATILE SECURITY DEFINER
> COST 100;
> ALTER FUNCTION rte."CURRENT_CLIENTID"()
> OWNER TO postgres;
>
> => select rte."CURRENT_CLIENTID"() returns '000'
>
> However I am not able to figure out why I am getting error message "Unable to find static method allDatabasesPk.Procedures.SP_getNextID with signature (Ljava/lang/String;)I" when doing the following:
> (exactely the same Java code which works fine in my Apache Derby environment)
>
> public static void SP_getNextID(int iNextVal[], String vcIDName)
> throws SQLException {
> Connection conn = getDefaultConnection();
>
> Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> String cSQL = "SELECT \"LastValue\" \n" + "FROM rte.\"TBL_IDs\" \n"
> + "WHERE \"IDName\" = '" + vcIDName + "'\n";
>
> ResultSet rs = stmt.executeQuery(cSQL);
>
> while (rs.next()) {
> iNextVal[0] = rs.getInt(1) + 1;
> rs.updateInt("LastValue", iNextVal[0]);
> rs.updateRow();
> }
>
> rs.close();
> stmt.close();
>
> return;
>
> }
>
> CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN "vcIDName" character varying)
> RETURNS integer AS
> 'allDatabasesPk.Procedures.SP_getNextID'
> LANGUAGE java VOLATILE SECURITY DEFINER
> COST 100;
> ALTER FUNCTION rte."SP_getNextID"(character varying)
> OWNER TO postgres;
>
> Can someone please point me in the right direction. I have consulted the user guide and searched the internet but didn't actually find an example showing the use of OUT parameters or a combination of IN and OUT parameters.
>
> Thanks a lot in advance
> Thomas
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pljava-dev
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] stuck with procedures having out parameters
@ 2012-10-26 06:35
2012-10-26 14:46 ` [Pljava-dev] stuck with procedures having out parameters
0 siblings, 1 reply; 4+ messages in thread
From: @ 2012-10-26 06:35 UTC (permalink / raw)
Classpath isn't the problem as I have another function (part of the same
JAR file
uploaded into same schema) working. It must have to do with the method
declaration and/or the method parameter handling and signature. Have reworked
the function definition to explicitely include the method signature, i.e.
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
Now the static method seems to be found. But when calling the function using
pgadmin and issuing a 'Select rte."SP_getNextID"('xy');' I am getting error
Too many parameters - expected 1
which I find confusing as I am only passing one parameter!?
Can someone please help here?
Note: if this goes through now, sorry for posting a new thread but trying to answer to existing previous thread did always lead to mail delivery errors.
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] stuck with procedures having out parameters
2012-10-26 06:35 [Pljava-dev] stuck with procedures having out parameters
@ 2012-10-26 14:46 `
0 siblings, 0 replies; 4+ messages in thread
From: @ 2012-10-26 14:46 UTC (permalink / raw)
So, I'd get rid of the out parameter, seeing as how that's what you're returning. Other than that, can you make the full test case available somewhere - github, etc? Email is a horrid place to put code...
On Oct 25, 2012, at 11:35 PM, Thomas Hill <Thomas.K.Hill at t-online.de> wrote:
> Classpath isn't the problem as I have another function (part of the same
> JAR file
> uploaded into same schema) working. It must have to do with the method
> declaration and/or the method parameter handling and signature. Have reworked
> the function definition to explicitely include the method signature, i.e.
>
> CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
> "vcIDName" character varying)
> RETURNS integer AS
> 'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
>
> Now the static method seems to be found. But when calling the function using
> pgadmin and issuing a 'Select rte."SP_getNextID"('xy');' I am getting error
> Too many parameters - expected 1
> which I find confusing as I am only passing one parameter!?
>
> Can someone please help here?
>
> Note: if this goes through now, sorry for posting a new thread but trying to answer to existing previous thread did always lead to mail delivery errors.
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pljava-dev
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-10-26 14:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-23 21:00 [Pljava-dev] stuck with procedures having out parameters
2012-10-23 21:30 `
2012-10-26 06:35 [Pljava-dev] stuck with procedures having out parameters
2012-10-26 14:46 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox