MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.

I'm not curious

Database Operations with esProc

Published on 13 October 14
esProc can retrieve data from databases, write data to them and call databasesâ stored procedures. Based on the three basic operations, esProc is well suited to many tasks relating to databases.

1. The process of data analysis and task presentation is:
a) Retrieve data from a database through SQL or stored procedures.
b) Get data from other sources (other databases, texts, hdfs, nosql databases, http data sources, json data sources, etc).
c) Process heterogeneous data uniformly.
d) Provide data for application programs or present data with report forms.

2. Tasks processed in batches that are similar to ETL
The process is similar to task analysis and presentation. Their difference is that data of the last operation is not used for presentation, but is written to other databases or other data sources.

3. Modify the current database in batches
One way is to retrieve data from the current database, process them and write them back to the database; the other is to directly process data of the database through SQL or stored procedures.

Now letâs look at in detail some examples of the three basic operations.
Aï¼Retrieve data from databases.
Database Operations with esProc - Image 1
In the above figure, cell A1 has connected to a hsql database named demo. Cell A2 uses SQL statements to query table employee, which is stored in this cell, a variable, as esProcâs table sequence; arg1 is a parameter from outside. Cell A3 closes database connection. Cell A4 returns query results outward. In order to make it easier for programmers to write SQL statements, table name and field name of database demo are displayed in the red box in bottom right corner of esProcâs integrated development environment.

Bï¼Write data to databases.
esProc can conveniently execute operations of add, delete and update, the simplest code is:
Database Operations with esProc - Image 2
In the above table, insert, update and delete are respectively executed from A2 to A4. Execution of each SQL will be submitted automatically. Note that:
1. It is too frequent access to a database that three SQL statements are submitted three times.

2. There exists no transaction relation between the three SQL statements. So, if the execution of one SQL statement fails, the previous SQL statement remains unaffected.

esProc can update in batches by directly using table sequences. For example: import studentsâ information from students.txt to update table students1 in the database. Since there are a lot of records to be modified, submitting transactions in batches is more reasonable.
Database Operations with esProc - Image 3
A1ï¼Define a file object in which studentsâ information is saved.
A2ï¼Import file content.
A3ï¼Use studentsâ information in A2 to update table students1 in batches. Here submitting SQL in batches can avoid accessing the database too frequently. Meanwhile, this can ensure data consistency, that is, simultaneous success or fail of writing the whole batch of data to the database.

esProc can aslo deal with the complete database transaction consisting of multiple SQL statements. For example, weâll add a new student, the studentâs id should be modified to 9 after data are inserted. In order to ensure data consistency, submission must be executed after the insertion and modification are proved to be successful. Otherwise rollback should be executed.
Database Operations with esProc - Image 4
A1ï¼Connect to the database. Note that connect function has used @e option and the subsequent code will return error message when something wrong happens. If the option is not used, the database will terminate esProc program immediately when errors occur.

A2ï¼Execute the insert SQL statement. Note that execute function uses @k option, meaning the transaction will not be submitted automatically after it is executed. If the option is not used, the insert SQL statement will be submitted immediately.

A3ï¼Get the result of last operation in the database, i.e., the insert statement. If err variable is zero, the execution is successful; otherwise, err is the error code.

A4ï¼Judging whether err variable, the execution result, is zero. If the answer is yes, the last operation of the insert statement is successful and modification in B4 can be executed.

C4ï¼Get execution result of update SQL.

A5ï¼Make judgment over variable err. If it is zero, submit the database; otherwise execute rollback.

A6ï¼Close database connection.

Cï¼Call stored procedures
For stored procedures that donât return parameters, esProcâs method of calling them is simple:
Database Operations with esProc - Image 5
A1ï¼Connect the database.

A2ï¼Call the stored procedure, value of output parameter is 4.

esProc call stored procedures with result sets in this way:
Database Operations with esProc - Image 6
Cell A2 uses proc function to call the stored procedure:orac.proc("{call proAA(?,?)}",:101:"o":a,:101:"o":b). It returns two result sets (table sequences) to form a sequence, i.e., a set of table sequence, which assigns value to A1. The following is to explain proc functionâs input parameters one by one.

1ï¼ SQL strings
"{call proAA(?,?)}" contains name of the stored procedure to be called, the question marks represent SQLâs parameters.

2ï¼ Output parameter 1
:101:"o":a defines an output parameter in which 101 represents that its data type is cursor ( for other types, please see appendix) and âoâ represents that it is an output parameter. a defines a variable by which returned results can be referenced.

3ï¼ Output parameter 2
:101:"o":b defines an output parameter in which 101 represents that that its data type is cursor and âoâ represents that it is an output parameter. b defines a variable by which returned results can be referenced.
Cell A3 returns cell A2âs first table sequence (table empâs result set).
Cell A4 and A5 use output variables a and b respectively in A2 to get the execution results corresponding to the stored procedure. a corresponds to data of table emp and assigns value to A3; b corresponds to data of table test and assigns value to A4.

Appendix: Definition of Parameter Type
Values of type are:
public final static byte DT_DEFAULT = (byte) 0; // by default, identify automatically
public final static byte DT_INT = (byte) 1;
public final static byte DT_LONG = (byte) 2;
public final static byte DT_SHORT = (byte) 3;
public final static byte DT_BIGINT = (byte) 4;
public final static byte DT_FLOAT = (byte) 5;
public final static byte DT_DOUBLE = (byte) 6;
public final static byte DT_DECIMAL = (byte) 7;
public final static byte DT_DATE = (byte) 8;
public final static byte DT_TIME = (byte) 9;
public final static byte DT_DATETIME = (byte) 10;
public final static byte DT_STRING = (byte) 11;
public final static byte DT_BOOLEAN = (byte) 12;

public final static byte DT_INT_ARR = (byte) 51;
public final static byte DT_LONG_ARR = (byte) 52;
Publicfinal static byte DT_SHORT_ARR = (byte) 53;
public final static byte DT_BIGINT_ARR = (byte) 54;
public final static byte DT_FLOAT_ARR = (byte) 55;
public final static byte DT_DOUBLE_ARR = (byte) 56;
public final static byte DT_DECIMAL_ARR = (byte) 57;

public final static byte DT_DATE_ARR = (byte) 58;
public final static byte DT_TIME_ARR = (byte) 59;
public final static byte DT_DATETIME_ARR = (byte) 60;
public final static byte DT_STRING_ARR = (byte) 61;
public final static byte DT_BYTE_ARR = (byte) 62;
public final static byte DT_CURSOR = (byte) 101;
public final static byte DT_AUTOINCREMENT = (byte) 102;
This blog is listed under Development & Implementations and Data & Information Management Community

Related Posts:
Post a Comment

Please notify me the replies via email.

  • We hope the conversations that take place on will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url