How to run a SQL script in ADS.

by Nov 2, 2018

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. 

Response

Thomas Conrad over 4 years ago
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

vndteksysmxe over 4 years ago in reply to Thomas Conrad

I appreciate the response, but a stored procedure seemed like overkill. While the example I provided was stripped down, I just need to run it once. Is there no way to run SQL code like this on ADS for an Informix DB?