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
