Re: Experiences with pl/Java

i think pl/java may expect the method signatures to match up precisely. not entirely sure, as there are no
examples published as to how pl/java expects out parameters to work.

richard

________________________________________
From: Thomas Hill [Thomas.K.Hill@t-online.de]
Sent: Monday, November 19, 2012 5:56 PM
To: Welty, Richard; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Experiences with pl/Java

thanks - not sure how plJava works here and if the implementation is
identical to Apache Derby - what I can tell however is that defining the
types the way I did (integer on one side vs an array of integers on the
other side) is exactely how Apache Derby needs this as there out parms
always need to be defined as arrays in the method declaration and are
then automatically returned as integers - I will try to use integers on
both sides for plJava tomorrow, but if this would solve the problem this
would also mean that method declaration is different and depending on
data base backend implementation - which would make could re-use impossible


Am 19.11.2012 22:58, schrieb Welty, Richard:
> i looked your stuff over. i never needed to make out params work, so i'm kind of guessing here, but the first thing
i'dlook at is the type mismatch between the args given in the pl/pgsql 'create or replace function' vs the args in the
javadeclaration, that is, the int on one side vs the array of ints on the other. due to the lack of examples available
thatyou previously pointed out, i have no real idea how that should look in a working example. 
>
> richard
>
> ________________________________________
> From: Thomas Hill [Thomas.K.Hill@t-online.de]
> Sent: Monday, November 19, 2012 2:55 PM
> To: Welty, Richard; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Experiences with pl/Java
>
> Thanks to all for providing feedback and sharing opinions. Looks like
> you have gone much further on it than I thought someone would have. So I
> think I might spend some more time with it, but not plan to use it for
> my application in a production environment.
>
> My initial attempts were to try to re-use/port some simple procedures
> which are running fine on Apache Derby, but then I got stuck quite early
> in the process and could not find documentation showing how things needs
> to be done and helping me to understand what I am doing wrong.
>
> My first use case was calling a procedure which does not have any
> parameter and this I actually got to run, i.e.
>
> 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'
>
> My second use case was to create a procedure with an out parameter, i.e.
>
> 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(int[], java.lang.String)'
>
> 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!?
>
> I tried some variations I could think of, but without success.
> Unfortunately I have not found an exmaple anywhere on the web showing
> how this needs to be done.
>
> Would be great if someone could have a look at above and spot what I am
> doing wrong.
>
> Thanks a lot in advance.
>
> Kind regards
> Thomas
>
> Am 19.11.2012 20:19, schrieb Welty, Richard:
>> Edson Richter [edsonrichter@hotmail.com] writes:
>>> Em 19/11/2012 15:26, Welty, Richard escreveu:
>>>> PL/Java requires that the methods being directly called from PostgreSQL are static.
>>>> while i don't disagree with the advice, PL/Java is limited in this respect.
>>> :-) as I said, I know little about pl/Java... thanks for pointing this out.
>>> So, after calling the static method, probably inside the static methods,
>>> the programmer will create his/her logic. Writing this logic considering
>>> GC behavior would make your apps more stable.
>>> I would recommend to not use any Java library that keeps "caches" (ones
>>> like EclipseLink or Hibernate), unless you know exactly how to configure
>>> caches and soft/weak references - and their limitations.
>> i would probably recommend severely limiting what you try to do in PL/Java.
>>
>> in my particular project, the overall goal was incrementally doing push updates to an Apache
>> Solr search engine. this entailed building xml descriptions of the update that was required,
>> sending it with an http request (which required untrusted pl/java), and providing a recovery & retry
>> mechanism in case the http request failed, which limited itself to using the provided jdbc.
>>
>> i got it working and working well, but i put a lot of care into insuring that the garbage collecter
>> never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue
>> that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should
>> rethink your application design.
>>
>> richard
>>
>>
>