cursor variable doesnt work

[jwiebe]

Hello,

i want to use a cursor in my procedure, but somehow it doesnt work.

DECLARE CURSOR <cursor_name> FOR SELECT "TITLE", "MESSAGE" FROM <table name> AD JOIN <table name> CD ON AD.<column_1> = CD.<column_1> AND AD.<column_2>" = CD.<column_2> WHERE CONTAINS (EXTENDED_KEYWORD, <specific string>) AND NEWS_ITEM_TIMESTAMP > '2020-03-19 15:00:00.000000000' AND NEWS_ITEM_TIMESTAMP < '2020-03-19 20:05:00.000000000' ORDER BY TITLE;

i want to iterate over the cursor to insert specific values to different tables.

somehow it doesnt work.

if i change "DECLARE CURSOR <cursor_name> FOR" with "temp_tab =", so a table variable would be initiated, it works.

I dont know why.

Thank you for any suggestions!

[michael_eaton3]

Show the actual code and the error.

[michael_eaton3]

"i want to iterate over the cursor to insert specific values to different tables" - you don't need a cursor to do that, just multiple INSERT statements.

[jwiebe]

"i want to iterate over the cursor to insert specific values to different tables" - you don't need a cursor to do that, just multiple INSERT statements. => i need to iterate over the rows to get a specific value of a column to concatenate it to one.

the problem ist that somehow the cursor dont get any data. and the for-loop should open at close the cursor(at least that is what i read in the documentation)

[michael_eaton3]

Just to be sure, you have checked that the SELECT statement returns a result set?

[jwiebe]

yes. and if i change the DECLARE CURSOR....with temp = SELECT .... the variable is shown in the XSA Debugger.

only the DECLARE CURSOR wont do anything. somehow.

[michael_eaton3]

It works for me...

create column table message (id int primary key, message_text varchar(50));
insert into message(id, message_text) values(1, 'quick');
insert into message(id, message_text) values(2, 'brown');
insert into message(id, message_text) values(3, 'foxy');
insert into message(id, message_text) values(4, 'fox');


create procedure proc_demo() as
begin
  declare all_text varchar(500);
  declare cursor c1 for select message_text from message where contains (message_text, 'fox', fuzzy(0.5));
  
  all_text = '';
  
  for cur_row as c1
  	do
  	all_text := all_text || cur_row.message_text;
  end for;
  
  select all_text from dummy;
end;


call proc_demo();


drop procedure proc_demo;
drop table message;

[michael_eaton3]

I made a small amendment to the code above to write a result out to prove its working.

The result is shown as foxyfox.

[jwiebe]

Thanks a lot! I have integrated this into my procedur for testing purposes and it all works. I have now learned that the cursor in the debugger is simply not displayed. I have found my error. In my actual procedure nothing was concatenated because I used the wrong operator. I forgot that sql uses || and not +.