UDT and stored procedures integration

[Lukas Eder]

Hello Espen,

I can see you cannot keep your fingers off of jOOQ either, in the new year. :)
With your help, the feature-set will become more and more complete for the RDBMS that we're currently supporting. In the mean time, I am trying to find bloggers on the web that share my enthusiasm for plain SQL and little abstraction. It's a lot of work, but they're out there. An interesting example:
http://database-programmer.blogspot.com/2010/12/historical-perspective-of-orm-and.html?showComment=1293745930191#c7418679640972829066

I hope to find people that test and write about jOOQ. In Google, jOOQ (or my articles, comments about jOOQ), slowly rise up in ranking, when searching explicitly for "stored procedures", "UDTs" and similar vendor-specific SQL extensions. So if you, or your company, has a blog and you're planning on integrating jOOQ, feel free to blog about it! :-)

Anyways...

I have modified the inner workings of jOOQ to allow for the handling of IN/OUT and OUT parameters that are declared as UDTs:
http://sourceforge.net/apps/trac/jooq/ticket/162

This turned out to be a rather tricky task. I found out how to do it correctly in general when using the Oracle JDBC driver:
http://oracleadvisor.com/documentation/oracle/database/11.2/java.112/e10589/oraoot.htm

The change of implementation is mainly found in org.jooq.impl.UDTConstant. As of release 1.5.3, UDT's should not be inlined anymore (where possible), but directly passed as bind variables. Instead of writing:

INSERT INTO T_AUTHOR (address) VALUES (u_address_type(?, ?, ?, ...));

We should have

INSERT INTO T_AUTHOR (address) VALUES (?);

This is easily possible with Oracle's implementation of the JDBC's various getObject(), and setObject() methods. Apart from Oracle, we have the current situation:

• DB2: #162 might resolve some problems as well, maybe?
• Postgres: There is no support for stored procedures / functions yet. This topic has to be reconsidered as of #170
• Other RDBMS: No UDT support (yet), so no problems.

Cheers
Lukas