C#How to invoke SPL scripts

The aggregator provides an ODBC interface, and C#can invoke SPL through the aggregator ODBC.The structure diagram is as follows:

ODBC Service

Install aggregator ODBC driver

To use the aggregator ODBC, first the client needs to install the ODBC driver. In the bin directory under the aggregator installation path, execute esprocOdbcinst.exe with administrator privileges to install the driver for the aggregator ODBC.

Start ODBC Service

In the aggregator [Install Root]\esProc\bin, double-click the esprocs.exe file (on Linux systems, you can run ServerConsole.sh to start the service window), and the following service windows pop up:

Select Odbc Server and click the Config button to open the aggregator ODBC service window as follows:

The configuration of ODBC service mainly configures IP, port of ODBC service, user name and password allowed to access.When the configuration is complete, click [OK] to save the configuration.Then click [start] to start the service.

Add ODBC Data Source

After successful driver installation, you can add the corresponding ODBC data source and select EsprocOdbc ODBC Driver

In the configuration window that pops up, configure the connection parameters of the aggregator ODBC, the data source name can be customized by the user, and the IP, port, username password and other parameters should be consistent with the configuration in the server, such as:

Click Connect Test to test the connection, and if configured correctly, it will show that the test connection was successful:

C#Call

Execute SPL statement

For example, create a data table, add two fields, baseNum and square2, insert 100 records of natural numbers and their square values into the data table, and return the data in the table as a result set.

The C#code is as follows:

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

{   

classDB

    {

publicvoid rset(OdbcConnectionconn, string selectSql)

        {

OdbcCommandcmd = newOdbcCommand(selectSql, conn);

OdbcDataReader reader = cmd.ExecuteReader();

int nCount = 0;

//Loop Output Column Name

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);

        }

    }

classProgram

    {

staticvoid Main(string\[\] args)

        {

//Generate ODBC connection string, where DSN, UID, PWD attributes represent ODBC data source name, user name, password in turn

stringconstr = "DSN=EsprocOdbc;"\+ "UID=user0;"\+ "PWD=123;";

OdbcConnectionconn = newOdbcConnection(constr);

            conn.Open();

//SPL statement to execute

stringspl = "10.new(~:baseNum,~*~:square2)";

DBdb = newDB();    

               db.rset(conn, spl);

Console.Write("end....");

Console.ReadKey();

            conn.Close();

        }

    }   

}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

{   

classDB

    {publicvoid rset(OdbcConnectionconn, string selectSql)        {

OdbcCommandcmd = newOdbcCommand(selectSql, conn);

OdbcDataReader reader = cmd.ExecuteReader();int nCount = 0;//Loop output column name 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);

        }

    }

classProgram

    {staticvoid Main(string\[\] args)        {//Generate ODBC connection string, where DSN, UID, PWD attributes represent ODBC data source name, user name, password stringconstr = "DSN=EsprocOdbc;"\+ "UID=user0;"\+ "PWD=123;";

OdbcConnectionconn = newOdbcConnection(constr);

            conn.Open();//The SPL statement stringspl = "10.new(~:baseNum,~*~:square2)";

DBdb = newDB();    

               db.rset(conn, spl);

Console.Write("end....");

Console.ReadKey();

            conn.Close();

        }

    }   

}

Execution results:

Accessing local files in SPL

Through SPL, you can also access local files, including Txt, Excel, Json, Csv, Ctx and many other types of files. You can find the location of files by absolute path or relative path when accessing them. When using relative path, you can find the home directory relative to the configuration file. So first, we configure the following home directory:
Add the following nodes to the node < Esproc ></ Esproc > of the raqsoftConfig.xml file:

<!--The main path of the aggregator, which is a single absolute path-->

<mainPath>D:\mainFile</mainPath> <!--Assembler main path, which is a single absolute path--><mainPath>D:\mainFile</mainPath>

We put the file employee.txt to be called under the home directory. When called in C#, the code for the parts such as establishing a connection is exactly the same as the above example. The SPL statement is as follows:

stringspl = "=file(\\"employee.txt\\").import@t()";//SPL statement stringspl ='=file (\"employee.txt\").import@t()';//SPL statement

