Learning from Scripting Samples

To write your own scripts to execute EMSE SQL statements in third-party databases, refer to the following samples.

Sample 1

Create a table in the customer database. The first column is for the name of a person, the second column is for the age.

var sql = "create table person("
 + "name varchar2(50),"
 + "age int "
+ ")";
aa.util.update("customer", sql, null)

Sample 2

Set the person’s name as “Thomas” and his age as 18.

var parmArray = new Array();
parmArray[0] = "Thomas";
parmArray[1] = 18;
var sql = "Insert into person(name, age) values(?,?)";
aa.util.update("customer", sql, parmArray)

Sample 3

Get the person’s name and age from the customer database. If Civic Platform gets the person’s name and age successfully, print the name and age; otherwise, print “Failed to get records from customer database."

var sql = "Select name, age from person";
var selectResult = aa.util.select("customer", sql, null);
if(selectResult.getSuccess())
{
 var selectOutput = selectResult.getOutput();
 for(var i=0; i<selectOutput.size(); i++)
 {
   var eachRecord = selectOutput.get(i);
   aa.print(eachRecord.get(“name”));
   aa.print(eachRecord.get(“age”));
 }
}
else
{
 aa.print(“Failed to get records from customer database!”)
}

Sample 4

Insert two records to the customer database in batch as shown in the following table.

Name Age
Thomas 18
Li 33
var arrayList = aa.util.newArrayList();
var parmArray = new Array();
parmArray[0] = "Thomas";
parmArray[1] = 18;
var parmArray1 = new Array();
parmArray1[0] = "Li";
parmArray1[1] = 33;
arrayList.add((aa.util.toObjectArray(parmArray)).getOutput());
arrayList.add((aa.util.toObjectArray(parmArray1)).getOutput());
var sql = "Insert into person(name, person) values(?,?)";
aa.util.batchUpdate("customer", sql, arrayList)