Re: Forms 3.0 question

From: <champs_at_cnb07v.hhcs.gov.au>
Date: 11 Mar 94 09:35:28 +1000
Message-ID: <1994Mar11.093528.1_at_cnb07v.hhcs.gov.au>

In article <1994Mar08.193953.17769_at_srg.srg.af.mil>, mholt_at_srg.srg.af.mil (Mark Holt) writes:
>
> I'm want to build a .sql script from within a SQL*Forms 3.0 trigger, and
> then I want to execute it. I've included the trigger text below. The indicated
> lines are giving me trouble. As long as the system.last_query select statement
> doesn't have a where clause, everything works fine. The trouble arises when a
> where clause is generated (e.g. where name='jerry'). The single quotation marks
> are screwing things up, since that's the character that groups characters into a
> string. So, if I expanded the string I manufacture below, it would look
> something like this:
>
> 'echo -n select name from table where name='jerry''
>
> As you can see, the extra quotation marks in the where clause are ruining my day. Can anyone see an obvious solution to this problem?
>
> if :system.last_query IS NULL then
> message( 'No query results to print.' );
> bell;
> pause;
> raise form_trigger_failure;
> end if;
> host( 'echo spool print > print.sql', NO_SCREEN );
> ---> buffer := 'echo -n ' || :system.last_query || ' >> print.sql';
> ---> host( buffer, NO_SCREEN );
> host( 'echo \; >> print.sql', NO_SCREEN );
> host( 'echo spool off >> print.sql', NO_SCREEN );
> host( 'echo quit >> print.sql', NO_SCREEN );
> host( 'sqlplus user/passwd _at_print > /dev/null &', NO_SCREEN );
> message( 'The report SHOULD be printing' );

If you create a local variable capable of holding :system.last_query then

	local_variable := replace(:system.last_query,'''','''''');
        buffer := 'echo -n ' || local_variable || ' >> print.sql';

I think this should fix your problem.

>
> This is the file that the trigger should generate (print.sql):
>
> spool print
> <select statement>
> spool off
> quit
>
> Thanks in advance
>
> Mark Holt
>
Bye for now.

---

Steven Champness                   champs_at_cnb07v.hhcs.gov.au

Dept of Human Services & Health Brisbane, Queensland, Australia The opinions expressed above were found in a box of 'Rice Crispies' Received on Fri Mar 11 1994 - 00:35:28 CET