SQL Server Service - Tables
All the CRUD (CREATE, READ, UPDATE, DELETE) methods including LIST will only be created as Service Object methods if there is a primary key on the table. If there is no primary key on the table only a List method will be created.
When using the SQL Server Service SmartObject, the return property in the SmartObject Method Wizard can be specified. However, it will not return any value as the database column is designed as an identity column and not as a primary key. The SmartObject will return Autonumbers. When an autonumber on a table is used, that column must contain an accompanying SQL Key, otherwise the Column as a return does not get populated by the SmartObject.
 |
Important: Output parameters are unsupported. |
Fig. 1. SQL Service Object Tables methods displayed in the K2 Object Browser
Samples of Table scripts
Without CRUD methods: In this sample code only a List method will be created as there is no primary key on the table. In the List the ID, Autonumber, Description,TextValue, IntValue will be returned.
 Copy Code |
|---|
|
CREATE TABLE [dbo].[WithoutCRUD]( [ID] [int] NOT NULL, [AutoNumber] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NOT NULL, [TextValue] [nvarchar](max) NULL, [IntValue] [int] NULL ) ON [PRIMARY]
|
With CRUD methods: In this sample code CREATE, READ, UPDATE, DELETE and LIST methods will be created as there is a primary key on the table.
The Return properties for all methods will be:
- ID,
- Autonumber,
-Description,
-TextValue,
-IntValue
The Input properties for all methods will be:
For the Create Method - Description, TextValue and IntValue
For Read - ID will be an input property and will be a required property
For Update - ID, Autonumber, Description, TextValue and IntValue with ID as the required property
For Delete - ID and it will be a required property
For List - ID, Autonumber, Description, TextValue and IntValue
| Copy Code |
|---|
|
CREATE TABLE [dbo].[WithCRUD]( [ID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NOT NULL, [TextValue] [nvarchar](max) NULL, [IntValue] [int] NULL, CONSTRAINT [PK_WithCRUD] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
|
Limitations
Dynamic result schemas for SQL Stored Procedure SmartObjects cannot be used with K2. The schema being returned must contain a static list of properties. See the explanation below for the supported scenario.
SmartObjects need the result dataset’s metadata, for example the number of fields and field name, type and size for each field, to do a List method and thus can only support a static result dataset and/or Stored Procedure output parameters. Stored Procedure output parameters are always static, however a result dataset can be dynamic when making use of temp tables.
When creating a SmartObject or registering a SQL Server Service Instance, K2 needs to execute the stored procedure to see it’s schema/metadata to be able to create all the input and return properties so they can be used. However, executing a stored procedure can cause data to be deleted, updated or inserted. K2 only needs the schema/Metadata to be able to create the SmartObject therefor the stored procedure is executed either with blank values or by using FMTONLY. If you have an existing stored procedure and the following command does NOT return a schema, K2 can’t describe it and no properties will be surfaced:
SET FMTONLY = ON
EXEC [Sproc]
SET FMTONLY = OFF
FMTONLY causes the execution to not access any data, but simulates the execution and returns the metadata with no data. This cannot be done if the resulting dataset’s schema is dynamic and can change depending on the data.
When using a temp table in a stored procedure, the result set becomes dynamic, and can change depending on the execution of the stored procedure.
Consider the below stored procedure:
| Copy |
|---|
|
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE TempTableExample AS BEGIN SET NOCOUNT ON; DECLARE @Type int SELECT @Type = ID FROM Table_1 IF @Type = 1 BEGIN SELECT * INTO Temp1 FROM Table_2 END ELSE BEGIN SELECT * INTO Temp1 FROM Table_3 END
UPDATE Table_1 SET [ID] = [ID] + 1 SELECT * FROM Temp1 DROP TABLE Temp1 END GO |
With Table_2 and Table3_:
| Copy |
|---|
|
CREATE TABLE [dbo].[Table_2]( [Name] [nvarchar](10) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Table_3]( [age] [int] NULL ) ON [PRIMARY] |
They have different types, and depending on the value of the @Type variable, different types of result sets will be returned. In this case it is not clear which return properties must be used by the SmartObject. For example, the field returning 1 can either be used as Nvarchar(10) or int. This is the reason why SmartObjects are unable to support dynamic result sets, and executes with FMTONLY which causes errors on temp tables,ect. One thing K2 can do with stored procedures, is use the static output parameters even if the result dataset is dynamic. This will only work for defined output parameters.
Example using Routing_By_Dept.sql.
| Copy |
|---|
|
USE [Delta] GO /****** Object: StoredProcedure [dbo].[Routing_By_Dept] Script Date: 7/31/2013 11:50:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[Routing_By_Dept] @i_CM_Code varchar(4)='THxx', @i_Dept_Code varchar(12)='002231010000', --Start Defined Output Parameters @Out_Dept_Code varchar(12) output --End Defined Output Parameters --the rest of the Stored Procedure --And then again right at the end before “drop table” order by t1.Item_ID set @Out_Dept_Code = @t_DeptCode drop table #tmp1 |
Executing the stored procedure will now return the defined outputs as per the image below
 |
This will only return values for a single record. Not for a table or a list. |
After registering or refreshing the SQL Server Service Instance, ensure that the Use parameters for stored procedures is set to false. The Output parameter defined above will be listed in the Service Instance properties and in the SmartObjects properties.
Executing the SmartObject will now return the defined output parameters for a single record.
