[Pljava-dev] postgresql jdbc driver

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

Hi Thomas
I asked the person working on Compiere with PLJava the following:

The PLJava web site says it comes with:
"An embedded, high performance, JDBC driver utilizing the internal
PostgreSQL SPI routines."
So why do you also need the jdbc3-810 driver?

His answer:

Yes, I know PLJAVA has a JDBC Native, the main problem is
Compiere 252 has the following requirements:

# Full support of ANSI SQL 99 (CASE, all JOIN types, ..)
# Support of Views and Views on Views
# Support of User Defined Functions
(preferably via SQLJ - Java running in the database)
# Inline Views (e.g. SELECT ... FROM (SELECT xx FROM yy) ..)
# JDBC 3.0 Support (especially RowSet)

http://www.compiere.org/technology/database/index.html

Can you comment Thomas?

BTW, I noticed that the reply-to defaults to the senders email, not
pljava-dev at gborg.postgresql.org. So if you are not careful, not all
messages will be seen by everyone on the list.

=========================
Bart Bremmers, Gen. Mgr.
Craft-Bilt Materials Ltd.
Markham, Ontario

Bart Bremmers wrote:

> Hi Thomas
> I asked the person working on Compiere with PLJava the following:
>
> The PLJava web site says it comes with:
> "An embedded, high performance, JDBC driver utilizing the internal
> PostgreSQL SPI routines."
> So why do you also need the jdbc3-810 driver?

PLJava doesn't need a jdbc3-810 driver in itself. The deployer utility
and the example tester (both client programs) uses it. Both test and
deployment can also be done using psql. In fact, PLJava comes with two
scripts, install.sql and uninstall.sql for this purpose.

> His answer:
>
> Yes, I know PLJAVA has a JDBC Native, the main problem is
> Compiere 252 has the following requirements:
>
> # Full support of ANSI SQL 99 (CASE, all JOIN types, ..)
> # Support of Views and Views on Views
> # Support of User Defined Functions
> (preferably via SQLJ - Java running in the database)
> # Inline Views (e.g. SELECT ... FROM (SELECT xx FROM yy) ..)
> # JDBC 3.0 Support (especially RowSet)
>
> http://www.compiere.org/technology/database/index.html
>
> Can you comment Thomas?

None of the things you line up here are things that are of any concern
for the JDBC driver except the last one. The JDBC driver will pass SQL
statements to the backend more or less unaffected. The right thing to do
here is to look at how well the PostgreSQL backend meets those
requirements. I think most of SQL 99 is well covered as well as the view
related stuff. PLJava *is* "Java running in the database" so that
specific item is well covered :-)

SQLJ is far more then "Java running in the database" b.t.w. It consists
of three parts.
Part 0: Embedded SQL in Java
Part 1: SQL routines using Java
Part 2: SQL types using Java.

A good overview can be found here:
http://www.service-architecture.com/database/articles/sqlj.html

Part 0:, a fairly horrible concept that mangles Java code and produces
something that cannot be debugged has been abandoned by most, even
Oracle. Read more here:
http://otn.oracle.com/tech/java/sqlj_jdbc/pdf/oracle_sqlj_roadmap.pdf

PLJava has every intention of following the Part 1 and part 2 as closely
as possible.

The last item, the RowSet, is not applicable when you run inside of a
database. The RowSet is specifically designed to allow remote (and
disconnected) perusal of a ResultSet and is thus not at all applicable
to running inside of database functions.

PLJava certainly doesn't prevent you from using RowSet's if you really
want to of course. I just can't see the use-case where it would be
beneficial.

> BTW, I noticed that the reply-to defaults to the senders email, not
> pljava-dev at gborg.postgresql.org. So if you are not careful, not all
> messages will be seen by everyone on the list.

I'm not an expert in managing mailing lists. I do appreciate any advice
on how to improve the way it's set up.

Hope this helps,
Thomas Hallgren

Bart,
> So why do you also need the jdbc3-810 driver?
>
I realize that I might have misread what you asked for. But if the
requirements that he listed was an answer to "why they use the jdbc3-810
driver as a complement (or instead of?) the PLJava driver", then his
answer just make no sense at all:

> # Full support of ANSI SQL 99 (CASE, all JOIN types, ..)
Neither of the JDBC drivers are concerned with this.

> # Support of Views and Views on Views
Neither of the JDBC drivers are concerned with this.

> # Support of User Defined Functions
> (preferably via SQLJ - Java running in the database)
PLJava *is* user defined functions and it's JDBC driver allows you to
call other JDBC driver functions through SQL. Neither of the JDBC
drivers are concerned with SQLJ.

> # Inline Views (e.g. SELECT ... FROM (SELECT xx FROM yy) ..)
> # JDBC 3.0 Support (especially RowSet)
>
As stated before, RowSet's just makes no sense at all in the PLJava
domain, i.e. inside of a user defined function running inside the
database backend.

A final note and I'm not at all sure it's in any way applicable:

Using the client jdbc driver inside of PLJava is a *very* bad idea since
it would result in a new remote connection to the backend and hence, not
only severely degraded performance but also a different transaction. The
PLJava jdbc driver runs within the transaction that was started by the
caller of the user defined function. Virtually everything will break if
your violate transaction boundaries.

Regards,
Thomas Hallgren