Executing select statement in I-desc subroutine

[Jon Wells]

Hi,

I'm trying to create my first I-Descriptor (Cache 2009.1). It was
suggested that I start by creating my subroutine that the I-desc. will
call and manually create the I-desc. using ED in order to troubleshoot
it there first. I'm able to directly assign values to my return
argument (debug code) and see the correct results in a LIST
statement. The subroutine needs to execute a select statement against
a different file; and simply needs to know how many records were
selected. I created a test program that asks for a value for the
input argument and CALLS the subroutine and to show me the number if
items selected. That seems to work fine. If it hits the execute
while performing a LIST statement, it blows up. Any help would be
appreciated.

Here's the results from the LIST statement ->

LIST O.Posting MaxSignups ShowFullPostings
Unable to open SQL cursor for the query!
LIST O.Posting MaxSignups ShowFullPostings 12:48:16pm 16 Nov 2009
PAGE 1
ID........... MaxSignups Full.
?

An internal CMQL/SQL error occurred!
SQLCODE=-400: Fatal error occurred
Unexpected error occurred: <UNDEFINED>%0AmCuncommitted
+1^cmql24759query *%C10d(14)

[401] No items present.
"1010" not found.
"1011" not found.
"1043" not found.
"1056" not found.
"1117" not found.
"1118" not found.
"1185" not found.
"1218" not found.
"1265" not found.
"1274" not found.
"1280" not found.
"1285" not found.

[John Lambert]

Jon,

Executing CMQL statements from within Itypes is not going to work
because we do not save the entire CMQL/SQL environment on every call to
an Itype routine, nor do we check for attempts to do this.
I think we are departing from the intent of Itype routines here :-)
We could prohibit all executes from code called by an itype but there
are probably some justifiable cases of simple commands that can safely
be executed.

Try to keep itypes simple and efficient since they are going to be
called a lot of times in a query.

John Lambert

--
You received this message because you are subscribed to the Google
Groups "InterSystems: MV Community" group.
To post to this group, send email to Cac...@googlegroups.com
To unsubscribe from this group, send email to
CacheMV-u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/CacheMV?hl=en

[Ed Clark]

You didn't give an example of your itype or subroutine, but it sounds
like you're trying to execute a query inside an itype running in a
query? That just isn't going to work. There are a number of variables
that queries use that are visible globally, like @RECORD and @ID.
Query is non-reentrant.

What are you trying to accomplish in the query?

[Michael Cohen]

Hi Jon,

As John suggested, I-Types are designed for simple calculations, such as PRICE*QUANTITY or LNAME:", ":FNAME.

Could you provide a simple example of what you are trying to accomplish?

For example, it sounds like you want something like:
Given two files DEPT and EMP (where each EMP has a DEPT field, how to define a calculated field in DEPT that reports the number of EMPs.

-----Original Message-----
From: Jon Wells [mailto:jwayt...@yahoo.com]
Sent: Monday, November 16, 2009 1:54 PM
To: InterSystems: MV Community
Subject: [InterSystems-MV] Executing select statement in I-desc subroutine

[Jon Wells]

Hi John,

The file I'm working in, O.Posting, contains a field called Maxsignups.  Records in S.SignUp contain a field containing the O.Posting ID it is connected to.  The subroutine reads the value of Maxsignups, if there's a positive number there, it tries to select S.SignUp records with that PostingID.  All it needs is how many records with that PostingId exist (so I suppose a COUNT would work).  If the count is => what is in Maxsignups, then it returns the string "Full".  I've worked with MV databases for 19 years so my thinking is obviously influenced by my experience with these databases.  Is there another way to do this (short of a major change to the file and supporting code)?


Idesc ->
ADEV:AE DICT O.Posting ShowFullPostings
ShowFullPostings
13 lines long.
Warning: Editing I Type descriptor
----:p
0001: I
0002: SUBR('IPF',PostingId)     <--- IPF stands for IsPostingFull
0003:
0004: Full] ?
0005: 5L
0006: S
0007:
0008:
0009:
0010:
0011:
0012:
0013: I72^|"ADEV"|MVI.1
Bottom at line 13.

Code from IPF subroutine (currently running with DEBUG.ON "true") ->
if MaxSignups > 0 then
 
  IF DEBUG.ON THEN
    SEL.STRING = 'COUNT S.SignUp WITH PostingId = "'
    SEL.STRING := postingid:'"'
    *results = SEL.STRING
    execute SEL.STRING
    rec.cnt = @selected
    results = rec.cnt

[Ed Clark]

Create an index on S.SignUp,PostingID. In your itype, use BSCAN or
SELECTINDEX to read a list of the ids you want, and return a count. In
your subroutine, do something like:

OPEN 'S.SignUp' TO FP ELSE ABORT
BSCAN KEY,ITEM FROM FP,postingid USING 'PostingId'
IF KEY=postingid THEN results=DCOUNT(ITEM,@AM) ELSE results=0

With this you are still opening the file for every record in the
query. You can get around that by opening the file once to a named
common or %variable.

[Jon Wells]

Hi Ed,

Thanks, this looks very promising. Now I hope we can figure out how
to set up the index.

Jon Wells

[Jon Wells]

Hi,

I have been looking into the world of indexes.  I cloned the two files involved in my current project.  I tried to use the 'Index Wizard' and could not figure out how to point it to the correct property.  I tried manually editing the class with -->
[ In Playground.jonw.SignUp.cls ]
//PidIdx = PostingId Index
Index PidIdx On PostingId;

Before compiling above class: From the colon prompt --> SELECT Playground.jonw.SignUp WITH PostingId EQ "814" finds 8 records

After compiling [the Inspector does show the index], and running [Home] > [SQL] > [Schemas] > [Tables] > [Table] --> Rebuild Indices in the System Management Portal, the above SELECT statement yields 0 records.  Also, (from the colon prompt), LIST.INDEX shows nothing.

Do I need to run any MV index commands from the colon prompt to make this work?


Thank you,