New post
AllAccepted answers
Marc Mundt · Oct 12, 2021

I created a simple inbound adapter and a simple business service that uses it, and the &sql() call succeeds for me. It would be good to check the event log and see if any additional error information got logged.

Class Example.InboundAdapter Extends Ens.InboundAdapter [ ProcedureBlock ]
{

Method OnTask() As %Status
{
        set suspendedCount=-1
        &sql(SELECT count(ID) into :suspendedCount FROM Ens.MessageHeader where TargetQueueName not like '_S%' and TargetQueueName not like 'ENS%' and Status='5')
        
        // log what we got back
        set ^ztest($now(),"SQLCODE") = SQLCODE
        set ^ztest($now(),"suspendedCount") = suspendedCount
        
        quit 1
}

}
Class Example.Service Extends Ens.BusinessService
{

/// The type of adapter used to communicate with external systems
Parameter ADAPTER = "Example.InboundAdapter";

}
Marc Mundt · Oct 1, 2021

You can query Ens_Config.Item on ClassName:

SELECT ID, Category FROM Ens_Config.Item WHERE ClassName = "EnsLib.HL7.Service.FileService"

But you'll want to keep in mind that a production can have multiple items that use the same class, so this query could return multiple rows all with different categories.

Category applies to the item as it is configured in the production, not to the underlying class it uses.

Marc Mundt · Sep 30, 2021

This is a great question. In your example you haven't created an index for name. But you can still find entries by name if you iterate through all of the entries.

You can do that like this:

set searchName="Xiang"

set id=""  
for {
    set id=$ORDER(^Data(id))  
    quit:id=""
    if ^Data(id,"name")=searchName {
        write ^Data(id,"surname"),":",^Data(id,"name"),":",^Data(id,"birthday"),!
    }
}

This gave me the output:

Kevin:Xiang:36411

The key command in this code is $ORDER. You'll definitely want to review the $ORDER documentation to understand what it is doing.

Another convenient utility is zwrite, which will allow you to see all of the entries you inserted in ^Data.

This is what it looks like if I run zwrite in the terminal:

DEMO> zwrite ^Data
^Data(1,"birthday")=44136
^Data(1,"name")="Murray"
^Data(1,"surname")="Nataliya"
^Data(2,"birthday")=33465
^Data(2,"name")="Black"
^Data(2,"surname")="Samantha"
^Data(3,"birthday")=49183
^Data(3,"name")="Lee"
^Data(3,"surname")="Liza"
^Data(4,"birthday")=44951
^Data(4,"name")="Ravazzolo"
^Data(4,"surname")="Quigley"
^Data(5,"birthday")=39369
^Data(5,"name")="Faust"
^Data(5,"surname")="Zoe"
^Data(6,"birthday")=31477
^Data(6,"name")="Gomez"
^Data(6,"surname")="Maria"
^Data(7,"birthday")=31574
^Data(7,"name")="Eagleman"
^Data(7,"surname")="Samantha"
^Data(8,"birthday")=62100
^Data(8,"name")="Ragon"
^Data(8,"surname")="Jose"
^Data(9,"birthday")=35228
^Data(9,"name")="Lee"
^Data(9,"surname")="Pam"
^Data(10,"birthday")=47920
^Data(10,"name")="Evans"
^Data(10,"surname")="Will"
^Data(11,"birthday")=63253
^Data(11,"name")="Rogers"
^Data(11,"surname")="Uma"
^Data(12,"birthday")=55410
^Data(12,"name")="Novello"
^Data(12,"surname")="Terry"
^Data(13,"birthday")=64002
^Data(13,"name")="Perez"
^Data(13,"surname")="Terry"
^Data(14,"birthday")=39783
^Data(14,"name")="Uberoth"
^Data(14,"surname")="Wolfgang"
^Data(15,"birthday")=46360
^Data(15,"name")="Zubik"
^Data(15,"surname")="Phyllis"
^Data(16,"birthday")=33271
^Data(16,"name")="Yang"
^Data(16,"surname")="Andrew"
^Data(17,"birthday")=40437
^Data(17,"name")="Zampitello"
^Data(17,"surname")="Martin"
^Data(18,"birthday")=46985
^Data(18,"name")="Orwell"
^Data(18,"surname")="Ralph"
^Data(19,"birthday")=42733
^Data(19,"name")="Waterman"
^Data(19,"surname")="Diane"
^Data(20,"birthday")=45346
^Data(20,"name")="Gold"
^Data(20,"surname")="Imelda"
^Data(21,"birthday")=37976
^Data(21,"name")="Frost"
^Data(21,"surname")="Richard"
^Data(22,"birthday")=41319
^Data(22,"name")="Koenig"
^Data(22,"surname")="Robert"
^Data(23,"birthday")=40915
^Data(23,"name")="Minichillo"
^Data(23,"surname")="Molly"
^Data(24,"birthday")=30632
^Data(24,"name")="Yang"
^Data(24,"surname")="Neil"
^Data(25,"birthday")=59938
^Data(25,"name")="Wilson"
^Data(25,"surname")="Wolfgang"
^Data(26,"birthday")=59897
^Data(26,"name")="Zampitello"
^Data(26,"surname")="Jules"
^Data(27,"birthday")=37417
^Data(27,"name")="Avery"
^Data(27,"surname")="Patricia"
^Data(28,"birthday")=46217
^Data(28,"name")="Edwards"
^Data(28,"surname")="Angelo"
^Data(29,"birthday")=64401
^Data(29,"name")="Humby"
^Data(29,"surname")="Clint"
^Data(30,"birthday")=44599
^Data(30,"name")="Nichols"
^Data(30,"surname")="Joe"
^Data(31,"birthday")=36633
^Data(31,"name")="Anderson"
^Data(31,"surname")="Zeke"
^Data(32,"birthday")=54905
^Data(32,"name")="Isaksen"
^Data(32,"surname")="John"
^Data(33,"birthday")=39307
^Data(33,"name")="Hanson"
^Data(33,"surname")="Heloisa"
^Data(34,"birthday")=55578
^Data(34,"name")="Jenkins"
^Data(34,"surname")="Filomena"
^Data(35,"birthday")=39818
^Data(35,"name")="Orlin"
^Data(35,"surname")="Nellie"
^Data(36,"birthday")=61299
^Data(36,"name")="Allen"
^Data(36,"surname")="Rob"
^Data(37,"birthday")=46647
^Data(37,"name")="Koivu"
^Data(37,"surname")="Mario"
^Data(38,"birthday")=61880
^Data(38,"name")="Winters"
^Data(38,"surname")="Al"
^Data(39,"birthday")=59597
^Data(39,"name")="Yoders"
^Data(39,"surname")="Nellie"
^Data(40,"birthday")=32317
^Data(40,"name")="Yoders"
^Data(40,"surname")="Stavros"
^Data(41,"birthday")=65110
^Data(41,"name")="Gallant"
^Data(41,"surname")="Umberto"
^Data(42,"birthday")=64331
^Data(42,"name")="Huff"
^Data(42,"surname")="Edward"
^Data(43,"birthday")=49679
^Data(43,"name")="Xavier"
^Data(43,"surname")="Barbara"
^Data(44,"birthday")=62300
^Data(44,"name")="Xander"
^Data(44,"surname")="Natasha"
^Data(45,"birthday")=38052
^Data(45,"name")="Quine"
^Data(45,"surname")="Brian"
^Data(46,"birthday")=45772
^Data(46,"name")="Mara"
^Data(46,"surname")="Nataliya"
^Data(47,"birthday")=61894
^Data(47,"name")="Lopez"
^Data(47,"surname")="Sally"
^Data(48,"birthday")=47017
^Data(48,"name")="Solomon"
^Data(48,"surname")="Jocelyn"
^Data(49,"birthday")=65868
^Data(49,"name")="Quigley"
^Data(49,"surname")="Ted"
^Data(50,"birthday")=46776
^Data(50,"name")="Gallant"
^Data(50,"surname")="Angelo"
^Data(51,"birthday")=45919
^Data(51,"name")="Rotterman"
^Data(51,"surname")="Juanita"
^Data(52,"birthday")=49368
^Data(52,"name")="Kratzmann"
^Data(52,"surname")="Neil"
^Data(53,"birthday")=36273
^Data(53,"name")="Lennon"
^Data(53,"surname")="Kirsten"
^Data(54,"birthday")=57894
^Data(54,"name")="Dunlap"
^Data(54,"surname")="Gertrude"
^Data(55,"birthday")=52763
^Data(55,"name")="Yakulis"
^Data(55,"surname")="Maria"
^Data(56,"birthday")=45293
^Data(56,"name")="Quigley"
^Data(56,"surname")="Wilma"
^Data(57,"birthday")=30457
^Data(57,"name")="Paladino"
^Data(57,"surname")="Jeff"
^Data(58,"birthday")=44654
^Data(58,"name")="Jafari"
^Data(58,"surname")="Patrick"
^Data(59,"birthday")=47310
^Data(59,"name")="Chadwick"
^Data(59,"surname")="Wolfgang"
^Data(60,"birthday")=32840
^Data(60,"name")="Orwell"
^Data(60,"surname")="Andrew"
^Data(61,"birthday")=46156
^Data(61,"name")="Quilty"
^Data(61,"surname")="Josephine"
^Data(62,"birthday")=63361
^Data(62,"name")="Zimmerman"
^Data(62,"surname")="Ted"
^Data(63,"birthday")=46049
^Data(63,"name")="Adams"
^Data(63,"surname")="Valery"
^Data(64,"birthday")=40616
^Data(64,"name")="Jafari"
^Data(64,"surname")="Jules"
^Data(65,"birthday")=31115
^Data(65,"name")="McCoy"
^Data(65,"surname")="Laura"
^Data(66,"birthday")=34784
^Data(66,"name")="Quilty"
^Data(66,"surname")="Elvis"
^Data(67,"birthday")=54016
^Data(67,"name")="Ingleman"
^Data(67,"surname")="Greta"
^Data(68,"birthday")=36432
^Data(68,"name")="Walker"
^Data(68,"surname")="Dick"
^Data(69,"birthday")=60717
^Data(69,"name")="Zampitello"
^Data(69,"surname")="Jose"
^Data(70,"birthday")=50594
^Data(70,"name")="DeSantis"
^Data(70,"surname")="Brendan"
^Data(71,"birthday")=33822
^Data(71,"name")="Anderson"
^Data(71,"surname")="Bart"
^Data(72,"birthday")=41826
^Data(72,"name")="Fripp"
^Data(72,"surname")="Molly"
^Data(73,"birthday")=30840
^Data(73,"name")="Ironhorse"
^Data(73,"surname")="Diane"
^Data(74,"birthday")=59455
^Data(74,"name")="Jaynes"
^Data(74,"surname")="Al"
^Data(75,"birthday")=42430
^Data(75,"name")="Chesire"
^Data(75,"surname")="Emily"
^Data(76,"birthday")=34392
^Data(76,"name")="Jenkins"
^Data(76,"surname")="Pam"
^Data(77,"birthday")=45341
^Data(77,"name")="Hanson"
^Data(77,"surname")="Sally"
^Data(78,"birthday")=54498
^Data(78,"name")="Nichols"
^Data(78,"surname")="Uma"
^Data(79,"birthday")=38272
^Data(79,"name")="Goncharuk"
^Data(79,"surname")="Zelda"
^Data(80,"birthday")=55832
^Data(80,"name")="Avery"
^Data(80,"surname")="Greta"
^Data(81,"birthday")=45135
^Data(81,"name")="Nagel"
^Data(81,"surname")="Michael"
^Data(82,"birthday")=42670
^Data(82,"name")="Quince"
^Data(82,"surname")="Terry"
^Data(83,"birthday")=35127
^Data(83,"name")="Jackson"
^Data(83,"surname")="Susan"
^Data(84,"birthday")=44011
^Data(84,"name")="Marks"
^Data(84,"surname")="Milhouse"
^Data(85,"birthday")=47140
^Data(85,"name")="Sato"
^Data(85,"surname")="Greta"
^Data(86,"birthday")=57187
^Data(86,"name")="Ingersol"
^Data(86,"surname")="Ted"
^Data(87,"birthday")=35111
^Data(87,"name")="West"
^Data(87,"surname")="Wilma"
^Data(88,"birthday")=60618
^Data(88,"name")="Braam"
^Data(88,"surname")="Greta"
^Data(89,"birthday")=45237
^Data(89,"name")="Klausner"
^Data(89,"surname")="Kyra"
^Data(90,"birthday")=51816
^Data(90,"name")="Eastman"
^Data(90,"surname")="Andrew"
^Data(91,"birthday")=63181
^Data(91,"name")="Nathanson"
^Data(91,"surname")="Robert"
^Data(92,"birthday")=56444
^Data(92,"name")="Newton"
^Data(92,"surname")="Mo"
^Data(93,"birthday")=33670
^Data(93,"name")="Xavier"
^Data(93,"surname")="Brenda"
^Data(94,"birthday")=60262
^Data(94,"name")="Ironhorse"
^Data(94,"surname")="Josephine"
^Data(95,"birthday")=51967
^Data(95,"name")="Klingman"
^Data(95,"surname")="Ralph"
^Data(96,"birthday")=40383
^Data(96,"name")="Donaldson"
^Data(96,"surname")="Stuart"
^Data(97,"birthday")=50563
^Data(97,"name")="Wells"
^Data(97,"surname")="Clint"
^Data(98,"birthday")=52105
^Data(98,"name")="Leiberman"
^Data(98,"surname")="Rhonda"
^Data(99,"birthday")=36713
^Data(99,"name")="Lepon"
^Data(99,"surname")="Laura"
^Data(100,"birthday")=36411
^Data(100,"name")="Xiang"
^Data(100,"surname")="Kevin"

