You can translate the question and the replies:

How to do error handling on accessing data sources that we do not control + Different REST Web Service formats don't seem to return the same information?

Hello! There are two problems, that my team was wondering if there were answers to. The first problem: There are several data sources that we have no control over, and therefore can't tell if those services are live or not. My team was wondering how we could use Denodo to access, normalize, and perform unions on the data sources, returning the data we need without error. For the data sources that are alive, we want to retrieve the data. For the data sources that are not, we want to skip querying the data for that source. Below is a following example of the error we get (Note: These examples are not our actual schema, but reflects the same concepts using the Denodo Express tutorial): ![](https://i.imgur.com/2pz7Ijw.png) That data source on the left is a 'not live' data source, that is connected through JDBC. The data source on the right is a live data source, that is connected through HTTP (SOAP). Our first thought was using a 'WHERE 0 = 1' on p_personal_data_crm to determine if the query should go through or not (Note: 1 = 0 is a hardcoded false which would be replaced by a value that does a health check). However, even though the query stopped being delegated, p_personal_data_crm normally projects to personal_data_crm which is a join of two more data sources, https://community.denodo.com/tutorials/browse/basics/2fs7derived, the projection itself still trys to make the JDBC connection, and returns an error as a result. What is Denodo approach of handling these types of connections/queries? We are aware that we could make a view for each combination of data source, but that does not scale as we would have to make the following number of views: (n choose n) + (n choose n-1) + (n choose n-2) + ... + (n choose 1) views for n data sources. We are also aware that we could pipe the information through an application that acts as a middleware to the application, but that eliminates the benefits of using Data Virtualization. The second problem: Given that we ignore error handling, we can still retrieve the data that we need through both the HTML format and XML format of Denodo's REST Web Service (Although errors are returned in the response). However, the JSON format does not return this data, and is not consistent with the other file formats: Has Data (XML) http://localhost:9090/server/tutorial/p_personal_data_crm_u_billing_information/views/p_personal_data_crm_u_billing_information?customer_id=C001&$format=xml ``` <?xml version="1.0" encoding="UTF-8"?> <denodo:view name="p_personal_data_crm_u_billing_information" xmlns="http://www.denodo.com/restful/tutorial/views/p_personal_data_crm_u_billing_information" xmlns:denodo="http://www.denodo.com/restful" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <p_personal_data_crm_u_billing_information> <client_id xsi:nil="true"/> <name xsi:nil="true"/> <surname xsi:nil="true"/> <client_type xsi:nil="true"/> <street xsi:nil="true"/> <city xsi:nil="true"/> <zip xsi:nil="true"/> <state xsi:nil="true"/> <primary_phone xsi:nil="true"/> <code xsi:nil="true"/> <value xsi:nil="true"/> <customer_id>C001</customer_id> <ssn xsi:nil="true"/> <billing_end_date>5/31/2006</billing_end_date> <phone_center xsi:nil="true"/> <due_date>2006-05-24 11:05:50</due_date> <balance>3.99</balance> <billing_period_id>Bi001</billing_period_id> <billing_start_date>4/31/2006</billing_start_date> <billing_id>b0003</billing_id> <return_customer_id>C001</return_customer_id> <package_id>p001</package_id> <amount_due>3.99</amount_due> <tax_id xsi:nil="true"/> </p_personal_data_crm_u_billing_information> <?xml version="1.0" encoding="UTF-8"?> <error xmlns="http://www.denodo.com/restful"> <message code="50001">Error executing query. Total time 2.063 seconds. QUERY [PROJECTION] [ERROR] QUERY [SELECTION] [ERROR] QUERY [SELECTION] [ERROR] P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [PROJECTION] [ERROR] P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [UNION] [ERROR] P_PERSONAL_DATA_CRM [VIRTUAL] [ERROR] P_PERSONAL_DATA_CRM [JDBC WRAPPER] [ERROR] P_PERSONAL_DATA_CRM#0 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Received exception with message 'Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.'</message> <message code="1">QUERY [PROJECTION] [ERROR]</message> <message code="1">QUERY [SELECTION] [ERROR]</message> <message code="1">QUERY [SELECTION] [ERROR]</message> <message code="1">P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [PROJECTION] [ERROR]</message> <message code="1">P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [UNION] [ERROR]</message> <message code="1">P_PERSONAL_DATA_CRM [VIRTUAL] [ERROR]</message> <message code="1">P_PERSONAL_DATA_CRM [JDBC WRAPPER] [ERROR]</message> <message code="11">P_PERSONAL_DATA_CRM#0 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.</message> </error> ``` Does not have Data (JSON) http://localhost:9090/server/tutorial/p_personal_data_crm_u_billing_information/views/p_personal_data_crm_u_billing_information?customer_id=C001&format=json ``` { "errors": [ { "code": 50001, "message": "Error executing query. Total time 2.057 seconds.\n\nQUERY [PROJECTION] [ERROR] \nQUERY [SELECTION] [ERROR] \nQUERY [SELECTION] [ERROR] \nP_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [PROJECTION] [ERROR] \nP_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [UNION] [ERROR] \nP_PERSONAL_DATA_CRM [VIRTUAL] [ERROR] \nP_PERSONAL_DATA_CRM [JDBC WRAPPER] [ERROR] \nP_PERSONAL_DATA_CRM#0 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. Received exception with message 'Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.'" }, { "code": 1, "message": "QUERY [PROJECTION] [ERROR]" }, { "code": 1, "message": "QUERY [SELECTION] [ERROR]" }, { "code": 1, "message": "QUERY [SELECTION] [ERROR]" }, { "code": 1, "message": "P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [PROJECTION] [ERROR]" }, { "code": 1, "message": "P_PERSONAL_DATA_CRM_U_BILLING_INFORMATION [UNION] [ERROR]" }, { "code": 1, "message": "P_PERSONAL_DATA_CRM [VIRTUAL] [ERROR]" }, { "code": 1, "message": "P_PERSONAL_DATA_CRM [JDBC WRAPPER] [ERROR]" }, { "code": 11, "message": "P_PERSONAL_DATA_CRM#0 [JDBC ROUTE] [CONNECTION_ERROR] Unexpected error creating a connection: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server." } ] } ``` Is there anything we can do on our side to get the data in a JSON format, or is this a Denodo bug? Denodo Versions: * Denodo Platform 7.0 * Denodo Express 7.0 We have also looked at: * https://community.denodo.com/answers/question/details?questionId=9060g0000000AedAAE&title=Error+handling+in+base+view+of+web+service+data+source * https://community.denodo.com/answers/question/details?questionId=9060g000000L70zAAC&title=Ignore+HTTP+errors+during+join+with+other+view

