Data Source Wizard - Define Custom Statements or Stored Procedures

The Define Custom Statements or Stored Procedures screen allows the user to write a SQL statement or select a stored procedure. In setting up K2 data source the SQL statements need to be bound to the SmartObject Methods, these methods are made available in the stored procedure drop-down list.

Feature	What it is
SQL Statement	Type in the SQL statement that will execute and retrieve the data from the selected database. Note each tab for the different SQL statements
Stored procedure	The drop-down box lists all the SmartObject methods that are available for selection. The SmartObject's name is displayed first followed by the method. For example, Employee.Create
SELECT tab	When using the K2 SmartObjects stored procedures the select statement will be the GetList or Load method
UPDATE tab	When using the K2 SmartObjects stored procedures the select statement will be the Save method
INSERT tab	When using the K2 SmartObjects stored procedures the select statement will be the Create method
DELETE tab	When using the K2 SmartObjects stored procedures the select statement will be the Delete method
Buttons
Query Builder	Builds the query and checks for any SQL Syntax Errors. The query can be executed from the Query Builder window. Build SQL statements using SmartObjects to ensure that the correct data is returned.

Examples of SQL Statements

Below are examples of SQL statements that will return the correct SmartObject data when executed:

Statement	Example
GetList	SELECT [SO1.GetList].* FROM [SO1.GetList]
Update	UPDATE [SO1.Save] SET Memo = @Memo, Multivalue = @Multivalue, Number = @Number, Text = @Text, YesNo = @YesNo WHERE (ID = @ID)
Insert	INSERT INTO [SO1.Create] (Memo, Multivalue, Number, Text, YesNo) VALUES (@Memo, @Multivalue, @Number, @Text, @YesNo)
Delete	DELETE FROM [SO1.Delete] WHERE (ID = @ID) Specifying a DELETE with the List method, might result in unexpected behaviour. Rather change the GetList to the name of the delete method. For Example: DELETE FROM [Obj.Delete] WHERE ([Obj.Delete].Id = 1) And NOT DELETE FROM [Obj.GetList] WHERE ([Obj.GetList].Id = 1)
Load	SELECT ID, Memo, Multivalue, Number, Text, YesNo FROM [SO1.GetList] WHERE (ID = @ID)