SQL Views

[rayawalther]

This is my pet peeve so I thought I would post this here to see if it gets any further attention.....

For some time we have had the ability to utilize SQL views in SLX and it is extremely powerful capability that makes development and updating of data much easier.  But SLX has left some gapping holes in how SQL Views are used in the development environment.

The issues I am aware of are as follows:

1. There is no to distribute views via the bundling technology.  The bundler needs the ability to include the creation and updates of SQL Views much like you do today for Tables/Plugins etc.   You can try and treat it like a table but it actually creates a table on the system you implement it on and does NOT create and initialize the SQL View on the target system.  I develop a bundle that is used by numerous companies that utilizes 5 or 6 SQL Views.  The distribution of these views are the most troublesome part of the entire process because there is no easy way to do it.  The most accurate/efficient process I have been able to come up with is requires the following steps:
   •  
     • Bundle Creation
       • Store Text of the SQL View statements in a custom table.
       • Delete existing SQL View records in the bundle.
       • Insert records from SQL Views table into bundle for distribution.
     • Bundle Installation
       • Delete existing views via Database Manager.
       • Execute each SQL view via a custom LAN form.
       • Enable new views via Database Manager.
2. In 7.5 web development you can not easily make a SQL view work in the new Entity environment.  I am just starting the process of converting our application to the web platform but I have already hit a major obstacle in how SQL views are NOT fully supported in the new Architect/Entity environment.  I have found some potential work around in the newsgroups that appear to require some manual updating of some of the SECTABLEDEFS table in addition to other not straight forward actions.  I have not been able to make it work yet but even more important not sure how I will be able to distribute these work arounds to our clients.  I have subsequently abandoned trying to make these work and have created Extended Entity Properties via code snippets to access these fields (SQL reads directly to SQL views via ADO.NET.  This is not a great way to handle this and requires a substantial amount of additional coding.

We need SLX to fully support the use of SQL Views within their system so that the distribution of this powerful technology is not painful to the developer and most importantly our clients.

Ray

[tagersea]

I agree that SQL Views are very powerful and should be supported.

[RJLedger]

YES! - SQL Views need to be completely and properly supported.

[scottdurgan]

In version 7.5.1 we included the ability to sync views out to remotes.

Additionally, you would need to do the following:

Open DBManager in Admin, locate the View that was created in the database, and select “ENABLE”.

Then insert the following data into ResyncTableDefs

  <RESYNCTABLEDEFS>

    <TABLENAME>CAMPAIGNTARGETSVIEW</TABLENAME>

    <KEYFIELD>CAMPAIGNTARGETSVIEWID</KEYFIELD>

    <SECURE>T</SECURE>

    <OMNIDIRECTIONAL>T</OMNIDIRECTIONAL>

    <DISPLAYNAME>Campaign Targets View</DISPLAYNAME>

    <MODIFYDATE>2009-01-06T09:19:09.167-07:00</MODIFYDATE>

    <MODIFYUSER>ADMIN       </MODIFYUSER>

    <CREATEDATE>2009-01-06T09:19:09.167-07:00</CREATEDATE>

    <CREATEUSER>ADMIN       </CREATEUSER>

  </RESYNCTABLEDEFS>

 

[rayawalther]

Unless I misunderstand the previous posts the functionality has been available since version 6.0 of SalesLogix.  The steps you show are accurate but enabling the view via the DB Manager already creates the Entry in the Resynctabledefs so manually inserting the record is not required.  I provide an add-in that we sell to SalesLogix customers and the issues I have with SQL Views that your email does not address are as follows:

1. No mechanism to move the SQL View to a new database/system via the bundling technology.
2. No ability to create an Entity in the Web Development environment to support the use of the data in the Web environment. 

Ray Walther

[RJLedger]

AFAIK Ray is 100% correct. Enabling Views has been available since the "Scorpion" release. However, there has been a "bug" in view enabling from teh beginning and I believe it's still there in 7.5.1 - The Keyfield data is NOT filled in and one has to do it manually.

Cutting a new remote db after view enablement will result in the view showing up in the remote db. Enabling a view after a cut will not sync. 

[rayawalther]

Actually RJ I have not had much of an issue with Views synching out to remotes as long as you Drop/Create the views.  Alter View does not work at all.   On occasion I have run into an issue that you can not cut a remote with the Views enabled.  I always advise the client to drop the views build the remote and create/enable the views again.  I understand there are some backdoor way to make NHibernator work with the views by updating the Keyfield but I have not been able to make it work so I gave up. 

It just should be easier than this.....

Ray

[scottdurgan]

I think that the following steps should address both of your questions:

   1. No mechanism to move the SQL View to a new database/system via the bundling technology.
   2. No ability to create an Entity in the Web Development environment to support the use of the data in the Web environment.
 
1. Bundling a View
   1. Create view in database in the sysdba schema
   2. Enable view using DB Manager in Administrator (this does create the entries in RESYNCTABLEDEFS)

   3. Get Create View SQL from database
   4. Create legacy bundle with Execute SQL action that contains Create View SQL

2. Create an Entity from a view
   1. Provided that steps 1 & 2 above have been followed you should be able to use the New Entity Wizard in Application Architect to create an Entity using the new view

Let me know if this answers your question, or if there is some part of it that isn't working for you.

Thanks

[scottdurgan]

One other note, the new functionality noted earlier was refering to the provider not recognizing "Create View", which has been fixed in 7.5.1.

Thanks

[RJLedger]

I've been able to do Create "whatever" since version 6.2.4.