Re: table valued functions (MS T-SQL)

[Lukas Eder]

Hello,

Unfortunately, TABLE types (and functions using TABLE types) are not yet explicitly supported for SQL Server:

https://github.com/jOOQ/jOOQ/issues/1070

Cheers

Lukas

2013/4/29 <g...@creativeworkline.com>

Hello,

The code generation for table valued functions seems to be inaccurate as jooq generates calls for stored procedures instead.. The only way I found was to manually write a low-level String-Select-Statement but that's a bad practice..

We are using jOOQ 2.6. Today I tried to update to 3.0, hoping to resolve this issue, by replacing the jOOQ-jars in the Maven-repository and updating the jooq-references in the jooq-config-files. But after that jOOQ only generated the system-folders and no tables or routines were generated.. So I reverted the update back to 2.6.

Is there any way to generate code for table valued functions including the table-schema from the returned datatable? If yes, can we stick to 2.6 or do we need to update to 3.0. If update is required, please give me a hint, what went wrong when I tried to update.

Kind regards
Michael

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Lukas Eder]

Good news!

Finally, I have implemented support for T-SQL table valued functions as documented in this issue here:

https://github.com/jOOQ/jOOQ/issues/1070

Given this function:

CREATE FUNCTION f_tables5 (@v1 INTEGER, @v2 INTEGER, @v3 INTEGER)
RETURNS @out_table TABLE (
    v INTEGER,
    s INTEGER
)
AS
BEGIN
    INSERT @out_table
    VALUES(@v1, @v1),
          (@v2, @v1 + @v2),
          (@v3, @v1 + @v2 + @v3)
    RETURN
END

The jOOQ code generator will generate a static utility method as such:

FTables5 fTables5(Integer v1, Integer v2, Integer v3) {
   // ...
}

This method would instanciate a table-valued function call, from which users can then typesafely dereference columns, e.g.

where(fTables5(1, 2, 3).S.eq(6))

Other databases (Derby, PostgreSQL) also support table-valued functions like SQL Server. Their support will be added in a later version.

Oracle knows TABLE, VARRAY, and CURSOR / REF CURSOR types, which are already supported by jOOQ.

Cheers

Lukas

2013/5/1 Lukas Eder <lukas...@gmail.com>