[Caché] Help with SQL Dynamic Query

[Pmkadow]

 

Hi, I am having a little trouble with an SQL Dynamic Query.

Normally I can go through these steps and execute my Dynamic Query:

 

 set MyQuery="SELECT * FROM Myclass"    

  set ResultSet=##class(%ResultSet).%New("%DynamicQuery:SQL")

  set status=ResultSet.Prepare(MyQuery)

  set status=ResultSet.Execute()

 While ResultSet.Next() {

  set Id=ResultSet.Data("ID")

           Set oref=##class(MyClass).%OpenId(Id)

           ----then do what I need to do ------

 }

But now I have a class that has three subscripts (siteId, patientId and documentId)

and what I get back is a bit different.

 

 D $system.OBJ.Dump(ResultSet)

 +----------------- general information ---------------

 |      oref value: 2

 |      class name: %Library.ResultSet

 |             OID: ""

 | reference count: 2 (system)

 +----------------- attribute values ------------------

 |           %Message = ""

 |          %Metadata = ""

 |      (%NextColumn) = ""

 |           %Objects = (("ID","tiu.ClinicalDocumentEvent","%QuickLoad^tiu.ClinicalDocumentEvent.T1(%rowid,%nolock,0,0)"))

 |        %Parameters = ""

 |     %PrivateTables =

 |          %RowCount = ""

 |     (%RuntimeMode) = ""

 |           %SQLCode = ""

 |            (%rsmd) = ""

 |            (AtEnd) = 0

 |          ClassName = "%Library.DynamicQuery"

 |       (ColExtInfo) = ""

 |         (ColIndex) = 7

 |      (ColIndex(1)) = ID

 |      (ColIndex(2)) = documentContent

 |      (ColIndex(3)) = documentId

 |      (ColIndex(4)) = documentSubject

 |      (ColIndex(5)) = documentTitle

 |      (ColIndex(6)) = patientId

 |      (ColIndex(7)) = siteId

 |          (ColInfo) = (("ID",10,"ID"),("documentContent",10,"documentContent"),("documentId",10,"documentId"),("documentSubject",10,"documentSubject"),("documentTitle",10,"documentTitle"),("patientId",10,"patientId"),("siteId",10,"siteId"))

 |       (HasExtInfo) = 0

 |          (HasInfo) = 1

 |           (IdInfo) = (1,"tiu.ClinicalDocumentEvent")

 |         (IsOpened) = 1

 |       (NextColumn) = 0

 |        (ParamInfo) = ""

 CacheSql239(QHandle) = X

 |          QueryName = "SQL"

 |        RuntimeMode = ""

 |    (StatementType) = ""

 +----------------- swizzled references ---------------

 |              %rsmd = 3@%ResultSet.MetaData

 +-----------------------------------------------------

 

Can someone help me with the syntax of obtaining the ID to ultimately get the OREF?

 

-thanks so much

-Paul Kadow

[Sukesh Hoogan]

Paul

a) If your IdKey is a composite of the three (siteId,
patientId,documentId) ,the following

Set vID=ResultSet.Data("ID")

should give you

vID="siteId||patientId||documentId"

and this should open the relevant instance.

b) You can post the index definitions of your class.

Regards
Sukesh Hoogan
Bombay, India
- Enterprise Resource Planning
- Business Intelligence
- Financial Accounting
- Offshore Development

> (("ID","tiu.ClinicalDocumentEvent","%QuickLoad^tiu.ClinicalDocumentEvent..T1(%rowid,%nolock,0,0)"))

> | %rsmd = 3@%ResultSet.MetaData <mailto:3@%ResultSet.MetaData>

[Enrico Parisi]

If you are using a recent (I guess from 2007 on) version of Caché you
can do something like:

SAMPLES>s rs=##class(%ResultSet.SQL).%Prepare("select * from sample.person")

SAMPLES>w rs.%Next()
1
SAMPLES>w rs.name
Koenig,Greta M.
SAMPLES>

This will make your life easier, no need to OpenId, just use the rs and
"do what you need to do" :)
Your code will look somenting like:

set MyQuery="SELECT * FROM Myclass"

set ResultSet=##class(%ResultSet.SQL).%Prepare(MyQuery,.error)
; check error here
While ResultSet.Next() {
Set oref=ResultSet ; this is just to give you the idea!

----then do what I need to do ------
}

HTH

Enrico

> | %rsmd = 3@%ResultSet.MetaData <mailto:3@%ResultSet.MetaData>

[Enrico Parisi]

Sorry, the sample is not complete/accurate! :)

SAMPLES>s rs=##class(%ResultSet.SQL).%Prepare("select %ID as id, * from

sample.person")

SAMPLES>w rs.%Next()
1
SAMPLES>w rs.name
Koenig,Greta M.

SAMPLES>w rs.id
2...@Sample.Person
SAMPLES>

So, your code may become:

set MyQuery="SELECT %ID as id, * FROM Myclass"

set ResultSet=##class(%ResultSet.SQL).%Prepare(MyQuery,.error)
; check error here
While ResultSet.Next() {

Set oref=ResultSet.id

----then do what I need to do ------
}

Neat, isn't it? :)

HTH
Enrico

[Pmkadow]

Sukesh - thanks for your help, your comments are always helpful!

 

Enrico - I understand I can just write the items returned from %ResultSet,

but I need the OREF to call an instance method, that is why I am interested in opening the object.

 

-Paul Kadow

[Enrico Parisi]

Hi Paul,

please note that you do have an instance of the object in the oref variable:

Set oref=ResultSet.id

No need to "manually" open it, just use it, for example to call your
instance method like:

Set sc=oref.MyInstanceMethod()

Or, if you prefer all in one line like:

Set sc=ResultSet.id.MyInstanceMethod()

What's wrong with this?

HTH
Enrico

Pmkadow wrote:
> Sukesh - thanks for your help, your comments are always helpful!
>
> Enrico - I understand I can just write the items returned from %ResultSet,
> but I need the OREF to call an instance method, that is why I am
> interested in opening the object.
>
> -Paul Kadow
>
>
>
> On Tue, Nov 3, 2009 at 11:34 AM, Enrico Parisi <par...@gaivota.com
> <mailto:par...@gaivota.com>> wrote:
>
>
> Sorry, the sample is not complete/accurate! :)
>
> SAMPLES>s rs=##class(%ResultSet.SQL).%Prepare("select %ID as id, * from
> sample.person")
>
> SAMPLES>w rs.%Next()
> 1

> SAMPLES>w rs.name <http://rs.name/>
> Koenig,Greta M.
> SAMPLES>w rs.id <http://rs.id/>

> 2...@Sample.Person
> SAMPLES>
>
> So, your code may become:
>
> set MyQuery="SELECT %ID as id, * FROM Myclass"
> set ResultSet=##class(%ResultSet.SQL).%Prepare(MyQuery,.error)
> ; check error here
> While ResultSet.Next() {
> Set oref=ResultSet.id
> ----then do what I need to do ------
> }
>
> Neat, isn't it? :)
>
> HTH
> Enrico
>
>
> Enrico Parisi wrote:
> > If you are using a recent (I guess from 2007 on) version of Caché you
> > can do something like:
> >
> > SAMPLES>s rs=##class(%ResultSet.SQL).%Prepare("select * from
> > sample.person")
> >
> > SAMPLES>w rs.%Next()
> > 1

> > SAMPLES>w rs.name <http://rs.name/>

> >> <mailto:3@ <mailto:3@>%ResultSet.MetaData>

[Keith Avery]

I'm confused. Just because the ID returned in the result set is complex doesn't mean you don't still use it to open the object and get your OREF, does it?

-Keith

[Enrico Parisi]

This is %Resultset.SQL class, not the "old" %Library.ResultSet, the new
class (introduced around 2007.1, if I recall it correctly) behave
differently in various aspects, I don't know all the magic and
unfortunately it is not (yet?) well documented.

Is anyone from ISC Documentation listening? :)

This resultset is more "object aware" (oriented?), when the query select
%ID it returns the OREF of the corresponding row/instance.
You can also "follow" object references without the need to specify it
in the select clause (i.e. MyProp->OtherProp) just using dot syntax
(i.e. rs.Spouse.Name).

A sample says more than a description, I encourage you to test it
yourself in the SAMPLE namespace.

SAMPLES>s rs=##class(%ResultSet.SQL).%Prepare("select %ID as oref,
home,* from sample.person",.error)

SAMPLES>w rs.%Next()
1
SAMPLES>w rs.name

Quixote,Quigley S.
SAMPLES>w rs.Home.City
Reston
SAMPLES>d rs.oref.PrintPerson()

Name: Quixote,Quigley S.
SAMPLES>w rs.Spouse.Name

SAMPLES>

I hope you are less confused now! :)

Ciao

Enrico

[Eric]

That was covered at Devcon but is not in the official documentation
AFAIK, but I love that feature;)
It is a great example of object/sql integration.

There are a few other aspects about this resultset:

1. it is at least 3 times faster than the old %ResultSet. There are
many resultset objects (and a new very nice one to replace them all in
2010. For those using the beta, look at %SQL.Statement) but %ResultSet
is orders of magnitude slower than the new ones.

2. %ResultSet.SQL works with SELECT statements only but accept a lot
of parameters (%ResultSet is limited at 16)

3. It is not (yet) compatible with Zen. Only %ResultSet is Zen
compatible for now.

Eric