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

Question

Question

prashanth ponugoti · Oct 12, 2021

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

Product version: IRIS 2020.1

Discussion (18)1

Log in or sign up to continue

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

}

0 0

Marc Mundt · Oct 12, 2021

Does my sample code work in your local HS installation?

1 0

Marc Mundt · Oct 12, 2021

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

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

0 0

Robert Cemper · Oct 12, 2021

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

0 0

Robert Cemper · Oct 12, 2021

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

0 0

prashanth ponugoti · Oct 13, 2021

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.

0 0

score 0 · Answer 1 · 2021-10-12T12:41:21-04:00

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

score 0 · Answer 2 · 2021-10-12T13:21:54-04:00

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

score 0 · Answer 3 · 2021-10-12T13:27:28-04:00

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

score 0 · Answer 4 · 2021-10-12T13:43:33-04:00

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

score 0 · Answer 5 · 2021-10-12T14:04:22-04:00

prashanth ponugoti · Oct 12, 2021

thank u

0 0

score 0 · Answer 6 · 2021-10-12T14:04:46-04:00

prashanth ponugoti · Oct 12, 2021

thank u

0 0

score 0 · Answer 7 · 2021-10-12T14:25:58-04:00

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

score 0 · Answer 8 · 2021-10-12T14:48:00-04:00

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
}

score 0 · Answer 9 · 2021-10-12T15:35:28-04:00

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.

score 0 · Answer 10 · 2021-10-12T15:50:03-04:00

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.

score 0 · Answer 11 · 2021-10-12T16:18:23-04:00

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.

score 0 · Answer 12 · 2021-10-12T16:39:11-04:00

prashanth ponugoti · Oct 12, 2021

I can see that folders in workspace namespace tab.

not available in project tab

0 0

score 0 · Answer 13 · 2021-10-13T04:25:43-04:00

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.