New post
Question
prashanth ponugoti · Oct 12, 2021

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

#HL7 #SQL #InterSystems IRIS

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
2
0 206
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
prashanth ponugoti · Oct 12, 2021

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

0 0
Marc Mundt · Oct 12, 2021

Does my sample code work in your local HS installation?

1 0
prashanth ponugoti · Oct 12, 2021

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
0 0
Marc Mundt · Oct 12, 2021

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
0 0
prashanth ponugoti · Oct 12, 2021

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

0 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
prashanth ponugoti · Oct 12, 2021

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

0 0
Robert Cemper · Oct 12, 2021

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
}
 

0 0
prashanth ponugoti · Oct 12, 2021

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.

0 0
prashanth ponugoti · Oct 12, 2021

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.

0 0
Robert Cemper · Oct 12, 2021

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.  

0 0
prashanth ponugoti · Oct 12, 2021

I can see that folders in workspace namespace tab.

not available in project tab

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