Calling Server API in a simple manner
This post is simple and provides a Excel VBA code and the corresponding R code for calling SQL query indirectly by using web server API. Sample SQL returns some swap schedule as an example. In the two codes below, the server URL address is not a valid address since it is just for illustration purpose. You can just copy and paste the necessary part and modify it for your purpose.
Excel VBA : MSXML2.ServerXMLHTTP
The following Excel VBA code uses MSXML2.ServerXMLHTTP object to do the above job. Of course, specific parts depend on in-house system.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | Sub Run_SQL_Btn_Click() Dim response As String Dim result As Variant Dim X As Variant Dim url As String Dim strQry As String ' SQL query strQry = " SELECT ID," & _ " to_char(RESET_DATE, 'yyyy-mm-dd')," & _ " to_char(START_DATE, 'yyyy-mm-dd')," & _ " to_char(END_DATE, 'yyyy-mm-dd')," & _ " to_char(PAYMENT_DATE,'yyyy-mm-dd') " & _ " FROM CASH_FLOW_TABLE" & _ " WHERE PRODUCT_ID = '3911737' and ID = '2'" & _ " ORDER BY PAYMENT_DATE, RESET_DATE" ' create object which is connected to server Dim objHttp Set objHttp = CreateObject("MSXML2.ServerXMLHTTP") ' This is for the illustration purpose and not valid url = "http://123.123.123.123:9080/importserver/InquiryOracle" objHttp.Open "POST", url, False objHttp.setRequestHeader "User-Agent", _ "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" objHttp.setRequestHeader "Content-Type", _ "application/x-www-form-urlencoded;charset=UTF-8" objHttp.send ("q=" & strQry) objHttp.waitForResponse 180000000 response = objHttp.responseText ' Convert Result X = Split(response, "|") ReDim result(UBound(X)) For i = 0 To UBound(X) result(i) = Split(X(i), ",") Next i ' Show Result i = 0 For Each Data In result Cells(3 + i, 2) = i + 1 For j = LBound(Data) To UBound(Data) Cells(3 + i, 3 + j) = Data(j) Next j i = i + 1 Next Data End Sub | cs |
Running the above Excel macro returns the following output.
R code with httr package
The following R code uses httr R package to do the above same job.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | #========================================================# # Quantitative ALM, Financial Econometrics & Derivatives # ML/DL using R, Python, Tensorflow by Sang-Heon Lee # # https://shleeai.blogspot.com #--------------------------------------------------------# # Excel VBA's MSXML2.ServerXMLHTTP in R #========================================================# library(httr) # SQL query to be executed sql <- paste( "SELECT ID,", " to_char(RESET_DATE, 'yyyy-mm-dd'),", " to_char(START_DATE, 'yyyy-mm-dd'),", " to_char(END_DATE, 'yyyy-mm-dd'),", " to_char(PAYMENT_DATE,'yyyy-mm-dd')", "FROM CASH_FLOW_TABLE", "WHERE PRODUCT_ID = '3911737' and ID = '2'", "ORDER BY PAYMENT_DATE, RESET_DATE"); # web server address which provides service APIs # But This url is for the illustration purpose and not valid url <- "http://123.123.123.123:9080/importserver/InquiryOracle" # create object which is connected to server and send it httpResponse = POST( url, body = list(q=sql), user_agent( "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"), add_headers( 'Content-Type'= "application/x-www-form-urlencoded;charset=UTF-8", 'User-Agent'= "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"), encode = 'form') # convert output as text str.out <- content(httpResponse, as ="text") # reshape output as data.frame read.table(text=gsub("[|]", "\n", str.out), sep=",", col.names = c('ID','RESET_DATE', 'START_DATE', 'END_DATE', 'PAYMENT_DATE')) | cs |
In the above R code, it is worth noting that a SQL query string is inserted as a member of list. Running this R code returns the following same output.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | > ID RESET_DATE START_DATE END_DATE PAYMENT_DATE 1 2 2021-10-15 2021-10-20 2021-11-10 2021-11-10 2 2 2021-10-15 2021-11-10 2021-12-10 2021-12-10 3 2 2021-10-15 2021-12-10 2022-01-10 2022-01-10 4 2 2021-10-15 2022-01-10 2022-02-10 2022-02-10 5 2 2021-10-15 2022-02-10 2022-03-10 2022-03-10 6 2 2021-10-15 2022-03-10 2022-04-10 2022-04-11 7 2 2021-10-15 2022-04-10 2022-04-16 2022-05-10 8 2 2022-04-15 2022-04-16 2022-05-10 2022-05-10 9 2 2022-04-15 2022-05-10 2022-06-10 2022-06-10 10 2 2022-04-15 2022-06-10 2022-07-10 2022-07-11 11 2 2022-04-15 2022-07-10 2022-08-10 2022-08-10 12 2 2022-04-15 2022-08-10 2022-09-10 2022-09-13 13 2 2022-04-15 2022-09-10 2022-10-10 2022-10-11 14 2 2022-04-15 2022-10-10 2022-10-16 2022-11-10 15 2 2022-09-15 2022-10-16 2022-10-20 2022-11-10 | cs |
Concluding Remarks
As we call server API function using MSXML2.ServerXMLHTTP in Excel VBA, we can also do the same job by using httr package. \(\blacksquare\)
No comments:
Post a Comment