StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Problem with Join View

Problem with Join View

Daniel Essin — Sun, 07 May 2006 00:21:38 GMT

I have a view thus:
SELECT MRN, FIRSTNAME, LASTNAME, MIDDLENAME, BIRTHDATE, GENDER, ETHNICITY, SSN, STREETADDRESS as ADDR1, OTHERDESIGNATION as ADDR2, CITY, STATE, POSTALCODE, HOMEPHONE, MOTHERMAIDENNAME, COUNTY, LANGUAGE, MARITALSTATUS, RELIGION, WORKPHONE
FROM AffinityData.dbo.ENTITY
WHERE (CREATINGFACILITY = 'G') AND (ACTIVE = 'Y')
union
SELECT MRN, FIRSTNAME, LASTNAME, MIDDLENAME, BIRTHDATE, GENDER, ETHNICITY, SSN, ADDR1, ADDR2, CITY, STATE, ZIP, HOMEPHONE, MOTHERMAIDENNAME, COUNTY, LANGUAGE, MARITALSTATUS, RELIGION, WORKPHONE
FROM dbo.SvcEntity

This forum won't let me upload the screen shot of the DDK, but all of the fields appear twice. In SQL the first select always determines the field names used in the result set. I don't know what to make of this duplication but I doubt that it would generate a valid BO.

RE: Problem with Join View

StrataFrame Team — Sun, 07 May 2006 00:21:38 GMT

Yes, we are continuing to work on new improvements to the framework and DDT, and the importing of views and stored procedures is on the list of future enhancements, as is improving the intelligence of the view parsing to produce a more accurate list of the fields returned by the view.

RE: Problem with Join View

Daniel Essin — Sun, 07 May 2006 00:01:15 GMT

OK. I guess that there is no way to automate this?

RE: Problem with Join View

StrataFrame Team — Sat, 06 May 2006 19:08:15 GMT

Yes, it does, however, you could create a profile within the DDT that would be separate from your real profile. In the separate profile, you could create "tables" that would contain the structure of the data returned by your stored procedures and views. Then you would be able to still map your business objects to meta-data within the DDT. This is the method that is generally used when you have a data source that you cannot use as the structure source within the Business Object Mapper (for instance, XML files).

RE: Problem with Join View

Daniel Essin — Sat, 06 May 2006 17:59:04 GMT

ok - but I thought that one of the advantages of the DDK was that you could develop without being connected to the database.

The combination of not being able to import a view into the DDK plus the necessity to map views (and stored procs?) directly to the database certainly complicates matters.

RE: Problem with Join View

StrataFrame Team — Sat, 06 May 2006 10:54:27 GMT

Daniel,

The Database Deployment Toolkit uses a regular expression to parse out the table names and fields that are part of the view. Unfortunately, it is confused by complex views, so that's why you're getting some strange results within the DDT. However, when the Business Object Mapper creates a business object from a view, it retrieves the field names through the GetSchema() method on the SQL Server native provider. This method isn't confused by complex views, and always returns the proper fields. My suggestion whould be to go ahead and deploy the view and see if the BOMapper will correctly create the business object on it... my bet is that it will.

RE: Problem with Join View

Daniel Essin — Fri, 05 May 2006 17:56:21 GMT

it also shows "AffinityData.Entity WHERE" for the Table name of the first field in the list (PostalCode) and shows "SvcEntity" for the table of all the other entries.