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, as it is possible in other RDBMS. If its not possible in informix how can we do it, is it thru 4gl or esqlc. Please advice. I would appreciate your prompt reply.

Best Regards,

lloyd

If you are using informix 9.1 and above, I think this feature is supported using Datablade (you will need to download and set this up on the server to make it work). You will also need to download also the docs (google: informix datablade) to understand how to implement this.

Informix 4GL supports dynamic SQL (prepared statements), although I'm not sure on how you are going to call the 4GL from your client.

You can also create multiple SPs to achieve this restriction, something like:

procedure add_user(col1 char(8), col2...)
insert into progusr
values (col1, col2, ...);
end procedure;

procedure del_user(...

procedure open_account(...

procedure close_account(...

Hope this helps.

Hi Ghed,

Thanks once again for your feedback, even i read about datablades, but then that would be in unix env since it uses c prgs.

Best Regards,

lloyd