Re: Embedded SQL: dif't behavior in PUBLIC procedures

[Ben Taylor]

Dan
Procedures do differ from subroutines. To use a variable in embedded SQL 
from within a procedure aka with {} PUBLIC, you must include any 
variables you want to use in the procdure in the 'declaration', for lack 
of the proper word.
This works:

GetName2()[y] PUBLIC{
        w !,"Name2 Routine"
        s y = ""
        &sql(SELECT name into :y FROM Animals.Dob WHERE born="2/12/1999")
        w !,"  Name: ",y
        q
}
Or if you want to to be able check for success against SQLCODE variable:
GetName2()[y,SQLCODE] PUBLIC{
        w !,"Name2 Routine"
        s y = ""
        &sql(SELECT name into :y FROM Animals.Dob WHERE born="2/12/1999")
        if (SQLCODE=0){
            w "SUCCESS"
        }else{
            w "FAILURE"
        }
        q
}
Ben Taylor
MPLNet, Inc.
Dan Bogaty wrote:
I'm seeing different behavior when using embedded SQL in near-identical
procedures that differ only in the way the proc is defined within the
routine. Am I missing something basic?
Thanks for any ideas.
This one works (i.e. the value of Name gets written out as expected):
----------------------------------------------------------------------
GetName1
 w !,"Name1 Routine"
 s x = ""
 &sql(SELECT name into :x FROM Animals.Dog WHERE born="2/1/2003")
 w !,"  Name:",x
 q
This one doesn't work (unless I get rid of the "PUBLIC" & brackets)
---------------------------------------------------------------------
GetName2() PUBLIC {
 w !,"Name2 Routine"
 s y = ""
 &sql(SELECT name into :y FROM Animals.Dog WHERE born="2/12/1999")
 w !,"  Name: ",y
 q

Class Animals.Dog Extends (%Persistent, %XML.Adaptor) [ ClassType = persistent, 
ProcedureBlock ]
{
Property breed;
Property born;
Property died;
Property weight;
Property name;
}
PopulatePack
        s a=##class(Animals.Dog).%New()
        s a.breed = "boxer"
        s a.born = "2/1/2003"
        s a.name = "fido"
        d a.%Save()
        
        s b=##class(Animals.Dog).%New()
        s b.breed = "schnauzer"
        s b.born = "12/11/2000"
        s b.name = "heidi"
        d b.%Save()
        
        s c=##class(Animals.Dog).%New()
        s c.breed = "german shepherd"
        s c.born = "2/12/1999"
        s c.name = "rex"
        d c.%Save()
        quit
        
GetName1
        w !,"Name1 Routine"
        s x = ""
        &sql(SELECT name into :x FROM Animals.Dog WHERE born="2/1/2003")
        w !,"  Name:",x
        q
        
GetName2() PUBLIC {
        w !,"Name2 Routine"
        s y = ""
        &sql(SELECT name into :y FROM Animals.Dog WHERE born="2/12/1999")
        w !,"  Name: ",y
        q
}