Excel VBA - Loading POST REST Webservice JSON

1. Open excel -> developer -> visual basic
2. Tools -> reference (enabled : Microsoft win http services , version 5.1 xx )
3. paste the below code on click sheet1.
4. Click run.
5. Save As excel macro enabled from file type . else your VBA code will not be save.


Dim strResult As String
Dim objHTTP As Object
Dim URL As String
  
Private Sub Worksheet_Activate()
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    json_body = "{'Commodity':'CRUDEOIL','Expiry':'17SEP2019'}"
    URL = "https://www.mcxindia.com/backpage.aspx/GetOptionChain"
    
    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/json"
    objHTTP.Send (json_body)
    strResult = objHTTP.ResponseText
    Worksheets("Sheet1").Range("A10:A10") = strResult
    
End Sub

Haproxy Load Balace - Both TCP & HTTP [ Rabbitmq and Rest Webservice ]

Example- 
1. HAProxy installed in             192.168.7.11
MQ installed in                          192.168.7.12,            192.168.7.13
REST API also running in        192.168.7.12,             192.168.7.13

2. http://192.168.7.11:8080
3. Run your queue sender to 192.168.7.11:5672
     //MQ send receiver code = http://drvijayy2k2.blogspot.com/2019/07/java-rabbitmq-sample-send-receiver.html


haproxy.cfg

global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon

# Default SSL material locations
ca-base /etc/ssl/certs
crt-base /etc/ssl/private

# Default ciphers to use on SSL-enabled listening sockets.
# For more information, see ciphers(1SSL). This list is from:
#  https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5:!DSS
ssl-default-bind-options no-sslv3

defaults
log global
mode http
option httplog
option dontlognull
timeout connect 5000
timeout client  50000
timeout server  50000
errorfile 400 /etc/haproxy/errors/400.http
errorfile 403 /etc/haproxy/errors/403.http
errorfile 408 /etc/haproxy/errors/408.http
errorfile 500 /etc/haproxy/errors/500.http
errorfile 502 /etc/haproxy/errors/502.http
errorfile 503 /etc/haproxy/errors/503.http
errorfile 504 /etc/haproxy/errors/504.http

frontend haproxy_in_ws
    bind *:8080
    mode http
    default_backend haproxy_http_ws

frontend haproxy_in_mq
    bind *:8890
    mode tcp
    default_backend haproxy_tcp_mq

backend haproxy_http_ws
    balance roundrobin
    mode http
    server rabbitmaster 192.168.1.12:8080 check
    server rabbitslave  192.168.1.13:8080 check

backend haproxy_tcp_mq
    balance roundrobin
    mode tcp
    server rabbitmaster 192.168.1.12:5672 check
    server rabbitslave  192.168.1.13:5672 check

Web Service in Java Sample

Go to web-inf\src :

use cp.bat to set classpath.
download the same jar and paste into ur lib folder..

jars
****
set AXIS_HOME=C:\Tomcat5.0\webapps\axis
set AXIS_LIB=%AXIS_HOME%\WEB-INF\lib

set AXISCLASSPATH=

%AXIS_LIB%\axis.jar;
%AXIS_LIB%\commons-discovery-0.2.jar;
%AXIS_LIB%\wsdl4j-1.5.1.jar;
%AXIS_LIB%\commons-logging-1.0.4.jar;
%AXIS_LIB%\jaxrpc.jar;
%AXIS_LIB%\saaj.jar;
%AXIS_LIB%\log4j-1.2.8.jar;
%AXIS_LIB%\xml-apis.jar;
%AXIS_LIB%\xercesImpl.jar;
%AXIS_LIB%\servlet-api.jar;



just paste into tomcat\webapps

(((( ex 1: ))))

webservice
***********
1) create a java program and rename it as filename.jws
2) copy that filename.jws into tomcat\webapps\axis (project-root)
3) re/start tomcat

client run steps:
*****************
cp.bat
javac CalcClient.java
java CalcClient add 5 5


(((( ex 2: ))))
1) create a java program "filename.java"
2) set classpath and compile and place that .class into WEB-INF\classes folder
3) create a deploy.wsdd and undeploy.wsdd file and store into your filename.java area
4) run this wsdd cmd into cmd promt (( Note : tomcat server must be in run when u exectue this cmd )))

C:\Tomcat5.0\webapps\axis\WEB-INF\src> java org.apache.axis.client.AdminClient deploy.wsdd

5) restart the tomcat
6) check : http://localhost:8080/axis/servlet/AxisServlet
or
http://mdu-vijay:8080/axis/services/MyService?wsdl

7) then run client... (change endpoint to point ur myservice)




Calculator.java
***************

public class Calculator {
public int add(int i1, int i2)
{
return i1 + i2;
}


public int subtract(int i1, int i2)
{
return i1 - i2;
}
}


cp.bat
******
set AXIS_HOME=C:\Tomcat5.0\webapps\axis
set AXIS_LIB=%AXIS_HOME%\WEB-INF\lib
set AXISCLASSPATH=%AXIS_LIB%\axis.jar;%AXIS_LIB%\commons-discovery-0.2.jar;%AXIS_LIB%\wsdl4j-1.5.1.jar;%AXIS_LIB%\commons-logging-1.0.4.jar;%AXIS_LIB%\jaxrpc.jar;%AXIS_LIB%\saaj.jar;%AXIS_LIB%\log4j-1.2.8.jar;%AXIS_LIB%\xml-apis.jar;%AXIS_LIB%\xercesImpl.jar;%AXIS_LIB%\servlet-api.jar;
set classpath=".;%AXISCLASSPATH%";


deploy.wsdd
*************

xmlns:java="http://xml.apache.org/axis/wsdd/providers/java">









undeploy.wsdd
*************





CalcClient.java
***************

//package samples.userguide.example2 ;

import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import org.apache.axis.utils.Options;

import javax.xml.rpc.ParameterMode;

public class CalcClient
{
public static void main(String [] args) throws Exception {
Options options = new Options(args);

//this is for filename.jws
String endpoint = "http://localhost:" + options.getPort() + "/axis/Calculator.jws";

//this is for service after create C:\Tomcat5.0\webapps\axis\WEB-INF\src> java org.apache.axis.client.AdminClient deploy.wsdd
//String endpoint = "http://localhost:8080/axis/services/MyService";

args = options.getRemainingArgs();

if (args == null || args.length != 3) {
System.err.println("Usage: CalcClient arg1 arg2");
return;
}

String method = args[0];
if (!(method.equals("add") || method.equals("subtract"))) {
System.err.println("Usage: CalcClient arg1 arg2");
return;
}

Integer i1 = new Integer(args[1]);
Integer i2 = new Integer(args[2]);

Service service = new Service();
Call call = (Call) service.createCall();

call.setTargetEndpointAddress( new java.net.URL(endpoint) );
call.setOperationName( method );
call.addParameter( "op1", XMLType.XSD_INT, ParameterMode.IN );
call.addParameter( "op2", XMLType.XSD_INT, ParameterMode.IN );
call.setReturnType( XMLType.XSD_INT );

Integer ret = (Integer) call.invoke( new Object [] { i1, i2 });

System.out.println("Got result : " + ret);
}
}





by
vijay.dr
9842088860
drvijayy2k2@gmail.com