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):
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