Next, if you want to add an index on name, you can add entries for each name with a reference to every id that has that name. In the global, it could look like this:

set ^DataIndex("name","Xiang",100)=""
set ^DataIndex("name","Xiang",123)=""

You could then use $ORDER again to iterate through all entries with the name of Xiang:

set id=""
for {
   set id=$ORDER(^DataIndex("name","Xiang",id))
   quit:id=""
   write "ID:",id," has the name Xiang",!
}

Of course, this is all very manual. Modern applications usually define classes that represent a SQL table. The table/class will specify an index on a column, and Caché will create and maintain that index automatically.

Marc Mundt · Sep 29, 2021

Yep, RawContent is potentially truncated and isn't meant for accessing the full message.

You can use the OutputTo* methods to get the whole message. OutputToLibraryStream is going to be the best option because a stream object can be of unlimited size. OutputToString will work, but only for messages that are smaller than the maximum string size (around 3 megabytes, assuming you have long strings enabled in the Caché config. If not, then 32k).

Marc Mundt · Aug 31, 2021

You can use the %Dictionary classes to get details of the class and methods:

USER>do $System.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>SELECT FormalSpec FROM %Dictionary.MethodDefinition WHERE parent='My.Test.Class' AND Name='TestMethod'
1.      SELECT FormalSpec FROM %Dictionary.MethodDefinition WHERE parent='My.Test.Class' AND Name='TestMethod'
 
FormalSpec
param1:%String,param2:%Numeric
 
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0033s/321/1482/0ms
          execute time(s)/globals/cmds/disk: 0.0006s/3/385/0ms
                          cached query class: %sqlcq.USER.cls14
---------------------------------------------------------------------------
[SQL]USER>>