stored functions returning "void"

[Lukas Eder]

Espen,

I have seen that your H2 stored function implementation skips code generation for H2's stored functions returning void. I am now tackling https://sourceforge.net/apps/trac/jooq/ticket/170, and I can see that I will run into a similar thing with Postgres. Personally, I think of functions without return value to actually be like procedures and I would like to map them as such, even if the term "procedure" does not exist in the underlying RDBMS.

I guess that's a matter of taste. As I am mainly an "Oracle guy", I prefer this convention. What do you think? I created https://sourceforge.net/apps/trac/jooq/ticket/193 for the H2 database.

In the mean time, I have googled the support for returning result sets a bit more. This is going to be a really tricky one to map. I found the following data types to be valid return types for stored procedures / functions. All of them are currently missing in jOOQ:

• ARRAY (that's a JDBC standard https://sourceforge.net/apps/trac/jooq/ticket/163)
• ResultSet (that's not a JDBC standard), such as cursors in Oracle, JDBC ResultSet in H2, Postgres has something similar as well
  
• Tables (not a JDBC standard either), various implementations for this. Functions returning tables can be used in FROM clauses... That will be interesting

The ResultSet seems to be the most difficult one to standardise.

Cheers
Lukas

[Espen Strømsnes]

I was thinking the exact same when I was implementing the H2 functions, but was not sure if it was the correct way of doing it so I ignored them for the time being.

I'll fix #193 and implement void functions as procedures.

When it comes to the more complicated return types I personally don't have any experience with such and I'm uncertain of the actual need for mapping such procedures. 

Regards,

Espen

2011/1/2 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

Hi Espen,

Thanks for fixing #193! One more thing: Does H2 support IN/OUT and OUT parameters? Because those will always have to be used in procedures - called through JDBC's CallableStatement. I'm asking because I'm afraid that some RDBMS might mix IN/OUT, OUT parameters with return values, which strictly speaking shouldn't be possible...

About the complex return types: I know the need is there from personal experience and from feedback I get for jOOQ. This is always a quite complicated thing to do with JDBC, even if it's very useful. One of jOOQ's primary goals is to support legacy databases with lots of stored procedures and UDT's. In legacy systems, this is not a rare thing. On the other hand, there are hardly any legacy systems using the H2 database... So for H2, it is certainly not a priority. For Oracle, though, it is...

Cheers
Lukas

2011/1/3 Espen Strømsnes <estro...@gmail.com>

[Espen Strømsnes]

I'm not sure with the IN/OUT parameters in H2. I think they are not supported. I'll look into this when I get home from work ;-)

When I was testing the H2 functions I only implemented a void function with no parameters.

I'll take you word for it that the support complex return types is a high priority ;-)

My personal experience with (legacy systems and) stored procedures is with DB2 where they almost exclusively return built-in types.

Regards,

Espen

2011/1/3 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

Right after work? You really are addicted! ;-)

That's probably because UDT's with DB2 are such a pain, as we've experienced :-)
But with Oracle, PL/SQL it's really a nice thing...