In Part One of this series we got to see some simple examples of M code being used as Stored Procedures from Java. We saw that how Stored Procedures are named and how to use the name to call it from Java or from the M command prompt.
Part two will cover a deeper example that involves storing and reading data in a M Global – similar to how you may end up using the Stored Procedures feature your applications. In the process we’ll cover in more detail how to write the M code, how to declare the procedure in dcSchema, how to call from Java and how to process the return values.
View the M routine dctToyTest.m. Most of the code is for Stored Procedure calls, but at the top are “setup” and “cleanup” functions. This example needs setup, so before we go any further lets run that from the M prompt.
GTM>d setup^dctToyTest GTM>
Verify that the global has been loaded, compare the code in the “setup” function with what you see in the global:
GTM>zwr ^dctData
^dctData("People",10000,"Age")=8
^dctData("People",10000,"Friends",1)=10001
^dctData("People",10000,"Friends",2)=10002
^dctData("People",10000,"Name")="Sally"
^dctData("People",10000,"Toys",1)="Legos"
^dctData("People",10000,"Toys",2)="Puzzle"
...
GTM>
There are now 6 people in our database. In addition to the records in ^dctData, there is also a ^dtcIndex which holds the indexes for the Toys and Friends fields. Sally, the example record above, has 2 friends and 2 toys. The indexes for her fields look like this:
^dctIndex("Friends",10001,10000)=1
^dctIndex("Friends",10002,10000)=1
^dctIndex("Toys","Legos",10000)=1
^dctIndex("Toys","Puzzle",10000)=1
The test data and index globals are not well designed, so the point here is just to see how the example works – not to mimic the design. When we look at the dcTables feature later the we’ll discuss design goals. That said, what we see here is that if we have a person id such as 1002 (Ginger) we can easily find who has friended that person. By looping the index – s friend=$o(^dctIndex(“Friends”,10002,friend)) – we can find all those people. Conversely, the people Ginger has friended will be in ^dctData(“People”,10000,”Friends”,n)=Friend Id.
Likewise we can find all the people who like a particular toy by looping, for example who likes Legos: s person=$o(^dctIndex(“Toys”,”Legos”,person)). If these concepts don’t make sense you need to resume review MUMPS data structures.
Lets delve into the first Stored Procedure, it just returns a list (JSON Array) of names (strings). All 6 people are listed:
GTM>d local^dcConn("QUERY dctListPeople")
Data:
:[ : Sally: Chad: Ginger: Kyle: Betty: Mike] !
Messages:
:[ ] !
GTM>
Recall that we find the M routine name in the ^Proc global.
GTM>zwr ^Proc
...
^Proc("dctListPeople")="listPeople^dctToyTest"
...
GTM>
So we should look at the “listPeople” function in “dctToyTest”. The first few lines handle parameters passed to the stored procedure. Parameters will (typically) be provided to procedures via the local variable “Params”. Parameters can be complex M structures, but typically they are just key and value pairs such as MinAge and MaxAge. When these parameters are present they limit the list of names returned so that only people within the range appear.
listPeople n id,minage,maxage
s minage=Params("MinAge"),maxage=Params("MaxAge")
s:minage="" minage=0
s:maxage="" maxage=200
Note that if the parameter keys are not present we default to a value. Another option would be to log an error and return – something like this: i minage=”" d err^dcConn(90010) quit. We’ll cover error handling in a future part of this series. But it is useful to know these rules:
a) You do not have to return any data at all from a stored proc
b) If you find that something expected is missing (parameters, data) then you may log and error or warning, that message will be presented to the calling code (Java or M)
c) If no error or warning is logged then the procedure is assumed to have run correctly even if no data was returned
d) It is good practice to check for errors before returning any data, as much as possible
e) When you do want to return data from a procedure you must return it either in a List or a Record. Your structures must be well formed and syntactically correct.
Lists and Records have a definite start and end, and you must make sure they are well formed (there is always an end for every start).
w StartList ; start list of people
;
f s id=$o(^dctData("People",id)) q:id="" d
. i (^dctData("People",id,"Age")<minage)!(^dctData("People",id,"Age")>maxage) q
. ;
. w ScalarStr_^dctData("People",id,"Name")
;
w EndList ; end list of people
;
quit
The start and end of the list is fairly obvious. Also by now the “for” loop should make sense. Inside the loop we first check to see if the person’s age is less than or greater than the age range provided to the procedure. If it is in the correct range we go to the next lines and write out the name.
Note that every item in a list must be preceded with a “Scalar”. Typically the Scalar is a “ScalarStr” if the following value is a string, “ScalarInt” if the following value is an integer or “ScalarDec” if the following value is a decimal.
Procedures may return complex data structures – Records in Lists, Lists in Lists, Records in Fields and Lists in Fields. Very much like JSON does. Consider the procedure “dctGetPeople” which links us to the “getPeople” function in “dctToyTest”. Here is a description of the return value for this procedure:
[
{
"Id": |int, person id|,
"Name": |string, person name|,
"Age": |int, person age|,
"Toys": [
|string, toy name|
],
"Friends": [
{
"Name": |string, person name|,
"Age": |int, person age|
}
],
"FriendedBy": [
{
"Name": |string, person name|,
"Age": |int, person age|
}
]
}
]
We see that Friends are records inside of a list, the list is inside a field, the field is in a record, the record is in a list. A complex structure, but not too hard to code in M. Run it just for kicks:
GTM>d local^dcConn("QUERY dctGetPeople")
Data:
:[ :{ , Id: 10000, Name: Sally, Age: 8, Toys:[ : Legos: Puzzle] , Friends:[ :{ ,
Name: Chad, Age: 8} :{ , Name: Ginger, Age: 9} ] , FriendedBy:[ :{ , Name: Chad
, Age: 8} ] } :{ , Id: 10001, Name: Chad, Age: 8, Toys:[ : Kite: Playdough] , Fr
iends:[ :{ , Name: Sally, Age: 8} :{ , Name: Ginger, Age: 9} ] , FriendedBy:[ :{
, Name: Sally, Age: 8} ] } :{ , Id: 10002, Name: Ginger, Age: 9, Toys:[ : Bike:
Softball] , Friends:[ :{ , Name: Kyle, Age: 10} :{ , Name: Mike, Age: 9} ] , Fr
iendedBy:[ :{ , Name: Sally, Age: 8} :{ , Name: Chad, Age: 8} :{ , Name: Betty,
Age: 9} ] } :{ , Id: 10003, Name: Kyle, Age: 10, Toys:[ : Softball Bat] , Friend
s:[ ] , FriendedBy:[ :{ , Name: Ginger, Age: 9} :{ , Name: Mike, Age: 9} ] } :{
, Id: 10004, Name: Betty, Age: 9, Toys:[ : Jump Rope: Softball Glove] , Friends:
[ :{ , Name: Ginger, Age: 9} :{ , Name: Mike, Age: 9} ] , FriendedBy:[ :{ , Name
: Ginger, Age: 9} ] } :{ , Id: 10005, Name: Mike, Age: 9, Toys:[ : Frisbee] , Fr
iends:[ :{ , Name: Kyle, Age: 10} ] , FriendedBy:[ :{ , Name: Ginger, Age: 9} :{
, Name: Betty, Age: 9} ] } ] !
Messages:
:[ ] !
GTM>
Procedures do not have to have any parameters, consider the first lines of “getPeople”.
getPeople n id,tnum,fnum,fid w StartList ; start list of people ;
We jump right into writing out a list. And at the end we be sure to end our list:
; w EndList ; end list of people ; quit
The rest should fairly easily to comprehend, just do loops within loops to get at all the data you want. We do use the index global here, lets peek at that code (which is already in a loop on the records):
. w Field_"FriendedBy"_StartList ; friended by list
. f s fnum=$o(^dctIndex("Friends",id,fnum)) q:fnum="" d
. . w StartRec ; friend info
. . w Field_"Name"_ScalarStr_^dctData("People",fnum,"Name")
. . w Field_"Age"_ScalarInt_^dctData("People",fnum,"Age")
. . w EndRec ; friend info
. w EndList ; end friended by list
Note the Start and End list for the friended by field. We just loop through the index and write out a small record (inside the List) for each friended by entry.
Lets run this via the Java Connector. Run testdb again, as you did in the Getting Connected post.
D:\dev\divconq\template>.\bin\run.bat testdb
...
option 8
...
-----------------------------------------------
Toy Database Menu
-----------------------------------------------
0) Main Menu
1) List People Names
2) List Toys
3) List People in Tabular
4) List People in Complex
5) List People directly to file (Json)
6) List People directly to file (Yaml)
7) Add Person
1
MinAge filter [empty for no filter]: 5
MaxAge filter [empty for no filter]: 8
dctListPeople Response:
[
"Sally",
"Chad"
]
And now for the more complex example, run option 4 and see the data coming back from the procedure formatted in JSON.
As mentioned earlier, Java calls the procedure names “dctListToys” and “dctGetPeople”. We know that the ^Proc global holds the mapping for the procedure name to the function name in M. But who sets up the mapping?
The answer is – the developer does. And so this brings us to a whole new topic in DivConq, Packages. Applications written using DivConq as assembled by combining Packages. Each Package contains Java and/or M code and adds some functionality to the overall application.
In a sense a Package is like an “add-on” only the whole application is composed of “add-ons”, separate units of functionality that may be installed or not. If you are familiar with OSGi then a package is somewhat similar in concept (we don’t use separation via class-loaders yet though, lets see what Java 8 brings). Also, to some extent, a WAR file is similar to a Package.
In any event, there is 1 core Package which all applications must have – it is “dcCore”. Combine this with your own Package “myApp” and you have all you need to make a distribution of your application. But if you choose to allow it, your customers may drop in other packages (their own customizations or third-party) on top of your install to add more functionality.
One of the great things about DivConq is that the contents of one package may override the contents of another package. Thus, if a customer dislikes images or locale translations or whatever in your application, it is fairly easy to create an override and put it in another package. This keeps their stuff separate from your distribution, and it also makes it very easy to identify the overrides during an update. You update installer can say to the user “we see you overrode X, but we changed X since your last install.” It takes work to setup that up in your installer, but the effort in checking for overrides is minimal once you know what you want to provides hints on.
This brings us to the “dcTest” package. This package would not be distributed with your application, it is just here to provide examples to developers learning DivConq. So “dcTest” keeps “dcCore” clean by keeping almost all the example files separate.
Look at your copy of the dcTest package in the Template folder: “template/packages/dcTest”. In here is the “package.xml” file that describes the package – more on that later. There is also an “m” folder that contains the M routines that dcTest relies on. Now look in “all/schema” to find the schema declarations.
Open the schema file and look for a Procedure element with Name=”dctListPeople”. Here is what that looks like:
<Procedure Name="dctListPeople" Execute="listPeople^dctToyTest"> <Description> Get a list of names of all people in test data. Optionally add in an age range filter </Description> <RecRequest> <Field Name="MinAge" Type="Integer" /> <Field Name="MaxAge" Type="Integer" /> </RecRequest> <ListResponse Type="String" /> </Procedure>
Every stored procedure in DivConq needs a definition like this. The definition will be in a schema file within the Package you are developing. For example, if you are coding some Divconq tests in template then you’d probably be editing the schema file “template/packages/dctTemplate/all/schema/schema.xml”.
Stored procedures need definitions so that Java can determine that it is calling with the right parameters and that the correct data structure is being returned by the procedure. You can see in the Execute attribute how the name gets mapped to the M function.
Recall when you did “run.bat sync” and updated the M globals (option 2). The sync utility copies information about the stored procedures to M. So first you edit the schema, then you run sync, then you may call your procedure.
A future post will expand on how to make schema definitions, but for now look just at how we declare the request:
<RecRequest> <Field Name="MinAge" Type="Integer" /> <Field Name="MaxAge" Type="Integer" /> </RecRequest>
RecRequest means we will pass in a Record (not a List) and that we’ll have MinAge and MaxAge as possible fields. So within M the Params(“MinAge”) looks like key value, but within Java/dcSchema we think of that as a record with fields. There is no Required=”True” with these fields, as such they may be absent from the call in Java.
And look at how we declare the response:
<ListResponse Type="String" />
ListResponse (not RecResponse) means we expect a list – the Type attribute tells use it will be a list of String. Each String is a single name, of course.
This brings us to the final example in this part of the Stored Procedure series, a call in Java.
RecordStruct ages = new RecordStruct();
ages.setField("MinAge", 3);
ages.setField("MaxAge", 8);
QueryRequest lpr = new QueryRequest("dctListPeople", ages);
Although we have not yet covered RecordStruct, you can probably guess that this is what we mean when we declare the parameter as being a “RecRequest” – a record. Yes, a record with 2 fields just as declared in dcSchema. The parameter is passed into the QueryRequest object, along with the procedure name, and we have all that we need to make a call to the database.
We’ll cover more about database calls in the next part. Along with that we’ll cover error handling in stored procedures (including localization) and review a stored procedure for updating globals.
Published on Friday December 30, 2011 at 05:45pm