esProc Getting Started: Basic Usage of JDBC

esProc can be embedded into Java program. So the latter can call the cellset program written in esProc using a way of connection such as JDBC. The method of calling the esProc program is the same as that of calling the stored procedure. The following is a brief introduction to esProc JDBC.

1. Description of the jars of esProc JDBC

esProc JDBC is like an incomplete database JDBC driver without physical tables. It can be regarded simply as a database that only supports the stored procedure. In addition, it is a built-in computing engine, thus no standalone servers are needed.

esProc JDBC has five basic jars, which are all situated in \esProc\ lib in installation directory:

dm.jar                              esProc computing engine and JDBC driver

poi-3.7-20101029.jar                process the access of Excel files

log4j_128.jar                       process logs

icu4j_3_4_5.jar                     handle internationalization

dom4j-1.6.1.jar                     parse the configuration files

If other databases are to be used as the datasources of esProc JDBC, then the driver jars of these databases are required to be in place. For example, hsqldb.jar is necessary to use the demo database. Please note the esProc JDBC requires JDK1.6 or higher versions. 

2. Basic usage of esProc JDBC

In the cellset code, the result set is returned by result statement.

In the application code, arg1 is a cellset parameter. This dfx file will be named as test.dfx.

Note: The result set of dfx is returned by result statement. When dfx is called, the parameter names that receive the parameters won’t be used; the values of parameters will be assigned according to their order instead. 

(1) Load the jars to be used. Load the basic jars of esProc JDBC mentioned above while launching the Java application. These jars can be put in the directory of WEB-INF/lib under a web application.

(2) Deploy dfxConfig.xml, config.xml and the dfx file

Prepare file config.xml, which contains the basic configuration information of esProc, such as registration code, searching path, datasource configuration, etc. The file can be found in the path esProc/config in esProc's installation directory. The information in it is the same as that set in the esProc Option page. The configuration is allowed to be adjusted before deployment (like modifying the Searching path which is used to search the dfx file): 

Or the datasources necessary for dfx can be configured in the Data Source Explorer: 

After the modification, config.xml and dfxConfig.xml, which are situated in esProc\classes in the esProc's installation directory, will be saved in the class path of the application that will use them.

Put the test.dfx created in Step 1 in the class path of the application, or put it in the path specified by file dfxConfig.xml's <paths/> node (i.e. the above-mentioned Searching path).

(1) Further configure file dfxConfig.xml manually if necessary. For detailed operation, please refer to related documents. Please note the name of configured file should still be dfxConfig.xml and must not be changed.

(2) Call dfx in Java program.

public void testDataServer(){

      Connection con = null;

      com.esproc.jdbc.InternalCStatement st;

      com.esproc.jdbc.InternalCStatement st2;

      try{

          //create a connection

          Class.forName("com.esproc.jdbc.InternalDriver");

          con= DriverManager.getConnection("jdbc:esproc:local://");

          //call the stored procedure in which test is the name of dfx file

          st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

          //set the parameters

          st.setObject(1,"3");

          //the result obtained by executing the following code is the same as that obtained using the above calling method

          st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call test(3)");

          //execute the stored procedure

          st.execute();

          //get result set

          ResultSet set = st.getResultSet();

}

       catch(Exception e){

          System.out.println(e);

       }

       finally{

          //close the connection

          if (con!=null) {

              try {

                   con.close();

                   }

              catch(Exception e) {

                   System.out.println(e);

                   }

               }

        }

}

To know more about calling methods and configuration, please refer to documents that cover a more in-depth discussion at this point. 

esProc Getting Started: Multilayer Parameters

esProc provides a large number of functions, many of which use many parameters. In order to clearly judge the positions of these parameters and make writing and reading easier, esProc is specially equipped with multilayer separators of function parameters.

1. Separators of function parameters

Colon (:), comma (,) and semicolon (;) are used as separators of function parameters in esProc. Their priority decreases in turn.

The most common method is to use commas to separate parameters, which is in line with function syntax in most programming languages. For example:

In this example, parameters of if(), create() and T.insert() function in A3, A4 and A5 are separated from each other by commas. After the code in A3 is executed, the result of A4 is as follows: 

Some functions have "coupled" parameters, which are closely related or work together. In this case, a colon is often used to separate them. 

For example, for if() function in A3, each condition corresponds to a returned result and colons are used to separate the results. In the in() function used in a condition, 150 and 180 are also separated by a colon and together they form a numerical interval [150,180]. In A5's T.insert() function, field value and field name also come in pairs with colons in between to separate them. After execution, the result in A4 is as follows:   

In some functions, indicative parameters can be added to certain parameters to change the computational method relating to them. In this case, colons are usually used as separators. See below: 

Both A2 and A3 sort records of cities according to state ID first, then sort by name if cities belong to the same state. Difference is that -1 is appended after NAME in A3’s function, meaning that sorting by name is in a descending order. The results in A2 and A3 are as follows: 

Sometimes, parameters in a function can be divided into different parts according to their roles. Semicolons are usually used to separate these parts. 

In A2's groups() function, the parameter before the semicolon is used for grouping, and those after it are for summarizing computation, whose parameters are separated by a colon to define name of the summarizing field. The result in A2 is as follows: 

In A3's top() function, the parameter after the semicolon defines that the top 5 records are fetched. The result is as follows: 

In some functions, parameters are quite many. Usually these parameters are divided into several groups which are separated by semicolons: 

T.switch() function in A4 transforms different fields into records of another table sequence, and a semicolon is used here. Besides, comma, colon and semicolon are all used in A4 as separators. This kind of code writing creates clear layers for function parameters. The result in A4 is as follows: 

2. Omission of function parameters

Some esProc parameter functions have default values and, therefore, can be omitted, making functions more concise.

The parameter after a colon is generally used to complement another’s computational model. If default mode is used, the parameter can be omitted. For example: 

In A5, parameters after the colons are used to designate field names corresponding to certain values when inserting records. Parameters for designating field names can be omitted in A6 because they use default field names to set field values one by one. Two records are inserted respectively in A5 and A6, then the result in A4 is as follows: 

But colons cannot be omitted when they are used to separate intervals: 

In A3, in(B1+B2,180:) and in(B1+B2,:120) represent respectively B1+B2>=180 and B1+B2<=120, in which the colons cannot be omitted. The result in A3 is as follows: 

For parameters separated by semicolons, the semicolon can be omitted if there is no parameter after it. For example, if n is not set in A.top() function and only the top one is needed; or when the transformation of a certain field is not needed in T.switch() function.

When commas are used as separators, they should generally be retained if parameters are set by default. For example: 

Expression in A2 is equal to =A1.to(4,A1.len()), expression in A3 is equal to =A1.to(1,4). Let’s compare the results in A2 and A3: 

esProc Getting Started: Option Syntax

1. esProc's function options

Many functions in esProc can use function options with which the same function can have different work patterns. The basic format of function options is f@o(…) in which o is f function’s option. For example:

We can use interval function to compute the number of days between two dates. In expressions in A3 and A4, @y and @m are interval function’s options. With these options, the function will use the year and the month as computational units in computing time interval. With function options, a function can meet multiple needs, extend its role and avoid too many function names or function parameters. The number of days, years and months between the two dates are computed respectively in A2, A3 and A4, as shown below: 

2. Common function options

Some function options are common in esProc. They can be used by many functions.

@1 and @a

@1 option and @a option are frequently used by position, select and join functions , such as A.pos(), A.select(), A.pmax(), A.pselect(), A.minp(), P.align(), etc.

Use of @a option can make functions that return the first query result by default return all eligible results. Contrary to @a, functions that return multiple members by default will return only the first query result by using @1 option.

We’ll look at the use of these two options through some examples:

The results in A2, A3, A4 and A5 are as follows: 

The cities whose names start with a C are selected in A2: 

The first city whose name starts with a C is selected in A3: 

