New post
Question
Kurro Lopez · Apr 28, 2021

Query method in a class %Persistent using itself as parameters

#SQL #Caché #Ensemble

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
0
0 176
Discussion (6)2
Log in or sign up to continue
Eduard Lebedyuk · Apr 28, 2021

is it possible to get the values of the object to use as parameter of the query?

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?

0 0
Timothy Leavitt · Apr 28, 2021

Perhaps pass the object ID into something like:

Query GetInfo(refId As %String) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String")
{
    SELECT list.IdList, list.IdProcess, list.Duration
    FROM Kurro.MyClass list
    JOIN Kurro.MyClass ref on ref.ID = :refId
    AND ref.KeyProcess = list.KeyProcess
    AND ref.CodeSpecialist = list.CodeSpecialist
    AND ref.CodeProvider = list.CodeProvider
    AND ref.CodeCenter = list.CodeCenter
    AND ref.Date = list.Date
}
0 0
Kurro Lopez · Apr 28, 2021

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

Query GetInfo(pKeyProcess As %String, pCodeSpecialist As %String, pCodeProvider As %String, pCodeCenter As %String, pDate as %TimeStamp) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String")
{
    SELECT IdList, IdProcess, Duration
    FROM Kurro.MyClass
    WHERE KeyProcess = :pKeyProcess
    AND CodeSpecialist = :pCodeSpecialist
    AND CodeProvider = :pCodeProvider
    AND CodeCenter = :pCodeCenter
    AND Date = :pDate
}

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.

0 0
Robert Cemper · Apr 28, 2021

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

set tResult = tStatement.%Execute($g(pObject.KeyProcess),$g(pObject.CodeSpecialist),$g(pObject.CodeProvider),$g(pObject.CodeCenter),$g(pObject.Date))

$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
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):
ClassMethod FillProp(pObject As Kurro.MyClass) As %Boolean [ SqlName = FILLP, SqlProc ]
{
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;
Query GetInfoRcc(objid As %Integer) As %SQLQuery(CONTAINID = 0, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String")
{
    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

 

1 0
Vitaliy Serdtsev · Apr 29, 2021

You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used.

You can do it differently:

Query GetInfo(pObject AS Kurro.MyClass) As %SQLQuery(CONTAINID = 1, ROWSPEC = "IdList:%String,IdProcess:%String,Duration:%String") [ SqlProc ]
{
    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
}


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 st=##class(%SQL.Statement).%New()
set sc=st.%PrepareClassQuery("Kurro.MyClass","GetInfo")
  if $$$ISERR(sc) {write "%PrepareClassQuery failed:" do $System.Status.DisplayError(sc) quit}
set result=st.%Execute(obj)
do result.%Display()
2 0
Kurro Lopez · Apr 29, 2021

Great!!!!

The problem was calling the Query using the "func" function, calling directly it works.

good point.

Thanks for all,
Kurro Lopez

0 0