by People building W4GL applications through dynamic frames may be
interested to know about some unusual behaviour which severely
limits the ability to generate SQL. There is a workaround, however.
A little background first.
I am building a W4GL application for a banking client who wants to browse
through tables containing audit trails derived via auditdb -f -t...
with the data being displayed in a tablefield. He needs a generic
browser for audit trail tables. I don't of course want to keep on writing
individual browse frames for 40-50 tables, especially since the list of such
tables is a growing one. For future expansion reasons I also don't want to
write a single frame which manages a dynamic tablefield and dynamic SQL.
So the answer is a dynamic application which generates a browse frame
dynamically for any specified audit trail table. You pass it a table name
and it creates a frame displaying a window with a tablefield full of audit
trail data.
The first stage works fine. The code is based on the Building a Frame
Dynamically chapter of the W4GL Programming Guide. I am very impressed
by this feature: you can create a template and then generate frames at a
fraction of the time. I suspect that a lot of the Architect module in
OpenRoad is a development of this.
But now for the problem. It concerns the dreaded quoted constant in dynamic
code. It's important because a lot of generated SQL hangs from it.
In my case, the user has to be able to specify a selection criterion for
the data. This is the 'date' attribute which all audit trail tables have
in common. He enters a 'from' and/or a 'to' date to define a range of data
for browsing. After being converted to char, this has to be placed as a
quoted constant in the where clause of the SQL SELECT statement inside the
dynamic frame.
Generalising a bit, the problem concerns parameter passing from frame A,
where the user supplies the value, to frame C, where the parameter ends up
in the where clause of an SQL statment, through frame B, which dynamically
generates frame C.
If coded according to TFM, W4GL will refuse to supply the closing quote
for the passed constant in the generated frame script and so the dynamic
frame won't compile because of an unterminated string. If you give up at
this point you have to admit that dynamic frames are useless because of a
bug which means that they can't handle about 80% of standard where clauses.
Stand by for simple workaround.
The essentials of this problem are as follows (it's W4GL code and we are
inside the dynamic frame generator):
(Parameter 'from_time', type date passed, )
('date' is our table attribute. )
LEVEL 1: (This is TFM code. It doesn't work. See LEVEL 2.)
/* Build W4GL script string containing SELECT */
...
where_string='where date >= '+HC_QUOTE+char(from_time)+HC_QUOTE;
...
/* etc. write to script file, set up Script attribute for frame */
LEVEL 2: (This is the above in the script generated for the dynamic frame)
...
/* select etc. from table */
where date>='01/01/94 00:00:00 <--- no closing quote!
...
(Dynamic frame won't compile. We crash here.)
(But it should have the closing quote:)
...
/* select etc. from table */
where date>='01/01/94 00:00:00' <--- closing quote!
...
The workaround is as follows:
LEVEL 1:
...
where_string='where date >= '+HC_QUOTE+char(from_time);
where_string=where_string+HC_QUOTE; /*concat() works too */
The closing quote needs to be in a separate statement.
Believe it or not, this took a day ... >: (
===========================================================================
Client/Server Systems (UK) Ltd. London. +44 (081) 692 2958