User defined persistent type as stored procedure return type

8 posts, 1 answers
  1. Robert
    Robert avatar
    21 posts
    Member since:
    Feb 2009

    Posted 28 Sep 2009 Link to this post

    Hello,

    I'm having problem with following: I have a stored procedure returning data from multiple tables + computed distance:

    SELECT TOP(@maxCnt) ctr.[Nameas [LongName], ctr.ISO2Char as ShortName, o.ObjectID as ObjectID,  
            p.PropValueID as PropValueID, p.[Value] as [Value], p.UnitValue as UnitValue, o.[Nameas [Name], 
            ((o.Latitude - @lat) * (o.Latitude - @lat) + (o.Longitude - @lng) * (o.Longitude - @lng)) as Dist  
        FROM PropsValues p 
        JOIN Object o ON p.OwnerID = o.ObjectID 
        JOIN Category c ON o.CategoryID = c.CategoryID 
        JOIN geoCountries ctr ON o.Country = ctr.CountryID 
        WHERE c.CategoryID = 2 AND p.PropID = 7 
        ORDER BY p.[Value], Dist 

    and have defined user-defined persistent class:

    public partial class GetChiepestDiesel 
        { 
            public GetChiepestDiesel() 
            { 
     
            } 
     
            [Telerik.OpenAccess.FieldAlias("longName")] 
            public string LongName 
            { 
                get { return longName; } 
                set { this.longName = value; } 
            } 
     
     
            [Telerik.OpenAccess.FieldAlias("shortName")] 
            public string ShortName 
            { 
                get { return shortName; } 
                set { this.shortName = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("objectID")] 
            public int ObjectID 
            { 
                get { return objectID; } 
                set { this.objectID = value; } 
            } 
     
     
            [Telerik.OpenAccess.FieldAlias("propValueID")] 
            public int PropValueID 
            { 
                get { return propValueID; } 
                set { this.propValueID = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("unitValue")] 
            public string UnitValue 
            { 
                get { return unitValue; } 
                set { this.unitValue = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("value")] 
            public string Value 
            { 
                get { return value; } 
                set { this.value = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("name")] 
            public string Name 
            { 
                get { return name; } 
                set { this.name = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("dist")] 
            public float? Dist 
            { 
                get { return dist; } 
                set { this.dist = value; } 
            } 
        } 
     
        [Telerik.OpenAccess.Persistent(IdentityField = "objectID")] 
        public partial class GetChiepestDiesel 
        { 
            private string longName; 
            private string shortName; 
            private int objectID; 
            private int propValueID; 
            private string unitValue; 
            private string value; 
            private string name; 
            private float? dist; 
     
        } 

    which is supposed to be a return type for the stored procedure:

       public static class StoredProcedure 
       { 
            public static IEnumerable<DataClasses.GetChiepestDiesel> GetCheapestDieselFromLatLng(IObjectScope scope,int? maxCnt,float? lat,float? lng) 
            { 
                Query<DataClasses.GetChiepestDiesel> query = scope.GetSqlQuery<DataClasses.GetChiepestDiesel>("GetCheapestDieselFromLatLng ?,?,?","INTEGER maxCnt,FLOAT lat,FLOAT lng"); 
                QueryResultEnumerable<DataClasses.GetChiepestDiesel> res = query.ExecuteEnumerable(new object[] {maxCnt,lat,lng}); 
                 
                return res; 
            } 
     
       } 
     

    Currently I'm getting this error message:

    Persistent class 'DataClasses.GetChiepestDiesel' was specified as candidate, but the ResultSet does not contain any/all of the pk columns.

    Is there a way how to solve this?

    Thanks,
    Robert
  2. Answer
    PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 01 Oct 2009 Link to this post

    Hi Robert,

    The problem here is that your column names are not named as the class fields. The class expects to receive different columns than the one that are being provided. To successfully run this you will need to rename your columns to match the class fields.

    All the best,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. DevCraft banner
  4. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 17 Feb 2010 Link to this post

    Hi Petar, I am also having trouble with this error. Here's what I have.

    I have a persistent class called PhotoLatLong.

     [Telerik.OpenAccess.Persistent(IdentityField = "photoID")] 
        public class PhotoLatLong //: Photo 
        { 
            public Guid photoID; // pk  
            private string cameraModel; 
            private string description; 
            private byte?[] geoData; 
            private DateTime lastUpdate; 
            private string lastUpdateUser; 
            private Guid parentID; 
            private string parentTable; 
            private DateTime? photoDateTime; 
            private int? photoNumber; 
            private string serialNumber; 
            private string vvlDirection; 
            private string vvlType; 
            private string witness; 
            private double? longitude; 
            private double? latitude; 
     
            //The 'no-args' constructor required by OpenAccess.  
            public PhotoLatLong() 
            { 
            } 
     
            [Telerik.OpenAccess.FieldAlias("photoID")] 
            public Guid PhotoID 
            { 
                get { return photoID; } 
                set { this.photoID = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("cameraModel")] 
            public string CameraModel 
            { 
                get { return cameraModel; } 
                set { this.cameraModel = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("description")] 
            public string Description 
            { 
                get { return description; } 
                set { this.description = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("geoData")] 
            public byte?[] GeoData 
            { 
                get { return geoData; } 
                set { this.geoData = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("lastUpdate")] 
            public DateTime LastUpdate 
            { 
                get { return lastUpdate; } 
                set { this.lastUpdate = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("lastUpdateUser")] 
            public string LastUpdateUser 
            { 
                get { return lastUpdateUser; } 
                set { this.lastUpdateUser = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("parentID")] 
            public Guid ParentID 
            { 
                get { return parentID; } 
                set { this.parentID = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("parentTable")] 
            public string ParentTable 
            { 
                get { return parentTable; } 
                set { this.parentTable = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("photoDateTime")] 
            public DateTime? PhotoDateTime 
            { 
                get { return photoDateTime; } 
                set { this.photoDateTime = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("photoNumber")] 
            public int? PhotoNumber 
            { 
                get { return photoNumber; } 
                set { this.photoNumber = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("serialNumber")] 
            public string SerialNumber 
            { 
                get { return serialNumber; } 
                set { this.serialNumber = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("vvlDirection")] 
            public string VvlDirection 
            { 
                get { return vvlDirection; } 
                set { this.vvlDirection = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("vvlType")] 
            public string VvlType 
            { 
                get { return vvlType; } 
                set { this.vvlType = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("witness")] 
            public string Witness 
            { 
                get { return witness; } 
                set { this.witness = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("longitude")] 
            public double? Longitude 
            { 
                get { return longitude; } 
                set { this.longitude = value; } 
            } 
     
            [Telerik.OpenAccess.FieldAlias("latitude")] 
            public double? Latitude 
            { 
                get { return latitude; } 
                set { this.latitude = value; } 
            } 
        } 

    I use this stored procedure in the StoredProcedures.cs class

     
            public static IEnumerable<SARTSData.PhotoLatLong> upGetPhotos(IObjectScope scope, Guid? parentID, string parentTable) 
            { 
                Query<SARTSData.PhotoLatLong> query = scope.GetSqlQuery<SARTSData.PhotoLatLong>("[upGetPhotos] ?,?""GUID parentID,VARCHAR parentTable"); 
                QueryResultEnumerable<SARTSData.PhotoLatLong> res = query.ExecuteEnumerable(new object[] { parentID, parentTable }); 
     
                return res; 
            } 

    And here is the SQL for the sproc

     
    ALTER PROCEDURE [dbo].[upGetPhotos] --'0b4f4dbb-cd0d-4ffb-a38d-2cc6b2d328c7','facilities' 
        -- Add the parameters for the stored procedure here 
        @parentID uniqueidentifier 
        ,@parentTable varchar(50) 
    AS 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON
     
        -- Insert statements for procedure here 
        SELECT [photoID] 
          ,[cameraModel] 
          ,[description] 
          ,[geoData] 
          ,[lastUpdate] 
          ,[lastUpdateUser] 
          ,[parentID] 
          ,[parentTable] 
          ,[photoDateTime] 
          ,[photoNumber] 
          ,[serialNumber] 
          ,[vvlDirection] 
          ,[vvlType] 
          ,[witness] 
          ,[geoData].STX as longitude 
          ,[geoData].STY as latitude 
          --,[photoID] as UnID 
      FROM [dbo].[photos] where  
      parentID = @parentID and parentTable = @parentTable 
      and vvlType <> 'Document' 
      order by photoNumber, ISNULL(photoDateTime,lastUpdate) 
     
    END 

    As far as I can tell, the columns being returned from the sproc matchesthe class field definitions. So I am stumped as to why this error isstill occurring.

    Any ideas? thanks

  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 23 Feb 2010 Link to this post

    Hi Kris,

    Can you please confirm that your column names exactly match your field names? We tested this here locally and it worked. Please note that it also worked for Robert. If you are sure that your column names match your field names, can you please send us a small project that represents this issue because it seems that we are not able to reproduce it as everything works as expected on our side. We are looking forward to your reply.

    Regards,
    Petar
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  6. Kris
    Kris avatar
    47 posts
    Member since:
    Feb 2008

    Posted 23 Feb 2010 Link to this post

    Thanks for the reply, I have submitted a support ticket with a sample project demonstrating the issue. Ticket #284362
  7. devoas
    devoas avatar
    69 posts
    Member since:
    Dec 2009

    Posted 12 Jul 2010 Link to this post

    Hello,

    We are using OA Q3 Build 1218 and having same problem when trying to get SP output in a Domain modal. 

    Persistent class was specified as candidate, but the ResultSet does not contain any/all of the pk columns.

    If we use Object as return type instead of Domain Modal, it work properly....

    Please confirm how to fix the issue. We have double check that Fields name returned by the SP and Persistence class are exactly same....

    Thanks
    devoas.
  8. devoas
    devoas avatar
    69 posts
    Member since:
    Dec 2009

    Posted 13 Jul 2010 Link to this post

    Hello,

    I found that if the primary key field name in both Stored Procedure and persistence view is renamed in a way that it should start with Underscore (eg:   _pkfield ), then it is working without any problem..... seems till Q3-2009 this issue is not be resolved. Please confirm if this is been resolved in Q1-2010.

    Thanks,
    devoas.
  9. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 15 Jul 2010 Link to this post

    Hello devoas,

    We do not analyze the field name during run time but just compare the procedure resultset column names with the class column names. An underscore should not matter at all. Can you please open a support ticket and send us a sample project where we can observe the problem as we were not able to reproduce it. We are looking forward to your reply.

    Greetings,
    Petar
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top
DevCraft banner