Values not inserted to table after insert_all

Help Procedural SQL
1

After using INSERT_ALL the table(PM_Table) is not updated, not sure what I am missing here ?

DELIMITER //
create or replace procedure PM (Ids ARRAY(varchar(4000) NOT NULL))
AS
DECLARE
len int = LENGTH(Ids);
j int = 0;
arr ARRAY(RECORD(Id_ VARCHAR(4000), source VARCHAR(4000), target VARCHAR(4000),
source_time DATETIME, target_time DATETIME, time_diff TIME, msg VARCHAR(4000)));
BEGIN
while j < len LOOP

        declare
        Id INT = Ids[j];
        
        stm text;
        qry query(Id_ VARCHAR(4000), source VARCHAR(4000), target VARCHAR(4000), 
		source_time DATETIME, target_time DATETIME, time_diff TIME,  msg VARCHAR(4000))  = 
        select Id_, source, target, source_time, target_time, time_diff, msg from report(Id);           
        x INT = 0;          
        
        BEGIN
        	arr = collect(qry);
            x = INSERT_ALL("PM_Table", arr); 
        END;

        j += 1;

END LOOP;
end //;

DELIMITER ;

CALL PM ([“1”, “31”]);

2

I’d put in some ECHO SELECT statements to print out some debugging info, like the length of arr after the collect(qry) line, and the count of rows in PM_TABLE before and after the INSERT_ALL.

And double check the schema of records in arr matches the target PM_Table.

And make sure you have committed the update transaction (e.g. you don’t have a multi statement transaction open).

If it still doesn’t work, then there might be a bug

3

I split the procedure into 2 and it worked, the problem was collect(qry) , the qry wasn’t updating the id.
Thanks @hanson for the help

4

Glad to hear it! What do you mean the “qry wasn’t updating the id”?

5

echo select Id_, source, target, source_time, target_time, time_diff, msg from report(Id) was working but the collect(qry) length was 0