"Fill in all rows to the table" does not work for output fields JDBC Adapter Service on CustomSQL

, ,
1

I’ve set up a JDBC Adapter Service based on a CustomSQL template since I had to use OUTER JOINs and prefer the ANSI nomenclature. The SQL statement is quite long, but even simple SELECT statements like the following are not working:

SELECT t1.field1 as field1, t1.field1 as field2
FROM schema1.table1 t1
WHERE t1.field3 = ?

While there are only a few input fields which I don’t mind adding manually row by row, there are literally dozens of output fields which I don’t want to add manually to the output field table. I would rather use the “Fill in all rows to the table” button, but it does not work. Despite having read and applied all instructions in the manual (see “webMethods Adapter for JDBC Installation and User’s Guide”, chapter “Configuring CustomSQL Services”), simply nothing happens upon a click on this button.

Though the manual reads it is not necessary to define all output fields, because the JDBC adapter would use the ones from the SQL statement, the WSD generated for this adapter service by the Designer has not recognized any output fields at all. Hence, I assume this feature does work either.

Any hints would be highly appreciated!

Designer version: 9.12 (including all fixes/patches)

Best regards,
Marcus

2

I figured that
(1) it does work only for simple SELECT statements and only if the upper/lower writing matches the table names (in conflict with the SQL standard),
(2) it does not work with statements containing any JOIN clause, but with a list of tables in the FROM clause,
(3) it does not work with any WHERE clause at the end.

So the workaround (not solution) is to temporarily:
(1) Replace the JOIN clauses by a comma-separated list of all required tables,
(2) remove the WHERE clause,
(3) click the “Fill in all rows to the table” button for the output fields and
(4) copy&paste the actual SQL statement into the SQL field again.

Cheers,
Marcus