&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 otherExtends %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
Comments
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,
Prashanth
Does my sample code work in your local HS installation?
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") = suspendedCountIn 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
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
You can log %msg to get more information about the error:
set ^ztest($now(),"%msg") = $G(%msg)
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
thank u
thank u
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
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
}
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.
.png)
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.
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
.png)
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.
I can see that folders in workspace namespace tab.
.png)
not available in project tab
.png)
There's a mismatch. You see the code but no data at runtime.
Project is always just a subset of Namespace
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.
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.