Cognos Analytics

 View Only
Expand all | Collapse all

RESTApi Connection

  • 1.  RESTApi Connection

    Posted Wed March 13, 2024 12:35 PM

    I am trying to set up an API connection to some publicly available statistical infomation.  The connection string I am putting into the Data Server set up will not work when I add in the sample = syntax.  To give an example of what I mean I've used the Pokemon API below - if I enter in a JDBC URL of the following:

     jdbc:ibmcognos:autorest:https://pokeapi.co/api/v2/pokemon/5

    The test returns a value of success but when I try to query the data in the autorest table there is no data of consequence in it.  If I try using the sample syntax so entering the following:-

    jdbc:ibmcognos:autorest:sample='https://pokeapi.co/api/v2/pokemon/5';

    The test fails with the following error:-

    XQE-DS-0006 Unable to logon to the data source.
    [IBM Cognos][AutoREST JDBC Driver][AutoREST]I/O Error Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: com.ibm.cognos.externals.org.apache.http_4_5_13.conn.HttpHostConnectException: Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: java.net.ConnectException: Connection timed out: connect
    I/O Error Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: com.ibm.cognos.externals.org.apache.http_4_5_13.conn.HttpHostConnectException: Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: java.net.ConnectException: Connection timed out: connect
    I/O Error Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: com.ibm.cognos.externals.org.apache.http_4_5_13.conn.HttpHostConnectException: Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Caused by: java.net.ConnectException: Connection timed out: connect
    Connect to pokeapi.co:443 [pokeapi.co/172.64.99.38, pokeapi.co/172.64.98.38] failed: Connection timed out: connect
    Connection timed out: connect

    I know the above should work as I followed a video provided by PMSquare that showed the API bringing back data.  I get the same issue no matter what JDBC URL I enter.  

    I'd appreciate any help on this one.



    ------------------------------
    Lorraine McAllister
    ------------------------------


  • 2.  RE: RESTApi Connection

    Posted Wed March 13, 2024 07:52 PM

    jdbc:ibmcognos:autorest:sample="https://pokeapi.co/api/v2/evolution-chain/1/"


    Additional info etc. under
    https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=details-progress-datadirect-autonomous-rest-connections





    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 3.  RE: RESTApi Connection

    Posted Thu March 14, 2024 05:45 AM

    Hi

     

    Thanks for your reply.  I tried the URL you provided (jdbc:ibmcognos:autorest:sample="pokeapi.co/api/v2/evolution-chain/1/") but get the error below:-

     

     

     

    XQE-DS-0006 Unable to logon to the data source.

    [IBM Cognos][AutoREST JDBC Driver][AutoREST]I/O Error Unable to scan pokeapi.co/api/v2/evolution-chain/1/

    Caused by: java.io.IOException: Stream closed on read()

    I/O Error Unable to scan pokeapi.co/api/v2/evolution-chain/1/

    Caused by: java.io.IOException: Stream closed on read()

    I/O Error Unable to scan pokeapi.co/api/v2/evolution-chain/1/

    Caused by: java.io.IOException: Stream closed on read()

    Stream closed on read()

     

     

    I've asked our infrastructure lead to have a look in case it is something to do with our firewall blocking the request.

     

    Lorraine



    The information contained in this email transmission is confidential and intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, or person responsible for delivering it to the intended recipient, you are not authorised to and must not disclose, copy, distribute or retain this message or any part of it. If you have received this email in error, please notify the sender and delete the email from your system.

    The opinions / views / comments on this e-mail are mine and do not necessarily reflect any views or policies of Invest Northern Ireland. This footnote also confirms that this email message has been scanned for the presence of computer viruses. Invest Northern Ireland however accepts no responsibility for changes to or interception of this email after it was sent or for any damage to the recipient's systems or data caused by this message or its attachments.



    Original Message


  • 4.  RE: RESTApi Connection

    Posted Thu March 14, 2024 07:39 AM

    In my reply the Connection URL was jdbc:ibmcognos:autorest:sample="https://pokeapi.co/api/v2/evolution-chain/1/"

    In your reply you wrote jdbc:ibmcognos:autorest:sample="pokeapi.co/api/v2/evolution-chain/1/", which is incorrect and would fail.




    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 5.  RE: RESTApi Connection

    Posted Fri March 15, 2024 07:00 AM

    Apologies Nigel - that was my error in copying and pasting back here.  I definitely put in the URL you provided but it still fails.  See screenshot below.  



    ------------------------------
    Lorraine McAllister
    ------------------------------

    Original Message


  • 6.  RE: RESTApi Connection

    Posted Fri March 15, 2024 05:55 AM

    Hi Lorraine,

    Don't forget to load the metadata using the following documentation:

    https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=servers-loading-metadata

    Best regards,



    ------------------------------
    Patrick Neveu
    Collaboration Betters The World (CBTW)
    IBM Champion
    ------------------------------

    Original Message


  • 7.  RE: RESTApi Connection

    Posted Fri March 15, 2024 07:09 AM

    Patrick

    Thanks for your reply.  Unfortunately I can't load the metadata as the API connection is not working for me.  I've tried the URL provided by Nigel above as a test but it fails.  

    jdbc:ibmcognos:autorest:sample="https://pokeapi.co/api/v2/evolution-chain/1/"

    If I take the sample = part of the URL out then I get a success reading on the test but there is no metadata returned. 

    jdbc:ibmcognos:autorest:https://pokeapi.co/api/v2/evolution-chain/1/

    Our COGNOS server can read in data from custom map layers that we have in MapBox so I don't think the issue is to do with it reading in external data.  The COGNOS server has internet access as we can browse to sites from it.  I'm not sure what else to try at this point. 



    ------------------------------
    Lorraine McAllister
    ------------------------------

    Original Message


  • 8.  RE: RESTApi Connection

    Posted Fri March 15, 2024 07:10 AM

    We are using COGNOS version 11.2.3 if that helps.



    ------------------------------
    Lorraine McAllister
    ------------------------------

    Original Message


  • 9.  RE: RESTApi Connection

    Posted Fri March 15, 2024 08:23 AM


    The driver is performing an http get  via  the URL you are providing. If the server can be reached and returns a JSON response, the driver will attempt to infer a schemata (sample).  Should it be unable to connect you would see errors similar to figures below. Should it connect and be unable to infer schemata, then you will have no "tables". 

    The conf= name value pair is for when you have multiple end points and defined schemata etc. 

    If you type https://pokeapi.co/api/v2/evolution-chain/1/ into your browser, it should result in a response similar to figure below.

    If you are continuing to have issue, suggest you open a support ticket.

    Here is another example, using the world time clock

    jdbc:ibmcognos:autorest:sample="http://worldtimeapi.org/api/timezone/America/Toronto"






    When you specify an invalid URL in sample= etc. , the driver will fail to get  request will fail and throw errors similar to.











    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 10.  RE: RESTApi Connection

    Posted Mon August 19, 2024 12:15 PM

    Hi Nigel, 

    Hope you are doing well. I made another post about an error I am receiving from progress connector but no response on it so thought I would try here since you seem to have some knowledge of the connector. I am able to successfully connect to various APIs using this connector and the functionality is great. However, we have an internal API on an SSL url and keep getting the following error:

    XQE-DS-0006 Unable to logon to the data source.[IBM Cognos][AutoREST JDBC Driver][AutoREST]I/O Error com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureCaused by: java.io.IOException: com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureCaused by: com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureI/O Error com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureCaused by: java.io.IOException: com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureCaused by: com.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failurecom.ibm.cognos.jdbc.autorest.util.aj: SSL handshake failed: Received fatal alert: handshake_failureSSL handshake failed: Received fatal alert: handshake_failure

    Any thoughts? API call works fine in postman or python from same local machine. Here is the separate post I made

    Progress DataDirect Connector - Post call | Cognos Analytics (ibm.com)



    ------------------------------
    John Bronzo
    ------------------------------

    Original Message


  • 11.  RE: RESTApi Connection

    Posted Thu March 14, 2024 09:05 AM

    Would you mind sharing the link or the title of the pmsquare article?



    ------------------------------
    Jackson Eyton
    ------------------------------

    Original Message


  • 12.  RE: RESTApi Connection

    Posted Thu March 14, 2024 12:11 PM

    Hi

     

    This is the video I watched.

     

    (2) Cognos Analytics and REST APIs - YouTube

     

    Kind regards

    Lorraine

     




    Original Message