2 Answers

Hi, For the first problem, the query fails because the condition is evaluated at the data source level. In order to evaluate the **WHERE** condition in the Virtual DataPort server, I would suggest you to change the parameter ‘**Delegate Selection**’ as ‘**No**’ in the **Source Configuration of data source**. For more details on Source Configuration, check the guide “[Data Source Configuration Properties](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/data_source_configuration_properties/data_source_configuration_properties#data-source-configuration-properties)”. Regarding the second problem, I published a web service like you and I received no data but only errors with the JSON format. This is a known limitation in the Denodo Platform and will be fixed in the future updates. Until then, you can use the HTML and XML representation of the REST Web services. Hope this helps!
Denodo Team
10-01-2019 05:28:31 -0500
Thank you for the helpful response! Depending on which provides a better experience, I think our team's go forward will be either: 1. Project the normalized views for health checking, and changing the parameter ‘**Delegate Projection**’ as ‘**No**’. We still want to delegate costly selections to the source data sources, when those data sources are online. Hopefully, It should be a smaller cost to not delegate projections, compared to the cost of not delegating selections. I was able to get this working with the example schema. 2. Query, knowing that errors will be returned from Denodo, and pulling the data as either HTML or XML (Changing the data into JSON within our web application). This should reduce the cost the most, because we don't turn off either of the delegations. Thanks for the assistance!
user
10-01-2019 10:31:48 -0500
You must sign in to add an answer. If you do not have an account, you can register here