A stored procedure search allows the server to locate entities using a stored procedure in a database.

The stored procedure must provide the server with a list of the identifiers that it should use to update the users selection.

There are three possible ways that the stored procedure can accomplish this:

return the list as the result of the stored procedure
return the list as an output parameter
populate a table

If a table and key are provided in the configuration then the third method will be used, otherwise one of the first two methods will be used. The second method will be used for Oracle (since it doesn't support the first method) and the first method for all other database (unless a special option, resultAsParameter, is set to indicate that the second method should be used for non-Oracle databases).

In all cases the parameters the user enters will be passed as input parameters to the stored procedure, in the order they are listed in the configuration.
It is up to the stored procedure to determine if the user did not provide a value for a particular parameter, as the server will always attempt to set a value for every parameter defined in the configuration. If the user did not supply a value then null will be passed (note that the administrator can specify that a value is required for each parameter in the configuration to ensure that values are populated with a non-null value).

Returning results directly

If the database supports returning results directly then the list of id's to use for the selection can be returned in this manner.

Returning results indirectly

If the database does not support returning results from a stored procedure then the results can be provided via an output parameter (and the stored procedure returns nothing). This is the default for Oracle (unless a table and key are set).

Returning results via a table

If the stored procedure wishes to return results via a table then the table and key parameters must be set in the configuration, and they will specify the table name and the column which contain the id's used to update the selection.

It is the responsibility of the stored procedure to clear any rows belonging to the userid that it does not wish to return. That is, the server will not clear any old records from the table before calling the stored procedure.

In addition to this the stored procedure must also store a userid in the table to indicate which rows in the table correspond to which user.
The value that should be stored in that column will be passed to the stored procedure as the first parameter (unless a special option, useridAtEnd, is set to indicate it is the last parameter). The other input parameters for the search will follow the userid.
Also, note that the name of the userid column can be changed by setting the useridColumn spacial option.

Namespace

urn:com.cohga.server.search.database#1.0

Tags

procedure

Properties

Name	Type	Required	Description
id	string	yes	unique identifier
entity	ref urn:com.cohga.server.entity#1.0:entity	yes	The id of the entity that this search will be searching for
label	string	yes	Text to be displayed to the user to represent this search
description	string	no	Description of the search that could be displayed to the user to explain this search
datasource	ref urn:com.cohga.server.datasource.jdbc#1.0:datasource	yes	The data source that should be connected to to perform the search
procedure	string	yes	Name of the stored procedure to execute
table	string	no	The name of the table in the datasource that the stored procedure will populate to provide the resultant ids
key	string	no	The column in table that the stored procedure will populate to provide the resultant ids
acl	ref urn:com.cohga.server.acl#1.0:acl	no	A reference to an ACL to attach to the search

Sub-tags

Name	Type	Cardinality
parameter	urn:com.cohga.server.search.database#1.0:parameter	1..n
acl	urn:com.cohga.server.acl#1.0:acl	0..1
cache	urn:com.cohga.server.cache#1.0:cache	0..1

Content

None

Notes

An ACL can either be defined in-line or referenced indirectly, but only one should be used (the in-line version will take priority).

Table and key must both be included if one is

parameter

Properties

Name	Type	Required	Default	Description
id	string	yes		A unique identifier for the parameter
label	string	yes		The prompt text displayed when user input the parameter value
column	string	yes		The name of the column within the table that this parameter references
displayname	string	no	label	Provides a user-friendly name for the element
helptext	string	no		Additional text to display for the parameter to explain how to use the parameter
hidden	boolean	no	false	Hides the parameter from the parameter UI
alignment	'left', 'center', 'right', 'auto'	no	'auto'	How the items should appear in the UI
controltype	'listbox', 'checkbox', 'radiobutton', 'textbox'	no	'textbox'	The suggested type of UI control to use when displaying the parameter
datatype	'any', 'boolean', 'datetime', 'decimal', 'float', 'integer', 'string'	no	'string'	The data type for the parameter
allownull	boolean	no	false	Whether a null value is allowed for this parameter
allowblank	boolean	no	true	Give the user the choice of an empty value in the listbox (as opposed to a null value)
allownewvalues	boolean	no	false	Allow the user to enter values not in the listbox already
defaultvalue	any	no		The default value of the parameter
displayformat	string	no		the formatting instructions for the parameter value within the parameter UI
datadefinition	ref urn:com.cohga.server.data.database#1.0:datadefinition	no		Where to get the values for a listbox
labelcolumn	string	no		Column in the datadefinition that supplies the label of the value to show the user
valuecolumn	string	no		Column in the datadefinition that supplies the value of the value to use in the SQL
uppercase	boolean	no	false	Should the value be converted to upper case in the generated SQL

Sub-tags

Name	Type	Cardinality
parameter	urn:com.cohga.server.data.database#1.0:parameter	0..n

Notes

If the controltype is listbox then a datadefinition must be provided, this datadefinition will provide the values to be displayed in the listbox.
If the listbox datadefinition only contains 1 column then that column will supply both the label and the value. If it contains two values then the first column will supply the label and the second the value.
If a parameter contains another parameter, then you are defining a cascading parameter where setting the first sub-parameter will enable, and filter, the second parameter, and setting the second parameter will do the same for the third, etc.
In a cascading parameter all sub-parameters must be of listbox type, and the datadefinition should be set in the outer parameter, not the sub parameters.
In a cascading parameter only one level of nesting should be used.
In a cascading parameter the datadefinition should supply the columns for all of the parameters, and valuecolumn properties should be set for each sub-parameter, and labelcolumn should be also set for all parameters if a different label is to be displayed to the user.

Examples

Stored procedure that returns result directly
Assume a database that supports returning a selection (in this case SQL Server)

CREATE PROCEDURE RoadByNameDirect @roadname varchar(20) AS
  SELECT DISTINCT id FROM roads WHERE name=@roadname

Stored procedure that returns result as a parameter
In this case Oracle requires the REF CURSOR type to be defined in a separate package (rather than being referenced directly in the parameters). Well this is what I have found, YMMV, either way the parameter must be a reference to a cursor.

CREATE PACKAGE cursor_types AS
  TYPE cursorType IS REF CURSOR;
END;

CREATE PROCEDURE RoadByNameParameter(roadname IN roads.name%TYPE DEFAULT NULL, results OUT cursor_types.cursorType) IS
BEGIN
  OPEN results FOR
    SELECT DISTINCT id
    FROM roads
    WHERE name = roadname;
END;

Stored procedure that returns results in a table

CREATE PROCEDURE RoadByNameTable(user IN road_table.userid%TYPE, roadname IN roads.name%TYPE DEFAULT NULL) IS
BEGIN
  DELETE FROM road_table
    WHERE road_table.userid = user;

  INSERT INTO road_table(userid, id)
    SELECT userid, id
    FROM roads
    WHERE name = roadname;
  COMMIT;
END;

<search:procedure id="road_direct">
	<entity>road</entity>
	<displayName>by Stored Procedure (Direct)</displayName>
	<description>Stored procedure returns result directly</description>
	<dataSource>ds.main</dataSource>
	<procedure>RoadByNameDirect</procedure>
	<parameter id="roadname">
		<parameter:label>Road Name</parameter:label>
	</parameter>
</search:procedure>

<search:procedure id="road_parameter">
	<entity>road</entity>
	<displayName>by Stored Procedure (Parameter)</displayName>
	<description>Stored procedure returns result as output parameter</description>
	<dataSource>ds.main</dataSource>
	<procedure>RoadByNameParameter</procedure>
	<parameter id="roadname">
		<label>Road Name</label>
	</parameter>
</search:procedure>

<search:procedure id="road_table">
	<entity>road</entity>
	<displayName>by Stored Procedure (Table)</displayName>
	<description>Stored procedure populates a table</description>
	<dataSource>ds.main</dataSource>
	<procedure>RoadByNameTable</procedure>
	<table>road_table</table>
	<key>id</key>
	<parameter id="roadname">
		<label>Road Name</label>
	</parameter>
</search:procedure>

Browser not supported