MySQL Forums
Forum List  »  Informix

Re: Using dynamic SQL in Informix's stored procedure..
Posted by: Anders Karlsson
Date: August 11, 2004 03:31AM

This is an Informix queston, but I'll try to reply anyway. You'd be better off with MySQL though :-)

To begin with, Informix SPL does not support dynamic SQL (i.e. you cannot use the PREPARE statement).

Secondly, the way you have written your code, the table name is a parameter, which is not allowed syntax. You cannot have a parameter as a table name, column name or a syntactic element, only as a value, typically in a WHERE clause.

Thirdly, the control flow in your procedure is incorrect (but this matters little, as it wouldn't work anyway because of the two problems noted above. In general, the FOREACH loop is used to loop through the rows of a declared cursor. So if we assume that the PREPARE statement really WAS supported, which it is not, the your code should look somethng like this (appologize for any small errors here, it was some years ago since I wrote Informix SPL code):
Let tmpstr='Employee';
prepare s1 from "Select Region_name from ?";
foreach curTemp For s1
FETCH s1 INTO somecol;
RETURN somecol WITH RESUME;
END FOREACH
END PROCEDURE;

(I admit that the above has several problems. For example that the procedure can only return 1 column. But the # of columns has to be known at the time the SPL routine is declared in Informix SPL).

Now, one possible way to deal with this would be to return a ROW type, and using table inheritance or something like that. I'm not SURE that would work though, probably not, this is just a thought. And this would only fix the problem of being able to return variable # of columns. The dynamic nature of thing MIGHT be solved with an external routine in a REAL programming language (like C) :-)

Good luck

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using dynamic SQL in Informix's stored procedure..
13941
August 11, 2004 03:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.