View source for Howto use a Button to Call a Database Function

Postgre SQL and other databases allow users to create functions - specialized SQL commands - that can be called to perform actions on the database and return results.  Functions are typically used to perform conversions on information or make bulk changes to the database.

ADempiere has methods that can be used to call these database functions from within the application.  For this Howto, we'll look at activating the database function from a button on a window.

==Creating the Function==

To create the function, it is best to read the documentation that comes with the database you're using with ADempiere. The only thing that must be considered is the function parameters that are passed to the function by ADempiere.  

For Postgre SQL ADempiere will activate the function through the JDBC driver using a call function of the form {call myfunction(?)} where the question mark is replaced by the parameters.

The function must accept at least one parameter whether it is used or not: integer AD_PInstance_ID.  Additional parameters can be defined if required.  Any return values from the function will be ignored.

The function creation will look (for Postgre SQL anyway) something like this ...

<PRE>
CREATE OR REPLACE FUNCTION adempiere.my_func(adpinstid integer, ....)
  RETURNS numeric AS
$BODY$
DECLARE
	myVari NUMERIC;
BEGIN

/****
 *
 * Your code here
 *
 */

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION adempiere.my_func(integer) OWNER TO adempiere;
</pre>

Be sure to use lower case in the function name.  Once you have the function created and working, you can activate it with a button in ADempiere by creating a process and attaching the process to a button.

==Create the Process Definition==
In the Application Dictionary, open the Report & Process definition.  Create a new record and give it a reasonable name.  In the "Procedure" field, enter the database function name in lower case.  For the example above, it would be my_func.
  
==Adding the Button to the Window==

In the Application Dictionary, open the table definition for the window and tab where you want the button and add a column - any column - as a place holder for the button.  It really doesn't matter what the field is, just that it exists. Give it a reasonable name for the button it will become.  Set the reference to "Button" and select the process defined above. Sync the table definition with the database.

In the window and tab definition, add the button field.  Add the window to the menu and give access to the proper roles.

Log in as the user and test the button.  If the process takes too much time, the GUI will time-out but the function may still be running to completion.  Wait a bit and check to see if the expected results are there.

Additional detail and notice, please see the discussion section.