K2 BLACKPEARL PRODUCT DOCUMENTATION: USER GUIDE
Tables

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.

 

 

 


K2 blackpearl Help 4.6.11 (4.12060.1731.0)