ADVANCED MOCA AND GROOVY
There are some more advanced features of MOCA.
• We can check for null values or use the #onstack to see if a variable
is on the MOCA context stack.
• onstack Check
if (@adr_id#onstack != 0)
{
publish data where adr = 'They gave us a address ID!'
}
Checking for NULL values continued...
• NULL Check
if (@adr_id != '' )
{
publish data where adr = 'They gave us a address ID!'
}
If (@adr_id is not null )
{
publish data where adr = 'They gave us a address ID!'
}
Joining Data using publish data.
• The & can be used to join data with publish data.
• Similar to using & to join result sets when executing two commands.
• In the example below we are using publish data to create a two row
result set.
publish data where a = 1 and b = 2 and c = 5
&
publish data where a = 2
This is what we get on context stack:
a b c
1 2 3
2
Being careful with context.
• Variables with the same name will over write previous variables.
• The rows published to the context control iteration downstream.
• Each of these selects always return 2 rows:
[select a, b from table_1]
|
[select b, c from table_2 where a=@a]
|
[select d, e from table_3 where c=@c]
> After line 1, you have 2 rows on your context
• Line 3 will execute 2 times.
> After line 3, you have 2 * 2 or 4 rows on your context
• Line 5 will execute 4 times!
> After line 5, you have 2 * 2 * 2 or 8 rows on your context.
> On line 3, the variable b is being overwritten from the previous context.
> On line 3, the variable c is being added to the previous context.
Clearing context.
• The PIPE | passes context down to the next command or SQL
statement.
• The semi-colon ; will clear the current context.
• In the following example, only one row containing a with a value of
'b' will be on the context. All the rows from the first select are gone.
• Useful when we are done doing one logical piece of work in a
MOCA command and are ready to do something different.
[select * from ord]
;
publish data where a = 'b'
Brackets and context.
• Brackets create a child context and closing a bracket closes the
child context
• The LAST results of the child context are placed on the stack of the
parent context.
[select ordnum from ord ] catch (-1403)
|
if (@? = 0)
{
[select pckqty from ord_line where ordnum = @ordnum]
|
publish data where pckqty_new = @pckqty + 10
}
|
publish data
where ordnum = @ordnum
and pckqty = @pckqty This is not on the stack and will be NULL
and pckqty_new = @pckqty_new This is on the stack
Joining result sets.
• There is another method to join result sets.
• First use the >> operator to pack a result set into a variable.
• Then use the publish data combination command.
• We can join the results of two or more commands or selects.
list users where usr_id= 'super' >> res1
|
list users where usr_id= 'train' >> res2
|
list users where usr_id= 'operator' >> res3
|
publish data combination where res1 = @res1 and res2 = @res2 and res3 =
@res3
Returning specific results from a result set.
• The publish rows command lets us return a specific set of rows.
• Start is a '0' based index, so 0 gives us the first row.
• The first example below returns a result set of 4 rows starting with
the 3rd row.
list users >> res1
|
publish rows where res = @res1 and rows = 4 and start = 2
Returning specific results from a result set.
• The publish top rows command lets us return the top X number of
rows.
• The example below returns a result set of the first 3 rows.
list users >> res1
|
publish top rows where res = @res1 and rows = 3
Returning specific results from a result set.
• The convert column results to string command will put all the
values for 1 column in the result set into a delimited string.
• In the example below, all the usr_id values in the result set returned
by list users will be packed into a single string with values delimited
by commas.
list users >> res1
|
convert column results to string where resultset = @res1 and colnam =
'usr_id' and separator = ','
result_set
user1,user2,user3,user4,user5
Returning specific results from a result set.
• The convert list command parse the values from a string and
convert them into a result set.
publish data where test_string = 'a,b,c'
|
convert list
where string = @test_string
and type = 'L'
retstr count
a 1
b 2
c 3
Returning specific results from a result set.
• The convert column to multicolumn command will split the values
of 1 column in a result set to multi columns.
• In the example below, all the usr_id values in the result set returned
by list users will be split into 3 columns.
list users >> res1
|
convert column to multicolumn where resultset = @res1 and colnam =
'usr_id' and column_count = 3
usr_id0 usr_id1 usr_id2
user1 user3 user5
user2 user4 user6
Session variables.
• What if we need to lose context for some reason and we need to
save a value that we need later so we can return it?
• The save session variable and get session variable commands
let us store and retrieve variables for the life of the command.
• Values saved using save session variable can be retrieved even if
the context stack is cleared.
save session variable where name = rows_processed and value =
'100'
|
get session variable where name = 'rows_processed'
|
publish data where rows_processed = @value
Session variables continued….
[select count(*) bad_rows from ord_line where ordqty < 0]
|
save session variable where name = 'bad_rows' and value = @bad_rows
|
{
[select ordnum, ordlin, ordsln from ord_line where ordqty < 0]
|
fix var bad orders
; This dumps our context and commits
noop noop does nothing and resets to 1 row on context
}
|
get session variable where name = 'bad_rows'
|
publish data where bad_rows = @value
Setting the return code.
• We can set the return code to override the last return code, clear
the context, and do a rollback.
• The MOCA architecture allows us to add an associated return code
to the MLS Catalog to associate it with an error message
• Or we can specify an error message.
• The first example sets the return code to 1000, which has an
associated error message in the MLS Catalog as mls_cat_id:
"err1000".
• Try running this from server command operations or WinMSQL.
set return status where status = 1000
Setting the return code continued.
• This second example sets the status and the message.
set return status where status = 99999 and message = 'Bad Error'
Running an explain plan on a query.
• This command will help analyze queries without running an explain
plan specific to the database vendor.
• Run this example below.
• Can you see how large estimaterows is? Why?
sl_explain query where sql = " select * from invdtl, invsub, invlod "
|
publish data where estimaterows = @estimaterows
Filter Data command.
• This command will pull back previous values in context.
• Run this example below.
• Notice how we pull a off the previous context?
publish data where a=1
|
publish data where b=2
|
filter data where moca_filter_level = 2 and c = 3
a c
1 3
Hiding variables on the context stack.
• The hide stack variable command will "hide" a variable from
context.
• Run this example below.
• Notice how wh_id is null in the result set because we hid the
variable.
publish data where wh_id='WMD1' and bldg_id = 'B1'
|
hide stack variable where name = 'wh_id'
|
publish data where wh_id = @wh_id and bldg_id = @ bldg_id
wh_id bldg_id
B1
Noop.
• Does nothing and returns empty result set with no error.
• As shown in a previous example, we can use noop to take a result
set with > 1 rows and collapse it to 0 rows.
• This way we avoid multiple iterations down the line if we do not
need data from previous commands.
[select * from aremst] returns 50rows
|
noop empties result set
|
[select * from ord] this is run 1 time, instead of 50
Introduction to Groovy – What is Groovy.
• Open source scripting language.
• Runs under the Java JVM.
• Can be invoked in Local Syntax MOCA commands.
• Can instantiate Java Classes and is similar to Java in syntax.
Introduction to Groovy – Groovy pros and cons.
• Pros
> No compiling is required and code can be embedded in Local
Syntax.
> Uses existing Java class libraries and Java Syntax.
> Provides more programming control than Local Syntax with
while-do loops, Java types, Java containers, etc.
> Uses standard Java syntax which is known world-wide.
• Cons
> Requires run-time compile and can reduce performance
compared to Local Syntax.
> Groovy Syntax is more complex than Local Syntax and can
require more coding to do SQL Statements
Introduction to Groovy – Groovy with local syntax.
• Groovy has access to anything published to context.
• Groovy is invoked by starting a script with [[ and finishing it with ]].
publish data where usr_id = 'SUPER'
|
[[
import com.redprairie.moca.*; import the MOCA java classes
res1 = new SimpleResults(); this class is a result set
res1 = moca.executeInline ("list users where usr_id = '" + usr_id + "' ");
while (res1.next()) loop through result set masking password
{
res1.setStringValue("usr_pswd", "<PASSWORD>");
}
res1; result set is published to context
]]
Introduction to Groovy – Groovy with local syntax.
• Groovy rules for publishing data.
• Only variables without a "def" or a Java type will be published to the
context stack.
[[
name = "This is name";
def name2 = "this won't show up";
_name3 = "this won't either";
String name4 = "also missing";
]]
Name
This is name
Introduction to Groovy – Groovy with local syntax.
• Groovy rules for publishing data - continued.
• Result Sets should always be LAST if you want that to be published
to the stack.
• A result set at the end will override all other values.
[[
import com.redprairie.moca.*;
res1 = new SimpleResults();
res1 = moca.executeInline ("list users ");
name = "This is name"; This will NOT be published
res1; This result set is published
]]
Introduction to Groovy – variables in Context.
• When calling commands or SQL to pass stack variables.
• We can either code variables Java style and have Groovy replace it.
• Or we can code varables in the command with Local Syntax style
reference.
• First example – trknum is already in context or it will be NULL.
Variable passed Local Syntax Style
[[
import com.redprairie.moca.*;
res = new SimpleResults();
// usr_id must be in context, or it will be null
cmd = " [select * from users_view where usr_id = @usr_id";
res = moca.executeInline(cmd);
res;
]]
Introduction to Groovy – variables in Context.
• When calling commands or SQL to pass stack variables.
• We can either code variables Java style and have Groovy replace it.
• Or we can code variables in the command with Local Syntax style
reference.
• Second example, variable replacement done by Groovy.
Variable passed Java style
[[
import com.redprairie.moca.*;
res = new SimpleResults();
// usr_id can be local or context
cmd = "[select * from users_view where usr_id = '" + usr_id + "' ";
res = moca.executeInline(cmd);
res;
]]
Introduction to Groovy – try/catch blocks.
• Example of MOCA command execution and try/catch block to get
the error message:
[[
vc_error_code = "0";
try {
res1 = moca.executeInline("list users");
}
catch (NotFoundException e) {
vc_error_code = String.valueOf(e.getErrorCode());
}
catch (MocaException em) {
vc_error_code = String.valueOf(em.getErrorCode());
}
vc_error_code;
]]
Introduction to Groovy – changing existing result sets.
• Sometimes we need to "edit" a result set and its easier to do it in
Groovy. Here we are removing rows from the result set:
command >> res2
|
[[
import com.redprairie.moca.*;
while ( res2.next() ) {
lodlvl = res2.getString("lodlvl")
if ( lodlvl.compareTo("L") == 0 ) {
moca.trace("found Load level pick - removing row")
res2.removeRow();
}
}
res2;
]]
Introduction to Groovy – changing existing result sets.
• We can also create a new result set on the context stack.
[[
import com.redprairie.moca.*;
SimpleResults res = new SimpleResults();
SimpleResults resout = new SimpleResults();
while ( res.next() ) {
resout.addColumn("prtnum", MocaType.STRING);
resout.addColumn("prtdsc", MocaType.STRING);
resout.addRow();
resout.setStringValue("prtnum", "12345678");
resout.setStringValue("prtdsc", "Test part 12345678");
resout;
]]
If we want to start looping through a result set again, the reset()
method will reset the result set for another loop.
[[
import com.redprairie.moca.*;
res = new SimpleResults();
res = moca.executeInline("list users");
while ( res.next() ) {
vc_temp = res.getString("usr_sts");
if ( vc_temp.compareTo("A") != 0 ) {
res.removeRow();
}
}
res.reset();
while ( res.next() ) { … }
]]
Groovy – MOCA data types.
• These are most common types – see MocaType class for more
details.
Method
MocaType.BINARY
MocaType.BOOLEAN
MocaType.DATETIME
MocaType.DOUBLE
MocaType.OBJECT
MocaType.RESULTS
MocaType.STRING
SimpleResults class details – row operations.
Method Description
public boolean next()Gets the next row in the result set.
public boolean hasNext()Is thera next row in the result set?
public void setRow(int rownum) Set the current row of the result set.
public void addRow() Add a row to the result set.
public void removeRow() Remove current row of the result set.
public void reset() Reset iterator to first row.
public int getRowCount() Get the number of rows in the result set.
SimpleResults class details – column operations.
Method. Description
public void addColumn(String columnName,
MocaType type)
Add a column to the result set.
public void addColumn(String columnName,
MocaType type, int length)
Add a column to the result set.
public void addColumn(String columnName,
MocaType type, int length, boolean nullable)
Add a column to the result set.
public MocaType getColumnType(int index)
Get the datat ype for column at index,
public MocaType get column type (String name)
Get the data type for column name.
public int getMaxLength(int index) Get max length for column at index.
public int getMaxLength(String name) Get max length for column at name.
SimpleResults class details - Column Operations Continued…
Method Description
public boolean isNullable(int index)
Is the column at index nullable?
public boolean isNullable(String name)
Is the column matching name nullable?
public String getColumnName(int index)
Get the name of column at index.
public int getColumnNumber(String name)
Get the column index of column matching name
public boolean containsColumn(String
name)
Is there a column matching name in the
result set?
public int getColumnCount()
Get the number of columns in the result set.
public void promoteColumn(int num,
MocaType type)
Changes the MOCA data type of column at
index. Invalid conversion results in null.
public void promoteColumn(String name,
MocaType type)
Changes the MOCA data type of columnmatching name. Invalid conversion results in null.
SimpleResults class details – Access methods.
Method Description
public String getString(int index)
Get the string value of the column at index.
public String getString(String name) Get the string value of the column matching name
public int getInt(int index)
Get the integer value of the column at index.
public int getInt(String name)
Get the integer value of the column matching name
public double getDouble(int index) Get the float double value of the column at index.
public double getDouble(String name)
Get the float double value of the column matching name
public boolean getBoolean(int index) Get the boolean value of the column at index.
public boolean getBoolean(String name)
Get the boolean value of the column matching name
public Date getDateTime(int index) Get the Date value of the column at index.
public Date getDateTime(String name)
Get the Date value of the column matching name
public Object getValue(int index)
Get the Object value of the column at index.
public Object getValue(String name) Get the Object value of the column matching name
Public void setBinaryValue(int num, byte[] value)
Set binary value of column at num.
public void setBinaryValue(String name, byte[] value)
Set binary value of column matching name.
public void setBooleanValue(int num,
boolean value)
Set boolean value of column at num.
public void setBooleanValue(String
name, boolean value)
Set boolean value of column matching name.
public void setDateValue(int num, Date value)
Set Date value of column at num.
public void setDateValue(String name,
Date value)
Set Date value of column matching name.
public void setDoubleValue(int num,
double value)
Set double value of column at num.
public void setDoubleValue(String name, double value)
Set double value of column matching name.
public void setIntValue(int num, int value)
Set integer value of column at num.
public void setIntValue(String name, int value)
Set integer value of column matching name.
public void setNull(int num)
Set column at num to NULL.
public void setNull(String name)
Set column matching name to NULL.
public void setStringValue(int num,
String value)
Set string value of column at num.
public void setStringValue(String name, String value)
Set string value of column matching name.
public void setValue(int num, Object
value)
Set Object value of column at num.
public void setValue(String name, Object value)
Set Object value of column at name.
Comments
Post a Comment