· Oct 12, 2021

&sql(.....) not working in and returns SQLCODE -400 if try to execute in non persistent cls , -30 in other persist cls

I have written below method inbound adapter myAdapter Extends Ens.InboundAdapter [ ProcedureBlock ] returns -400

If i have written in any other Extends %Persistent  cls , always returning -30 which means Table not found

ClassMethod FetchMsgCount() As %Integer
    set msgCount=-1
&sql(SELECT count(ID) INTO :msgCount FROM Ens.MessageHeader)
If SQLCODE=0 Quit msgCount

Could you please some one tell me where should write this class method? What I am doing wrong?



Product version: IRIS 2020.1
Discussion (18)1
Log in or sign up to continue

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";


Thanks Marc , you are correct. This code was written long back and available in live . I need to do some enhancements on top of the existing code. I have exported from live and deployed in my local HS setup for development.

I am seeing this error. Live still it is working fine. 

Is there any problem with my local HS installation?



Thanks Marc,

no luck , see the log below for exampleBS


Your last finding signals to me that you get blocked by [ProcedureBlock]

either try using %suspendedCount instead of suspendedCount

Method OnTask() As %Status [ PublicList = suspendedCount,SQLCODE ] 
{    //implementation }

or put it into a PublicList together with SQLCODE

Thanks Robert ,

my version is:

  IRIS for Windows (x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:14:33 EDT [HealthConnect:2.1.0]

I literally struck from last two week only because of this issue. Could you please help me.

I need to execute below query in inbound adapter.

SELECT count(ID) into :suspendedCount FROM Ens.MessageHeader 

Marc mentioned in previous post His example business service is working fine for the same requirement.

What wrong with my HS version? or please show me the way forward.

Thanks a lot


I think this should work if your namespace is enabled for Ensemble / Interoperability

Check this in SMP:
SELECT count(IDFROM Ens.MessageHeader

ClassMethod FetchMsgCount() As %Integer [ PublicList = msgCount,SQLCODE ] 
    set msgCount=-1
&sql(SELECT count(IDINTO :msgCount FROM Ens.MessageHeader)
If SQLCODE=0 Quit msgCount

or just simpler

ClassMethod FetchMsgCount() As %Integer  [ ProcedureBlock = 0 ]
    set msgCount=-1
&sql(SELECT count(IDINTO :msgCount FROM Ens.MessageHeader)
If SQLCODE=0 Quit msgCount