agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] pl/java and oracle compatibility
4+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] pl/java and oracle compatibility
@ 2005-02-22 21:32
0 siblings, 1 reply; 4+ messages in thread
From: @ 2005-02-22 21:32 UTC (permalink / raw)
Hi Thomas,
I'm developing Oracle Database Triggers and Stored Procedures in Java.
I'm testing PL/Java Database Triggers in Postgresql 8.0.1.
Stored Functions are OK, mas database triggers are not compatible.
I read on gname that Oracle don't use java standards.
Is possible pl/java and oracle compatibility ?
Regards
Rodrigo Foscarini
__________________________________________________________________________
Acabe com aquelas janelinhas que pulam na sua tela.
AntiPop-up UOL - ? gr?tis!
http://antipopup.uol.com.br/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20050222/67c7383a/attachment.html;
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] pl/java and oracle compatibility
@ 2005-02-22 21:49
parent:
0 siblings, 0 replies; 4+ messages in thread
From: @ 2005-02-22 21:49 UTC (permalink / raw)
Rodrigo Foscarini wrote:
> Hi Thomas,
> I'm developing Oracle Database Triggers and Stored Procedures in Java.
> I'm testing PL/Java Database Triggers in Postgresql 8.0.1.
> Stored Functions are OK, mas database triggers are not compatible.
> I read on gname that Oracle don't use java standards.
> Is possible pl/java and oracle compatibility ?
> Regards
> Rodrigo Foscarini
It's certainly possible to create an Oracle compatibility layer that
makes the transition much easier. I'm not sure if it's possible to make
it 100% compatible. Do you have some brief text somewhere that describes
the Oracle trigger Java API? It's been a while since I last looked at it.
Regards,
Thomas Hallgren
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] pl/java and oracle compatibility
@ 2005-02-22 22:55
0 siblings, 0 replies; 4+ messages in thread
From: @ 2005-02-22 22:55 UTC (permalink / raw)
Thomas Hallgren wrote:
> Rodrigo Foscarini wrote:
>
>> Hi Thomas,
>> I'm developing Oracle Database Triggers and Stored Procedures in
>> Java. I'm testing PL/Java Database Triggers in Postgresql 8.0.1.
>> Stored Functions are OK, mas database triggers are not compatible.
>> I read on gname that Oracle don't use java standards.
>> Is possible pl/java and oracle compatibility ?
>> Regards
>> Rodrigo Foscarini
>
> It's certainly possible to create an Oracle compatibility layer that
> makes the transition much easier. I'm not sure if it's possible to
> make
> it 100% compatible. Do you have some brief text somewhere that
> describes
> the Oracle trigger Java API? It's been a while since I last looked at
> it.
I just converted a bunch of Oracle triggers to PG (PL/SQL, not PL/Java).
The biggest problem I saw was that in Oracle, you can have the entire
body of your trigger code in the stored proc for the trigger. In PG,
you can only execute a function. So you have to move your Oracle logic
into a separate function, then have the trigger invoke that. But the
rest carried over pretty straightforward. But my triggers are very
simple.
>
> Regards,
> Thomas Hallgren
>
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
--
Guy Rouillier
^ permalink raw reply [nested|flat] 4+ messages in thread
* [Pljava-dev] pl/java and oracle compatibility
@ 2005-02-23 08:17
0 siblings, 0 replies; 4+ messages in thread
From: @ 2005-02-23 08:17 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-23 08:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 21:32 [Pljava-dev] pl/java and oracle compatibility
2005-02-22 21:49 `
2005-02-22 22:55 [Pljava-dev] pl/java and oracle compatibility
2005-02-23 08:17 [Pljava-dev] pl/java and oracle compatibility
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox