7 Replies Latest reply on Nov 3, 2014 9:41 PM by rareddy

Error while executing a VDB with REST API as a source

jjaideep2000 Nov 3, 2014 2:51 PM

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?

1. Re: Error while executing a VDB with REST API as a source

tejones Nov 3, 2014 3:08 PM (in response to jjaideep2000)

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
Actions

2. Re: Error while executing a VDB with REST API as a source

rareddy Nov 3, 2014 3:44 PM (in response to jjaideep2000)

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..

3. Re: Error while executing a VDB with REST API as a source

jjaideep2000 Nov 3, 2014 5:20 PM (in response to rareddy)

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,
Actions
4. Re: Error while executing a VDB with REST API as a source

shawkins Nov 3, 2014 6:41 PM (in response to jjaideep2000)

> 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?
Actions
5. Re: Error while executing a VDB with REST API as a source

jjaideep2000 Nov 3, 2014 7:55 PM (in response to rareddy)

Ramesh,
I made the changes you suggested, but now getting another error. Please see the attached image.
Actions
6. Re: Error while executing a VDB with REST API as a source

jjaideep2000 Nov 3, 2014 9:02 PM (in response to shawkins)

Hi Steven,
I am running thsi query on SQL scrapbook:

* from ( exec "MyViewModel"."getBookByISBN"('0-7356-0877-6') ) AS X_X
Actions
7. Re: Error while executing a VDB with REST API as a source

rareddy Nov 3, 2014 9:41 PM (in response to jjaideep2000)

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
Actions

Go to original post