$(db)sql;…

Read(391) 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 no data source is specified in the previous statement, then use any 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

Example:

Ø  SELECT statement

 

A

 

1

$select * from EMPLOYEE

Error report appears because parameter (db) is absent, no data source is specified in the previous statement, and there isn’t a currently connected data source. Error:"The database connection factory is missing"

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"

 

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

Ø  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 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, 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. If there are parameters in the statement, 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);

  }

  }

  }

  }

$()sql;…

Description:

Query an external table using simple SQL.

Syntax:

$()sql; 

Note:

The function performs a query over an external data file. As a normal table sequence, the external file can be directly queried by using “file name.extension” as its name, which makes it the external table. The query syntax, which we call simple SQL, is similar to the SQL syntax of the database query.

Support external data files in the format of txt, csv, xlsx, xls, btx (segmented binary files). An external data table file has headers by default. Use a relative path or an absolute path to access an external data table file; the relative path is relative to esProc main directory.

Refer to db.query(sql) function to learn more about the syntax of simple SQL.

Parameters:

sql

A “simple SQL” statement, like select * from filename.txt

A parameter value in the simple SQL statement

 

 

 Note: the parentheses in the $()sql;… function can be omitted.

Return value:

   Table sequence

Example:

 

A

 

1

$()select * from Persons.txt

Retreive records from the external table Persons.txt and return result as a table sequence

2

$select * from D:/Orders.txt

Make the query via the absolute path and return result as a table sequence

3

$select * from Persons.csv where Id_P=? or Id_P>?;2,2

Get records where Id_P is equal to or greater than 2 from the external table

4

$select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P

Perform a query through a join

5

$select  distinct(Id_P)  from Orders.btx

Get records where Id_P value is unique from the external table