Here’s how to use $(db)sql;… function.
Description:
Execute the specified SQL statement on the data source and return the result.
Syntax:
$(db)sql;…
Note:
On the data source db, execute the specified SQL statement sql and return the execution result. db is the data source name. If omitting (db), then use the data source specified by the previous statement. If no data source is specified in the previous statement, then use any of the currently connected data sources.
You can use simple SQL in this statement.
Parameter:
|
sql |
A SQL statement in the form of select * from table, for example; the SQL statement must be one of select/insert/delete/update statements |
|
(db) |
Data source name |
|
… |
Argument value passed to the SQL statement |
Return value:
Table sequence
Example:
SELECT statement:
|
|
A |
|
|
1 |
$select * from EMPLOYEE |
No data source is specified in the previous statement since parameter (db) is absent, and error report appears if there isn’t a currently connected data source. |
|
2 |
$(demo)select * from EMPLOYEE where EID=?;1 |
Find the employees whose EID is 1 from the demo data source. |
|
3 |
$select * from EMPLOYEE where EID in (?) or GENDER=?;[1,3,5,7],"M" |
Since parameter (db) is absent, the program uses the demo data source defined in the previous statement to find the employees whose EID is [1,3,5,7] and gender is M. |
INSERT statement:
|
|
A |
B |
|
|
1 |
$(demo)insert into EMPLOYEE (EID, NAME) values(?,?);100,"test" |
|
Insert statement. |
|
2 |
[51,52,53,54] |
|
|
|
3 |
for A2 |
|
|
|
4 |
|
$insert into STATECAPITAL (STATEID) values(?);A3 |
Since parameter (db) is absent, the program uses the demo data source defined in the previous statement; pass members of A2’s sequence to B4’s statement as parameters by loop. |
DELETE statement:
|
|
A |
|
|
1 |
$(demo)delete from EMPLOYEE where EID =? or EID=?;100,101 |
|
|
2 |
$delete from EMPLOYEE where EID in(?);[1,5,7,9] |
|
|
3 |
$delete from EMPLOYEE where NAME ='Rebecca' |
Since parameter (db) is absent and no data source is specified in the previous statement, the program uses one of the currently connected data sources. |
UPDATE statement:
|
|
A |
|
|
1 |
$(demo)update EMPLOYEE set NAME =?, GENDER=? where EID =?;"testnew","M",100 |
|
|
2 |
$update EMPLOYEE set NAME ='Peter' where EID =10 |
Since parameter (db) is absent, the program uses the data source defined in the previous statement to modify the NAME of the employee whose EID is 10 to Peter. |
|
3 |
$(sql)update Family set Name='Rose' where Eid=?;2 |
Update the name of a record of Family whose Eid is 2 by connecting to a data source named sql. |
Related function:
Description:
Through esProc JDBC, execute a SQL statement in the data source and return the result set.
Syntax:
$(db)sql;…
Note:
In the specified database db, the function executes the specified SQL statement, and returns the result set once executed. Use st.executeQuery() for the execution and return the result set. Make sure the database db must be connected.
In JDBC, a string starting with $select or $with will be interpreted as a simple SQL statement for execution.
Parameter:
|
sql |
A SQL statement in the form of select * from table; it should be one of select/insert/delete/update statements |
|
(db) |
Data source name; will be interpreted as a simple SQL statement when omitted |
|
… |
SQL parameter’s value |
Return value:
Set
Example:
Execute SQL statement in the database:
public void testDataServer() {
Connection con = null;
java.sql.Statement st;
try{
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st=con.createStatement();
// Query demo database student table to find the data of students who are older than 16
ResultSet set = st.executeQuery("$(demo)select * from STUDENTS where AGE>?;16");
printRs(set);
}
catch(Exception e){
System.out.println(e);
}
finally{
// Close the connection
if (con!=null) {
try {
con.close();
}
catch(Exception e) {
System.out.println(e);
}
}
}
}
Execute a simple SQL statement to query a data file:
public void testDataServer(){
Connection con = null;
java.sql.Statement st;
try{
// Establish data source connection
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st =con.createStatement();
//Use executeQuery method to execute the simple SQL statement and query data in cities.txt
ResultSet set = st.executeQuery ("$select * from cities.txt ");
ResultSetMetaData meta = set.getMetaData();
while (set.next()) {
for(int i=0; i<meta.getColumnCount(); i++){
System.out.print(set.getObject(i+1) + "\t");
}
System.out.println();
}
}
catch(Exception e){
System.out.println(e);
}
finally{
// Close data source connection
if (con!=null) {
try {
con.close();
}
catch(Exception e) {
System.out.println(e);
}
}
}