Subject: [Pljava-dev] pl/java and oracle compatibility
Date: Wed, 23 Feb 2005 09:17:20 +0100
Message-ID: <thhal-0k9T3AqPYxicqnRYG9AviDakRnHkMDj@mailblocks.com> (raw)
In-Reply-To: <ICCLWN$0040A8FD36E7EE4B959BA9914F0E5656@uol.com.br>
References: <ICCLWN$0040A8FD36E7EE4B959BA9914F0E5656@uol.com.br>
<421C3C10.2070005@mailblocks.com>
<thhal-0ktT3Ap/YxichWbt4tRose+2vMMw/sH@mailblocks.com>
<thhal-0ktT3AqHYxiclynf8nRR0PpkdURjG6u@mailblocks.com>
Rodrigo,
Thanks for the examples. I can directly conclude that the parameter
passing from the trigger to the procedure in the Oracle case is
something that PLJava has no control over and thus can do nothing about.
The Oracle trigger passes a non-constant value as a parameter to the
stored procedure and the stored procedure makes use of an OUT parameter.
The PostgreSQL syntax stipulates that you call a function with zero or
more constant arguments (you use this to pass a name of a column since
the non-constant value of that column cannot be passed on). Further
more, PostgreSQL doesn't have stored procedures and their functions does
not allow OUT parameters.
Having said that, what *can* be done using PLJava?
Well, first of all, I'd like to rewrite your PLJava example so that it
performs the exact same task as your Oracle example (the examples you
sent where very different):
public static void DeptIdJava(TriggerData td)
throws SQLException
{
Connection c = DriverManager.getConnection( "jdbc:default:connection" );
String vQuery = "select lkup_key.nextval from dual";
PreparedStatement st = c.prepareStatement( vQuery );
ResultSet rs = st.executeQuery();
rs.next();
td.getNew().updateInt(td.getArguments()[0], rs.getInt( "proximo" ));
st.close();
}
Now there is one single line differentiateing the two.
If you want to keep your Oracle trigger 100% intact, you can instead add
a dispatch class with for the PLJava case like so:
public class PLJavaTesteTrigger
{
public static void DeptIdJava(TriggerData td)
{
int[] deptIdOut = new int[1];
TesteTrigger.DeptIdJava(deptIdOut);
td.getNew().updateInt(td.getArguments()[0], deptIdOut[0]);
}
}
(the use of td.getArguments()[0] could perhaps be replaced by a
hardcoded "dept_id" if that's always the case)
Keep your dispatch classes in a separate package that you don't bundle
with the Oracle version.
A side note:
I see a System.exit() in your example. Starting with the next version of
PLJava (due this week), an attempt to do System.exit() will yield a
SecurityException since such a call effectively kills the backend
process. My advice is to let your method throw an SQLException when it
encounters a problem. That will cause a controlled transaction rollback.
Regards,
Thomas Hallgren
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pljava-dev@postgresql.org
Subject: Re: [Pljava-dev] pl/java and oracle compatibility
In-Reply-To: <thhal-0k9T3AqPYxicqnRYG9AviDakRnHkMDj@mailblocks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox