How do I get a Read method for an SQL view?

  • 11 January 2018
  • 5 replies
  • 58 views
B
Badge +4

I have a smart object that uses a SQL Server Service Instance. When I point the Service Object Method source to a sql table, I have available to me Read, Update, List, and Create. But when I set it to a view, I only get List. But I really would like the Read method from a view, why is the read method not available for views? How can I make it available?

 

Presently I found a workaround using rules to call the List method with a filter and pick the top result. But that doesn't seem like a "right" way to do this. Any ideas?

5 replies

K
Userlevel 6
Badge +16

Hi,


 


Sorry, if I misunderstood you. It sounds like the view is a List View and you want to use Read method for the Data source? With List view scenario, the data source must be pointed to a List method. Hope that helps.


https://help.k2.com/onlinehelp/k2five/userguide/5.0/default.htm#Create/Views/ListViews.htm%3FTocPath%3DCreate%7CSmartForms%7CViews%7CList%2520Views%7C_____0


 


 

B
Badge +4

Hi Khanh, thanks for this infor, but I don't think you understand me. This has nothing to do with a K2 view, I'm talking about an SQL view. I have a smart object that uses a SQL Server Service Instance that connects to a SQL view. The only method available with SQL views is List, the Read method does not exist. I want to be able to populate a single smart object from a single record from an SQL view - not a list.

Z
Badge +9

It's because SQL Views does not have a Primary Key, and a Primary Key is needed for the SQL Service Instance to generate the CRUD methods. 

C

Hi bmorgan,

As Ziqing has stated, using a sql view, read methods are not possible OOTB. I have dealt with this problem by creating read stored procedures and then attaching that sp to the smart object.

 

The trick is to define your fields as output parameters in the SP. I've found this teqnique usefull for tables where i want a read method for an alternate key too.  

 

For a view where the PK is called [Key], and there are columns [Data1], [Data2] and [Data3] your SP would look like

 

Create proc ViewRead

@Key int, -- can be any data type 

@Data1 nvarchar(MAX) = NULL OUTPUT,

@Data2 nvarchar(MAX) = NULL OUTPUT,

@Data2 nvarchar(MAX) = NULL OUTPUT

 

AS

SELECT

@Data1 = [Data1],

@Data2 = [Data2], 

@Data3 = [Data3] FROM View WHERE Key = @Key

K

Wow, I gotta say I did not know this was a thing. I've learnt something new today!

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings