7 Replies Latest reply on Nov 20, 2018 12:45 PM by shawkins

Configuring multirow result for rest service

gadeyne.bram Nov 16, 2018 10:25 AM

Hi,

I'm trying to configure a REST service in Teiid.

I have this procedure definition:

CREATE PROCEDURE restnumber()
RETURNS TABLE(
    nr integer not null
)
OPTIONS (
    "teiid_rest:method" 'GET',
    "teiid_rest:uri" '/restnumber'
)
AS
BEGIN
    SELECT 0 AS nr
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
    UNION SELECT 7
    UNION SELECT 8
    UNION SELECT 9;
END

When I access the procedure using http://localhost:8080/vdb_1.2.0/rest/restnumber , I get a 0 (integer value) as a result. Is it possible that this is only returning the first row from the query?

This is the full log

teiid | 15:22:40,498 DEBUG [io.undertow.request] (default I/O-4) Matched prefix path /vdb_1.2.0 for path /vdb_1.2.0/rest/restnumber

teiid | 15:22:40,503 DEBUG [io.undertow.request.security] (default task-3) Security constraints for request /vdb_1.2.0/rest/restnumber are [SingleConstraintMatch{emptyRoleSemantic=PERMIT,

requiredRoles=[rest]}]

teiid | 15:22:40,513 DEBUG [io.undertow.request.security] (default task-3) Authenticating required for request HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[text/html

,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip, deflate,

br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic cmVzdHVzZX

I6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.1542352980.

8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-Requests=[1], Host=[localhost:8080]} response {X-Powered-By=[Undertow/1], Server=[Wild

Fly/11]}}

teiid | 15:22:40,513 DEBUG [io.undertow.request.security] (default task-3) Setting authentication required for exchange HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[

text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip,

deflate, br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic c

mVzdHVzZXI6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.15

42352980.8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-Requests=[1], Host=[localhost:8080]} response {X-Powered-By=[Undertow/1], Ser

ver=[WildFly/11]}}

teiid | 15:22:40,513 DEBUG [io.undertow.request.security] (default task-3) Attempting to authenticate HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[text/html,applicat

ion/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip, deflate, br], Use

r-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic cmVzdHVzZXI6MTIzNDU

2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.1542352980.8; survey

=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-Requests=[1], Host=[localhost:8080]} response {Expires=[0], Cache-Control=[no-cache, no-store,

must-revalidate], X-Powered-By=[Undertow/1], Server=[WildFly/11], Pragma=[no-cache]}}, authentication required: true

teiid | 15:22:40,513 DEBUG [io.undertow.request.security] (default task-3) Authentication outcome was NOT_ATTEMPTED with method io.undertow.security.impl.CachedAuthenticatedSessionMechanis

m@1d7a52c6 for HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-N

L,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip, deflate, br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) C

hrome/70.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic cmVzdHVzZXI6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none);

_ga=GA1.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.1542352980.8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Ins

ecure-Requests=[1], Host=[localhost:8080]} response {Expires=[0], Cache-Control=[no-cache, no-store, must-revalidate], X-Powered-By=[Undertow/1], Server=[WildFly/11], Pragma=[no-cache]}}

teiid | 15:22:40,513 DEBUG [io.undertow.request.security] (default task-3) Found basic auth header restuser:123456 (decoded using charset UTF-8) in HttpServerExchange{ GET /vdb_1.2.0/rest/

restnumber request {Accept=[text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-ag

e=0], Accept-Encoding=[gzip, deflate, br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36], Connection=[keep-al

ive], Authorization=[Basic cmVzdHVzZXI6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.1.181878873.1535716647; __utma=111872281.181878

873.1535716647.1542283142.1542352980.8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-Requests=[1], Host=[localhost:8080]} response {Ex

pires=[0], Cache-Control=[no-cache, no-store, must-revalidate], X-Powered-By=[Undertow/1], Server=[WildFly/11], Pragma=[no-cache]}}

teiid | 15:22:40,514 DEBUG [io.undertow.request.security] (default task-3) Authenticated as restuser, roles [rest]

teiid | 15:22:40,514 DEBUG [io.undertow.request.security] (default task-3) Authentication outcome was AUTHENTICATED with method io.undertow.security.impl.BasicAuthenticationMechanism@14294

832 for HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=

0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip, deflate, br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/7

0.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic cmVzdHVzZXI6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1

.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.1542352980.8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-R

equests=[1], Host=[localhost:8080]} response {Expires=[0], Cache-Control=[no-cache, no-store, must-revalidate], X-Powered-By=[Undertow/1], Server=[WildFly/11], Pragma=[no-cache]}}

teiid | 15:22:40,514 DEBUG [io.undertow.request.security] (default task-3) Authentication result was AUTHENTICATED for HttpServerExchange{ GET /vdb_1.2.0/rest/restnumber request {Accept=[t

ext/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8], Accept-Language=[nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7], Cache-Control=[max-age=0], Accept-Encoding=[gzip,

deflate, br], User-Agent=[Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36], Connection=[keep-alive], Authorization=[Basic cm

VzdHVzZXI6MTIzNDU2], Cookie=[__utmz=111872281.1535716647.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); _ga=GA1.1.181878873.1535716647; __utma=111872281.181878873.1535716647.1542283142.154

2352980.8; survey=92a5006507111474ca24de02b2d154a0; PHPSESSID=ea531a9cb996f91242c35edd1c847641], Upgrade-Insecure-Requests=[1], Host=[localhost:8080]} response {Expires=[0], Cache-Control=[no-

cache, no-store, must-revalidate], X-Powered-By=[Undertow/1], Server=[WildFly/11], Pragma=[no-cache]}}

teiid | 15:22:40,515 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) RESTEASY002315: PathInfo: /rest/restnumber

teiid | 15:22:40,529 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) MessageBodyWriter: org.jboss.resteasy.spi.ResteasyProviderFactory$SortedKey

teiid | 15:22:40,530 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) MessageBodyWriter: org.jboss.resteasy.plugins.providers.StreamingOutputProvider

teiid | 15:22:40,530 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) Interceptor Context: org.jboss.resteasy.core.interception.ServerWriterInterceptorContext, Method : pro

ceed

teiid | 15:22:40,534 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) WriterInterceptor: org.jboss.resteasy.security.doseta.DigitalSigningInterceptor

teiid | 15:22:40,535 DEBUG [org.jboss.resteasy.security.doseta.i18n] (default task-3) Interceptor : org.jboss.resteasy.security.doseta.DigitalSigningInterceptor, Method : aroundWriteTo

teiid | 15:22:40,535 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) Interceptor Context: org.jboss.resteasy.core.interception.ServerWriterInterceptorContext, Method : pro

ceed

teiid | 15:22:40,536 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) MessageBodyWriter: org.jboss.resteasy.spi.ResteasyProviderFactory$SortedKey

teiid | 15:22:40,536 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) MessageBodyWriter: org.jboss.resteasy.plugins.providers.StreamingOutputProvider

teiid | 15:22:40,536 DEBUG [org.jboss.resteasy.resteasy_jaxrs.i18n] (default task-3) Provider : org.jboss.resteasy.plugins.providers.StreamingOutputProvider, Method : writeTo

teiid | 15:22:40,537 DEBUG [org.teiid.AUDIT_LOG] (default task-3) [vdb.1.2.0, null anonymous USERPASSWORD null null null true] <session.logon-request>

teiid | 15:22:40,538 DEBUG [org.teiid.SECURITY] (default task-3) authenticateUser anonymous JDBC

teiid | 15:22:40,544 DEBUG [org.teiid.SECURITY] (default task-3) Logon successful, created session: sessionid=2QFHglxhS/PM; userName=restuser; vdbName=vdb; vdbVersion=1.2.0; createdTime=Fr

i Nov 16 15:22:40 UTC 2018; applicationName=JDBC; clientHostName=null; clientHardwareAddress=null; IPAddress=null; securityDomain=teiid-security; lastPingTime=Fri Nov 16 15:22:40 UTC 2018

teiid | 15:22:40,544 DEBUG [org.teiid.AUDIT_LOG] (default task-3) [restuser] <session.logon-success>

teiid | 15:22:40,545 FINE [org.teiid.jdbc] (default task-3) Successfully obtained a session.

teiid | 15:22:40,545 FINE [org.teiid.jdbc] (default task-3) Connection Url=

teiid | 15:22:40,545 FINE [org.teiid.jdbc] (default task-3) ApplicationName=JDBC

teiid | 15:22:40,545 FINE [org.teiid.jdbc] (default task-3) PassthroughAuthentication=true

