Unable to use SP to insert/update/delete from view

[John]

I'm using OpenAccess v2010.2.714.1 with MySQL 5.0.37-community-nt-log and I can't seem to get my views to insert/update/delete using stored procedures.  I got it working once in Visual Studio 2010, then I re-created my model and can't get it working again.  I've tried using both VS 2008 and 2010 with no luck.  All I'm currently working with is 1 view and 3 stored procedures (1 each for insert/update/delete).  First, I select my view and open the "Mapping Details Editor", check "Use stored procedures for" on the "Create" tab, but none of my stored procedures appear.  If I select "< Create new procedure... >" it will create a procedure for me so I figure I would do this then just update my stored procedure to the same name as it auto-generated.  So I continue on mapping the "Parameter Name" to the "Property Name".  After I'm done I click "Save".  Then I went back to the "Mapping Details Editor" and all of the "Property Name" mappings are set to "< None >".

Please let me know what I am doing wrong so that I can get this to work for my project.  I was able to map directly to table objects and use those without any problems, however I did not try to use sp's for the insert/update/delete commands.

Image Notes
1.jpg - Shows my 1 view & 3 sp's pulled from the database also the sp's do not appear in the "Mapping Details Editor"
2.jpg - Creating a new sp for the "Create" tab (Note: all "Property Name" fields were filled out)
3.jpg - I pressed "Ctrl + S" to save the changes, clicked on the "Update" tab then back to the "Create" tab

[Serge]

Hi John,

 First I would like to thank you for your valuable input. As it turns out there is a bug in the visualization of the stored procedures mappings. Even though the value is saved and it is not displayed in the details editor. If you are not convinced you can easily check this out by opening the rlinq file with a default xml editor in the Rlinq -> Runtime -> orm:namespace -> orm:class node you can find the definition of a serialized class. If you look around you will find the procedure mapping to have a field-name attribute that holds the property this parameter is mapped to.

The problem here is that we fully support mapping to procedures only with MS SQL. While you might be able to create such and map procedures to a model using MySql it is almost sure that you will find yourself in trouble. As a starting point I suggest configuring the views to not use any concurrency control. You can do this by selecting the view in the diagram and clicking F4, this will open up the property pane for this class. 

You should note that when you create a procedure through the property pane it is not persisted to the database. In order to do so you have to open up the Update Database from Model Wizard and generate a script that would update your database with the desired procedure. We have found out that in some cases the wizard will try to generate a table with the name of the view. If that is the case just copy and execute only the parts you need from the create procedure scripts.

When you have this setup, I would suggest modifying the procedures that you have just created.

I do hope this helps. Do not hesitate to contact us back if you need assistance or face further issues. Please find your Telerik points updated for reporting this bug.

Kind regards,
Serge
the Telerik team

[John]

Serge,
Thanks for the information.  Since you said that only MSSQL is fully supported for mapping to procedures I decided to try to create a simple view (vCustomer) along with 3 stored procedures (sp_VCustomerDelete_oa, sp_VCustomerInsert_oa & sp_VCustomerUpdate_oa) in the NorthwindOA database on MSSQL.  I only added vCustomer and the 3 stored procedures to my mapping and it appears that OpenAccess is not using the stored procedures to perform at least the insert (I didn't test the update or delete functions).  In each of the stored procedures I not only insert/update/delete to the Customers table, but I also insert information into another table named Test which stores the string "Insert", "Update" or "Delete" so I know that the stored procedure is actually being called.  This table is not being populated when using OpenAccess, however the Customers table is being populated.  If I call the sp_VCustomerInsert_oa directly it works as expected (inserts into both the Customers and Test tables).

I'm not sure if I should open a support ticket for this, but I have uploaded a zip file that you can look at.  The link for this is http://www.sharefile.org/showfile-2661/web__mssql_.zip.  You will find the following files:

1) Web.sln - VS 2010 C# solution containing two projects
     a. Web.csproj - a web site
     b. WebDAL_Microsoft.csproj - a DLL containing the OpenAccess information
2) SQL.txt - Contains all of the extra SQL code to generate the view/stored procedures and "Test" table

Thanks,
John

[PetarP]

Hi John,

 Can you please open your sql server and confirm that the stored procedures are indeed created on the server. Everything in the configuration of your project seems fine and the procedures should work as long as they are present on the server.

Kind regards,
Petar
the Telerik team

[John]

Petar,
I have verified that the stored procedures are on my server (please see "sp-1.jpg").  Sill have the same results.  Do you have an example of doing insert/update/delete using a stored procedure from a view object that works with the Northwind database?

Thanks,
John

[Serge]

Hi John,

 I have successfully inserted an item to a view through the stored procedures that you have provided. The problem here most likely lies in the fact that you view does not have a primary key specified. You surely have a validation error in your error list saying so. 

Please specify a primary key property for your view and try again. Also it would be a good idea to start up a Profiler in order to see what sql scripts OpenAccess is creating and executing. 

I do hope this helps.

All the best,
Serge
the Telerik team