Query method in a class %Persistent using itself as parameters
Hi all,
I'm wondering if is possible to get the values of itself to run a query.
I want to create some query to find a value into a %Persistent class but each one use diferent values.
Class Kurro.MyClass Extends %Persistent { /// Key of process Property KeyProcess As %String(MAXLEN = ""); /// Specialist Property CodeSpecialist As %String; /// Provider Property CodeProvider As %String; /// Center Property CodeCenter As %String; /// Date Property Date As %TimeStamp; /// IdList Property IdList As %String; /// IdProcess Property IdProcess As %String; /// Duration Property Duration As %String; Query GetInfo(pObject AS Kurro.MyClass) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String") { SELECT IdList, IdProcess, Duration FROM Kurro.MyClass WHERE KeyProcess = :pObject.KeyProcess AND CodeSpecialist = :pObject.CodeSpecialist AND CodeProvider = :pObject.CodeProvider AND CodeCenter = :pObject.CodeCenter AND Date = :pObject.Date } }
Then I call it by this way:
set obj=##class(Kurro.MyClass).%New() set obj.KeyProcess="1033004-1#" set obj.CodeSpecialist = "surgery" set obj.CodeProvider = "PR002" set obj.CodeCenter = "CENTER-01" set obj.Date = $ZDATETIME($ZDATETIMEH("2021-04-30 15:45:00",3,1),3,1) set result = obj.GetInfoFunc(obj)
But I have the following error
%Message = "ERROR #5002: Error de cache: <OBJECT DISPATCH>zGetInfoFunc+5^Kurro.MyClass1 *Property 'KeyProcess' in class 'Kurro.MyClass' must be MultiDimensional "
It's weird, because this property is a simple %String param.
Then, I've tried to get itself values, and call using the same instance
Query GetInfo() As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String") { SELECT IdList, IdProcess, Duration FROM Kurro.MyClass WHERE KeyProcess = KeyProcess AND CodeSpecialist = CodeSpecialist AND CodeProvider = CodeProvider AND CodeCenter = CodeCenter AND Date = Date }
and call it using
set result = obj.GetInfoFunc()
But is doesn't work
%Message = "ERROR #5002: Error de cache: <PARAMETER>zGetInfo+1^Kurro.MyClass.1"
is it possible to get the values of the object to use as parameter of the query?
Best regards,
Kurro
Product version: HealthShare 2017.2
$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U) Thu Apr 12 2018 10:02:23 EDT
I don't think so. SQL must work from xDBC context and objects don't exist there.
How would you call your function from Management Portal or any SQL Editor?
Perhaps pass the object ID into something like:
It's a good idea, but I haven't the ID of the row.
My idea was use the object as a parameter to pass a lot of values, and the query reads these values, instead of pass one by one the params.
I've changed my query with all of the parameters that I need to run the query
It works, but I need to pass all params. I wanted to create a class to pass all parameters, like a c# method, and try to use itself.
Thanks for all mate.
¡Hola @Kurro Lopez !
It's a really interresting issue.
#2) your second example can't work by principal as you don't pass a call parameter.
#1) It is not foreseen to use object properties as host-variables in SQL
The code fails in the generated Execute method.
$GET for oblect-properties is just not implemented. It is definded by the class and always there,
But it is required and makes sense for multidimensional properties !
This is valid for ObjectScript as such and not related to SQL.
But in handwritten code you can use $g(NOTHING,pObject.KeyProcess)
The generator just doesn't do it.
Workaround #1: directly fiddle in the generated code . Not recommended
ClassMethod FillProp(pObject As Kurro.MyClass) As %Boolean [ SqlName = FILLP, SqlProc ]
Query GetInfoRcc(objid As %Integer) As %SQLQuery(CONTAINID = 0, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String")
Workaround #2: move your properties with a helper function into local variables
and adjust your query to those variables
example helper (with obj or id):
{
if '$isObject(pObject) set obj=##class(Kurro.MyClass).%OpenId(pObject)
else set obj=pObject
set %Kurro("kp")=obj.KeyProcess
,%Kurro("sp")= obj.CodeSpecialist
,%Kurro("pr")= obj.CodeProvider
,%Kurro("cs")= obj.CodeCenter
,%Kurro("dt")= obj.Date
quit 1
}
example query;
{
SELECT IdList, IdProcess, Duration
FROM Kurro.MyClass
WHERE KeyProcess = :%Kurro("kp")
AND CodeSpecialist = :%Kurro("sp")
AND CodeProvider = :%Kurro("pr")
AND CodeCenter = :%Kurro("cs")
AND "Date" = :%Kurro("dt")
AND FILLP(:objid) = 1
}
works as expected.
Sorry for being late. I was interrupted by .... 15 times at least with nonsense
You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used.
You can do it differently:
Great!!!!
The problem was calling the Query using the "func" function, calling directly it works.
good point.
Thanks for all,
Kurro Lopez
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue