In this series we’ll review how to write stored procedures, both for querying data and for updating data. We’ll be using the stored procedures from the dcTest Package mentioned in the previous post on getting connected.
To follow this post you’ll need to have DivConq downloaded and configured, see Getting Connected with DivConq. You’ll also need a working knowledge of M, at least the concepts covered up through Introduction to MUMPS part 3.
Return to your desktop command prompt and get into the DivConq template directory. Run the testdb command and select option 4:
D:\dev\divconq\template>.\bin\run.bat testdb
Command testdb
Starting Hub
...
4) Load stored proc results (testProc1)
...
4
...
testProc1 Response:
{
"Name": "Sally",
"Age": 5,
"Toys": [
"Legos",
"Puzzle"
] ,
"Friends": [
{
"Name": "Chad",
"Age": 5
} ,
{
"Name": "Ginger",
"Age": 6
}
]
}
What you see here is the output from a call to a stored procedure. It is formatted as JSON because DivConq uses data structures that easily format to JSON. However, the database is not returning JSON, this is just the format for display on the console.
Since we have already covered some M code on this blog lets jump into the code for the stored procedure first. After getting a feel for that we can come back and look at the Java code. As before, sign-in to your GT.M server and “sudo su gtmuser”. Then get into the M routines directory and load the file dctTest.m into a text editor. Start another shell for gtmuser and get to the M prompt.
In the text editor you should see this code:
; d local^dcConn("QUERY testProc1")
;
testProc1 n data,tnum,fnum
;
s data("Name")="Sally"
s data("Age")=5
s data("Toys",1)="Legos"
s data("Toys",2)="Puzzle"
s data("Friends",1,"Name")="Chad"
s data("Friends",1,"Age")=5
s data("Friends",2,"Name")="Ginger"
s data("Friends",2,"Age")=6
;
w StartRec
w Field_"Name"_ScalarStr_data("Name")
w Field_"Age"_ScalarInt_data("Age")
;
w Field_"Toys"_StartList
f s tnum=$o(data("Toys",tnum)) q:tnum="" d
. w ScalarStr_data("Toys",tnum)
w EndList
;
w Field_"Friends"_StartList
f s fnum=$o(data("Friends",fnum)) q:fnum="" d
. w StartRec
. w Field_"Name"_ScalarStr_data("Friends",fnum,"Name")
. w Field_"Age"_ScalarInt_data("Friends",fnum,"Age")
. w EndRec
w EndList
;
w EndRec
;
quit
;
The first part of this routine simply sets up a hard coded data structure. From what you learned in Parts 1 – 3 of Intro to M you can see how this data structure could be a global and designed to hold more than one person. This test is not about the M structure so much as about the returning of data.
When we come to the part of the routine with “w StartRec” is where the code needs explanation. Our Java code is going to call this function and in order for us to provide results back to Java we use the “write” command. Responses will always start with “w StartRec” or “w StartList”. Like JSON, the two composite structures supported are objects (called Records) and arrays (called Lists). For every StartNNNN there is an EndNNNN. Lets carve down the code to the basics and then add more, so start with:
;
w StartRec
w Field_"Name"_ScalarStr_data("Name")
w Field_"Age"_ScalarInt_data("Age")
w EndRec
;
quit
;
OK, so if Rec is like Object in JSON then StartRec is like “{” and EndRec is like “}”. Field indicates the start of a field. ScalarNNN indicates the start of a value (either in a List or a Field). ScalarStr is a string value and ScalarInt is a number value. Those four lines tell Java that we have a structure like this:
{
"Name": "Sally",
"Age": 5
}
Keep in mind that stored procedures are not actually writing out JSON syntax (although there is an option to do so) but it is a functionally similar output structure.
Lets add to the example:
;
w StartRec
w Field_"Name"_ScalarStr_data("Name")
w Field_"Age"_ScalarInt_data("Age")
;
w Field_"Toys"_StartList
f s tnum=$o(data("Toys",tnum)) q:tnum="" d
. w ScalarStr_data("Toys",tnum)
w EndList
;
w EndRec
;
quit
;
In JSON a field can contain an array or object value, not just scalars, and so it is here. After the field name is written (e.g. the “Toys” field) then write StartNNN instead of ScalarNNN. Make sure you provide an EndNNN to your start. Before each entry in the list use ScalarNNN to indicate the data type.
Scalars do not require an end because their ending is self evident (always end scalar when a new scalar starts or when another Field starts or when a EndNNN occurs.
Look back at the full code for this function. How the “Friends” field output works should also be fairly apparent by now. This part of the example shows how a List may contain Records. Lists may also contain other Lists.
OK, so how does Java call this code? For starters Java is going to call a procedure called “testProc1″. M is going to figure out how to call the function we are viewing. The procedure name “testProc1″ is mapped to a function in the ^Proc global. Do “zwr ^Proc” to see the procedure name mappings currently installed.
From the M prompt we can closely emulate a stored procedure call from Java by calling “local^dcConn”. The major caveat is the output normally written to the Java Connector will instead print to the screen, which is nice for debugging but it is not in true JSON format so a little hard to decode. So lets run that code now:
GTM>d local^dcConn("QUERY testProc1")
Data:
{ , Name: Sally, Age: 5, Toys[ : Legos: Puzzle] , Friends[ { , Name: Chad, Age: 5} { , Name: Ginger, Age: 6} ] } !
Messages:
[ ] !
GTM>
We call “local^dcConn” using the procedure’s name, not the M function name. Note the “QUERY” before the name. There are two operations of note: UPDATE and QUERY. The distinction has to do with the audit levels. QUERY calls should not update *real* data in globals (cache or temp data is fine to change). UPDATE has more auditing and is therefore the correct way to call procedures that will change *real* data in globals. Eventually the DivConq active-active db replication system will use that audit information to provide an option for multiple active M servers for the same database.
As you can see this is not real JSON output, but it is similar with the “{“, “}”, “[" and "]“. Once you get used to “, ” preceding every field and “: ” preceding every scalar then it is not too bad to read. This is for debugging only, these characters are not used with the Java Connector.
Speaking of, lets run that Java Connector now. You can use option 4 of “testdb” as you did at the start of this post, however, lets try the same thing with different code. Run the following (still in the template folder):
D:\dev\divconq\template>bin\run.bat class divconq.template.Main
Command class
Starting Hub
...
-----------------------------------------------
Test dcDb Connector Menu
-----------------------------------------------
0) Exit
1) Load stored proc results (testProc1)
1
Response:
{
"Name": "Sally",
"Age": 5,
"Toys": [
"Legos",
"Puzzle"
] ,
"Friends": [
{
"Name": "Chad",
"Age": 5
} ,
{
"Name": "Ginger",
"Age": 6
}
]
}
-----------------------------------------------
Test dcDb Connector Menu
-----------------------------------------------
0) Exit
1) Load stored proc results (testProc1)
0
D:\dev\divconq\template>
Same output as before, but this code is more concise and therefore better for review. Look in the source folder for the Main class (the one we just ran), it should be in ./template/divconq.template/src/divconq/template/Main.java. Here is the code for the entire application:
package divconq.template;
import java.util.Scanner;
import divconq.db.ObjectCallback;
import divconq.db.ObjectResult;
import divconq.db.QueryRequest;
import divconq.lang.Hub;
import divconq.lang.HubResources;
import divconq.lang.OperationResult;
import divconq.lang.TaskContext;
import divconq.log.Logger;
import divconq.test.TestDb;
import divconq.util.StringUtil;
public class Main {
public static void main(String[] args) {
// prepare resources for Hub start
HubResources resources = new HubResources("00101", true);
OperationResult or = resources.init();
if (or.hasErrors()) {
Logger.error("Unable to continue, hub resources not properly configured");
return;
}
// start the hub
Hub.instance.start(resources);
// in prep for the console ui, use a new user context (for the new log id)
TaskContext.useNewRoot();
Scanner scan = new Scanner(System.in);
boolean running = true;
while(running) {
try {
System.out.println();
System.out.println("-----------------------------------------------");
System.out.println(" Test dcDb Connector Menu");
System.out.println("-----------------------------------------------");
System.out.println("0) Exit");
System.out.println("1) Load stored proc results (testProc1)");
String opt = scan.nextLine();
Long mopt = StringUtil.parseInt(opt);
if (mopt == null)
continue;
switch (mopt.intValue()) {
case 0:
running = false;
break;
case 1:
// call the test procedure that is part of the dcTest package
// so that package must be installed for this to work
QueryRequest tp1 = new QueryRequest("testProc1", null);
ObjectCallback callback = new ObjectCallback() {
@Override
public void process(ObjectResult res) {
if (res.hasErrors()) {
System.out.println(" Error:");
TestDb.printPretty(res.getMessages());
}
else {
System.out.println(" Response:");
TestDb.printPretty(res.getResult());
}
}
};
Hub.instance.getDatabase().submit(tp1, callback);
break;
}
}
catch(Exception x) {
System.out.println("CLI error: " + x);
}
}
Hub.instance.stop();
}
}
Much of this code is about starting stuff up, and while that’s important, lets stick to how the topic of “how to call procedures”. At this point the summary is “start the Hub object before using databases”. Lets focus in on what happens when option 1 is picked:
// make a request to run a query (remember QUERY from above) procedure using no parameters (null)
QueryRequest tp1 = new QueryRequest("testProc1", null);
// calls to the database are asynchronous so we need a callback to handle the results
// there are other types of callbacks, but the ObjectCallback is typical - it creates
// JSON like objects (Records, Lists and Scalars) for easy use in Java code.
ObjectCallback callback = new ObjectCallback() {
@Override
public void process(ObjectResult res) {
// if the result has errors then print only the messages
// from the result, otherwise print the data
// note that printPretty just takes the JSON like objects
// and formats them as JSON to the console.
if (res.hasErrors()) {
System.out.println(" Error:");
TestDb.printPretty(res.getMessages());
}
else {
System.out.println(" Response:");
TestDb.printPretty(res.getResult());
}
}
};
// submit the request to the database queue, when the database has a free connection
// it will process the request and hand the result to the callback object
Hub.instance.getDatabase().submit(tp1, callback);
Obviously we need to cover how to process the result to do more than just print as JSON. However, in an effort to avoid too many topics at once lets review just a few more calls to M so that that process becomes more comfortable. In that vein, lets look at sending parameters to M. Go back to running the “testdb” command from the template folder:
D:\dev\divconq\template>bin\run.bat testdb
Command testdb
Starting Hub
...
-----------------------------------------------
Test dcDb Connector Menu
-----------------------------------------------
0) Exit
1) Ping
2) Echo with user input
3) Echo with hardcoded international chars
4) Load stored proc results (testProc1)
5) Check validity of toy list (testProc2)
6) Check validity of toy list (testProc3)
7) Switch Locale
8) Toy Database Example
5
Enter toys, one per line. Empty to finish.
hammer
yoyo
-----------------------------------------------
Test dcDb Connector Menu
-----------------------------------------------
0) Exit
1) Ping
2) Echo with user input
3) Echo with hardcoded international chars
4) Load stored proc results (testProc1)
5) Check validity of toy list (testProc2)
6) Check validity of toy list (testProc3)
7) Switch Locale
8) Toy Database Example
2011-12-13T15:49:53.549Z 00101_20111213T154913834Z_000000000000003 M02 |Code|2|A hammer may not be safe for this age
testProc2 Response:
[
"HAMMER",
"YOYO"
]
In the example we created a list of two toys to send to M and then got back a warning message and an array of two toys (echoed back but in upper case).
The code for “testdb” can be found at ./hub/divconq.core/src/divconq/test/TestDb.java. However, the part of importance to this topic is as follows:
// create a List
ListStruct toys = new ListStruct();
// prompt user for toys
System.out.println("Enter toys, one per line. Empty to finish.");
opt = scan.nextLine();
while (StringUtil.isNotBlank(opt)) {
// add the toy name to the List
toys.addItem(opt);
opt = scan.nextLine();
}
// create a query request as before, only give a parameter this time - the list
QueryRequest tp2 = new QueryRequest("testProc2", toys);
// then submit to the database queue as before
In M the parameter will show up in the variable “Params”. Since our parameter is a List then so Params will be. Params(n)=value. It could be Params(0 – n) or Params(1 – n) since our code is not written to care which starting index is given. Here what does M do with this:
; d local^dcConn("QUERY testProc2")
;
; pass in a Toy list - Params(1)="Truck", Params(2)="Paints", etc
; will present error if a toy in list is not allowed
; otherwise will process the list (names to upper)
;
testProc2 n tnum,toy
;
; validate your inputs, if toy list is not there do not continue
i $d(Params)<10 d errMsg^dcConn("Missing toy list") quit
;
; continue input validation, check for undesirable toys
f s tnum=$o(Params(tnum)) q:tnum="" d
. s toy=Params(tnum)
. s toy=$$toUpper^dcStrUtil(toy)
. i toy["GUN" d errMsg^dcConn("Gun not allowed in toy list") q
. i toy["CANDY" d errMsg^dcConn("Candy not allowed in toy list") q
. i toy["HAMMER" d warnMsg^dcConn("A hammer may not be safe for this age") q
;
; if any errors occured (Gun or Candy) then quit, don't even return data
i Errors quit
;
; if only warnings or no validation messages then proceed
w StartList
f s tnum=$o(Params(tnum)) q:tnum="" d
. s toy=Params(tnum)
. s toy=$$toUpper^dcStrUtil(toy)
. w ScalarStr_toy
w EndList
;
quit
Note that we have both potential error messages (d errMsg^dcConn) and potential warning messages (d warnMsg^dcConn). We triggered the warning in the example call above. If we get any errors at all (the variable "Errors" is a boolean flag - booleans in M are numbers 0 false and 1 true) we quit and do not return any data. Procedures are not required to return data if they encounter errors.
Just for fun lets try running that from the M prompt as well. You need parameters, so set those before the call. For example:
GTM>s Params(0)="gun"
GTM>s Params(1)="candy"
GTM>d local^dcConn("QUERY testProc2")
Data:
!
Messages:
[ { , Level: Error, Code: 1, Message: Gun not allowed in toy list} { , Level: Error, Code: 1, Message: Candy not allowed in toy list} ] !
GTM>
If the parameter was a Record then Params would have key value pairs such as Params("Age")=9. We'll get to see more of those structures in a future post.
To wrap up this first post on procedures, consider procedure testProc3 which is very similar. The error handling is really the only difference, check this out:
; validate your inputs, if toy list is not there do not continue i $d(Params)<10 d err^dcConn(90000) quit ; ; continue input validation, check for undesirable toys f s tnum=$o(Params(tnum)) q:tnum="" d . s toy=Params(tnum) . s utoy=$$toUpper^dcStrUtil(toy) . i (utoy["GUN")!(utoy["CANDY") d err^dcConn(90001,toy) q . i utoy["HAMMER" d warn^dcConn(90002,toy) q
We use error and warning codes instead of strings. This is one way that Localization works in DivConq. Given the code and translation parameters (e.g. "toy") we get back a localized string. Try running these examples:
GTM>d local^dcConn("QUERY testProc3")
Data:
!
Messages:
[ { , Level: Error, Code: 90001, Message: Toy not allowed: gun} { , Level: Error, Code: 90001, Message: Toy not allowed: candy} ] !
GTM>s ^Hub("Locale")="x-pig-latin"
GTM>d local^dcConn("QUERY testProc3")
Data:
!
Messages:
[ { , Level: Error, Code: 90001, Message: Oytay otnay allowedway: gun} { , Level: Error, Code: 90001, Message: Oytay otnay allowedway: candy} ] !
GTM>s ^Hub("Locale")="x-rtl-en"
GTM>d local^dcConn("QUERY testProc3")
Data:
!
Messages:
[ { , Level: Error, Code: 90001, Message: : gunallowed not Toy} { , Level: Error, Code: 90001, Message: : candyallowed not Toy} ] !
GTM>s ^Hub("Locale")="en_US"
GTM>d local^dcConn("QUERY testProc3")
Data:
!
Messages:
[ { , Level: Error, Code: 90001, Message: Toy not allowed: gun} { , Level: Error, Code: 90001, Message: Toy not allowed: candy} ] !
GTM>
Three Locales are supported. Typically the Locale comes from Java, we are only doing the set ^Hub("Locale") here because we do not have the same context as Java calls do, so we need to change the default Locale to make it work.
Recall from the "Getting Connected" post that in Java, running the testdb command, we can change locale and get the translated results:
7
Enter new locale: x-pig-latin
-----------------------------------------------
Test dcDb Connector Menu
-----------------------------------------------
0) Exit
1) Ping
2) Echo with user input
3) Echo with hardcoded international chars
4) Load stored proc results (testProc1)
5) Check validity of toy list (testProc2)
6) Check validity of toy list (testProc3)
7) Switch Locale
8) Toy Database Example
6
Enter toys, one per line. Empty to finish.
candy
hammer
...
testProc3 Error:
[
{
"Occur": "2011-12-13T16:31:39.215Z",
"Message": "Oytay ot\nay allowedway: candy",
"Level": "Error",
"Code": 90001
} ,
{
"Occur": "2011-12-13T16:31:39.217Z",
"Message": "Oytay aymay ot\nay ebay afesay: hammer",
"Level": "Warn",
"Code": 90002
}
]
That wraps up this part. In the next part we will go over a more detailed example working with globals.
Published on Tuesday December 13, 2011 at 10:44am