How to run a SQL script in ADS.

I am trying to run the following SQL code in Aqua Data Studio for an Informix Database:

DEFINE  testVal    integer

FOREACH WITH HOLD

    SELECT test_val

    INTO testVal

    FROM tmp_values

    BEGIN WORK;

        DELETE FROM data_values

        WHERE data_Val = testVal

    COMMIT WORK

 END FOREACH

I would also like to display the value of 'testVal' every iteration (or maybe every Nth iteration). 

For the life of me I can’t get it to work.  I’ve looked on your web site and on-line but I have found nothing helpful. 

Any help would be greatly appreciated. 

Parents
  • Hello,

    You probably want to do this with a stored procedure. Informix allows you to trace statements but will probably give you more information then you want. I would suggest creating another table and adding information to that table about the records that you are deleting. You can even set an interval so that every x row will get inserted. Here is a store procedure that I put together as an example. Notice the interval passed to the store procedure is 5. Also, I commented out the trace. If you do use the trace, the file will be located on the server and not locally.

    Hope that helps,

    Tom

    CREATE PROCEDURE test_stored_proc(action_interval integer)
    DEFINE testVal float;
    DEFINE increment integer;
    DEFINE action integer;
    LET increment = 1;
    LET action = action_interval;
    -- SET DEBUG FILE TO 'c:/users/tom/atrace.txt';
    -- TRACE ON;
    -- TRACE 'Start';
    FOREACH SELECT total_due INTO testVal FROM informix.bistudio_example
    IF (MOD(increment, action) = 0) THEN
    INSERT INTO informix.test_sp_output(c1) VALUES(testVal);
    LET increment = 1;
    ELSE
    LET increment = increment + 1;
    END IF
    END FOREACH
    -- TRACE OFF;
    END PROCEDURE
    GO
    EXECUTE PROCEDURE test_stored_proc(5)
    GO
    SELECT * FROM informix.test_sp_output
Reply Children
No Data