[Caché] embedded cursor SQL in class property not working

[James Glaser]

Greetings,

 

I have the following bit of code as a property in a class:

 

Property ProductGroupsOnOrder As %String(COLLATION = "SQLUPPER") [ Calculated, SqlComputeCode = {
                                set InnvoiceNumber = {InvoiceNumber}
                                set prodGrpId = ""
                               
                                NEW SQLCODE
               
                                &sql(
                                                DECLARE C1 CURSOR FOR
                                                SELECT DISTINCT Part->ProductGroupId
                                                INTO :ProdGroupId
                                                FROM MVFILE.INVFT_LineItems
                                                WHERE INVFT = :InvoiceNumber
                                                AND PartNumber <> 'C'
                                                ORDER BY Part->ProductGroupId
                                )
               
                                &sql(OPEN C1)
                                &sql(FETCH C1)
               
                                While (SQLCODE = 0) {
                                                set prodGrpId = prodGrpId _ ProdGroupId _ ","
                                                &sql(FETCH C1)
                                }
               
                                set {*} = prodGrpId
                }, SqlComputed ];

 

 

Which when selected gives me the following error:

 

Error: [C:\Program Files (x86)\Synametrics Technologies\WinSQL\Winsql.exe]

[SQLCODE: <-400>:<Fatal error occurred>]

[Cache Error: <<UNDEFINED>%0Berr+1^CacheSql2 *%qsct(15,3)>]

[Location: <ServerLoop - Query Fetch>]

[%msg: <Unexpected error occurred:  <UNDEFINED>%0Berr+1^CacheSql2 *%qsct(15,3)>] (State:S1000, Native Code: 190)

 

 

I took virtually the same code and put it into a routine and it does what I expect:

 

                                set InvoiceNumber = "704504"
                                set prodGrpId = ""
                               
                                NEW SQLCODE
                                &sql(
                                                DECLARE C1 CURSOR FOR
                                                SELECT DISTINCT Part->ProductGroupId
                                                INTO :ProdGroupId
                                                FROM MVFILE.INVFT_LineItems
                                                WHERE INVFT = :InvoiceNumber
                                                AND PartNumber <> 'C'
                                                ORDER BY Part->ProductGroupId
                                )

                                &sql(OPEN C1)
                                &sql(FETCH C1)
                               
                                While (SQLCODE = 0) {
                                                set prodGrpId = prodGrpId _ ProdGroupId _ ","
                                                &sql(FETCH C1)
                                }
                               
                                &sql(CLOSE C1)

                                w prodGrpId

 

Any idea what I'm doing wrong in the property?

 

 

James Glaser

Software/Web Development Manager

Marco

3425 East Locust Street

Davenport, IA  52803

www.marcousa.com

ph: 800.BLAST.IT (800.252.7848)

ph: 563.324.2519 - ext. 289

cell: 563.508.3351

fax: 563.324.5792

 

--
InterSystems: Advanced software technologies for breakthrough applications
 
Caché 2010.1.1 released on April 8, 2010

[OldMster]

You are not closing the cursor in the computed code, which might leave
%variables laying around causing your problem.
Mark

> www.marcousa.com<http://www.marcousa.com/>

[James Glaser]

Oh, you are correct. In the code I copied and pasted I'm not closing
the cursor. But it is actually in there. I just missed it in the copy
paste.

[OldMster]

If it were a problem with the calculation, I would expect the error to
be in the class routines, not the CacheSql routine that is built to
process the query. To make sure the calculation is really the
problem, you might want to change the calculation to set the computed
value to a simple string, rerun the query and see if you still get the
error.
Mark