The use of absolute and relative paths is supported here.

The result set output section code is as follows:

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++;

            }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++;

            }

Execution results:

For this simple operation, you can also use simple SQL syntax:

stringspl = "$select * from employee.txt";stringspl = "$select * from employee.txt";

Where $means access to the local file system, the result set for both writings is the same.

SPL statement with parameters

Parameters are an important part of the SQL statement, and the use of parameters is also supported in SPL statements. For example, in the example above, to query the data in the employee.txt file, but only records with wages between 1000 and 15000 are required to be queried, sorted according to the wage ascending order:

The calling code is as follows:

stringspl = "$select * 
              from employee.txt 
              where SALARY > ? and SALARY< ? 
              order by SALARY";stringspl = "$select * 
              from employee.txt 
              where SALARY > ? and SALARY< ? 
              order by SALARY";

Use it?Represents a parameter.

The code for the parameter part of the rset method is as follows:

OdbcCommandcmd = newOdbcCommand(selectSql, conn);

          cmd.Parameters.Add("arg1", OdbcType.Int).Value = 10000;

          cmd.Parameters.Add("arg2", OdbcType.Int).Value = 15000;OdbcCommandcmd = newOdbcCommand(selectSql, conn);

          cmd.Parameters.Add("arg1", OdbcType.Int).Value = 10000;

          cmd.Parameters.Add("arg2", OdbcType.Int).Value = 15000;

Execution results:

SPL statement with data source

Since the aggregator is a data calculating tool, one of the important ways of data source is the database. How can SPL statements with data source be invoked in C#?Look down:
Before C#invokes an SPL statement with a data source, you need to add the corresponding database driver to the application project and then configure the data source information in the configuration file raqsoftConfig.xml.
For example, if the data source name used in the SPL statement invoked is dm and the database type is HSQL, the configuration is as follows:
First, load HSQL's dataset-driven hsqldb.jar into the application project;
Second, configure the data source information in the < Runtime ></ Runtime > node of raqsoftConfig.Xml:

<DBList>

<DB name="dm">

<!--Data Source Name-->

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

 <!\-\- url Connect-->

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

 <!--Database Driver-->

<property name="type" value="13" ></property>

<!--Database type-->

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

 <!--User name-->

<property name="password" value=""></property>

 <!--Password-->

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

<property name="autoConnect" value="true" ></property>

<!--Whether to connect automatically, if set to true,Then you can directly $initial SQL Statement to access the database if false,It will not connect automatically and must be used before use connect(db)Statement to create a database connection-->

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

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

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

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

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

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

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

</DB>

</DBList><DBList><DB name="dm"><!--Data Source Name--><property name="url" value="jdbc:hsqldb:hsql://127.0.0.0.1/demo "/> <<!!! \\- url connection--><property name="driver "value=" value="driver"value="org.hsqldb.jdbcDriver"/><!!!--Database driver--><property name= "value type=" value="value="13 "/><!--Database type--><property name=="property name="user"value=="user"value=="/>>!--User name--><property=" name="password password \\\\\\\\"1000 "/><property"name="autoConnect" value="true"/><!--Whether to connect automatically, if set to true, you can access the database directly with a SQL statement starting with $and if false, it will not connect automatically. You must create a database connection with a connect(db) statement before using --><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>

Now we query the SALES table from the dm data source by SPL to filter out all order information for employees with SELLERID 3 between November 11, 2014 and December 12, 2014:

The calling code is as follows:

stringspl = "$(dm)select * 
             from SALES  
             where  SELLERID = ? and  ORDERDATE>TO\_DATE(?,'YYYY-MM-DD') and  ORDERDATE<TO\_DATE(?,'YYYY-MM-DD')";stringspl = "$(dm)select * 
             from SALES  
             where  SELLERID = ? and  ORDERDATE>TO\_DATE(?,'YYYY-MM-DD') and  ORDERDATE<TO\_DATE(?,'YYYY-MM-DD')";

The parameter pass-through part of the code in the rset method is as follows:

 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"; 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 result set output section code is as follows:

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

Tags: Big Data odbc Database SQL xml

Posted on Wed, 08 Apr 2020 15:21:14 -0400 by taboo