Problem

Cycle Features built to interact with and test the JDA WMS need to run queries, execute statements and perform validations directly against the database. 

Solution

JDA WMS provides a framework to ease database interaction called MOCA.

Cycle includes the step I execute MOCA command which allows the user to provide MOCA and Local Syntax Inline SQL statements for execution within a Feature. This requires establishing a MOCA connection using the step, I connect to MOCA at "<ADDRESS>" logged in as "<USERNAME>" with password "<PASSWORD>"

Examples

Quick Introduction

Local Syntax

Local syntax commands are not surrounded by any square brackets.  They are take the form of <verb> <noun/phrase> <optional where clause>.  Common examples include publish data, list printers, list shipments, close trailer, dispatch trailer, list shipment lines, list inventory for shipments ('for' is not a keyword in moca), etc.  "Publish Data" is a good command to use for testing; it will just echo back the result set you create via the where clause.  Multiple arguments in a where clause are separated by the keyword 'and'.  Parts of your where clause will be in <variable_name> = <value> format.  In moca, you can use either single quotes, double quotes, or a mix or both, so long as they're nested properly, around your strings.

Note

In Cycle, the "I execute MOCA command" step only fails if Cycle is unable to get a valid response from the moca server.  It does not reflect whether the step actually ran successfully on the server or not.  You must check the returned moca status code if you want to validate the command ran successfully.  If the command you pass to moca to run is complete garbage, the "I execute moca command" step will still run.  Your result set will have status 501, indicating what you sent to the moca server was not a valid moca command.  Other statuses will indicate other error conditions, like there was no matching data (510) (this is a very, very common error, and is often not obvious at first what data the command was looking for when it returned this error), a trailer could not be closed because it was not fully loaded, you tried to assign a carrier to an international shipment that doesn't offer international shipping, etc.

Feature:  Writing MOCA

Background: Establish the connection
Given I connect to MOCA at "address/service" logged in as "super" with password "super"

After Scenario: Close the connection
If I close MOCA connection
EndIf

Scenario: Run basic local syntax
When I execute MOCA command "publish data where a = 1 and b = 'green'"
Then I verify MOCA status is 0
Then I assign row 0 column "a" to variable "foo"
Then I verify text $foo is equal to "1"
Then I verify number $foo is equal to 1

Given I assign "WMD1" to variable "wh_id"
When I execute MOCA command "list warehouses where wh_id = '" $wh_id "'"
# 0 is success
Then I verify MOCA status is 0
Then I assign row 0 column "wh_id" to variable "foo"
Then I echo $foo

# this step does not fail, even though this is not a real moca command
When I execute MOCA command "list best rock band ever"
# you must validate the returned status, if you really care whether or not the above command worked
Then I verify MOCA status is 501
When I execute MOCA command "list inventory for shipments where ship_id = 'nonsense'"
# 510 is "No Data Found"
Then I verify MOCA status is 510
Then I verify 0 rows in result set

Inline SQL

In moca, you can run straight SQL, so long as it's wrapped in a pair of single brackets.  Moca will try to do some massaging of your SQL to be database agnostic, so you may be able to get away with some syntax that normally isn't valid for a given DB vendor (like 'where rownum < 10', which is normally not valid for all DB vendors).

Feature:  Writing MOCA

Background: Establish the connection
Given I connect to MOCA at "address/service" logged in as "super" with password "super"

After Scenario: Close the connection
If I close MOCA connection
EndIf

Scenario: Run some inline SQL
When I execute MOCA command "[select * from wh where rownum < 2]"
Then I verify MOCA status is 0
Then I verify 1 rows in result set

Inline Groovy

In moca, you can also embed some groovy in your moca command, so long as it's wrapped in a pair of double brackets.  Since local syntax lacks many features of most programming languages, if you really want to do anything interesting without a lot of hacky code, groovy is the way to go.  All the standard java libraries are now available to you as well.  However, until we can support multi-line step arguments and nested quotes, customers will have to write their moca using groovy in external files to do anything really interesting.

Feature:  Writing MOCA

Background: Establish the connection
Given I connect to MOCA at "address/service" logged in as "SUPER" with password "SUPER"

After Scenario: Close the connection
If I close MOCA connection
EndIf

