C#How to invoke SPL scripts

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:

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

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:

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

Add new comment

0 comments