Wednesday, April 10, 2013

MCMD - How to flat out a result set?

Often when working with labels I see the need to flat down a result set.  This is necessary because each row in the result set will become a printed label.  But the label itself could need multiple rows from the query executed.  Hence the need to flatten down the result set from multiple rows into one row.

Assume my label will print up to 3 lines of information, and each row will contain wh_id, ordnum, prtnum, and ordqty.  For this to print within only 1 label we need to flatten out the result set.  Let's see the example.

My original query for data might look like this:

[select rownum idx, wh_id, ordnum, prtnum, ordqty 
   from ord_line where rownum < 4] >> res
|
if (rowcount(@res) > 3)
{
   set return status where status = 999999
}
|
publish data combination where res = @res

idx wh_id ordnum prtnum ordqty
1 WMD1 SMORDTEST02 D0C2AB8 1
2 WMD1 SMORDCABLE02 SOUCARR 8
3 WMD1 EDTEST0123 WOTRAUV 20



In order to use this data for the label, I need to flatten it out.

[select rownum idx, wh_id, ordnum, prtnum, ordqty 
   from ord_line where rownum < 4] >> res
|
if (rowcount(@res) > 3)
{
   set return status where status = 999999
}
|
{
    save session variable 
      where name = 'cmd'
        and value = ''
        || 'publish data'
        || ' where wh_id = ''' || @wh_id || ''''
    |
    publish data combination where res = @res
    |
    get session variable where name = 'cmd'
    |
    save session variable 
      where name = 'cmd'
        and value = @value
        || ' and ordnum_' || @idx || ' = ''' || @ordnum || ''''
        || ' and prtnum_' || @idx || ' = ''' || @prtnum || ''''
        || ' and ordqty_' || @idx || ' = ' || @ordqty 
    ;
    get session variable where name = 'cmd'        
} 
|
execute server command where command = @value


wh_id ordnum_1 prtnum_1 ordqty_1 ordnum_2 prtnum_2 ordqty_2 ordnum_3 prtnum_3 ordqty_3
WMD1 SMORDTEST02 D0C2AB8 1 SMORDCABLE02 SOUCARR 8 EDTEST0123 WOTRAUV 20



See the difference and how I am using "context" and "session variables" to achieve the functionality very easily.  There are other ways to flatten data and MOCA offers some "convert..." commands which can also help in other scenarios without re-inventing the wheel.

Monday, April 8, 2013

MOCA - Advance Syntax

In this post we will be covering MOCA local syntax advance operators and get a better understanding of how context works.  This will help you develop more robust mcmds and control the expected behavior of local syntax.  This post doesn't cover what MOCA and local syntax is which can be found in:  <MOCA 101>  for more information.

MOCA is RedPrairie proprietary server technology and uses a scripting language called local syntax.  The code pattern for this language is quite unique due to the nature of how code actions works.  The paradigm of thinking how to develop local syntax components differs from normal languages like: JAVA, C#, etc...  The problem with local syntax is that people only learn the basics of the language which is to pipe ( | ) one action to another, and disregard the other constructs to build correct programs.


Simple example command:
list orders where ordnum = 'ORD123'
|
list order lines where ordnum = @ordnum


Not understanding how context works and what really is happening can be very dangerous.  A badly coded component can either create performance issues or even worst it could execute over an unintended context.

Let's start with what is "Context"?  I define context as the current knowledge your program has and actions is going to execute over it.  For every action there is a current set of variables and values, and that's your current context.  As actions execute, the context changes.  The looping nature of MOCA local syntax means that if you are piping a result set into a command, each iteration (or record piped in) will be it's own context. 

But that's simple... and I think most developers understand it.  The trick (or advance) comes into how to control context in scenarios where the normal pipe ( | ) controller is not enough.  For this you need to understand the advance features of MOCA local syntax and how each construct affects context execution.

In this post we will cover the following control mechanisms:
  • & - (ampersand):  combines result sets.
  • >> - (redirection):  stores a current result set into a variable
  • {} - (curly-brackets):  creates new context.
  • ; - (semi-colon):  breaks current context.
  • @variable - variable operators


& - (ampersand):  combines result sets

Let's start easy...  The & (ampersand) is just a control operator for combining result sets.  This is very handy "union" operator for MOCA.  What needs to be guaranteed is the results sets for A & B have the same structure.  Meaning the fields and types are the equivalent plus they are in the same order.

list orders where ordnum = 'ORD123'
&
list orders where ordnum = 'ORD456'

wh_id client_id ordnum ordtyp
WMD1 ---- ORD123 CUSTOMER
WMD1 ---- ORD456 CUSTOMER


>> - (redirection):  stores a current result set into a variable

The redirection of a result set into a variable is a handy operator.  Some mcmds can process these type of complex variables and become useful for post-processing or combining results.

[select distinct ordnum
   from ord_line
  where prtnum = 'SOMEPART' 
    and prt_client_id = '----' 
    and wh_id = 'WMD1'
  order by ordnum] >> res
|
convert column results to string 
  where res = @res
    and colnam = 'ordnum'
    and separator = ','

result_string
ORD001,ORD002,ORD003



{} - (curly-brackets):  creates new context

The way to understand context is to compare it against the concept of "scope" in normal programming languages.  The current context is the set of variable and values your actions are executing upon.  This is why is so important to understand how context behaves.  Because is not just about the context you have for the current action, but also the context you publish out for the next actions.

The #1 rule you need to know about context is that only your last result will get published out ALWAYS.  All internal context will not survive and make it into an exterior context.  Think of this as each mcmd you execute runs within their own context and nothing you do inside of it gets published out except the last result. Also keep in mind that every time you open a new context {} (curly-brackets), all the current context will be piped into this new context.

The best way to show this is with examples...

First let's use a simple case where @a gets piped into a new context and the inner context has visibility at the values from it's exterior context.  Yet, the result of the inner context gets published out where @a has a value of a=1.

publish data where a = 1
|
{
    publish data where a=@a and b=2
}

a b
1 2

Now let's look at a case where inner context doesn't makes it out.  In this example you can see how @a gets overwritten and the value is a=2, not a=1.  Also did you see @b?  The value of @b is not published out because is not the last result of the inner context.

publish data where a = 1
|
{
    publish data where b = 2
    |
    publish data where a = 2
}
|
publish data where a = @a and b = @b

a b
2

Changing the case a little, you can see that @a is NOT overwritten and the value stays as a=1.  Also see how @b now gets publish out of the context as well.

publish data where a = 1
|
{
    publish data where a = 2
    |
    publish data where b = 2
}
|
publish data where a = @a and b = @b

a b
1 2

On an interesting note... when  you do an if (@clause) statement, the {} curly-brackets for the if statement causes a new context and only the last result gets published out.

publish data where a = 1
|
if (@a=1)
{
    publish data where a = 2
    |
    publish data where b = 2
}
|
publish data where a = @a and b = @b

a b
1 2

After you start using new context correctly in your code, you will realize the following advantages:
  • Code behavior will be more predictable and you can control better the (IN/OUT) values.
  • New context behaves as piping into a mcmd.  Meaning that you can develop a complex command in one long mcmd script, and then safely turn those contexts into mcmds themselves for better maintenance.
  • Actually you can control multiple actions over a common context.  See more below.


; - (semi-colon):  breaks current context

The ; (semi-colon) is the operator for "breaking" current local context.  When context gets "broken", all the variables above gets "trashed" so you can start with a new context.  You can imagine the following independent sequences:   CMD1;  CMD2; ETC;  All these actions execute independent from each other.

But that's not really the power of this operator. Instead this operator becomes very powerful when you need multiple actions over a common context.  Like always, examples are the best!

In the code below we get the orders to process from the the first command.  The processing needs to execute different actions over the published context of "get rush orders...".  As you see in the sub-sequence actions, we need to first notify the host and then print the label.


get rush orders for processing where wh_id = @wh_id
|
{
    notify host where ordnum = @ordnum
    ;
    print label where ordnum = @ordnum
}
|
publish data where ordnum = @ordnum


So why use ; (semi-colon) and not | (pipe)?  Well, because the results published of "notify host" could potentially change the expected behavior of "print label".  And really your code for "print label" needs to use the context of the "get rush orders".

Dissecting the code above, when you "break context" it really only breaks the current local context.  For that reason after "get rush orders..." we open a new context.  This new context will operate over the results piped in.  As execution flows in the inner context and you break it, the next action will start with the same context piped in.  Which in this case is the row(s) from "get rush orders...".

Other details to notice of this example are the following.  The last publish statement is there just to make obvious that breaking context only affect local context.  So the @ordnum variable publish from "get rush orders..." will still be available.  Another detail to be careful is the result set of "print label" does make it out of the local context since it is the last result.  For such case you probably want to add "; noop" or redirect the result to ">> trash".


@variable - variable operators

In MOCA we have special variable operators which manage how values get substituted.  I am just presenting them quick for completion of this post.  (Thanks Yang Ren @LinkedIn)
  • @variable:  normal variable
  • @+variable:  optional variable
  • @@variable:  environment variable
  • @%variable: like operator variable
  • @*:  pull all variables from context
  • @-variable: force substitution
  • ^aliasname:  variable value is "alias" as..

Below are examples where you can see how the operators work base on having the variable on context or not.  It shouldn't be that hard to follow, but ask if you have questions.

LocalSyntax with variables in context

publish data where ordnum = 'JON%' and exactorder = 'JONORD005'
|
[select * from ord 
  where 
    (ordnum = @ordnum
      or ordnum like @ordnum
      or @%ordnum
      or @+ordnum
      or @+ord.ordnum^exactorder
      or ordnum = @-ordnum
    )
    and wh_id = @@wh_id
    and rownum < 10
    and @*] catch(-1403)


LocalSyntax with variables in context - SUBSTITUTION

 select * from ord 
  where 
    (ordnum = 'JON%'
      or ordnum like 'JON%'
      or ordnum LIKE 'JON%'
      or ordnum = 'JON%'
      or ord.ordnum = 'JONORD005'
      or ordnum = 'JON%'
    )
    and wh_id = 'SBN01'
    and rownum < 10
    and 1=1



LocalSyntax NO context

[select * from ord 
  where 
    (ordnum = @ordnum
      or ordnum like @ordnum
      or @%ordnum
      or @+ordnum
      or @+ord.ordnum^exactorder
      or ordnum = @-ordnum
    )
    and wh_id = @@wh_id
    and rownum < 10
    and @*] catch(-1403)


LocalSyntax NO context - SUBSTITUTION

 select * from ord 
  where 
    (ordnum = NULL
      or ordnum like NULL
      or 1=1
      or 1=1
      or 1=1
      or ordnum = NULL
    )
    and wh_id = 'SBN01'
    and rownum < 10
    and 1=1
    

Tuesday, March 26, 2013

SONY VAIO - SVE14A390X Premium Plus - Review and Enhancements

First of all this is not an "expert review" and it will be totally bias.  Not that I am a fan boy of one brand or another, but I really dedicate myself to find what I want.

REVIEW

For this round I needed to find a laptop that fits all my needs like:

  • Power for development (lots of RAM, decent CPU, lots of memory)
  • Small and light so it can be with me almost everywhere (work, home, on the go...)
  • Touch screen and Windows 8 (to have latest technology changes)


In my wish list of laptops before I found the VAIO SVE14A390X the contenders were:  MacBook Pro - Retina Display and Asus Zenbook - UX51VZ.  But after considering issues I felt with those machines, not having touch screen for Windows 8, and the excessive price (at the moment $2500+), I finally hit the spot with the VAIO SVE14A.  The VAIO SVE14A390X Premium Plus is a customized version direct from Sony Store.  For flat $2000 I got a machine with very similar specs to the ones I was looking for and more...


These are some of the highlights:
  • 3rd gen Intel® Core™ i7-3632QM quad-core processor (2.20GHz / 3.20GHz with Turbo Boost)
  • 16GB RAM DDR3 @1600MHz
  • AMD Radeo HD 7670M (2GB) hybrid graphics
  • 1TB Hard disk @ 5400 RPM
  • Touch screen
  • Windows 8 Pro
  • 2 year SONY Protection plan (covers fall, spills, etc..)
  • Customization / additions included in the $2000 budget

My benchmark for performance was the Asus Zenbook - UX51VZ.  After switching the hard disk for SSD, I got to say the results are very close.  Also not having a RAID 0 SSD configuration is mind easing.  SSD are dangerous still for reliability so adding RAID 0 to the mix is not really something I am looking for.  Plus many reports questions RAID 0 over SSD, but this is just derailing the focus.  Graphics performance are satisfactory but I don't play games so I am good.  The noise of the fan is ok when hot but otherwise is silent.  Surprisingly the temperature of the machine never gets hot bottom or top wise, and this was after installing software for hours.   


So comparing Windows Experience Index we can see the following results:

UX51VZ



SVE14A390X Premium Plus
Look at the hard disk transfer rate increasing from 5.9 to 8.1.  Also the memory operation per second went down, but I am guessing this part of not being good benchmark test process.  Just simple execute and done tests.

OEM Hardware




With Custom SSD



ENHANCEMENTS

Swap HDD for SSD

The only major customization is swapping the HDD (hard disk) for the SSD (solid state drive).  Before I did it myself, I google around for DIY / tutorials about what is needed.  Unfortunately the information I found can be quite confusing for such an easy process.  For this scenario we are keeping the original configuration from the recovery disk and do a new install in the SSD.  Please don't just install an image in the SSD as this can result in bad performance and premature damage to your SSD.

1. Create the Recovery Media

Sony doesn't distribute the Recovery Disks anymore.  Instead they exists in a partition of your OEM disk.  All you have to do is follow the instructions in this link to burn the recovery disks.


2. Swap the HDD for SSD

Below are some images of the swap, but this should be plug and play.  Just take your time and don't force anything.  The only important note is to swap the bracket holding the HDD in place and place it in the SSD as shown in the photos below.







3. Re-install the system with Recovery Media

Once you finish placing the SSD now you need to install Windows 8 OS.  Follow this link for the instructions.  I got to say I am surprise with how easy is with Windows 8 to re-install the OS.  No questions about disk partition, file system format, etc...  Even no need to specify the activation as with Windows 8 this is kind of built in into the hardware (I think motherboard).



Replace DVD  for HDD

Now for the other modification of converting the DVD player into a secondary HDD.  For that we need the caddy to hold the HDD.  The process is simple but take your time!  There are plenty of tiny screws and the face plate is fragile.  Read the following instructions prior to starting the swap.

The only "hard" steps is shown below in the photos.  The trick for taking the face plate out press the tab down while pulling the plate in a rotational motion.  I don't remember the exact direction of the rotation, but just don't pull straight.  This is plastic so if you break it, you are going to be sorry.

The results.  Well the machine is a little heavier but not enough to make a real difference because this is not an ultrabook light.  Actually, having the hard drive makes the machine more sturdy when I grab it on that side versus feeling hollow.  Meaning I can hold it with a solid grip without worrying.  Having a 1TB disk is awesome for system image backups, store movies, ISOs, etc.  Since this is the OEM disk, it contains partitions for recovery and even Windows 8 still installed for the case my SSD fails and I need to react quick.  Noise and heat is the same or none.

Only weird finding is that I can access my old user account in Windows 8 partition.  I don't know what credential Windows 8 in the SSD found to give me access to the old user account, but that sounds like security breach to me.



Press the tab:




While pulling in a rotational manner because the tabs have hooks:





Tuesday, March 5, 2013

MOCA - Useful commands

MOCA itself provides a collection of useful commands (mcmds) that can be found in $MOCADIR/src/cmdsrc.  These commands can help with development in tasks like scripting local syntax, transforming result sets, viewing database objects, among other functions.  I will show a list of useful commands quick and then cover some of them.  If you have questions about any in particular, feel free to ask.

Useful Commands:


Commands Explanation


list active commands

This command will retrieve the repository of commands loaded in the MOCA server.  Is very useful for seeing if a specific command is overridden by another or checking other similar commands are available.  Usually during design reviews if the command is in local syntax, I like calling "list active commands where command = 'the command i need to check'".  This way I can directly copy / paste the command into my editor for review without having to find the file or any additional steps.



publish data combination

Sometimes you need to store the results of a previous result set and then perform another actions to then publish all the results together.  This commands allows you to republish the results of that save result set.



publish top rows

Use this command if you ever need to publish the top few rows of a result set.  Please keep in mind that this is not meant for you querying a million row and publish out just a few.  If you have that issues, think about the query or design of the command.



[get|set] session variable

In your local syntax script you might need to track values that prevail beyond the current sub-context.  In this scenarios is good to have session variables, so values manifest out into the session scope.



sort result set

Convenient way to publish the result set ordered by a criteria.



convert column results to string

If you need to take values from a column and turn them into a CSV (comma separated values) list, this command will do that for you quick.



convert column to multicolumn

I have used this in very rare cases where you have one column, but you need to break each number of rows into its own column.



convert list

This command has different conversion types that can be specified.  [S,D,L].  Here showing S and L which come handing when manipulating data.  "S" will take a CSV list and put single quotes for encapsulating the values.  "D" will do the same but with double quotes.  "L" will take the CSV list and turn it inot a column.





filter data

This command is handy whenever you need to have the current context to use a previous result set.  Be careful with the moca _filter_level value as it can change depending the previous execution pass your code took.


do loop

Ever need to handle create a loop, well you can.  It might sound weird due to the natural used of MOCA, but I have actually use it (very rare...).


execute server command

In order to build dynamic commands, use this command to execute dynamically generated code which is passed in as the string argument "cmd".



list user tables

Get a full listing of the tables in the database schema.



list table columns

Displays the fields in a table and their properties.



list code descriptions

Quickly investigate the possible values and descriptions for a code.



list command usage

Use this command to view statistics about the MOCA commands executed in the server.  Great tool for analyzing performance.  Call "clear command usage" just before calling the functionality that has the performance issue to erase the statistics from the server and start tracking from that point on.  Execute the problem point and when complete, call the "list command usage" to extract the results to an Excel sheet for further analysis.  Usually you want to target either single commands that take too long to execute, or commands that get executed too often and collectively become an issue.



Monday, February 25, 2013

WMS - Dock Appointments

RedPrairie WMS can manage the dock appointments in your facility and give visibility to the schedule across the calendar.  These appointments can even be limit by capacity constraints like number of appointments or pallets in a given area during a time frame.   In this post we will cover the configuration require to achieve this, such that the clerk can use the Dock Scheduling Operations to manage the doors traffic.

Setup - Dock Capacity by Appointments 

Dock Scheduling

This form is the WMS standard functionality to maintain appointments.  In this screen you we can see lots of information like standard calendar schedules with slots and dock doors configured for a given area.  The clerk uses this screen to maintain the appointments across the calendar.

In this scenario we are looking at the slot = INBOUND with dock doors = {DD003} in the area = DOCKDOOR



Appointment Maintenance 

For handling appointments in Dock Scheduling Operations, the appointment maintenance modal form pops up.  Here the clerk confirms the appointment date as well as other details related to the work being performed at the door.

Here we are creating an appointment for door = DD003 in which is for a Receiving Trailer.



Dock Slot Maintenance

The concept of slots exists to configure the docks into a common grouping.  This way appointments can be created against a slot versus a specific dock location.  Is kind of saying the trailer can use any of those available doors at the given appointment time.

Here the slot = INBOUND has only the dock door = DD003 assigned to it


  

Location Maintenance

To have dock doors, physical locations needs to be configure in the system.

Here we have dock door = DD003 which is part of the area = DOCKDOOR



Area Maintenance 

Dock door locations are defined by the area configuration when either the Receiving Dock or Shipping Dock flags are check.

The area = DOCKDOOR has both flags set, which makes the location DD003 a shipping and receiving dock door.


Standard Dock Calendar 

The standard dock calendar configures the schedules in which the area dock doors can be attended.




When creating entries in the area column, you are defining capacity constrains for the area during the given timeframe.  The area can be limited to either a maximum number of appointments or pallets during that period.



In this example we have the area = DOCKDOOR with a capacity from [7am..8am] of maximum 4 appointments allowed.  This means that even though we have many doors  schedule as available during that period, there is only a limited capacity of 4 appointments that can be handled for that  area.


Also from the Standard Dock Calendar screen we can define calendar exceptions.  These defines days where the standard calendar is overridden to not apply a common schedule.  These are use for days off like holidays, no work reasons, etc..



Sunday, February 24, 2013

MOCA - What it is? How to use it?

What is MOCA?

MOCA is RedPrairie server engine for executing functional actions which drives the database state.  It uses a component based client-server architecture and facilitates development through it's simple scripting language.

When coding with MOCA you will be able to query databases very easily and even add programmatic behavior to your logic.  This is something that was usually done through a querying structured language like PL/SQL.  If you don’t like C, then you don’t want to go there.  MOCA facilitates many of necessary coding structure require from PL/SQL into three very important concepts:  syntax, context, and piping.

A big asset from MOCA is the reusability of code through components better known as MOCA commands.  The commands are instances of components that are loaded in the MOCA server memory.  This avoid recoding the same logic over and over, and instead just call the equivalent MOCA command that will take care of the job for you.


Show a MOCA example!!

Imagine that we have in the database two tables:  ORDER and ORDER_LINES.  Someone comes and ask you to create a query which gives you the information from all the order lines for order number ‘123’.

Let’s compare 3 different ways of getting the same result:

(Don’t worry about details now…)

SQL

Select ol.*
  From order o,
       Order_lines ol
 Where o.ordnum = ‘123’
   And ol.ordnum = o.ordnum 
   

MOCA (non-component)

[Select ol.*
   From order o,
        Order_lines ol
  Where o.ordnum = ‘123’
    And ol.ordnum = o.ordnum] 
    

MOCA (component)

List orders where ordnum = ‘123’
|
list order lines
     Where ordnum = @ordnum


That wasn’t that bad, was it?  Anyways, let’s get into the details of the MOCA syntax.  I will assume you understand SQL so I will skip that section.

MOCA (non-component):
If you look at this section it looks just exactly like SQL except for the brackets [..].  In MOCA syntax the brackets mean that we will be calling a SQL statement.  That’s why the code example is exactly as the SQL equivalent.

MOCA (component):
Now is when the good part comes…  Where is the SQL code?  To understand MOCA programming you need to understand three things that any MOCA code will have:  syntax, context, and piping.  These are the control mechanism of the MOCA scripting language.


How Syntax works?

First, keep in mind MOCA primarily interacts with databases by querying and handling results.  This means that you will need to use SQL code to query the database.  In order to invoke SQL from MOCA you will need to create a SQL section with the use of brackets as follows:

SQL section à [ my sql
    code
    goes here ]

MOCA syntax allows calling components known as MOCA commands (mcmd).  For a complete list of available mcmds in the server, execute the list active commands mcmd.  The naming of the commands is standardize by following the concept of CRUD commands.  CRUD stands for create, remove, update, display which are the most common actions done against the database. 

You will notice that most of the mcmds are called as follows:

create <something> where a=’1’ and b=’2’
remove <something> where a=’1’ and b=’2’
change <something> where a=’1’ and b=’2’
  list <something> where a=’1’ and b=’2’


Also note that mcmds always follow a grammatical rule of:
moca command name where arg1=’x’ and arg2=’y’ [and argX=’…’]



How Context works?

Context is memory space that holds your stack of variables and values.  It works as global variables do in any other programming language but limited to the current context.  We could get a little fancier than this and talk about context scope, but let’s leave that for later.  Think as global variables for now that are part of the current execution session.

Variables in MOCA follow the idea of being publish and available.  To publish a variable means to store something in context.  Every time you execute anything in MOCA, the complete result set gets published to context.  This makes data available and values can be retrieved back for further use.  The @variablename syntax is used to retrieve a value from context.

For example:
publish data where ordnum = ‘123’
|
list orders where ordnum = @ordnum

In this scenario we publish the variable/value ordnum=‘123’ into context for later use, and then the value for the variable ordnum gets retrieved by using @ordnum.



How Piping works?

Here is where the magic happens…  In order to keep your context available for other commands, you need to use the pipe character ‘|’.  People familiar with UNIX have seen this before, but I am not that old so…  What is a pipe?  Think of it as a way of connecting information from one command to another.  Every time you pipe a command to another the context flows into the next command. 

Why I mean it flows versus is available?  Well things can get a little complicated with piping.  Since we are working with result sets which probably contain more than one row.  What happens is the next command piped down will execute for every row passed in.  The current row becomes in a way the current context being executed by the next command.

So if you have a result set with ten rows, then the piped command will execute ten times.  Be careful when piping commands because you can get into scenarios where if a million rows where retrieved, then the next commands will execute a million time as well.  This can create a big performance issue.  And definitely understanding how to control context is very important for  development.



Hello MOCA

There is no better way of learning than practice.  And this is probably one time that for sure it will pay off.  You should experiment with piping commands and retrieving information.  Some useful commands to get started are the following:  list active commands, list user tables, list table columns.

Below you will find a simple example of how MOCA command works.

This script will be retrieving all the order lines for orders of type ‘CUST’
publish data where ordtyp = ‘CUST’
|
list orders where ordtyp = @ordtyp
|
list order lines
     where ordnum = @ordnum catch(-1403)


  1. In the first line, the variable ordtyp is published to the context with value CUST
  2. The second statement pipes the context and calls the mcmd list orders.  The value for @ordtyp is retrieved from context and during execution the expression is substituted by ordtyp = 'CUST'.
  3. Then again the context is piped with the results of the previous command into the next command list order lines.  For every row in the orders result set, the mcmd for retrieving the order lines is called.
  4. Each execution will return the order lines for the order specified in the mcmd.  At the end a final result set is built which combines all the results from each execution.

In the last line you can see there is an instruction catch(-1403).  Catch works the same as any catch instruction (if an error is expected, we might want to keep processing).  Catch(-1403) is when you need to handle no rows returns.  In this case, if we passed an order number which didn’t have order lines we don’t want to fail.  Instead we want to retrieve all orders lines for orders of type CUST for which order lines exists.

Tracing MOCA

Like any development tool, you need a way to debug.  MOCA has tracing functionality where you can see the actions and context being used during each execution.  Is good idea to see tracing in action and get use to it with simple examples.


More MOCA...

Realistically we can't cover MOCA in just a post.  There is actually a full MOCA Developer guide which mentions lots of the functionality of the language and its architecture.

Quick list of things that I should cover:
  • Using context correctly with:  {}, |, ;
  • Transaction and Session management
  • MOCA Component Override
  • MOCA Triggers
  • Technologies supported:  SQL, C, JAVA, Groovy, WebServices
  • Remote MOCA calls
  • Tracing
  • Performance
  • Design patterns


MOCA Client - LextEdit

Any customer using RedPrairie (RP) solutions have heard the term MOCA.  This scripting language is the heart of RP Dlx solutions and allows connecting database query results with actions for RAD (rapid application development).  In order to develop MOCA commands (mcmd) you need a client which can connect to the server.

So far I know of 5 ways to develop MOCA.  I don't plan to cover the pros / cons of each, but rather just list them.
  1. Msql - standard RP command prompt client
  2. WinMsql - standard RP Windows based client
  3. Server Command Operations - RP DLx client
  4. Oracular - Moca Client - 3rd party Java based MOCA client
  5. Orbital Services - LextEdit - 3rd party .Net based MOCA client

This post is about my favorite MOCA client LextEdit, so let's focus on that.

Download LextEdit

LextEdit is a feature rich MOCA client for developing commands.  This tool can really speed up productivity in so many ways... but I don't plan to cover them all.  Really, that many!  Instead I plan just to show up the basic most common features that will tempt you trying it out!


LextEdit - Client Window

The layout is clean and simple.  A text area on the top for scripting and result set on the bottom.  Nice highlighter colors, result row colors makes easy reading, and lots of quick accessible functionality.


Connection Profile

Where is my note with the customers connection?  Forget that!  Save it in the connection profile.  It also support import and export of the configuration.



Execute only selected script portion (Ctrl+Shft+Enter)

The best part of this tool is you can select the scripts to execute within the text area.  This allows to have many commands in the same editor and execute quickly



Day in work

Took this shot to show how a real day at work looks like with many commands to execute and searching for information.




Code Suggestions...

 

Command listing

See available MOCA commands as you type.


SQL - Database Table and Fields

Database syntax aid tool works like a charm. See the available tables and fields quickly with even information about their data type.  It also helps with SQL functions and syntax highlighting.





Copy Functionality...

The copy functionality for the result set is amazing.  There are a lot of options how to copy the results out.

 

Datarow into CSV format (Ctrl+Shft+C)

Among my favorites is copy the results for a CSV file.  This is great creating the data for the mload control files.



Trace Profile

Great trace profile.  You can see the execution tree as well as the trace log completely.  Highlighter helps differentiate information quickly.



Mload files

This functionality is awesome!  The other day I had a customer who didn't give use execution rights over the Unix server, but we needed to mload a lot of data.  There expectation was to write all those as SQL Insert/Update statements ourselves.

Well finding that I can select local Mload files (control and data) and load the data into their system was so awesome!  Big plus here for added functionality to LextEdit.



SQL Explain Plan

Another that surprise me was Explain Query.  I was used to use this from SQL Server when writing big queries for optimization purposes.  Well being able to have a glimpse directly from LextEdit is another big plus.