Error while executing a VDB with REST API as a source

[jjaideep2000]

Created a source model, view model and a VDB for a REST API as a source. Followed all the instructions specified in this blog: How to Consume a JSON REST Web Service in Teiid Designer

While executing the query select * from ( exec "MyViewModel"."getBookViaISBN"('0-7356-0877-6') ) AS X_X, getting following error:

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30152 Failed to evaluate XQuery expression; Please check the query and correct errors in syntax or usage.

Here is the code for the procedure:

DECLARE STRING VARIABLES.qp = ('http://demo33-prod.apigee.net/v0/observationtypes' || MyViewModel.getBookViaISBN.isbn);

SELECT A.ISBN AS ISBN, A.TITLE AS TITLE FROM (EXEC MySourceModel.invokeHttp('GET', 'http://demo33-prod.apigee.net/v0/observationtypes', VARIABLES.qp, 'TRUE')) AS f, XMLTABLE('/response/books' PASSING JSONTOXML('response', f.result) COLUMNS ISBN string PATH 'book/ISBN/text()', TITLE string PATH 'book/TITLE/text()') AS A;

Can someone help?

[tejones]

Jaideep,

I'm assuming this is during preview? A couple of things.. try setting the Connection Profile on your source model before previewing.

Also, try deploying and executing the VDB with your query.

Thanks,

Ted

[rareddy]

Jaideep,

Did you hand code the above procedure or used Designer? It does not look correct to me. IMO it should more be like

DECLARE STRING VARIABLES.qp = ('http://demo33-prod.apigee.net/v0/observationtypes' || MyViewModel.getBookViaISBN.isbn);
SELECT A.ISBN AS ISBN, A.TITLE AS TITLE FROM (EXEC MySourceModel.invokeHttp('GET', null, VARIABLES.qp, 'TRUE')) AS f, 
  XMLTABLE('/response/books' PASSING JSONTOXML('response', f.result) COLUMNS ISBN string PATH'book/ISBN/text()', TITLE string PATH 'book/TITLE/text()') AS A;

depending upon which server version you are using, it may be better you use like

DECLARE STRING VARIABLES.qp = ('http://demo33-prod.apigee.net/v0/observationtypes' || MyViewModel.getBookViaISBN.isbn);
SELECT A.ISBN AS ISBN, A.TITLE AS TITLE FROM (EXEC MySourceModel.invokeHttp(action=>'GET', endpoint=>VARIABLES.qp, stream=>'TRUE')) AS f, 
  XMLTABLE('/response/books' PASSING JSONTOXML('response', f.result) COLUMNS ISBN string PATH'book/ISBN/text()', TITLE string PATH 'book/TITLE/text()') AS A;

try updating the transformation manually and see if that works. For full details about invokeHTTP call, see Web Services Translator - Teiid 8.9 (draft) - Project Documentation Editor

Ramesh..

[jjaideep2000]

Ramesh,

As soon as I create the source model and view model, this is the procedure I see:

BEGIN

DECLARE STRING VARIABLES.qp = '';

SELECT A.ISBN AS ISBN, A.TITLE AS TITLE FROM (EXEC MySourceModel.invokeHttp('GET', null, VARIABLES.qp, 'TRUE')) AS f, XMLTABLE('/response/books' PASSING JSONTOXML('response', f.result) COLUMNS ISBN string PATH 'book/ISBN/text()', TITLE string PATH 'book/TITLE/text()') AS A;

END

At this moment, I am seesing an error message: The number of columns/elements in getBookByISBN are less than the number defined in the SQL transformation.

Then I replace null with 'http://demo33-prod.apigee.net/v0/observationtypes'. Error goes away, but I get other exception.

Let me try your solution.

Thanks,

[shawkins]

> At this moment, I am seesing an error message: The number of columns/elements in getBookByISBN are less than the number defined in the SQL transformation.

This means that the procedure has a result set that does not match what is projected by the query (ISBN, TITLE).

> Then I replace null with 'http://demo33-prod.apigee.net/v0/observationtypes'. Error goes away, but I get other exception

Meaning the XQuery exception?  That will only occur if you submit a malformed query that looks like an XQuery, for example something that starts with "<" or "declare".  What is the sql you are submitting and what is the client you are using?

[jjaideep2000]

Ramesh,

I made the changes you suggested, but now getting another error. Please see the attached image.

[jjaideep2000]

Hi Steven,

I am running thsi query on SQL scrapbook:

* from ( exec "MyViewModel"."getBookByISBN"('0-7356-0877-6') ) AS X_X

[rareddy]

See Steve Hawkins response above to fix the error. As he says the the columns defined in the XMLTable construct must match with resultsset defined on the "getBookbyISBN" method