SQL queries with the script wrapper

SQL queries that are made through the script wrapper use the custom function to carry the parameter input values for the script.

Any SELECT statement that passes parameter values to a script through the script wrapper must contain at least one predicate with a custom function to take the parameter input values for the script.

Root nicknames

For example, the following statement creates a root nickname for a script named myscript:

CREATE NICKNAME customers (
	argle double OPTIONS(SWITCH '-argle', POSITION 1, DEFAULT 1.0),
	argfile CLOB() OPTIONS(SWITCH '-file', INPUT_MODE 'FILE_INPUT', POSITION 2),
	argpos	varchar() OPTIONS(SWITCH' ', POSITION 3),
	id	 varchar(10) OPTIONS(XPATH'./@id'),
	name varchar OPTIONS(XPATH '/name'))
	FOR SERVER script_server
			OPTIONS(DATASOURCE 'myscript', XPATH 'doc/customer', 
       TIMEOUT '300', VALIDATE 'YES');

The custom function statements are as follows:

CREATE FUNCTION wsscript.args (varchar(), varchar())
	RETURNS INTEGER AS TEMPLATE
	DETERMINISTIC NO EXTERNAL ACTION;

CREATE FUNCTION wsscript.args (date(), date())
	RETURNS INTEGER AS TEMPLATE
	DETERMINISTIC NO EXTERNAL ACTION;

CREATE FUNCTION wsscript.args (integer(), integer())
	RETURNS INTEGER AS TEMPLATE
	DETERMINISTIC NO EXTERNAL ACTION;

CREATE FUNCTION wsscript.args (CLOB(), CLOB())
	RETURNS INTEGER AS TEMPLATE
	DETERMINISTIC NO EXTERNAL ACTION;

CREATE FUNCTION wsscript.args (double(), double)
	RETURNS INTEGER AS TEMPLATE
	DETERMINISTIC NO EXTERNAL ACTION;

The configuration file specifies the following configuration parameters:

SCRIPT_OUT_DIR_PATH=C:\temp
myscript=C:\perl\bin\perl myscript.pl -model

To run a query and send the contents of the table t1.bigdata to the daemon and to the local file C:\temp\f12345, issue the following query and wsscript.args command:

SELECT id, name FROM customers, t1
	WHERE wsscript.args (customers.argfile, t1.bigdata) = 1

The preceding query results in the following command line:

C:\perl\bin\perl myscript.pl -model -argle 1.0 -file C:\temp\f12345

To run a query that uses the default values for all parameters, run a query on the nickname with no predicates. To avoid problems with excessive output, include the STREAMING option in the nickname.

Child nicknames

The script wrapper maps the XML result set from the script into nicknames that have a parent-child relationship. To retrieve data from a child nickname, join the child nickname to its parent nickname up to the root. The SELECT statements that reference a child nickname must be joined with the parent nickname of the child nickname by using primary and foreign key columns.

The following query displays the customer names and amounts for each order of each customer:

SELECT c.name, o.amount FROM customers c, orders o
	WHERE c.cid=o.cid
	AND wsscript.args (customers.argfile, t1.bigdata) = 1
	AND wsscript.args (customers.argpos, VARCHAR('test1')) = 1
	AND wsscript.args (customers.argle, FLOAT('3.5')) = 1

Specify the join c.cid=o.cid statement to indicate the parent-child relationship between the customers nickname and the orders nickname. If you join a child nickname to itself, an error message is returned.