Informix Stored Procedures 1

[mynwo]

Dear All,

I need some help on stored procedures, whether we can use dynamic sql in procedures. My query is say i have a table test1 with 2 columns, I want to create a stored procedure where my user will only supply the values to me, i will enter the insert/update statements in the backend in my procedures. for eg. if the user wants to insert values thru java, he will pass the string & values to me thru the procedure, he will not write insert statements, based on that values i will insert data thru procedures to my table. Basically we do not want the user to write insert/update codes. Pls. let me know if its possible in stored procedure, or else how we can do in esqlc. I would appreciate your prompt reply.

Best Regards,

lloyd

 

[vpshriyan]

lloyd:

Here is a demo table:
create table testtab (f1 smallint,f2 char(3));

and here goes a demo stored procedure:
create procedure testtab_proc(
mf1 char(8), mf2 char(7))

define cnt int;

set lock mode to wait;

while 1=1
insert into testtab values (mf1, mf2);

select dbinfo('sqlca.sqlerrd2') into cnt from
systables where tabid=1;
if cnt is null then
let cnt=0;
end if
if cnt = 1 then
exit while;
end if
end while
end procedure;

and finally this is how you may invoke the SPL using SQL:
execute procedure testtab_proc(1,'AAA');

Regards,
Shriyan

"An ounce of example is worth a pound of advice."

 

[mynwo]

Hi Shriyan,

Thanks for your prompt reply, how can i execute this procedures thru java, is it possible. Because we don't want our users to write the insert/update statements.

Best Regards,

lloyd

 

[vpshriyan]

lloyd:

execute procedure procedure_name(argument, argument,...); is a SQL syntax. That means you can use it in any frontend tool, since most of the generic frontend tools support passing of SQL strings to the backend database server. It is upto the dbserver to decipher it; and see, is it supported and executable.

It is to be noted that in the above syntax the coder or the user is not issuing any insert statement. They only pass the argument to the SPL; and SPL takes care of the passed parameters.

I am not a java programmer, Thus I may not be much helpful in this regard.

Regards,
Shriyan

 

[mynwo]

Hi Shriyan,

Thanks for your prompt feedback, i did try the procedure & it worked fine thru sql. Can you pls. tell me what does this statement do, i think sqlerrd2 gets the serial value of the column. When i run this sql, first it gives me 3 rows affected, then it gives me only 1 row affected.
select dbinfo('sqlca.sqlerrd2') into cnt from
systables where tabid=1;
Also if you could give some examples for update & delete clause. Appreciate your feedback.

Best Regards,

lloyd

 

[vpshriyan]

lloyd,

select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;

This sql statement will return you a count, that is number of rows that have got affected in the immediate previous insert or update statement.

For update & delete stored procedure to function properly, one must have primary key available in a given table, and should pass it as one of the parameter to a SP.

Here is a demo update stored procedure:
create procedure testupd_proc(mf1 smallint, mf2 char(3))

define cnt smallint;
set lock mode to wait;

while 1=1
update testtab set f2=mf2 where f1=mf1;

select dbinfo('sqlca.sqlerrd2') into cnt from
systables where tabid=1;
if cnt is null then
let cnt=0;
end if
if cnt = 1 then
exit while;
end if
end while
end procedure;

For delete procedure DML line will look like:
delete from testtab where f1=mf1;
rest of the source codes will remain same.

Demo update SP execution:
execute procedure testupd_proc(1,'BBB');

Demo delete SP execution:
execute procedure testdel_proc(1);

Regards,
Shriyan

 

[mynwo]

Hi Shriyan,

Thanks a lot for your feedback.

Best Regards,

lloyd