Re: How to save the query from the form and re-use it later?

In article <107_at_timer1.UUCP> john_at_timer1.UUCP (john) writes:
>Hello, World:
>
>We are implementing a form to access customer database. So, it is kind of
>basic like name, address, phone numbers and titles etc.
>
>Oracle Sql*Form 3.0 provides query by form (kind of nice). We would like to
>use this feature but we also want to save the query for future use because
>some queries can be so complicated and our users don't want to re-type it
>over and over again. In addition to that, we also like to dump the results
>not only on the screen but also into a file so we can use it with mail
>merger of word processor.
>
>Is there any one out there who can give us a little guide, instruction or
>pointer on how to implement this mechanism ?

You can create a POST-QUERY trigger to do this, having the following body:

declare hoststr char(256);

begin
  hoststr := 'echo "' || :system.last_query || '" > /tmp/filename_here';   host(hoststr,NO_SCREEN);
  redisplay;
end;

The idea here is simple, take the last query entered, put the 'echo' string together (containing the :system.last_query), then use it in the 'host' function, which executes the 'echo' command and dumps the output to a file. There are a couple of things to think about:

0. The :system.last_query stores the query used to populate the last block.

   Hence, if you have a multi-block form, you will have to create/modify the    triggers for these blocks to store the last_query from that block before    moving on. If you have a master-detail relatoinship, you can do this    storage before executing the 'query_details' code (at least this was    given to us when we created forms w/ master-detail relationships).

1. Related to (0.), if you store the last_query in a global variable, you are restricted to 256 characters. This is a real pain in the neck if you are trying to pass this last_query to a report writer to do a query dynamically (without knowing beforehand which fields get filled during 'enter-query' mode). Our solution was to parse the 'where' clause from the last_query, store it in a global variable (and hope it still isn't too long) and pass this to the report writer.

The example code is an adaptation of what I have written for work and is in active use. There may be better ways to do it, but this will work.

Hope that helps.

>Any help will be highly appreciated.
>
>
>John Y. Chan

-pmm

-- 
Paul M. Mickel			  	   Internet:mickel_at_oes.orst.edu
Sysadmin, Oregon Extension Service	   Corvallis, OR 97331
Database Programmer, Teledyne Wah Chang    Albany, OR 
Disclaimer: Neither employer has *ever* claimed any of my opinions.