teiid | 15:22:40,545 FINE [org.teiid.jdbc] (default task-3) VirtualDatabaseName=vdb.1.2.0

teiid | 15:22:40,546 FINE [org.teiid.jdbc] (default task-3) The JDBC Driver successfully obtained a connection.

teiid | 15:22:40,546 DEBUG [org.teiid.COMMAND_LOG] (default task-3) 2QFHglxhS/PM START USER COMMAND: startTime=2018-11-16 15:22:40.546 requestID=2QFHglxhS/PM.0 txID=nul

l sessionID=2QFHglxhS/PM applicationName=JDBC principal=restuser vdbName=vdb vdbVersion=1.2.0 sql=SELECT VDBName PROCEDURE_CAT, SchemaName AS PROCEDURE_SCHEM, Procedu

reName AS PROCEDURE_NAME, p.Name AS COLUMN_NAME, convert(decodeString(TYPE, 'In,1, Out,4, InOut,2, ReturnValue,5, ResultSet,3', ','), short) AS COLUMN_TYPE, TypeCode AS DATA_TYPE, TypeName AS

TYPE_NAME, ColumnSize AS "PRECISION", TypeLength AS LENGTH, convert(case when scale > 32767 then 32767 else Scale end, short) AS SCALE, Radix AS RADIX, convert(decodeString(NullType, 'No Null

s, 0, Nullable, 1, Unknown, 2', ','), integer) AS NULLABLE, p.Description AS REMARKS, DefaultValue AS COLUMN_DEF, NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, NULL AS CHAR_OCTET_LENGTH, p.

Position AS ORDINAL_POSITION, CASE NullType WHEN 'Nullable' THEN 'YES' WHEN 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE, p.ProcedureName as SPECIFIC_NAME FROM SYS.ProcedureParams as p WHER

E UCASE(VDBName) LIKE ? ESCAPE '\' AND UCASE(SchemaName) LIKE ? ESCAPE '\' AND UCASE(ProcedureName) LIKE ? ESCAPE '\' AND UCASE(p.Name) LIKE ? ESCAPE '\' ORDER BY PROCEDURE_SCHEM, PROCEDUR

E_NAME, case TYPE when 'ReturnValue' then 0 when 'ResultSet' then 2 else 1 end, POSITION

teiid | 15:22:40,547 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Request Thread 2QFHglxhS/PM.0 with state NEW

teiid | 15:22:40,554 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.0 Command has no cache hint and result set cache mode is not on.

teiid | 15:22:40,554 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.0 executing prepared SELECT VDBName PROCEDURE_CAT, SchemaName AS PROCEDURE_SCHEM, ProcedureName A

S PROCEDURE_NAME, p.Name AS COLUMN_NAME, convert(decodeString(TYPE, 'In,1, Out,4, InOut,2, ReturnValue,5, ResultSet,3', ','), short) AS COLUMN_TYPE, TypeCode AS DATA_TYPE, TypeName AS TYPE_NAM

E, ColumnSize AS "PRECISION", TypeLength AS LENGTH, convert(case when scale > 32767 then 32767 else Scale end, short) AS SCALE, Radix AS RADIX, convert(decodeString(NullType, 'No Nulls, 0, Nu

llable, 1, Unknown, 2', ','), integer) AS NULLABLE, p.Description AS REMARKS, DefaultValue AS COLUMN_DEF, NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, NULL AS CHAR_OCTET_LENGTH, p.Position

AS ORDINAL_POSITION, CASE NullType WHEN 'Nullable' THEN 'YES' WHEN 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE, p.ProcedureName as SPECIFIC_NAME FROM SYS.ProcedureParams as p WHERE UCASE(

VDBName) LIKE ? ESCAPE '\' AND UCASE(SchemaName) LIKE ? ESCAPE '\' AND UCASE(ProcedureName) LIKE ? ESCAPE '\' AND UCASE(p.Name) LIKE ? ESCAPE '\' ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME,

case TYPE when 'ReturnValue' then 0 when 'ResultSet' then 2 else 1 end, POSITION

teiid | 15:22:40,567 DEBUG [org.teiid.PLANNER] (default task-3) 2QFHglxhS/PM [LOW [Relational Planner] SearchedCase is not supported by source SYS - CASE WHEN p.Type = 'ReturnValue' THEN 0

WHEN p.Type = 'ResultSet' THEN 2 ELSE 1 END was not pushed]

teiid | 15:22:40,571 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM ProcessTree for 2QFHglxhS/PM.0 SortNode(0) output=[p.VDBName AS PROCEDURE_CAT, p.SchemaName AS PROCEDURE_SCHEM

, p.ProcedureName AS PROCEDURE_NAME, p.Name AS COLUMN_NAME, convert(CASE WHEN p.Type = 'In' THEN '1' WHEN p.Type = 'Out' THEN '4' WHEN p.Type = 'InOut' THEN '2' WHEN p.Type = 'ReturnValue' THE

N '5' WHEN p.Type = 'ResultSet' THEN '3' ELSE p.Type END, short) AS COLUMN_TYPE, p.TypeCode AS DATA_TYPE, p.TypeName AS TYPE_NAME, p.ColumnSize AS "PRECISION", p.TypeLength AS LENGTH, convert(

CASE WHEN p.Scale > 32767 THEN 32767 ELSE p.Scale END, short) AS SCALE, p.Radix AS RADIX, convert(CASE WHEN p.NullType = 'No Nulls' THEN '0' WHEN p.NullType = 'Nullable' THEN '1' WHEN p.NullTy

pe = 'Unknown' THEN '2' ELSE p.NullType END, integer) AS NULLABLE, p.Description AS REMARKS, p.DefaultValue AS COLUMN_DEF, null AS SQL_DATA_TYPE, null AS SQL_DATETIME_SUB, null AS CHAR_OCTET_L

ENGTH, p.Position AS ORDINAL_POSITION, CASE WHEN p.NullType = 'Nullable' THEN 'YES' WHEN p.NullType = 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE, p.ProcedureName AS SPECIFIC_NAME] [SORT]

[PROCEDURE_SCHEM, PROCEDURE_NAME, CASE WHEN p.Type = 'ReturnValue' THEN 0 WHEN p.Type = 'ResultSet' THEN 2 ELSE 1 END, ORDINAL_POSITION]

teiid | ProjectNode(1) output=[p.VDBName AS PROCEDURE_CAT, p.SchemaName AS PROCEDURE_SCHEM, p.ProcedureName AS PROCEDURE_NAME, p.Name AS COLUMN_NAME, convert(CASE WHEN p.Type = 'In' THEN

'1' WHEN p.Type = 'Out' THEN '4' WHEN p.Type = 'InOut' THEN '2' WHEN p.Type = 'ReturnValue' THEN '5' WHEN p.Type = 'ResultSet' THEN '3' ELSE p.Type END, short) AS COLUMN_TYPE, p.TypeCode AS DA

TA_TYPE, p.TypeName AS TYPE_NAME, p.ColumnSize AS "PRECISION", p.TypeLength AS LENGTH, convert(CASE WHEN p.Scale > 32767 THEN 32767 ELSE p.Scale END, short) AS SCALE, p.Radix AS RADIX, convert

(CASE WHEN p.NullType = 'No Nulls' THEN '0' WHEN p.NullType = 'Nullable' THEN '1' WHEN p.NullType = 'Unknown' THEN '2' ELSE p.NullType END, integer) AS NULLABLE, p.Description AS REMARKS, p.De

faultValue AS COLUMN_DEF, null AS SQL_DATA_TYPE, null AS SQL_DATETIME_SUB, null AS CHAR_OCTET_LENGTH, p.Position AS ORDINAL_POSITION, CASE WHEN p.NullType = 'Nullable' THEN 'YES' WHEN p.NullTy

pe = 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE, p.ProcedureName AS SPECIFIC_NAME, CASE WHEN p.Type = 'ReturnValue' THEN 0 WHEN p.Type = 'ResultSet' THEN 2 ELSE 1 END] [p.VDBName AS PROCE

DURE_CAT, p.SchemaName AS PROCEDURE_SCHEM, p.ProcedureName AS PROCEDURE_NAME, p.Name AS COLUMN_NAME, convert(CASE WHEN p.Type = 'In' THEN '1' WHEN p.Type = 'Out' THEN '4' WHEN p.Type = 'InOut'

THEN '2' WHEN p.Type = 'ReturnValue' THEN '5' WHEN p.Type = 'ResultSet' THEN '3' ELSE p.Type END, short) AS COLUMN_TYPE, p.TypeCode AS DATA_TYPE, p.TypeName AS TYPE_NAME, p.ColumnSize AS "PRE

CISION", p.TypeLength AS LENGTH, convert(CASE WHEN p.Scale > 32767 THEN 32767 ELSE p.Scale END, short) AS SCALE, p.Radix AS RADIX, convert(CASE WHEN p.NullType = 'No Nulls' THEN '0' WHEN p.Nul

lType = 'Nullable' THEN '1' WHEN p.NullType = 'Unknown' THEN '2' ELSE p.NullType END, integer) AS NULLABLE, p.Description AS REMARKS, p.DefaultValue AS COLUMN_DEF, null AS SQL_DATA_TYPE, null

AS SQL_DATETIME_SUB, null AS CHAR_OCTET_LENGTH, p.Position AS ORDINAL_POSITION, CASE WHEN p.NullType = 'Nullable' THEN 'YES' WHEN p.NullType = 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE,

p.ProcedureName AS SPECIFIC_NAME, CASE WHEN p.Type = 'ReturnValue' THEN 0 WHEN p.Type = 'ResultSet' THEN 2 ELSE 1 END]

teiid | AccessNode(2) output=[p.VDBName, p.SchemaName, p.ProcedureName, p.Name, p.DataType, p.Position, p.Type, p.Optional, p."Precision", p.TypeLength, p.Scale, p.Radix, p.NullType, p.

UID, p.Description, p.TypeName, p.TypeCode, p.ColumnSize, p.DefaultValue] SELECT SYS.ProcedureParams.VDBName, SYS.ProcedureParams.SchemaName, SYS.ProcedureParams.ProcedureName, SYS.ProcedurePa

rams.Name, SYS.ProcedureParams.Type, SYS.ProcedureParams.TypeCode, SYS.ProcedureParams.TypeName, SYS.ProcedureParams.ColumnSize, SYS.ProcedureParams.TypeLength, SYS.ProcedureParams.Scale, SYS.

ProcedureParams.Radix, SYS.ProcedureParams.NullType, SYS.ProcedureParams.Description, SYS.ProcedureParams.DefaultValue, SYS.ProcedureParams.Position FROM SYS.ProcedureParams WHERE (UCASE(SYS.P

rocedureParams.VDBName) LIKE ? ESCAPE '\') AND (UCASE(SYS.ProcedureParams.SchemaName) LIKE ? ESCAPE '\') AND (UCASE(SYS.ProcedureParams.ProcedureName) LIKE ? ESCAPE '\') AND (UCASE(SYS.Procedu

reParams.Name) LIKE ? ESCAPE '\')

teiid |

teiid | 15:22:40,572 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@57eee64f(2QFHglxhS/PM)

teiid | 15:22:40,577 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM after getOrCreateTransactionContext : 2QFHglxhS/PM NONE ID:NONE

teiid | 15:22:40,578 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Created intermediate sort buffer 21

teiid | 15:22:40,580 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Created intermediate sort buffer 22

teiid | 15:22:40,585 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM QueryProcessor: closing processor

teiid | 15:22:40,585 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.0 Finished Processing

teiid | 15:22:40,586 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM [RequestWorkItem.sendResultsIfNeeded] requestID: 2QFHglxhS/PM.0 resultsID: 20 done: true

teiid | 15:22:40,586 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Request Thread 2QFHglxhS/PM.0 - processor blocked

teiid | 15:22:40,587 FINE [org.teiid.jdbc] (default task-3) Successfully executed a query SELECT VDBName PROCEDURE_CAT, SchemaName AS PROCEDURE_SCHEM, ProcedureName AS PROCEDURE_NAME, p.N

ame AS COLUMN_NAME, convert(decodeString(TYPE, 'In,1, Out,4, InOut,2, ReturnValue,5, ResultSet,3', ','), short) AS COLUMN_TYPE, TypeCode AS DATA_TYPE, TypeName AS TYPE_NAME, ColumnSize AS "PRE

CISION", TypeLength AS LENGTH, convert(case when scale > 32767 then 32767 else Scale end, short) AS SCALE, Radix AS RADIX, convert(decodeString(NullType, 'No Nulls, 0, Nullable, 1, Unknown, 2

', ','), integer) AS NULLABLE, p.Description AS REMARKS, DefaultValue AS COLUMN_DEF, NULL AS SQL_DATA_TYPE, NULL AS SQL_DATETIME_SUB, NULL AS CHAR_OCTET_LENGTH, p.Position AS ORDINAL_POSITION,

CASE NullType WHEN 'Nullable' THEN 'YES' WHEN 'No Nulls' THEN 'NO' ELSE '' END AS IS_NULLABLE, p.ProcedureName as SPECIFIC_NAME FROM SYS.ProcedureParams as p WHERE UCASE(VDBName) LIKE ? ESCAP

E '\' AND UCASE(SchemaName) LIKE ? ESCAPE '\' AND UCASE(ProcedureName) LIKE ? ESCAPE '\' AND UCASE(p.Name) LIKE ? ESCAPE '\' ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, case TYPE when 'Retur

nValue' then 0 when 'ResultSet' then 2 else 1 end, POSITION and obtained results

teiid | 15:22:40,588 FINE [org.teiid.jdbc] (default task-3) Successfully obtained metadata info for the procedure column matching % in the procedure whose name matches restnumber.

teiid | 15:22:40,593 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM closeQuery for requestID=2QFHglxhS/PM.0

teiid | 15:22:40,593 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Restarting processing using the calling thread 2QFHglxhS/PM.0

teiid | 15:22:40,594 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Request Thread 2QFHglxhS/PM.0 with state CLOSE

teiid | 15:22:40,594 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Removing tuplesource for the request 2QFHglxhS/PM.0

teiid | 15:22:40,594 DEBUG [org.teiid.COMMAND_LOG] (default task-3) 2QFHglxhS/PM END USER COMMAND: endTime=2018-11-16 15:22:40.594 requestID=2QFHglxhS/PM.0 txID=null

sessionID=2QFHglxhS/PM principal=restuser vdbName=vdb vdbVersion=1.2.0 finalRowCount=1

teiid | 15:22:40,594 FINE [org.teiid.jdbc] (default task-3) Statement closed successfully.

teiid | 15:22:40,595 DEBUG [org.teiid.COMMAND_LOG] (default task-3) 2QFHglxhS/PM START USER COMMAND: startTime=2018-11-16 15:22:40.595 requestID=2QFHglxhS/PM.1 txID=nul

l sessionID=2QFHglxhS/PM applicationName=JDBC principal=restuser vdbName=vdb vdbVersion=1.2.0 sql={ CALL "rest"."restnumber"() }

teiid | 15:22:40,598 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Request Thread 2QFHglxhS/PM.1 with state NEW

teiid | 15:22:40,599 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.1 Command has no cache hint and result set cache mode is not on.

teiid | 15:22:40,599 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.1 executing { CALL "rest"."restnumber"() }

teiid | 15:22:40,600 DEBUG [org.teiid.AUDIT_LOG] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.1 [restuser] <STORED_PROCEDURE.getInaccessibleResources-request> [rest.restnumber]

teiid | 15:22:40,604 DEBUG [org.teiid.AUDIT_LOG] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.1 [restuser] <STORED_PROCEDURE.getInaccessibleResources-granted all> []

teiid | 15:22:40,607 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM ProcessTree for 2QFHglxhS/PM.1 ProjectNode(20) output=[rest.restnumber.nr] [rest.restnumber.nr]

teiid | PlanExecutionNode(21) output=[rest.restnumber.nr]

teiid |

teiid | 15:22:40,612 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM before getOrCreateTransactionContext:org.teiid.dqp.internal.process.TransactionServerImpl@57eee64f(2QFHglxhS/PM)

teiid | 15:22:40,613 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM after getOrCreateTransactionContext : 2QFHglxhS/PM NONE ID:NONE

teiid | 15:22:40,614 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM QueryProcessor: closing processor

teiid | 15:22:40,615 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM 2QFHglxhS/PM.1 Finished Processing

teiid | 15:22:40,615 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM [RequestWorkItem.sendResultsIfNeeded] requestID: 2QFHglxhS/PM.1 resultsID: 23 done: true

teiid | 15:22:40,621 FINE [org.teiid.jdbc] (default task-3) Successfully executed a query { CALL "rest"."restnumber"() } and obtained results

teiid | 15:22:40,622 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM closeQuery for requestID=2QFHglxhS/PM.1

teiid | 15:22:40,623 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Restarting processing using the calling thread 2QFHglxhS/PM.1

teiid | 15:22:40,623 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Request Thread 2QFHglxhS/PM.1 with state CLOSE

teiid | 15:22:40,623 DEBUG [org.teiid.PROCESSOR] (default task-3) 2QFHglxhS/PM Removing tuplesource for the request 2QFHglxhS/PM.1

teiid | 15:22:40,624 DEBUG [org.teiid.COMMAND_LOG] (default task-3) 2QFHglxhS/PM END USER COMMAND: endTime=2018-11-16 15:22:40.624 requestID=2QFHglxhS/PM.1 txID=null

sessionID=2QFHglxhS/PM principal=restuser vdbName=vdb vdbVersion=1.2.0 finalRowCount=10

teiid | 15:22:40,624 FINE [org.teiid.jdbc] (default task-3) Statement closed successfully.

teiid | 15:22:40,629 DEBUG [org.teiid.SECURITY] (default task-3) 2QFHglxhS/PM closeSession 2QFHglxhS/PM

teiid | 15:22:40,630 DEBUG [org.teiid.AUDIT_LOG] (default task-3) 2QFHglxhS/PM [restuser] <session.logoff>

teiid | 15:22:40,631 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM before cancelTransactions:org.teiid.dqp.internal.process.TransactionServerImpl@57eee64f(2QFHglxhS/PM,false)

teiid | 15:22:40,631 DEBUG [org.teiid.TXN_LOG] (default task-3) 2QFHglxhS/PM after cancelTransactions : null

teiid | 15:22:40,631 FINE [org.teiid.jdbc] (default task-3) Connection successfully closed.

teiid | 15:22:40,678 DEBUG [io.undertow.request] (default I/O-4) Matched default handler path /favicon.ico

1. Re: Configuring multirow result for rest service

rareddy Nov 19, 2018 6:35 AM (in response to gadeyne.bram)

What is the behavior when you execute the procedure directly?
Actions
2. Re: Configuring multirow result for rest service

gadeyne.bram Nov 19, 2018 7:57 AM (in response to rareddy)

When I execute "call rest.restnumber()" through a JDBC connection, I receive 10 rows containing the expected result.
Actions
3. Re: Configuring multirow result for rest service

shawkins Nov 19, 2018 9:49 AM (in response to gadeyne.bram)

Bram,

The behavior you are seeing is currently expected for the rest service - it looks only for a single return or result set value as the full result. It does not attempt to generate a mapping of a result set to some output format. Handling like that overlaps greatly with odata support.

You can create a single result by using xmlelement/xmlforest or the json construction functions.

Steve
1 of 1 people found this helpful
Actions
4. Re: Configuring multirow result for rest service

rareddy Nov 19, 2018 4:20 PM (in response to shawkins)

Steve,

This came about once before I think, should we consider changing the behaviour when the return type is TABLE perhaps?

Ramesh..
Actions
5. Re: Configuring multirow result for rest service

shawkins Nov 19, 2018 4:27 PM (in response to rareddy)

> This came about once before I think, should we consider changing the behaviour when the return type is TABLE perhaps?

There is no handling of multi-row in the rest layer. A long time ago there was something like that, but was removed when odata became an option.

We can certainly add more docs or a warning when multiple rows are detected.
Actions
6. Re: Configuring multirow result for rest service

gadeyne.bram Nov 20, 2018 7:13 AM (in response to shawkins)
I've changed my procedure to the one below and now I receive the expected result.

CREATE PROCEDURE restnumber() RETURNS ( ret clob ) OPTIONS ( "teiid_rest:method" 'GET', "teiid_rest:uri" '/restnumber', "teiid_rest:produces" 'json' ) AS BEGIN select JSONARRAY_AGG(jsonobject(v.nr)) as ret from ( SELECT 0 AS nr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) v; END

This suffies for our use case. It's very flexible since you can even nest additional types togheter.

I have two additional questions regarding the documentation.

1) The PRODUCES type "any", what does it actually do? I'm wondering if it is possible to create a rest service that, according to the HTTP request header does return a certain type. E.g. application/json -> json, application/xml -> xml, ...

Specifically for our current use case we only use json but maybe for future projects the other return types might be interesting.

2) What is the difference between the types "plain" and "text"?
Actions
7. Re: Configuring multirow result for rest service

shawkins Nov 20, 2018 12:45 PM (in response to gadeyne.bram)

> The PRODUCES type "any", what does it actually do?

I have to correct my previous answer. The documentation formatting is the issue it should be "any text" as the last option. It's not separate any and text options. In any case I've logged [TEIID-5547] Issues with REST produces property - JBoss Issue Tracker to update the docs / code to make things clearer.
Actions

Go to original post