How to Call an SPL Script using HTTP Service

 

esProc supports HTTP service. Users can get the result set of executing an SPL script through an URL. Below is the process:

undefined 

Deployment

Follow two steps to deploy an HTTP server  server configuration & server startup.

Server configuration

Run esprocs.exe in esProc\bin path under esProcs installation directory to configure and start the server. Below is the pop-up window after running the file:

undefined 

The initial configuration information will be loaded and displayed on the window when esprocs.exe is running. The configuration is done in configuration file raqsoftConfig.xml. Click Options button in the vertical menu bar on the right to configure the server information on the pop-up window:

undefined 

You can configure license file, main path, search path, date and time format, default character set, log level and file buffer size and other server-related information.

Click Config button on HTTP Server tab to get the following window for configuring HTTP host and port:

undefined 

The information covers server IP and port and the max number of parallel threads. Then click OK to auto-configure the corresponding configuration file HttpServer.xml, as shown below:

<?xml version="1.0" encoding="UTF-8"?>

<!--Note: In order to avoid problems while the program is running, the charset of HttpServer.xml must be UTF-8 -->

<Server Version="1" host="127.0.0.1" port="8503" parallelNum="10"/>

Server startup

After configuration is finished, click Start on HTTP Server tab to run the server:

undefined 

To terminate the service, click Stop button; then you can click Quit to exit the HTTP service. By clicking Reset button, the service will initialized and restarted while all global variables are deleted and memory resource is released.

On the Linux platform you can run ServerConsole.sh to start the server class. The runtime info window is the same as that on Windows platform.

You can also add the h parameter at command line to launch the HTTP server in a non-GUI environment by executing command ./ServerConsole.sh h.

HTTP invocation

Invoking an SPL script with HTTP is in essence reading in the result set of a dfx file using an URL. The result of the to-be-called dfx file must be returned using a return statement. Lets look at how to do the invocation.

Calling one dfx file

To get the employee table stored in HSQL database through the HTTP service, for instance, we need to first configure the data source information in raqsoftConfig.xmls < Runtime ></ Runtime > node:

 

<DB name="HSQLDB">  <!data source name-->

                <!—url connection-->

<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" /> 

<!—database driver-->

                <property name="driver" value="org.hsqldb.jdbcDriver" /> 

                <property name="type" value="13" /> <!—database type-->

                <property name="user" value="sa" /> <!user name -->

                <property name="password" value="123456"/> <!-- password -->

                <property name="batchSize" value="1000" />

                <property name="autoConnect" value="true" /><!—automatically connect or not -->

                <property name="useSchema" value="false" />

                <property name="addTilde" value="false" />

                <property name="dbCharset" value="UTF-8" />

                <property name="clientCharset" value="UTF-8" />

                <property name="needTransContent" value="false" />

                <property name="needTransSentence" value="false" />

                <property name="caseSentence" value="false" />

            </DB>

 

Below is the dfx script:

 


A

1

=connect("HSQLDB")

2

=A1.query("select * from employee")

3

=A1.close()

4

return A2

 

We name the dfx file emp.dfx and save it under the main directory. The syntax of calling it through an URL in HTTP is http://127.0.0.1:8503/emp.dfx. 127.0.0.1:8503 is the configured IP and port number, which is followed by the dfx file name with the extension.

 

The browser visits the URL and returns the following result:

undefined 

The result is non-format text. Thats why we use the browser to perform debugging. In real-world business scenarios, users can call a URL in their own application and process the result set as needed. To visit the above URL in a Java program, for example:

You can use the following Java code:

 

 URL url = new URL("http://127.0.0.1:8503/emp.dfx");  //Pass in URL string

         HttpURLConnection urlcon = (HttpURLConnection)url.openConnection();

         urlcon.connect();         //Get the connection

         InputStream is = urlcon.getInputStream();

         BufferedReader buffer = new BufferedReader(new InputStreamReader(is));

         StringBuffer bs = new StringBuffer();

         String l = null;

         

         while((l=buffer.readLine())!=null){

             bs.append(l).append("\n");

         }

         System.out.println(bs.toString());

 

Final result:

undefined 

Calling a dfx file containing parameters

The dfx file to be called may contain parameters. For instance, to query SALES table in a database to find orders won by salespeople whose SELLERID is 3 during the period November 11  December 12 in 2014.

 

Heres dfx script:

        


A

1

=connect("HSQLDB")

2

=A1.query("select * from SALES  where  SELLERID = ? and  ORDERDATE>? and ORDERDATE<?",arg1,arg2,arg3)

