Re: SQL*Forms problem with : SYSTEM. variable

In article <fragante.701380935_at_chilko.ucs.ubc.ca>, fragante_at_unixg.ubc.ca (Gv Fragante) writes:
|>Has anyone used the :SYSTEM.LAST_QUERY variable in SQL*FORMS V3.0 ?
|>
|>I am trying to use save the value of this variable, but encountered the ff:
|>problems.
|>
|>- if the query is very long (> 500 characters), SQL*FORMS will abend (quit)
|> with a dump of the stacks and registers. This sounds like a buffer overflow
|> problem.
|>- however, if I shorten the query I don't get this abend. I measured
 the length
|> of the query at 456. Is that maximum length of :SYSTEM.LAST_QUERY set at
|> 512 characters ? If so, can this be increased ?
|>- when the query is OK (i.e. - doesn't cause an abend), when I try to save
|> :SYSTEM.LAST_QUERY to a LONG column in a table via an INSERT command, the
|> value of :SYSTEM.LAST_QUERY gets chopped off. It's storing only the first
|> 80 or so characters. But when I save :SYSTEM.LAST_QUERY to a field in the
|> form, I can see the entire query text which is correct.
|>
|>What I am trying to do here is to save the query so I can generate a report
|>based on the query criteria. The SQL*Forms documentation mentions this as a
|>primary use for :SYSTEM.LAST_QUERY, although it doesn't show how to do it.
|>
|>Any suggestions regarding this would be greatly appreciated. Thanks.

One approach to the problem is to use Dynamic SQL Method4 in a user exit. The value of :SYSTEM.LAST_QUERY and its length are passed from the form to the exit. The lenght is used to dynamically malloc the space needed to hold the query. I have run queries of over 1000 bytes using this method. There are a couple of problems: you are running the query twice, once for the form and once for the report; and in a coordinated master detail relationship the value of :SYSTEM.LAST_QUERY is the query statement for the detail block. I have used this method with SQL*FORMS 3.0 and Pro*C running under VMS with the kernel on both VMS and VM/CMS. It works, but the problems mentioned above led me to comclude it was not worth the effort.                   

                    Ian MacGregor
                    [Stanford Linear Accelerator Center]