Hi Richard, I am now installing PPAS's limited use edition / trial version to see what can be done from our side. Looks like there is really a nice business case for ourselves here, given that people want to migrate their PL/SQL off Oracle onto PPAS, and then don't have the right Java integration tooling. This would be a perfect match for jOOQ - much better than JPA whose stored procedure support is only very limited.
Will keep you posted on progress. Best Regards, Lukas 2016-01-17 23:54 GMT+01:00 G. Richard Bellamy <[email protected]>: > Hey Lukas, > > As you mentioned, PPAS has pretty good support for OBJECT and TABLE types > at the database level, but is unfortunately missing the sophistication > necessary to support those types well via JDBC. This is our biggest > challenge right now. We rely heavily on stored procedures and functions > returning composite types, including collection types. > > While the Oracle JDBC drivers have had the extensions supporting STRUCT > and ARRAY for years, and further JDBC version 4 support for setObject and > getObject with type maps allowing a pure JDBC implementation of the SQLData > interface, the EDB & PostgreSQL Community JDBC drivers still rely on the > JDBC version 2 API getObject and createStruct methods which only support > the weakly referenced java.sql.Struct interface. > > So, yes, I had come to the same conclusion - in order to support these > PPAS types, jOOQ (and EclipseLink, Hibernate, etc acting as providers to > JPA) will have to include internal support. In jOOQ parlance, this is a > SQLDialect family. > > -rb > > On Sunday, January 17, 2016 at 11:55:15 AM UTC-8, Lukas Eder wrote: >> >> Hi Richard, >> >> Thank you very much for your enquiries and feedback. It is very >> interesting to see that Postgres Plus supports advanced ORDBMS syntax from >> Oracle, including PL/SQL OBJECT and TABLE types. That's something I had >> been missing in the past from the "ordinary" PostgreSQL distribution. Also, >> the PostgreSQL JDBC driver suffers from various issues when serialising >> composite types. >> >> From what you have mentioned, and from what I had assumed previously, >> this database product shouldn't be used with jOOQ's SQLDialect.POSTGRES, as >> many features, including these types, will work subtly differently. The >> best way forward would be for us to formally support and integration-test >> this database as an independent SQLDialect family. I have registered a >> feature request for this, which we can probably implement in jOOQ 3.8: >> https://github.com/jOOQ/jOOQ/issues/4944 >> >> At this point, I cannot comment on your concrete findings (e.g. the >> ClassCastException), except that I suspect that SQLDialect.POSTGRES is just >> not able to work with these Postgres Plus features. >> >> I'll keep you posted on our progress with #4944. I'm hoping to be able to >> present results any time soon. >> >> Best Regards, >> Lukas >> >> >> >> 2016-01-14 7:04 GMT+01:00 G. Richard Bellamy <[email protected]>: >> >>> I've been reading the GitHub issues (#1139 >>> <https://github.com/jOOQ/jOOQ/issues/1139> and #3375 >>> <https://github.com/jOOQ/jOOQ/issues/3375>) and the forum posts about >>> this (e.g. Strange behaviour of routines compared to regular SELECTs >>> <https://groups.google.com/forum/#!msg/jooq-user/rR8JDMJS-0E/WAVarRKYQxEJ>), >>> and I think I'm getting closer, but need more guidance. >>> >>> The closest I've got so far is the following: >>> >>> Record1<Object[]> results = >>> dslContext.select(BooksPkg.getGetBooksByAuthorId(authorId)).fetchAny(); >>> >>> ArrayList<BookObj> bookObjectRecords = new ArrayList<>(); >>> for (Object record : results.value1()) { >>> bookObjectRecords.add((BookObjectRecord)record); >>> } >>> return bookObjectRecords; >>> >>> >>> This produces the following SQL syntax: >>> >>> select "test"."author_pkg"."get_books_by_author"("authorId" := 1) >>> >>> And generates the following exception: >>> >>> java.lang.ClassCastException: com.edb.jdbc2.Struct cannot be cast to >>> com.test.udt.records.AuthorObjRecord >>> >>> DSL.selectFrom() doesn't work at all - the package routine signatures >>> will only allow returns of type Object[] or Field<Object[]>, and therefore >>> are not compatible with using the function in the FROM clause. Also, it >>> appears you deliberately decided against the "select <function name>" >>> syntax for returning TABLE valued functions - and here I am trying to use >>> that legacy syntax. >>> >>> -rb >>> >>> On Monday, January 11, 2016 at 11:47:02 PM UTC-8, G. Richard Bellamy >>> wrote: >>>> >>>> Like PostgreSQL, Postgres Plus Advanced Server (PPAS) has OBJECT types. >>>> Unlike PostgreSQL, it also has packages, and TABLE object types. And of >>>> course you can nest these types. You can also return these table types from >>>> functions. This will look very familiar to those of you familiar with >>>> Oracle: >>>> >>>> CREATE TYPE author_obj AS ( >>>> id numeric, >>>> address address_obj, >>>> books book_tbl >>>> ); >>>> >>>> CREATE TYPE book_obj AS ( >>>> id numeric, >>>> title varchar(50) >>>> ); >>>> >>>> CREATE TYPE author_tbl IS TABLE OF author_obj; >>>> CREATE TYPE book_tbl IS TABLE OF book_obj; >>>> >>>> FUNCTION get_books_by_author(authorId numeric) RETURN book_tbl IS >>>> >>>> bookTbl book_tbl := book_tbl(); >>>> >>>> BEGIN >>>> >>>> select * BULK COLLECT INTO bookTbl from (select book_tbl( >>>> id, >>>> title) >>>> from books >>>> where author_id = authorId; >>>> >>>> return bookTbl; >>>> >>>> END; >>>> >>>> FUNCTION get_authors() RETURN author_tbl IS >>>> >>>> authorTbl author_tbl := author_tbl(); >>>> >>>> BEGIN >>>> >>>> select * BULK COLLECT INTO authorTbl from (select * from authors) >>>> return authorTbl; >>>> >>>> END; >>>> >>>> Traditional PostgreSQL syntax would lead us (and jOOQ) to believe that >>>> we should get our data out of this function using the following syntax: >>>> SELECT "get_books_by_author" FROM "get_books_by_author"("authorId" := >>>> 1); >>>> >>>> However, the above produces an exception: >>>> ERROR: function "get_books_by_author" in FROM has unsupported return >>>> type book_tbl >>>> SQL state: 42804 >>>> >>>> Now, I can get ROWS back from the above using the following syntax: >>>> SELECT * FROM TABLE("get_books_by_author" FROM >>>> "get_books_by_author"("authorId" := 1)); >>>> >>>> I haven't tried it, but presumably the jOOQ "table" function should do >>>> the trick. I'll be testing that, and looking particularly at whether it'll >>>> work with further nested table object types (say, I wanted to get the >>>> author_tbl, which includes the nested book_tbl). >>>> >>>> Now to the purpose of this message - jOOQ understands the object >>>> notation used by Postgres to represent objects where "(1, \"Wind and >>>> the Willows\")" would represent a single book_obj and {"(1, \"Wind and >>>> the Willows\")","(2, \"The Hobbit\")"} would represent the book_tbl. >>>> >>>> In PPAS, in order to get that table type returned from a function, >>>> without unnesting, you need the query to look like this: >>>> SELECT "get_books_by_author"("authorId" := 1); >>>> >>>> In other words, I need to be able to alter the Dialect, extending it to >>>> the point where the emitted SQL from the DSL is altered to accommodate this >>>> new(ish) syntax. >>>> >>>> What is the best way to accomplish this? I've found where jOOQ alters >>>> the emitted SQL just before sending it off to JDBC (e.g. >>>> "executeSelectFromPOSTGRES"), but I'm guessing there's a better way than to >>>> have an "execute" override in all my codegen'd routines which extend >>>> "AbstractRoutine", which would then heavily modify the behavior. >>>> Furthermore, the abstract class is clearly annotated as INTERNAL ONLY, so >>>> that's a clue (heh) that I should stay away from implementing anything to >>>> do with that class myself. >>>> >>> -- >>> 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 [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
