Forms 3.0 question

From: Mark Holt <mholt_at_srg.srg.af.mil>
Date: Tue, 08 Mar 94 19:39:53 GMT
Message-ID: <1994Mar08.193953.17769_at_srg.srg.af.mil>

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' );

This is the file that the trigger should generate (print.sql):

spool print
<select statement>
spool off
quit

Thanks in advance

Mark Holt Received on Tue Mar 08 1994 - 20:39:53 CET