SmartForm SQL Stored Procedure List vs Execute Methods

  • 29 June 2016
  • 5 replies
  • 18 views

Badge +1

new to k2 : I have 2 sprocs that inserts and selects and when added as smartobjects - the insert sproc has only the 'Execute' method listed and the select sproc has only the 'List' method listed. I cannot create a smartobject with only the 'Execute' method, but i can create a smartobject with only the 'List' method or both methods.

 

Creating a view off the smartobject shows 2 method - item view and list view and these methods are mapped only to the 'List' method of the smartobject, but on selecting the item view - the fields shown are for the 'List' method that are mapped to the select sproc and the button control has only the 'Execute' method listed. 

 

After creating the view, i click the button that is mapped to 'Execute' method and a validation pop up shows up asking that the 'Execute' method params are required - meaning the fields on the form are mapped to the 'List' method and the button is mapped to the 'Execute' method and no connection between them. 

 

My question: how do i use the execute method to create the view so the right fields are mapped on button click? I've tried to manually map the fields from the list method to the fields on the execute method but the option is not listed. Do i need to modify the sproc or merge them?


5 replies

Userlevel 1
Badge +8

Hi,

 

The difference between list and execute is that the stored procedures with a list were discovered to have return values where the stored procedure with just an execute method did not. For example, a stored procedure with just an Insert statement would be exposed as an execute as it doesn't return anything. End that procedure with a SELECT @@IDENTITY AS ID; to return the identity value of the record that was inserted and the stored procedure would have a list method instead.

 

This does present challenges if you want to auto generate views as K2 doesn't know what sort of activity a stored procedure is doing as list or execute doesn't lend itself to create, read, update or delete. One way I have found to get around that nuance is to create my smart object in a thick client tool. When mapping the methods in the smart object I can go back in once I am finished with the mapping and change the defaulted list or execute type to a more appropriate type of create read etc..

I don't know if ability to override the method type exists in the web designer. It would be a good feature request if it does not.

 

In short, the different between list and execute is if the procedure is discovered to be returning a value or not. Other than that, those procedures can be used in whatever fashion that meets your requirement. I use procedures exclusively for interacting with the database to have greater control over the queries that are created and optimize for performance. Plus, if the data structure changes, it gives me an extra layer of abstraction to be able to manage that change and minimize the amount of rework.

 

Hope this helps.

 

S.

Badge +1

Thanks for your quick response - so this is my scenerio : i have multiple tables with relationships and i need to create a form that inserts into these tables using a sproc and also get a list of inserted rows from these tables using another sproc. In k2 interpretation - list and execute methods. How would you approach this? Also what is a thick client tool?

Userlevel 1
Badge +4

One quick addition to Scott's sample SP.  If your table have any triggeres on them (or if this schema is not designed by you) then it is a good idea to use SCOPE_IDENTITY() instead of @@IDENTITY.  Here is a quick blog post that describes the differences between the two:

 

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

 

 

 

Userlevel 1
Badge +8

Sorry, I am bit confused. I was with the understanding that you were already working with the SQL Service Broker which is what you use to expose stored procedures from a SQL Database.  Can you tell us what piece you are having difficultly with? I know more of this coversation was spent talking about the differences between list and execute methods so that had me thinking that you were exposing the stored procedure in K2 via the service.

Badge +1

Sorry  about the confusion, yes i'm using the SQL Service Broker and my problem was just figuring out how to map the fields on the form to the parameters on the store procedure and i figured that out. Thanks for your help!

Reply