Server Side Functions with Input Parameters

If the server function requires parameters (called input host variables), these can be supplied by placing parameter markers in the syntax (indicated with a '?') and then specifying the parameters as the second and subsequent arguments to $ServerFunction.

Here is an example:

> change all orders where orderno = 1003 let RequiredByDate = $ServerFunc('trunc( ?, ? )', $addmonths( $date, 1 ), 'MM' )

Note the parameter markers in the syntax for the Oracle function "trunc". It truncates a date giving another date based on the format string, the second argument. In the following example, the  format 'MM' returns the first day of the month. This change command sets RequiredByDate to the first day of the next month.

The generated syntax is

UPDATE orders set RequiredByDate = TRUNC(?, ?) where OrderNo = 1003

The second parameter marker was unnecessary. It can be inserted as part of the function as follows:

> Change all orders where orderno = 1003 let RequiredByDate = $ServerFunc("trunc( ?, 'MM' )", $addmonths( $date, 1 ) )

Note that strings in SQL are delineated by single quotation marks. This requires the use of double quotation marks around argument 1, or escaping the quotation marks with a '\'.

There are no types to coerce parameters to for the server side function. Users have to ensure that function arguments are of the correct type. Use

add Orders from DSZomOrders90000 let RequiredByDate = \
$ServerFunction( 'LAST_DAY( ? )', \
$ToDate( DSZomOrders9000.OrderDate )

to set RequiredByDate to the last day of the month in which the order was placed. Omitting $ToDate causes a data type mismatch error since DSZomOrders9000.OrderDate is a field in a document and therefore of type CHAR.

If the command is going to be executed entirely on the server, any input host variables to $ServerFunc must be constant values that can be evaluated on the client side. The example above sets RequiredByDate to the first day of the next month. The expression $AddMonths( $Date, 1) is constant and can be evaluated on the client side before passing it to the server. If the change command were changed to set RequiredByDate to the first day of the next month of the OrderDate, modify the syntax for the host variable to $AddMonths( OrderDate, 1) to

> change all orders where orderno = 1003 let RequiredByDate = $ServerFunc("trunc( ?, 'MM' )", $AddMonths( OrderDate, 1 ) )

This expression is not constant since OrderDate varies with every row in the table. This expression cannot be evaluated on the client side since the command is executed entirely on the server.

The SQL syntax generated is

UPDATE Orders set RequiredByDate = trunc( ?, 'MM' ) )

When executed, the command returns an SQLErrCode of -932, inconsistent data types. In the prior example, the Zim client calculated the value of the $AddMonths expression and passed it to Oracle. In this case, the only constant expression Zim can find is for the constant 1, which it passes to the engine, resulting in the error. Re-code this using Oracle functions as shown below:

> change all orders let RequiredByDate = $ServerFunc( \"trunc( Add_Months(OrderDate, 1 ), 'MM' )" )

If the command is coded as

> change all orders let RequiredByDate = $ServerFunc( \"trunc( ?, 'MM' )",$ToDate( $AddMonths( OrderDate, 1) ))

it works. This is because $ToDate is not supported on the Server so Zim retrieves every row before updating each row, which enables Zim to properly evaluate the input host variable. In general, do not rely on the syntax Zim generates. Do not use field values in the input host variables; pass them directly as part of the syntax in argument 1 of $ServerFunction.

The SQL syntax for this last example (not recommended) is

SELECT RequiredByDate, OrderDate, OrderNo from Orders
UPDATE Orders set RequiredByDate = TRUNC( ?, 'MM' ) where OrderNo = ?