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
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?