topic Re: How DO I Create a REST API for ORACLE DB Access in Developers

How DO I Create a REST API for ORACLE DB Access

BillSut — Thu, 05 Dec 2019 13:12:59 GMT

I've been tasked with creating a REST API to be used to connect to an Oracle Cloud DB and retrieve data from a single table and store it in a data set. I have a lot of programming experience but I'm new to SAS. I also do not have access to VIYA so it will have to be old school code. I'm using SAS 9.4. Do you know of any place I could find some examples for this. I would greatly appreciate any help. Thanks in advance.

Re: How DO I Create a REST API for ORACLE DB Access

ChrisHemedinger — Thu, 05 Dec 2019 21:44:50 GMT

Do you mean create an API for others to use, or call an API from Oracle Cloud DB, that fetches data for you to store?

If the latter, then you want to look at PROC HTTP as a REST client and probably the JSON libname engine to format the results into a table.

Re: How DO I Create a REST API for ORACLE DB Access

AllanBowe — Thu, 05 Dec 2019 21:51:12 GMT

You might find this product interesting: https://sas9api.io/

It lets you access your complete SAS 9 estate using REST APIs. You could create an STP and use it as a REST endpoint.

Other options are SAS Web Services, or calling the Stored Process Web Application directly. Some papers on that are below.

https://support.sas.com/resources/papers/proceedings17/1091-2017.pdf
https://www.rawsas.com/building-web-apps-with-sas/
https://www.linkedin.com/pulse/5-tips-sas-app-developers-allan-bowe/
http://support.sas.com/resources/papers/proceedings17/1372-2017.pdf (apps for enterprise)

Build an app in 5 minutes: https://vimeo.com/349223867

Re: How DO I Create a REST API for ORACLE DB Access

BillSut — Mon, 09 Dec 2019 11:43:16 GMT

Yes, you are correct. I posted my question before meeting with the Oracle guy. I will be using their REST API in Oracle Cloud Services. I will check out PROC HTTP and the JSON libname engine.. Any code examples where someone has done this would be appreciated. Thanks for your reply!

Re: How DO I Create a REST API for ORACLE DB Access

BillSut — Mon, 09 Dec 2019 11:43:55 GMT

Thank you for the information. I will check out the links you provided.

Re: How DO I Create a REST API for ORACLE DB Access

BillSut — Mon, 09 Dec 2019 12:45:40 GMT

Chris,

I just finished reading your article "How to test PROC HTTP and the JSON library engine" from Jan 2018. I tried using the sample code and received the below errors. I'm using SAS 9.4m5 so I'm assuming that the errors are because of the government firewall I'm using. Do you concur?

ERROR: The TCP/IP tcpSockEstablishSSL() support routine failed with error 10054 (The connection was reset by a peer.).
ERROR: The tcpSockRead call failed. The system error is 'The connection was reset by a peer.'.
ERROR: Call to tcpSockContinueSSL failed.
NOTE: PROCEDURE HTTP used (Total process time):
real time 0.18 seconds
cpu time 0.03 seconds

NOTE: The SAS System stopped processing this step because of errors.

ERROR: Physical file does not exist, D:\1067590410C\_TD9872_TYMXW-F7H8026GL_\now.json.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Physical file does not exist, D:\1067590410C\_TD9872_TYMXW-F7H8026GL_\now.json.
ERROR: Error in the LIBNAME statement.
NOTE: Writing HTML Body file: sashtml2.htm
ERROR: Libref TIME is not assigned.

NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.93 seconds
cpu time 0.43 seconds

Thanks.