3. No result or multiple results
It is allowed to return no result during calling a cellset file. Take the following cellset file – outputData1.dfx – as an example:
| A | |
| 1 | $(demo) select * from CITIES |
| 2 | =A1.select(Arg.pos(left(NAME,1))>0) |
| 3 | =file(“Cities.txt”) |
| 4 | >A3.export@t(A2) |
The file uses Arg, a parameter of string sequence type:
A1 directly calls the demo database to query data, instead of establishing a connection using connect statement. In this case, the property of demo datasource’s autoConnect in config.xml’s database configuration should be true:
<property name=”autoConnect” value=”true”/>
Please refer to esProc Integration & Application: Deploying JDBC for detailed configuration. To call the cellset in Java, the code should be as follows:
// call the stored procedure; outputData1 is the name of dfx file
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call outputData1(?)”);
// set the parameters
com.raqsoft.dm.Sequence seq = new com.raqsoft.dm.Sequence();
seq.add(“A”);
seq.add(“B”);
seq.add(“C”);
st.setObject(1, seq);
// execute the stored procedure
boolean hasResult = st.execute();
// cannot get the result set as there is no return result; hasResult is false
Note: The cellset file uses a parameter of sequence type, which can only be set by generating a Sequence object. As with this example, a sequence parameter [A,B,C] is set for writing information of cities whose initials are A, B and C to the file Cities.txt. If no path has been specified for storing the output file in cellset, store it in the main path, which is specified by config.xml’s <mainPath/> node. Data of the file are as follows:
It is probably that a cellset file returns multiple result sets, like the following cellset file – createTable3.dfx:
| A | B | |
| 1 | $(demo) select EID, NAME+’ ‘+SURNAME FULLNAME, GENDER, STATE, BIRTHDAY from EMPLOYEE | |
| 2 | =A1.select(STATE == state && age(BIRTHDAY)<age) | |
| 3 | =A2.select(GENDER==”F”) | =A2\A3 |
| 4 | return A3 | return B3 |
This cellset returns the data of employees who are younger than the specified age and who, at the same time, come from the specified state as two table sequences according to genders respectively. The cellset uses two parameters, respectively specifying the employees’ oldest age and the state from which the employees come from:
This cellset file will return multiple result sets when executed.
// call the stored procedure; createTable3 is the name of dfx file and ? represents the parameters
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call createTable3(?,?)”);
// set the parameters in order
st.setObject(1, 30);
st.setObject(2, “California”);
// execute the stored procedure
boolean hasResult = st.execute();
// get the multiple result sets and print them out
while (hasResult) {
ResultSet rs = st.getResultSet();
// print out the results
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());
}
}
System.out.println();
// check if there are other return results
hasResult = st.getMoreResults();
}
When the code is executed, data of the female and male employees who are younger than 30 and who come from state of California will be fetched respectively. The printed out result is as follows:
In the cellset file – createTable3.dfx, a single statement – result A3,B3 – returns two record sequences. The case in Java is the same as here.
4. Execute statements directly
Besides calling a cellset file, a Java program also uses esProc to execute statements directly. Actually, serving as a call statement, the invocation can be directly executed. Let’s look at in detail how other statements can be executed. For example:
// execute the statement directly and return the result set
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
ResultSet rs1 = st.executeQuery(“=age(date(\”1/1/1990\”))”);
ResultSet rs2 = st.executeQuery(“=5.(~*~)”);
Here executeQuery is used to directly execute the esProc expression starting with = and return the result as a result set. Note that an escape character needs to precede the double quotation marks in the expression. Use the similar code to print out the result sets as follows:
Notice that if the return result is a sequence, JDBC will return a one-column-and-multiple-row result set. Or another method can be used to call the statement:
// first judge if there exists a result set; get it if there is one
boolean hasResult1 = st.execute(“=age(date(\”1/1/1990\”))”);
ResultSet rs1,rs2;
if (hasResult1) {
rs1 = st.getResultSet();
}
boolean hasResult2 = st.execute(“=5.(~*~)”);
if (hasResult2) {
rs2 = st.getResultSet();
}
Based on the statement being executed, the execute function will return a value to make clear whether there is a result set. The effect of the corresponding code is the same as the previous one.
If parameters are needed in the statement, use the operator – (x1, x2,…) to compute the expression successively and return the last result. For example:
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
ResultSet rs1 = st.executeQuery(“=(pi=3.14,r=4,r*r*pi)”);
The above code computes the area of a circle. The output result set is as follows:
The data set query statement starting with $ can also be used in esProc JDBC. For example:
// execute the data set query statement
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
ResultSet rs1 = st.executeQuery(“$(demo) select * from CITIES where POPULATION > 2000000”);
Note that demo, the data set used here, needs to be configured as automatically connected. The result is as follows:
Use the file name to call a cellset file so that call function can be omitted. For example:
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
boolean hasResult1 = st.execute(“createTable3 30,\”California\””);
The cellset file – createTable3.dfx will be called during the execution. If parameters need to be set for the file, they should be fixed in the statement and separated from the file name with a space. The above statement also returns multiple result sets, which are the same as that got using the other method in the above:
You can also execute a piece of code written in a cellset with multiple lines and columns in JDBC. The execution requires composing the code into a string, with lines separated by \n and columns of the same line separated by \t. For example:
ResultSet rs = st.executeQuery(“=[1,1]\nfor 10\t>A1=A1|(A1.m(-1)+A1.m(-2))\nresult A1″);”);
The execution of this string is equivalent to computing the following cellset:
| A | B | |
| 1 | =[1,1] | |
| 2 | for 10 | >A1=A1|(A1.m(-1)+A1.m(-2)) |
| 3 | return A1 |
Output the ResultSet got from the execution:
