SQL+

Read(17) Label: sql+,

SQL+ is an identifier-driven query language based on SQL syntax. It is the medium of switchover from SQL to SPL. A SQL+ statement is semantically equivalent to its SQL counterpart and can execute over database tables.

Though it has a SQL-style syntax, SQL+ supports queries over bin files (with btx extension) and composite tables (with ctx extension) only. See Composite Tables to learn more about composite tables.

Basics

The following code generates two bin files b_emp.btx and b_st.btx from EMPLOYEE table and STATES table in demo database, for the convenience of comparison with SQL:

 

A

1

=demo.query("select * from EMPLOYEE")

2

>file("d_emp.btx").export@b(A1)

3

=demo.query("select * from STATES")

4

>file("d_st.btx").export@b(A3)

To get all data in a btx file, you can use a SQL+ statement like this:

select * from b_st.btx

It looks like a normal SQL statement except for the complete file name, which is searched for in the main path and search path. The file can also be represented by an absolute path.

You can convert the statement into a SPL using an add-in according to a special identifier. Select File >Import SQL(+) to open the add-in dialog box:

 

Enter the SQL+ statement into the add-in box:

Click OK to get the SPL statement:

 

A

B

1

=file("D:/files/txt/b_st.btx").cursor@b().fetch()

 

2

 

 

An empty row and an empty column are generated in case that the SPL statement needs manual optimization and debugging. Below is the result of executing the SPL statement:

The result is the same as a SQL query over the database table.

To get certain fields from the table in SQL+:

Convert the SQL+ to SPL:

 

A

B

1

=file("D:/files/txt/b_st.btx").cursor@b(STATEID,NAME,ABBR,CAPITAL).fetch()

 

2

 

 

SPL query result:

The result is the same as a SQL query over the database table.

To perform a join in SQL+:

An alternative of the above SQL+ statement is: select e.NAME, e.GENDER, s.ABBR from b_emp.btx e, b_st.btx s where e.STATE = s.NAME, which generates same result. Convert it to SPL:

 

A

B

1

=file("D:/files/txt/b_emp.btx").cursor@b(STATE,NAME,GENDER).fetch()

 

2

=file("D:/files/txt/b_st.btx").cursor@b(ABBR,NAME).fetch()

 

3

=join(A1:L,STATE;A2:R,NAME)

 

4

>$1=A3.new(L.STATE:e_STATE,L.NAME:e_NAME,L.GENDER:e_GENDER,R.ABBR:s_ABBR,R.NAME:s_NAME)

 

5

=$1.new(e_NAME:e_NAME,e_GENDER:e_GENDER,s_ABBR:s_ABBR)

 

6

 

 

You can optimize an auto-generated SPL script to make it more efficient - here, for example, by skipping A4’s creation of a temporary table sequence:

 

A

B

1

=file("D:/files/txt/b_emp.btx").cursor@b(STATE,NAME,GENDER).fetch()

 

2

=file("D:/files/txt/b_st.btx").cursor@b(ABBR,NAME).fetch()

 

3

=join(A1:L,STATE;A2:R,NAME)

 

4

=A3.new(L.NAME:e_NAME,L.GENDER:e_GENDER,R.ABBR:s_ABBR)

 

5

 

 

Here’s the result:

The result is similar to that of executing a SQL query over a database table:

 

A

1

=demo.query("select e.NAME, e.GENDER, s.ABBR from employee e join states s on e.STATE = s.NAME")

A1’s result:

Compared with the SPL query result converted from SQL+, this result set has different field names and record order.

6.8.2 Identifiers

SQL+ syntax has some unique features, such as using file name as the table name. Generally a file name includes the extension. In SQL, it will be a mess if you try to reference a field in a table name with an extension. For example, you can’t write a SQL query in the following way:

select b_emp.btx.NAME as Name, b_emp.btx.GENDER as Gender from b_emp.btx

SQL+ supports identifiers in commenting format to acquire more abilities. The above SQL statement can be rewritten as the following SQL+:

The /*+BTX*/ identifier marks b_emp as a btx file in the main directory and enables using b_emp as the table name. To convert the SQL+ statement to SPL:

 

A

B

1

=file("D:/files/txt/b_emp.btx").cursor@b(NAME,GENDER).fetch()

 

2

=A1.new(NAME:Name,GENDER:Gender)

 

3

 

 

Below is A2’s result:

An identifier can define a more complicated operation:

In the above SQL+ statement, /*var(v)*/ defines that result of the enclosed expression is used as a variable in the would-be SPL. Below is the corresponding SPL statement:

 

A

B

1

=file("D:/files/txt/b_st.btx").cursor@b(NAME).fetch()

 

2

=file("F:/files/txt/b_emp.btx").cursor@b(EID,STATE).fetch()

 

3

=A2.groups(STATE:group_1;count(EID):count_1)

 

4

>v=A3.new(count_1:$17,group_1:STATE)

 

5

>v=if(ift(v),v,create($17,STATE).insert@r(0:v))

 

6

>v=v.keys(STATE).index()

 

7

>$18=A1.derive(v.find(~.NAME).(#1):$17)

 

8

=$18.new(NAME:State,$17: count)

 

9

 

 

Result of executing SPL:

There are many other SQL+ identifiers. SQL+ is also equipped with some unique syntax, such as first and median. You can refer to Function Reference to learn more about SQL+.

6.8.3 Execution

There are two work modes for SQL+: Translate and Execute. Examples in the above section use Translate mode. You can run a SQL+ query to get result via JDBC or ODBC call.

A JDBC call of SQL+ is similar to an execution in JDBC. For example:

public void testDataServer(){

Connection con = null;

java.sql.PreparedStatement st;

try{

//Establish a connection

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

//Get connection according to the specified url; the option sqlfirst=plus is needed.

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

st =con.prepareCall("call createTable1()");

//Eexecute the SQL+ query to get result set

ResultSet rs1 = st.executeQuery("select b_emp.NAME as Name, b_emp.GENDER as Gender from b_emp/*+BTX*/ where b_emp.ID < 6");

//Further process the result set and then output field names and detailed data

ResultSetMetaData rsmd = rs.getMetaData();

int colCount = rsmd.getColumnCount();

for ( int c = 1; c <= colCount;c++) {

String title = rsmd.getColumnName(c);

if ( c > 1 ) {

System.out.print("\t");

}

else {

System.out.print("\n");

}

System.out.print(title);

}

while (rs.next()) {

for (int c = 1; c<= colCount; c++) {

if ( c > 1 ) {

System.out.print("\t");

}

else {

System.out.print("\n");

}

Object o = rs.getObject(c);

System.out.print(o.toString());

}

}

}

catch(Exception e){

System.out.println(e);

}

finally{

//Close the connection

if (con!=null) {

try {

con.close();

}

catch(Exception e) {

System.out.println(e);

}

}

}

}

Remember to add sqlfirst=plus in the connecting string to directly execute the query, instead of translating it to SPL, when you call a SQL+ query in JDBC. Make sure a bin file or a composite table file used in a SQL+ query is put in the main directory in advance. Below is the result of executing the above code:

To call a SQL+ query in ODBC, you need to configure SqlFirst property value as plus in ODBC data soruce configuration, as shown below: