&sql(.....) not working in and returns SQLCODE -400 if try to execute in

Question

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
Quit SQLCODE
}

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

Thanks

PRASHANTH

#HL7 #Caché #Global Masters #HealthShare #Online Learning

Product version: IRIS 2020.1

1 0 18 134

Marc Mundt · Oct 12

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

}

Accepted answer

prashanth ponugoti · Oct 12

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,

Prashanth

Marc Mundt · Oct 12

Does my sample code work in your local HS installation?

Accepted answer

prashanth ponugoti · Oct 12

I have tested , not working . Could you please tell me where can i see the log in management portal for below 2 statements

// log what we got back
        set ^ztest($now(),"SQLCODE") = SQLCODE
        set ^ztest($now(),"suspendedCount") = suspendedCount

Marc Mundt · Oct 12

In the management portal you can view that under System Explorer >> Globals. From terminal you can use zwrite. In both cases, make sure you're in the correct namespace:

zwrite ^ztest

prashanth ponugoti · Oct 12

Thanks Marc,

no luck , see the log below for exampleBS

^ztest("66029,63544.417241","SQLCODE")=-400
^ztest("66029,63544.41725","suspendedCount")=-1
^ztest("66029,63549.440962","SQLCODE")=-400
^ztest("66029,63549.440972","suspendedCount")=-1
^ztest("66029,63554.469506","SQLCODE")=-400
^ztest("66029,63554.469512","suspendedCount")=-1
^ztest("66029,63559.484237","SQLCODE")=-400
^ztest("66029,63559.484244","suspendedCount")=-1
^ztest("66029,63564.510576","SQLCODE")=-400
^ztest("66029,63564.510586","suspendedCount")=-1
^ztest("66029,63569.551258","SQLCODE")=-400
^ztest("66029,63569.551266","suspendedCount")=-1
^ztest("66029,63574.596674","SQLCODE")=-400
^ztest("66029,63574.596684","suspendedCount")=-1
^ztest("66029,63579.611594","SQLCODE")=-400
^ztest("66029,63579.611599","suspendedCount")=-1

Marc Mundt · Oct 12

You can log %msg to get more information about the error:

set ^ztest($now(),"%msg") = $G(%msg)

Accepted answer

Robert Cemper · Oct 12

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

https://docs.intersystems.com/iris20211/csp/docbook/DocBook.UI.Page.cls?KEY=ROBJ_method_publiclist

Accepted answer

prashanth ponugoti · Oct 12

thank u

prashanth ponugoti · Oct 12

thank u

prashanth ponugoti · Oct 12

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

Prashanth

Robert Cemper · Oct 12

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

Check this in SMP:
SELECT count(ID) FROM Ens.MessageHeader

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

or just simpler

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

prashanth ponugoti · Oct 12

Thanks Rabert for your presious time ,

Unfortunately it is not working .

here problem is , it is working fine with out any workaround in Live . Getting this error in Dev (Laptop) only. Is this any permission issue?

same code is giving -30 in other cls which is extends Persistent.

prashanth ponugoti · Oct 12

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

this code is giving always -30

which means table does not exist.

Robert Cemper · Oct 12

which means table does not exist.
SO your namespace is not setup for Interoperability aka Ensemble

as you use Studio jus look for some Ens* classes

if you see nothing of them,
then the setup of your namespace is just wrong

You have to create it again or change your configeratiion manually.

prashanth ponugoti · Oct 12

I can see that folders in workspace namespace tab.

not available in project tab

Robert Cemper · Oct 12

There's a mismatch. You see the code but no data at runtime.
Project is always just a subset of Namespace

Accepted answer

prashanth ponugoti · Oct 13

Thanks Robert,

I extended my Namespace with %sys. Due to this I faced all the above issues. As per your suggestion, I have created new namespace extending HSSYS. Now all issues got resolved.

Thank you so much for your help.

prashanth ponugoti · Oct 13

Thanks Robert,

I extended my Namespace with %sys. Due to this I faced all the above issues. As per your suggestion, I have created new namespace extending HSSYS. Now all issues got resolved.

Thank you so much for your help.

Accepted answer

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

Social networks

InterSystems resources