Stored Procedure Help (Not urgent)

[trentm]

Hi All,

I'm fairly new to Ingres and have muddled my way through creating stored procedures that select & return rows, but when it comes to SPs that update and return rows i'm having a little issue with:

DROP PROCEDURE si_testtbl;
CREATE PROCEDURE si_testtbl ( 
  p_cola INTEGER4)
RESULT ROW RETURNING (
    vchar VARCHAR(255)) 
AS
  DECLARE 
    l_msg VARCHAR(255) WITH NULL;

BEGIN

  INSERT INTO testtbl (
    cola)
  VALUES (
    :p_cola);

  IF iirowcount = 1 THEN
    l_msg = 'Employee was marked';
    COMMIT;
  ELSE
    l_msg = 'Employee was not marked - record error';
    ROLLBACK;
  
  ENDIF;
  
  RETURN ROW(:l_msg);
  
END;

When i try and run

SELECT * FROM si_testtbl(p_cola = 8)

When running this (through the Ingres SQL tool) i get: Unsupported procedure statement type: APP

Anyone know how this is corrected? (Can exchange Ingres syntax help with any complex SQL, business analysis or system architecture help).

Thanks All.

Regards,
Trent

[Bodo]

I assume this is 9.3 (otherwise you couldn't use a row-producing procedure in the FROM clause) of the SELECT statement.
Have you tried without the COMMIT and ROLLBACK statement within your DB procedure?
IMHO, It makes no sense to allow them when being executed within a SELECT statement.

[trentm]

This is where i'm unsure. I'm used to writing Informix SPL procedures, where my direct command line is EXECUTE PROCEDURE si_testtbl(p_cola = 4) and it would return the message, so i'm not sure how:

A) I'm supposed to be writing the stored procedure (I tried to mimic how i would do it in SPL); and
B) how to actually execute the procedure in pure SQL (for example at the SQL editor).

You are correct also, i'm using 9.3 for windows.

[Bodo]

You can use "EXECUTE PROCEDURE si_testtbl(p_cola = 4)" in Ingres as well.

Rather than specifying a RESULT ROW you can just use the MESSAGE statement within the DB procedure, e.g.:
MESSAGE 'Employee was marked';
ISQL will print all messages in the output.
In ESQL/C you can specify a messagehandler or use the WHENEVER SQLMESSAGE statement in connection with INQUIRE_SQL to get the last messagenumber or messagetext.

[trentm]

Bodo,

So far what i've found. When I removed the return row expression, the SP worked fine and inserted the record - I was also able to call it using EXECUTE PROCEDURE.

Any attempt to use return row, with any type of transactional operation (update, insert, delete) fails miserably. With the SP defined as RETURN ROW you can only use the SELECT * FROM ss_spname() syntax, EXECUTE PROCEDURE fails with: Row producing procedure 'xxxx' cannot be called using Dynamic SQL or from within another procedure.

I guess now what i need to know is: Does Ingres actually support stored procedures that can return multiple rows with defined outputs, as well as complete update/insert/deletes/selects all within the same SP? Also, does it allow SPs to be called from within other SPs if they return rows, i.e. inside a "FOR/DO/ENDFOR" block?

Regards,
Trent

[manjeera]

Inserts, Updates and Deletes are not valid operations within Row Producing / Table Procedures. This is not a supported feature as of 9.3.

Yes Table Procedures / Procedures returning multiple rows can be invoked within a normal SP and Row Producing SP.

Here are some egs:


create procedure jdbc53_tproc1(p1 int not null) result row tpr1(tp_emp_id int, tp_emp_gender char, tp_emp_name varchar(10), tp_dept_code nchar(3),tp_emp_bonus_percentage float, tp_emp_hiredate ingresdate,tp_emp_salary money,tp_emp_status byte(8), tp_emp_joining_time timestamp)as declare l1 int;l2 char; l3 varchar(10); l4 nchar(3);l5 float;l6 ingresdate; l7 money; l8 byte(8); l9 timestamp; begin for select emp_id,emp_gender,emp_name,dept_code,emp_bonus_per centage,emp_hiredate,emp_salary,emp_status,emp_joi ning_time into :l1,:l2,:l3,:l4,:l5,:l6,:l7,:l8,:l9 from jdbc53_emp where emp_id >=1 do return row(:l1,:l2,:l3,:l4,:l5,:l6,:l7,:l8,:l9); endfor; end;\g


create procedure jdbc53_tNest result row tpr1(tp_emp_id int, tp_emp_gender char, tp_emp_name varchar(10), tp_dept_code nchar(3),tp_emp_bonus_percentage float, tp_emp_hiredate ingresdate,tp_emp_salary money,tp_emp_status byte(8), tp_emp_joining_time timestamp)as declare l1 int;l2 char; l3 varchar(10); l4 nchar(3);l5 float;l6 ingresdate; l7 money; l8 byte(8); l9 timestamp; begin for select tp_emp_id,tp_emp_gender,tp_emp_name,tp_dept_code,t p_emp_bonus_percentage,tp_emp_hiredate,tp_emp_sala ry,tp_emp_status,tp_emp_joining_time into :l1,:l2,:l3,:l4,:l5,:l6,:l7,:l8,:l9 from jdbc53_tproc1(1) do return row(:l1,:l2,:l3,:l4,:l5,:l6,:l7,:l8,:l9); endfor; end;\g


create procedure tblproc000_tproc_normproc(p1 int not null)
as declare l1 varchar(20) not null;begin for select tp_emp_name into :l1 from
jdbc53_tproc1(1) where tp_emp_id=1 do message l1; endfor; end; \g

Regards,
Manjeera