Home » Developer & Programmer » Forms » populating multi-record block
populating multi-record block [message #80770] Tue, 19 November 2002 09:20 Go to next message
Tyler
Messages: 123
Registered: January 2002
Senior Member
Hello All,
I am not allowed to populate a multi-record db block with an "execute_query", I have to loop through the db and populate each record in the multi-block with a cursor loop value. How do I do this? I have written the loop, but how do I assign the value that is retrieved each loop to a single row of the multi-block?

s_status shows the current value of the loop, now how do I assign in to only one row of the multi-record block?

DECLARE

s_sqlstr VARCHAR(1000);
s_status VARCHAR(100);

connection_id EXEC_SQL.CONNTYPE;
c_cur EXEC_SQL.CursType;

c_col1Len NUMBER;
i_rows INTEGER;
n_counter NUMBER := 0;

BEGIN

s_sqlstr := 'SELECT marital_status FROM marital_status_class';

connection_id := EXEC_SQL.DEFAULT_CONNECTION;
c_cur := EXEC_SQL.OPEN_CURSOR;
EXEC_SQL.PARSE(c_cur, s_sqlstr);

c_col1Len := 100;
EXEC_SQL.DEFINE_COLUMN(c_cur, 1 , s_status,c_col1Len );

WHILE n_counter < 10 LOOP

n_counter := n_counter+1;
i_rows := EXEC_SQL.EXECUTE(c_cur);

WHILE EXEC_SQL.FETCH_ROWS(c_cur) > 0 LOOP

EXEC_SQL.COLUMN_VALUE(c_cur, 1, s_status);
message(s_status); <-------------------------------------------------------use this var. to populate

END LOOP;

END LOOP;

EXEC_SQL.CLOSE_CURSOR(c_cur);

EXCEPTION

WHEN EXEC_SQL.INVALID_CONNECTION THEN
message('ERROR:Unexpected Invalid Connection error from EXEC_SQL');

WHEN EXEC_SQL.PACKAGE_ERROR THEN
message('ERROR:Unexpected error from EXEC_SQL: '
||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(connection_id))
||EXEC_SQL.LAST_ERROR_MESG(connection_id)|| 'Query string = ' || s_sqlstr);

END;

Report message to a moderator

Re: populating multi-record block [message #80771 is a reply to message #80770] Tue, 19 November 2002 10:59 Go to previous messageGo to next message
F. Tollenaar
Messages: 64
Registered: November 2002
Member
Just out of curiosity. Why can't you use execute_query ??
You can always change queryability (sic) programmatically:
begin
  go_block(block_name)
  set_block_property(block_name, query_allowed, true);
  execute_query;
  set_block_property(block_name, query_allowed, false);
exception
  when others
  then
    set_block_property(block_name, query_allowed, false);
    raise;
end;


hth
Frank

Report message to a moderator

Re: populating multi-record block [message #80775 is a reply to message #80770] Wed, 20 November 2002 00:27 Go to previous messageGo to next message
F. Tollenaar
Messages: 64
Registered: November 2002
Member
This procedure fills a multi-record block of emp with data from emp.

procedure Jan IS
  l_sqlstr VARCHAR(1000);
  r_emp    emp%rowtype;

  l_connection_id EXEC_SQL.CONNTYPE;
  l_cur EXEC_SQL.CursType;
  l_rows INTEGER;

BEGIN

  l_sqlstr := 'SELECT * FROM emp';

  l_connection_id := EXEC_SQL.DEFAULT_CONNECTION;
  l_cur := EXEC_SQL.OPEN_CURSOR;
  EXEC_SQL.PARSE(l_cur, l_sqlstr);

  EXEC_SQL.DEFINE_COLUMN(l_cur, 1 , r_emp.empno);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 2 , r_emp.ename, 10);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 3 , r_emp.job, 9);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 4 , r_emp.mgr);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 5 , r_emp.hiredate);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 6 , r_emp.sal);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 7 , r_emp.comm);
  EXEC_SQL.DEFINE_COLUMN(l_cur, 8 , r_emp.deptno);

  l_rows := EXEC_SQL.EXECUTE(l_cur);

  go_block('EMP');
  first_record();
  WHILE EXEC_SQL.FETCH_ROWS(l_cur) > 0 
  LOOP

    EXEC_SQL.COLUMN_VALUE(l_cur, 1, r_emp.empno);
    :emp.empno := r_emp.empno;
    EXEC_SQL.COLUMN_VALUE(l_cur, 2, r_emp.ename);
    :emp.ename := r_emp.ename;
    EXEC_SQL.COLUMN_VALUE(l_cur, 3, r_emp.job);
    :emp.job := r_emp.job;
    EXEC_SQL.COLUMN_VALUE(l_cur, 4, r_emp.mgr);
    :emp.mgr := r_emp.mgr;
    EXEC_SQL.COLUMN_VALUE(l_cur, 5, r_emp.hiredate);
    :emp.hiredate := r_emp.hiredate;
    EXEC_SQL.COLUMN_VALUE(l_cur, 6, r_emp.sal);
    :emp.sal := r_emp.sal;
    EXEC_SQL.COLUMN_VALUE(l_cur, 7, r_emp.comm);
    :emp.comm := r_emp.comm;
    EXEC_SQL.COLUMN_VALUE(l_cur, 8, r_emp.deptno);
    :emp.deptno := r_emp.deptno;
    set_record_property(get_block_property('emp', current_record), 'EMP', status, query_status);
    next_record;
  END LOOP;

  EXEC_SQL.CLOSE_CURSOR(l_cur);

EXCEPTION
  WHEN EXEC_SQL.INVALID_CONNECTION 
  THEN
    message('ERROR:Unexpected Invalid Connection error from EXEC_SQL');
  WHEN EXEC_SQL.PACKAGE_ERROR 
  THEN
    message('ERROR:Unexpected error from EXEC_SQL: '||TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(l_connection_id)));
    message(EXEC_SQL.LAST_ERROR_MESG(l_connection_id)|| 'Query string = ' || l_sqlstr); 
    message('');
END;


hth
Frank

Report message to a moderator

Re: populating multi-record block [message #80777 is a reply to message #80775] Wed, 20 November 2002 08:22 Go to previous messageGo to next message
Tyler
Messages: 123
Registered: January 2002
Senior Member
This code entails NEXT_RECORD... This built in can only be used when records already exist, not if you are trying to populate and unpopulated block.
Regardless I figured it out with the built in CREATE_RECORD and a loop...
But Thanks anyway...

Report message to a moderator

Re: populating multi-record block [message #80781 is a reply to message #80775] Thu, 21 November 2002 01:45 Go to previous message
F. Tollenaar
Messages: 64
Registered: November 2002
Member
I tested it and it works. (Forms 9i)

Frank

Report message to a moderator

Previous Topic: Logon to db with new user from forms trigger
Next Topic: hand sign as mouse pointer
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 21:49:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.