How to Call an SPL Script in C#
A C# program can call an SPL script through the ODBC interface provided by esProc.
To use esProc ODBC service, you need to first install the ODBC driver on the client side. Find esprocOdbcinst.exe in \bin folder under esProc’s installation directory and execute it as admin to install the driver.
Double-click esprocs.exe (ServerConsole.sh for Linux) in esProc’s [installation root directory]\esProc\bin to get the following window:
Select Odbc Server and click【Config】button to get the Odbc Server configuration window:
Here you can configure IP address, port number, users permitted to access the service and their password, and other information. Click【OK】to finish and save the configuration. Then click【start】to start the service.
After ODBC driver is installed and the service is configured, you can add a proper ODBC data source, like EsprocOdbc ODBC Driver:
On the pop-up window below, you can configure esProc ODBC connection parameters. The data source name can be user-defined, but IP address, port number, user name and password must be consistent with those in server configurations:
Click 【Connect Test】button to check the connection status. The following message will appear if the configuration is correct and data source is successfully connected.
Here’s an example. We create a data table, add two fields baseNum an square2 to it, insert 100 records of natural numbers equal to or less than 100 and their squares, and return the table as a result set.
A C# program:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Text;
using System.Data.Odbc;
namespace ODBCtest
{
class DB
{
public void rset(OdbcConnection conn, string selectSql)
{
OdbcCommand cmd = new OdbcCommand(selectSql, conn);
OdbcDataReader reader = cmd.ExecuteReader();
int nCount = 0;
//Export column names iteratively
for (int i=0; i< reader.FieldCount;i++)
{
Console.Write( reader.GetName(i) + "\t");
}
Console.Write("\n");
while (reader.Read())
{
Console.Write(reader.GetInt32(0) + "\t");
Console.WriteLine(reader.GetInt32(1) + "\t");
nCount++;
}
Console.WriteLine("while end column="+ reader.FieldCount);
Console.WriteLine("while end row=" + nCount);
}
}
class Program
{
static void Main(string[] args)
{
//Generate ODBC connection strings where DSN,UID,PWD properties represents ODBC data source name, user name and password respectively
string constr = "DSN=EsprocOdbc;" + "UID=user0;" + "PWD=123;";
OdbcConnection conn = new OdbcConnection(constr);
conn.Open();
//The SPL statement to be executed
string spl = "10.new(~:baseNum,~*~:square2)";
DB db = new DB();
db.rset(conn, spl);
Console.Write("end....");
Console.ReadKey();
conn.Close();
}
}
}
Result set:
We can access a local file in a SPL program using an absolute path or a relative path. Types of files can be TXT, Excel, JSON, CSV and CTX. A relative path is one relative to the main path configured in the configuration file. You can configure the main path as follows:
Add a node under raqsoftConfig.xml’s < Esproc ></ Esproc > node:
<!-- esProc main path, which can only be an absolute path-->
<mainPath>D:\mainFile</mainPath>
Now put the to-be-called employee.txt in the main directory. The C# code of establishing a data source connection at calling is the same as that in the previous example. Below is the SPL statement:
string spl = "=file(\"employee.txt\").import@t()"; //SPL statement
Both an absolute path and a relative path are OK.
The following is a part of the code for outputting the result set:
while (reader.Read())
{
Console.Write(reader.GetInt32(0) + "\t");
Console.Write(reader.GetString(1) + "\t");
Console.Write(reader.GetString(2) + "\t");
Console.Write(reader.GetString(3) + "\t");
Console.Write(reader.GetString(4) + "\t");
Console.Write(reader.GetDate(5).ToString("yyyy-MM-dd") + "\t");
Console.Write(reader.GetDate(6).ToString("yyyy-MM-dd") + "\t");
Console.Write(reader.GetString(7) + "\t");
Console.WriteLine(reader.GetInt32(8) );
nCount++;
}
Result set:
You can use simple SQL syntax to do a computation as simple as this example:
string spl = "$select * from employee.txt";
The symbol $ means accessing a local file system. The simple SQL statement gets same result set.
Parameters are an important part of a SQL statement. So are they in SPL statements. To find records from employee.txt where salary falls between 10000 and 15000 and sort them by salary in ascending order, for example:
Part of the SPL code to be called:
string spl = "$select * from employee.txt where SALARY > ? and SALARY< ? order by SALARY";
The question mark？represents a parameter.
Below is a part of the code for passing in parameter with the rset method:
OdbcCommand cmd = new OdbcCommand(selectSql, conn);
cmd.Parameters.Add("arg1", OdbcType.Int).Value = 10000;
cmd.Parameters.Add("arg2", OdbcType.Int).Value = 15000;
Result set:
As a data computing tool, esProc computes data that coming from various sources. Databases are one of the most frequently used sources. Let’s look at how to call an SPL script having a database source.
First you need to put corresponding data base drivers in the application project; then you configure data source information in configuration file raqsoftConfig.xml:
For example, the data source used in an SPL script is dm and its type is HSQL, then we can make configurations as follows:
First, load HSQL driver hsqldb-2.2.8.jar onto the application project;
Second, configure data source information in raqsoftConfig.Xml’s < Runtime ></ Runtime > node:
<DBList>
<DB name="dm">
<!—data source name-->
<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" />
<!—URL connection-->
<property name="driver" value="org.hsqldb.jdbcDriver" />
<!—database driver-->
<property name="type" value="13" />
<!—database type-->
<property name="user" value="sa" />
<!—user name-->
<property name="password" value=""/>
<!--password-->
<property name="batchSize" value="1000" />
<property name="autoConnect" value="true" />
<!--auto-connect or not; if the autoConnect value is true, you can use a SQL statement headed by $ to access the database; if it is false the database won’t be automatically connected and you need to use connect(db) to establish a connection-->
<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>
</DBList>
To query SALES table in dm database to find orders where SELLERID is 3 during 2014-11-11 to 2014-12-12 in SPL, for example:
Below is part of the code for calling the SPL script:
string spl = "$(dm)select * from SALES where SELLERID = ? and ORDERDATE>TO_DATE(?,'YYYY-MM-DD') and ORDERDATE<TO_DATE(?,'YYYY-MM-DD')";
Below is a part of the code for passing in parameter with the rset method:
cmd.Parameters.Add("arg1", OdbcType.Int).Value = 3;
cmd.Parameters.Add("arg2", OdbcType.Date).Value = "2014-11-11";
cmd.Parameters.Add("arg3", OdbcType.Date).Value = "2014-12-12";
The following is a part of the code for outputting the result set:
while (reader.Read())
{
Console.Write(reader.GetInt32(0) + "\t");
Console.Write(reader.GetString(1) + "\t");
Console.Write(reader.GetInt32(2) + "\t");
Console.Write(reader.GetDecimal(3) + "\t");
Console.WriteLine(reader.GetDateTime(4).ToString("yyyy-MM-dd HH:mm:ss") + "\t");
nCount++;
}
Result set:
A complex SPL script is a file with dfx extension. Take the following dfx file as an example:
| A | B | C | |
|---|---|---|---|
| 1 | =connect("dm").query("select NAME as CITY, STATEID as STATE from CITIES") | [] | |
| 2 | for A1 | =dm.query("select * from STATES where STATEID=?",A2.STATE) | |
| 3 | if left(B2.ABBR,1)==arg1 | >A2.STATE=B2.NAME | |
| 4 | >B1=B1 | ||
| 5 | return B1 |
SPL script explanation:
Loop over CITIES records to filter STATES records by CITIES.STATES. The rule is like this: if the first letter of STATES.ABBR value is parameter arg1, assign STATES.NAME to CITIES.STATE and add the CITIES record to B1. The result set is returned by B1.
The cellset file gets data from database dm and defines a parameter arg1:
Data source configuration was already explained in the above. The cellset file is saved as city.dfx and can be put in the application project’s class path or the main directory configured in raqsoftConfig.xml. If there are multiple dfx files, we can put them all onto the dfx search path for the convenience of management and maintenance. Configure a search path as follows:
Add the following node in raqsoftConfig.xml’s < Esproc></ Esproc> node:
<dfxPathList>
<!-- The dfx search path is an absolute one; separate multiple paths by semicolon -->
<dfxPath>D:\dfxFile</dfxPath></dfxPathList>
Below is part of the code for calling the SPL script:
string spl = "call city(?)";
Below is a part of the code for passing in parameter with the rset method:
cmd.Parameters.Add("arg1", OdbcType.VarChar).Value = "A";
The following is a part of the code for outputting the result set:
while (reader.Read())
{
Console.Write(reader.GetString(0) + "\t");
Console.WriteLine(reader.GetString(1) + "\t");
nCount++;
}
You can also pass a parameter while calling the dfx file using call statement:
string spl = "call city('A')";
Result set:
Those are the common scenarios of calling an SPL script from a C# program. Click Here to learn more about esProc integration and deployment.