[Pljava-dev] stuck with procedures having out parameters

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