WBI ESQL - only 1 level of function call?

The forecast data exported by i2 are fixed width decimals (10,3)
e.g. WK1=' 123.000', WK2=' 89.000', etc.
.000 is redundant as they only contain integer data.

So the ESQL code to insert them into an Oracle table with Integer fields was:

PASSTHRU('INSERT INTO ...',
CAST(TRIM(WK1) AS INTEGER),
CAST(TRIM(WK2) AS INTEGER),
...);
The idea is simple.
TRIM(' 123.000') becomes '123.000' and then it is CASTed as integer 123.
You cannot call CAST(' 123.000' AS INTEGER) directly as the leading spaces will throw errors.

However, this always silently failed. No records were inserted into Oracle.
The trace shows:
Evaluating expression TRIMBOTH( BOTH FROM ' 123.000')
The result was '123.000'
But where is the CAST?
It looks like WBI is not capable of evaluating further and call CAST.

I had to change the code to:

DECLARE pattern CHARACTER '##########.000';
PASSTHRU('INSERT INTO ...',
CAST(WK1 AS INTEGER FORMAT pattern),
CAST(WK2 AS INTEGER FORMAT pattern),
...);

Then the trace becomes:
Evaluating expression CAST( ' 123.000' AS INTEGER FORMAT '##########.000')
The result was 123
and everything worked.

Merge statement not based on another source table

CREATE TABLE test_table (name VARCHAR(10), cnt INT);

-- MS SQL Server 2008 allows select without from

merge into test_table T
USING (select 'ABC' as name, 5 as cnt) S
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)

-- or using table value constructor

merge into test_table T
USING (values('ABC', 5)) as S(name,cnt)
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)

-- Oracle must select from dual

merge into test_table T
USING (select 'ABC' as name, 5 as cnt from dual) S
ON (S.name = T.name)
WHEN matched THEN
UPDATE SET T.cnt = T.cnt + S.cnt
WHEN NOT matched THEN
INSERT (name, cnt) VALUES (S.name, S.cnt)

Setup SunOne/iPlanet/Netscape to front jBoss

[1 - Generate nsapi_redirector]
The first thing to do is to obtain the nsapi_redirector from the Tomcat Connectors project. http://tomcat.apache.org/connectors-doc/
The Tomcat Connectors folks no longer make the nsapi_redirector binary available for download under (http://www.apache.org/dist/tomcat/tomcat-connectors/jk/binaries/), you must compile the tomcat-connectors-xxx-src-xxx under (http://www.apache.org/dist/tomcat/tomcat-connectors/jk/source/)
Untar/unzip the source code and go to tomcat-connectors-xxx-src/native

/tomcat-connectors-xxx-src/native> ./configure –enable-netscape
/tomcat-connectors-xxx-src/native> cd netscape

[1a - nsapi_redirector Solaris version]
You must have gcc or the Sun cc compiler
Edit Makefile.solaris and make sure
SUITSPOT_HOME points to your iPlanet installation directory
JAVA_HOME points to your Java JDK directory

/tomcat-connectors-xxx-src/native/netscape> make –f Makefile.solaris

This will generate nsapi_director.so
[1b - nsapi_redirector Windows version]
You must have Microsoft Visual C++ Version 6.0
Edit nsapi.dsp and make sure
SUNONE_HOME points to your iPlanet installation directory
JAVA_HOME points to your Java JDK directory

/tomcat-connectors-xxx-src/native/netscape> MSDEV nsapi.dsp /MAKE ALL

This will generate nsapi_director.dll
[2 – Configure SunOne/iPlanet/Netscape]
[2a – workers.properties]
Create a text file called workers.properties

----------------------------------------
# workers.properties -
#
# This file provides minimal jk configuration properties needed to
# connect to Tomcat.
#
# The workers that jk should create and work with
#
worker.list=jboss,status
#
# Defining a worker named ajp13w and of type ajp13
# Note that the name and the type do not have to match.
#
worker.ajp13w.type=ajp13
worker.ajp13w.host=localhost
worker.ajp13w.port=8009
#
# Defining a load balancer
# 
worker.jboss.type=lb
worker.jboss.balance_workers=ajp13w
#
# Define status worker
#
worker.jkstatus.type=status
----------------------------------------

[2b – mangus.conf]
Edit your web server’s config/magnus.conf and add the following 2 lines
(make sure /path/to/ are correct)

Init fn="load-modules" shlib="/path/to/nsapi_redirector.so" funcs="jk_init,jk_service" shlib_flags="(global|now)"
Init fn="jk_init" worker_file="/path/to/workers.properties" log_level="debug" log_file="/path/to/nsapi.log"

[2c – obj.conf]
Edit your web server’s config/obj.conf and add an object called jboss:

<Object name="jboss">
ObjectType fn=force-type type=text/plain
Service fn="jk_service" method="*" worker="jboss"
</Object>

For a simple setup, edit the default object, intercept all calls to your application and forward the requests to jBoss. For example, I created a Seam application called seamapp on jBoss so the default object in my obj.conf looks like this:

<Object name=default>
NameTrans fn="assign-name" from="/seamapp(|/*)" name="jboss"
…
</Object>

However, for performance reasons, you probably want SunOne/iPlanet/Netscape to serve the static contents (images, stylesheets, etc.). You should make the static contents available under your web server’s document root and intercept only *.seam.
For my example, the default object in obj.conf looks like this:

<Object name=default>
NameTrans fn="assign-name" from="/seamapp/*.seam(|/*)" name="jboss"
…
</Object>