3

=A1.close()

4

return A2

 

Set cellset parameters:

   undefined

We name the dfx script sales.dfx and save it in the main directory. The syntax of calling it through an URL in HTTP is http://127.0.0.1:8503/sales.dfx(3,2014-11-11,2014-12-12). The parentheses afther the dfx file contains multiple parameter values separated by commas.

Calling multiple dfx files

We can reformat the result set of the dfx file as needed or desired using another dfx file. In this case, the HTTP server calls two dfx files.

The URL calling syntax is http://IP:port/dfx1.dfx(...)dfx2.dfx., in which dfx2.dfx, which has a parameter whose value is dfx1s return value, references dfx1.dfx to make it return a single result set.

For example, to query a local file Sales.txt with dfx1 to get all values in STATE field:

 

Heres p1.dfx:

 


A

1

$()select * from  D:\Sales.txt

2

=A1.(STATE)

3

return A2

 

By default the file returns a sequence of STATE values. But if we need to return comma-separated strings, we can reformat the result set using p2.dfx.

 

Heres p2.dfx:

 


A

1

=arg1.concat@c()

2

return A1

 

Set a cellset parameter:

undefined 

The URL calling syntax in HTTP is http://127.0.0.1:8503/dfx1.dfx()dfx2.dfx().

SAP Result style

In the above examples, the dfx files are all saved in the main directory. But how can we call a dfx file located in a subdirectory under the main directory?

esProc offers a way similar to SAP Result style. First we configure a dfx path relative to the main directory in sapPath property in httpServer.xml. The path can be of multilevel directory structure or an empty string. The latter means the file settles under the main directory. Use comma separated multiple paths. Heres an example:

 

<?xml version="1.0" encoding="UTF-8"?>

<Server Version="1" host="127.0.0.1" port="8503" parallelNum="10" sapPath="/sf,,/sr/pm"/>

 

In this XML code snippet, three relative paths are configured in sapPath. They are [main directory]/sf, [main directory] and [main directory]/sr/pm.

 

The URL syntax for calling a dfx without a parameter is http://IP:port/sapPath / dfxName; and that for call one containing parameters is http://IP:port/sapPath / dfxName/arg1[value1]/arg2[value2],....

sapPath represents the path configured in httpServer.xmls sapPath property;

dfxName is the dfx name without an extension;

arg1[value1]/arg2[value2],... are slash-separated parameters and their names in the dfx file. A parameter name must be a string of letters and parameter value should be a string starting with a number. The name and value will be combined together to respond to a call.

 

Example1: To query a local data file using gy.dfx:

 


A

1

$()select * from Geography.txt

2

return A1

 

As the dfx file is in [main directory]/sf path, the URL ishttp://127.0.0.1:8503/sf/gy.

/sf represents the dfx files location relative to the main directory; gy represents the dfx file gy.dfx

 

Example2: To call a dfx file having parameters using gypb.dfx:

 


A

1

$()select * from Geography.txt where ID>? and Parent=?;id,parent

2

return A1

 

   Set two cellset parameters:

undefined 

The dfx file, which is under [main directory]/sr/pm, uses two parameters id and parent. The URL is http://127.0.0.1:8503/sr/pm/gypb/id3/parent15.

/sr/pm is the dfx files location relative to the main directory; gypb is the dfx file gypb.dfx; id3/parent15 means that parameter ids value is 3 and parameter parents value is 15.

 

Example3:

If an empty string is configured in xmls sapPath, you can call a dfx file under main directory in syntax http://127.0.0.1:8503/emp (Take emp.dfx as an example). Note that the dfxs extension is omitted here.

Syntax for IPV6 protocol

esProc HTTP service supports IPV6 protocol. This requires a JDK 1.7 version or above. The URL syntax is different by enclosing the IP address with square brackets ([]).

For example, if the server configuration is like this:

 

undefined 

 

Then the URL for calling the dfx file is http://[fe80::9c9d:a071:3abc:e207]:8503/localData.dfx. Note: the percent sign % is a special character; when it appears in an IP address it should be replaced with %25 in URL. Or you can just omit the percent sign and the characters after it at the end of an IPV6 address since they are not indispensable.

An Online Tutorial is offered for you to learn more details about HTTP service and esProc.

 

Read these articles to find how to call the SPL script in different languages and reporting tools:
How to Call an SPL Script in Java

How to Call a Remote SPL Script in Java
How to Call an SPL Script in BIRT
How to Call an SPL Script in C#
How to Call an SPL Script in JasperReport