$(db)sql;…

Read(315) Label: dbsql,

Here’s how to use $(db)sql;… function.

$(db)sql;…

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 database connection object. If omitting (db), then use the data source specified by the previous statement. If there isn’t an existing data source for uset, then use any one of the currently connected data sources.

Parameters:

sql

A SQL statement in the form of select * from table, for example; here the SQL statement must be a select/insert/delete/update statement.

(db)

Data source name

Argument value passed to the SQL statement

Return value:

A table sequence composed of results of executing the SQL statement.

Example:

Ø  SELECT statement

 

A

 

1

$select * from EMPLOYEE

Error:" Missing the database connection factory "

2

$(demo)select * from EMPLOYEE where EID=?;1

Find the employees whose EID is 1

3

$select * from EMPLOYEE where EID in (?) or GENDER=?;[1,3,5,7],"M"

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"

 

2

[51,52,53,54]

 

3

for A2

 

 

4

 

$insert into STATECAPITAL (STATEID) values(?);A3

 

Ø  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'

 

Ø  UPDATE statement

 

A

 

1

$(demo)update EMPLOYEE set NAME =?, GENDER=?

where EID =?;"testnew","M",100

 

2

$update EMPLOYEE set NAME ='Peter' where EID =10

 

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 functions:

db.query()

$(db)sql;…

Description:

Through esProc JDBC, execute the specified SQL statement in the database and return the result set.

Syntax:

$(db)sql;

Note:

In the specified database db, the function executes the specified SQL statement, with the parameters represented by … after semicolons, 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. There aren’t necessarily parameters used in the statement; but if there are, they always start with arg.

In JDBC, a string starting with select or with will be interpreted as a simple SQL statement for execution. If the string is followed by a pair of parentheses, it will be interpreted as a dfx file name. For instance, selectTab() represents a dfx file name.

Parameters:

sql

A SQL statement, like select * from table; it should be any of the select/insert/delete/update statements

(db)

Datasource name

SQL parameter’s value

Return value:

A set

Example:

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);

        }

      }

    }

  }