The first cities whose STATEIDs are 6, 35 and 40 respectively are selected in A4: 

All cities whose STATEIDs are 6, 35 and 40 are selected and grouped by states in A5: 

By the way, since digit 1 is difficult to distinguish from the lowercase letter l, in most cases, the former is used in esProc's options.

One other thing need to be made known is that the same option in different functions can have different meanings. For example, @a used in position functions means returning all results, while in functions for file writing, like f.write() and f.export(), it means appending.

@z

@z option is often used in some functions related to order, like sort, position, select, etc. Such as A.rank(), A.sort(), A.pos(), A.pselect(), A.select() and so on. 

With @z option, positioning or selecting data in a sequence or a table sequence will be executed from back to front. Thus in this example, A2 returns the position of the last 2:  

A3 returns results of sorting in descending order: 

Records obtained in A5 are also sorted in descending order: 

@b

@b option is often used in functions to position, select, etc., like A.pos(), A.pselect(), A.select(), and so on. The use of @b option is usually accompanied by binary search algorithm, which is more efficient in query, with the prerequisite that A is ordered; otherwise results may be wrong. 

Since data in A1 is not sorted according to state ID, only one result is obtained in A2 with the binary search when @b option is used: 

while A4 obtains the correct results because data is sorted in A3: 

But it is another thing when @b is used in functions to read and write, such as f.import(), f.export(), f.cursor(). In this case, it is the binary files that being read in or written out. In esProc, binary files use less memory space and have faster access speed, therefore the use of binary files will bring higher efficiency.

3. Use multiple options simultaneously

Multiple options can be used simultaneously in esProc when needed. There is no specific order among these options. For example: 

Because both A 2 and A3 get positions of all the 2 in A1 from back to front, their results are identical: 

A5 gets the first city whose STATEID is 5 from back to front: 

Note that some function options are mutually exclusive , thus cannot be used simultaneously. Such as @a and @1, or @t and @b, options of f.import(). 

esProc Getting Started: Use of Common Data

To make data analysis, first you need to load the original data. The data used most frequently comes from the text files or the databases. In esProc, you can load data from the text files or the databases easily and quickly.

1. Text file data

esProc can load data from a text file as a table sequence. For example, the text file empolyee.txt contains employee information as follows:

EID   NAME       SURNAME        GENDER  STATE        BIRTHDAY        HIREDATE         DEPT         SALARY

1       Rebecca   Moore      F       California 1974-11-20       2005-03-11       R&D          7000

2       Ashley      Wilson      F       New York 1980-07-19       2008-03-16       Finance    11000

3       Rachel      Johnson   F       New Mexico     1970-12-17       2010-12-01       Sales         9000

4       Emily         Smith        F       Texas        1985-03-07       2006-08-15       HR    7000

5       Ashley      Smith        F       Texas        1975-05-13       2004-07-30       R&D          16000

6       Matthew Johnson   M     California 1984-07-07       2005-07-07       Sales         11000

7       Alexis        Smith        F       Illinois       1972-08-16       2002-08-16       Sales         9000

8       Megan     Wilson      F       California 1979-04-19       1984-04-19       Marketing        11000

9       Victoria    Davis        F       Texas        1983-12-07       2009-12-07       HR    3000

…

import function is used in esProc to import data from files: 

In A2, the use of option @t in import function means that, during data importing, the first row of the text file will be regarded as the column names in the imported table sequence. The data in A2 is as follows:

Let's look at what it will be like if option @t isn't used. The imported table sequence in A3 is as follows:

2. Database data

esProc can access various kinds of databases through JDBC. Click menu item Datasource Connection in Tool to view the datasource manager:

You can connect to or disconnect from a certain datasource, as well as configure the database to be connected through the datasource manager. demo is esProc's built-in datasource which is launched by executing esProc\bin\startDataBase.bat under the installation directory. Once it connects to the datasource, esProc gains access to the database and fetches data using SQL:

query function can be used to get the result set after executing the SQL command and retrieve it as a table sequence, like the code in A1. When the database is connected, SQL statement can be written directly behind $, like what is shown in A2. The results in A1 and A2 are the same, as shown below:

Besides using the datasource manager, you can also use connect function to connect to a datasource. In this case, you should close the connection using close function after data is retrieved from the database:

The same method is used in A2 to import the table sequence of cities information.  

esProc Getting Started: Constants

In esProc computing, we use constants frequently or sometimes store the data in the cellset directly. In this article, let's learn the usage of constants in the esProc.

1. Directly using constant in the expressions

Constants can be directly used in the esProc expressions:

When constants are used in the expressions, the integer and the float can be used directly, but the string must be enclosed in quotes. For the Boolean constants true and false, they can be used directly in the expressions, but there are few cases of using them. The results in A1, A2 and A3 are as follows:

In particular, the capitalized L can be appended to the integer to indicate the long integer. Compared with the integer, the long integer has a wider range of value; the hexadecimal long integer can be represented with a string whose first two characters are 0x:

Because the value range of normal integers is -2³¹~2³¹-1, that is, -2147483648~2147483647, the result in A2 exceeds the value range of integer. In A1, a long integer is used, and the value range is increased to -2⁶³~2⁶³-1. By this way, the correct result can be obtained. The computed results in A1, A2 and A3 are as follows:

As can be noticed, during a certain step of the computation, if one of the operands involved in the integer computing is the long integer, the result is the long integer.

In a string used in an expression, if there are special characters like " and \, an escape character \ is required as an indicator before each special character:

After execution, the strings in A1 and A2 are as follows:

The time and date constants are not allowed in the expressions. Instead, only the type conversion functions, such as date(), time(), and datetime(), can be used to convert strings or long integers:

The result in A1 is as follows:

2. Constant cells

When the data is written to the cell directly, if the cell can interpret the string as a constant, then this cell can be regarded as a constant cell. Its cell value is the constant.

The default text in the constant cell is pink. Based on the data in the cell, the constant will be parsed into various data types. If the data is unidentifiable, it will be interpreted as a string.

The double quotation marks are not necessary when a string is defined in the constant cell.

In addition, in the constant cell, the constant can be represented as a percentage, like 5%:

After the computation, the cell values of A1 and A2 are as follows:

In the constant cell A1, 5% will be converted to the corresponding float value 0.05. Note: The writing style of 5% cannot be used in the expressions. It is only valid in the constant cells.

In particular, the value in the constant cell can also be true, false or null:

As can be seen from above, the constant cell value is the corresponding Boolean value or null value at this point:

The string constant cell can be used to specify the value of a cell as a string. The cell string in a string constant cell starts with the single quotation mark ' of English font. The string constant cell is used to define the strings containing special characters or those composed of figures:

In the above, the value of the constant cell A1 is the string abc\d; the cell value of A2 is the string 1234.5; the cell value of A3 is the number 1234.5:

In the constant string cells, the escape character \ is not necessary if any special character exists in the string:

Values of strings in A1and A2 are as follows:

In a constant cell, we can also define the sequence constant with the brackets [], and use commas to separate the sequence members. For example,

The sequence constants in A1 and A2 are as follows:

In defining a sequence constant, the double quotation marks are also unnecessary for indicating the string members in a sequence.

In an expression, we can use the cell name to reference the constant cell:

The result in A2 is as follows:

3. The sequence of constant cells

In esProc, once constants are saved in multiple cells, these cells are often combined to form a constant sequence for use in the expression. For example,

In A2, the expression [A1:F1] indicates a sequence composed of the values of all cells in the A1~F1 area. The sequence is as follows:

The cells in the sequence can also be in a continuous area:

In A3, expression [A1:C2] indicates a sequence composed of values of cells in an area from A1 to C2, and in which the members follow the order of first-row-then-column.

In esProc, an area of constant cells is often used to make up a TSeq:

In A5, a new TSeq is created. In A6, the record function is used to fill the TSeq with the values of the constant cells in the area of A1~E4.

With such method, the dfx file can be used to store the tabular data.