Scenario: Run some groovy code
When I execute MOCA command "[[ Date d = new Date() ]]"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I assign row 0 column "result" to variable "foo"
Then I echo $foo

Command Streams and Variable Replacement

You can pipe the output of one command into another command using the pipe operator.  Once a command puts its output "on the stack", it's available to any command downstream from that command.  You can use a semi-colon to separate commands.  This will help if you need to prevent upstream results from affecting the results of later commands.  You can use curly braces to help control what's "on the stack" after running a group of command (it will be only the result of the last command).  You can also join two commands together using an ampersand.  The number of rows will be equal to the sum of the rows from both commands.  Any columns in one result set but not the other will be null.

Variable replacement will be done using '@'.  Using '@@' will be for any global variables.  In variable replacement, moca will handle adding quotes as needed for you in SQL and local syntax. Note that in groovy, you don't use the '@', variables are automatically created for you.

Feature:  Writing MOCA

Background: Establish the connection
Given I connect to MOCA at "address/service" logged in as "SUPER" with password "SUPER"

After Scenario: Close the connection
If I close MOCA connection
EndIf

Scenario: Command streams and variable replacement
When I execute MOCA command "publish data where myloc = 'DEV' | [select count(0) as wh_count from wh where wh_id = @myloc]"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I assign row 0 column "wh_count" to variable "foo"
Then I echo $foo

When I execute MOCA command "publish data where myloc = 'WMD1' | [select count(0) as wh_count from wh where wh_id = @myloc] | publish data where a = @wh_count"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I assign row 0 column "a" to variable "foo"
If variable "wh_count" is defined
Then I echo $wh_count
EndIf
Then I echo $foo

When I execute MOCA command "publish data where magic = @@usr_id"
Then I assign row 0 column "magic" to variable "foo"
Then I verify text $foo is equal to "SUPER"

When I execute MOCA command "publish data where myloc = 'DEV' and foo = 1 & publish data where myloc = 'WMD1'"
Then I verify MOCA status is 0
Then I verify 2 rows in result set
Then I verify "DEV" in row 0 column "myloc" in result set
Then I verify "1" in row 0 column "foo" in result set
Then I verify "WMD1" in row 1 column "myloc" in result set
Then I verify "" in row 1 column "foo" in result set

When I execute MOCA command "publish data where myloc = 'DEV' | publish data where loc = @myloc"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I verify "DEV" in row 0 column "loc" in result set

When I execute MOCA command "publish data where myloc = 'DEV' ; publish data where loc = @myloc"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I verify "" in row 0 column "loc" in result set

When I execute MOCA command "publish data where myloc = 'DEV' | [[ Date d = new Date(); magic = myloc + d ]]"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I assign row 0 column "magic" to variable "foo"
Then I echo $foo

When I execute MOCA command "publish data where a = 5 | {publish data where a = 1 ; publish data where b = 2} | [[ magic = b + a ]]"
Then I verify MOCA status is 0
Then I verify 1 rows in result set
Then I assign row 0 column "magic" to variable "foo"
Then I verify number $foo is equal to 7
Then I echo $foo

Exception Handling

You can just stick a catch at the end of your moca command.  If you supply one or more status, codes, those are caught, anything else is thrown.  Using catch(@?) will catch all.

Feature:  Writing MOCA

Background: Establish the connection
Given I connect to MOCA at "address/service" logged in as "super" with password "super"

After Scenario: Close the connection
If I close MOCA connection
EndIf

Scenario: Catch
# error if no rows are returns (510 is no data found)
When I execute MOCA command "[select * from wh where wh_id = 'DEV']"
Then I verify MOCA status is 510
# it's OK if this query returns no data, but if I get any other errors, I don't want this to return a 0 (ok) status (-1403 is another no data found)
When I execute MOCA command "[select * from wh where wh_id = 'DEV'] catch(-1403,510)"
Then I verify MOCA status is 0
# obviously not a valid moca command, should error
When I execute MOCA command "drop it like its hot"
Then I verify MOCA status is 501
# who cares if this isn't a valid command, catch all!
When I execute MOCA command "drop it like its hot catch(@?)"
Then I verify MOCA status is 0