Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Converted datawindow with SQLModelMapper - Correlation Name not found

  1. How-to
0
Votes
Undo
  1. Dan Harrel
    Dan Harrel
  2. SnapObjects
  3. Tuesday, 8 December 2020 17:09 PM UTC

I'm building a web API in SnapDevelop using SQLModelMapper and ODBC, and this is all new to me so far.  I have converted a PowerBuilder datawindow to the following (omitted Usings):

namespace MyApp.Models
{
    [DataWindow("d_prt", DwStyle.Default)]
    [Table("part")]
    #region DwSelectAttribute  
    [DwSelect("select \r\n "
                  +"@(_COLUMNS_PLACEHOLDER_) \r\n "
                  +"from \r\n "
                  +"part p \r\n "
                  +"where \r\n "
                  +"p.prt_id=:a_prt_id")]
    #endregion
    [DwParameter("a_prt_id", typeof(double?))]
    [DwKeyModificationStrategy(UpdateSqlStrategy.DeleteThenInsert)]
    [UpdateWhereStrategy(UpdateWhereStrategy.KeyAndConcurrencyCheckColumns)]
    public class D_Prt
    {
        [Key]
        [Identity]
        [DwColumn("p", "prt_id")]
        public int Prt_Id { get; set; }

        [ConcurrencyCheck]
        [DwColumn("p", "prt_cd")]
        public string Prt_Cd { get; set; }

        [ConcurrencyCheck]
        [DwColumn("p", "prt_nm")]
        public string Prt_Nm { get; set; }

    }

}

My Service class contains the following line which throws an error:

D_Prt d_prt = _dataContext.SqlModelMapper.Load<D_Prt>(prt_id).FirstOrDefault();

The error is:

OdbcException: ERROR [42S02] [SAP][ODBC Driver][SQL Anywhere]Correlation name 'p' not found

The correlation name is defined in the DwSelect attribute value, above.  Is SQLModelMapper modifying my Select statement?  If so, how can I see what it modifies it to?  How can I get around this problem?  

Thanks!

  1. SqlModelMapper
Getting Error Details from SelectProcedure?
Sending PbBlob to PowerBuilder
Responses (2)
Dan Harrel
Dan Harrel Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 19:36 PM UTC
  2. SnapObjects
  3. # 1

Thank you, Armeen.  Indeed I am using ODBC to connect to SQL Anywhere, so I am good there.  

I assumed that a datawindow "generate to C#" would produce SQLModelMapper objects for a SQLModelMapper project.  

We are planning to use SQLModelMapper in our web API rather than .NET Datastore because our web API client will be .NET Core Xamarin running on Android.  Theoretically a .NET Datastore could be received in that environment, but I understand it is not tested or recommended.  

Can I ask that someone on your end replies with SqlModelMapper class declaration examples, appropriately decorated with attributes, for the following 2 SQL statements?  I think it would be good to have these examples available on the forum:

select
  p.prt_id, /* primary key, autoincrement */
  p.prt_cd, /* unique */
  p.prt_nm /* not null */
from
  part p
where
  p.prt_id=:a_prt_id

;

And:

select
  pi.prt_id, /* primary key: prt_id, prt_loc_id */
  pi.prt_loc_id,
  pl.prt_loc_abs_nm_shr, /* not null */
  pi.prt_inv_onhnd /* not null, decimal */
from
  part_inventory pi join part_location pl
where
  pi.prt_id=:a_prt_id
;

 

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 8 December 2020 20:15 PM UTC
There is no problem to use .NET DataStore with non-PowerBuilder UI. The .NET DataStore supports returning plain JSON instead of DataWindow formatted JSON, and the SnapDevelop tool has templates to scaffold the REST API for use by non-PB client. I really recommend you use .NET DataStore over SqlModelMapper.
  1. Helpful
Dan Harrel
  1. Dan Harrel
  2. Tuesday, 8 December 2020 21:06 PM UTC
Thanks, Armeen. I figured out appropriate SqlModelMapper attribute decorations between the documentation you referred me to and your SnapObjectsDemo application. I now have my SqlModelMapper web API returning exactly the JSON I wanted:



{"d_Prt":{"prt_Id":1,"prt_Cd":"Light-S1","prt_Nm":"Strobe Light 150W"},"d_Prt_Inv_Lst":[{"prt_Id":1,"prt_Loc_Id":46,"prt_Loc_Abs_Nm_Shr":"\\SR2\\A3\\B30","prt_Inv_Onhnd":11.0000}]}



You will notice this is a single master object followed by a list of child objects. I plan to display this master/child relationship on a Xamarin page.



Considering I want one Web API call to return compound objects, and further considering that I want my web API to perform most, if not all, data updates via stored procedure calls, do you maintain your recommendation that I use the .NET Datastore?
  1. Helpful
Govinda Lopez @Appeon
  1. Govinda Lopez @Appeon
  2. Tuesday, 8 December 2020 21:28 PM UTC
Hi Dan,



Yes, it is recommended to create the .NET DataStores because you can maintain them more easily with the visual tools (DataWindow painter). SqlModelMapper does not provide these tools, therefore, you can take advantage of so many productivity enhancements provided through the .NET DataStore.



Now, following are some examples you may refer to for both .NET DataStore and SqlModelMapper:



https://github.com/Appeon/.NET-DataStore-Example

https://github.com/Appeon/SnapObjects-Example



Regards,
  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 8 December 2020 17:27 PM UTC
  2. SnapObjects
  3. # 2

Your model looks like one generated for the .NET DataStore instead of SqlModelMapper. 

There is no model generation tool for SqlModelMapper... you need to hand code it.  Here is relevant doc: https://docs.appeon.com/snapobjects2.0/api_reference/SnapObjects.Data/ModelAttribute/ModelAttribute.html

Also, ODBC is currently only for connecting to SQL Anywhere.  In the next version, you can use it to also connect to ASE.  For all other supported databases you use the native drivers instead of ODBC.  Are you using SQL Anywhere?

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 8 December 2020 23:21 PM UTC
Appeon made both .NET DataStore and SqlModelMapper so certainly they are both good ;-). Overall, to PB developers I recommend .NET DataStore over SqlModelMapper because .NET DataStore has almost no learning curve, it can be visually created in PB IDE, and supports more features than SqlModelMapper. However, that is not to say SqlModelMapper doesn't have its advantages over .NET DataStore. I think the key advantage is SqlModelMapper supports nested models, for example: https://github.com/Appeon/SnapObjects-Example/blob/master/Appeon.SnapObjectsDemo.Service.SqlServer/Models/SalesOrder.cs
  1. Helpful
Dan Harrel
  1. Dan Harrel
  2. Friday, 11 December 2020 19:43 PM UTC
Thanks again, Armeen. I now have both a SqlModelMapper web API and .NET DataStore web API that both return the same compound JSON structure that I'm looking for: one instance of a parent object, list of instances of a child object. I will go forward with the .NET DataStore API. My next step is to validate that I can call a database stored procedure from the .NET DataStore API code.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 11 December 2020 21:15 PM UTC
Sounds good! Glad to see you are enjoying the new C# features! And yes, .NET DataStore supports stored procedures.

BTW, in PB 2019 R3, we enhanced .NET DataStore to support asynchronous programming: https://www.appeon.com/conference/elevate-2020/live?id=289
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.