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'd look at is the type mismatch between the args given in the pl/pgsql 'create or replace function' vs the args in the java declaration, that is, the int on one side vs the array of ints on the other. due to the lack of examples available that you previously pointed out, i have no real idea how that should look in a working example.
richard
________________________________________
From: Thomas Hill [Thomas.K.Hill@xxxxxxxxxxx]
Sent: Monday, November 19, 2012 2:55 PM
To: Welty, Richard; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxxx] 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
