Re: Need fetch row by row, without foreach loop (SPL)
The poster wanted to fetch rows one at a time inside an Informix SPL routine, without using a FOREACH loop (effectively wanting explicit cursor/FETCH control or dynamic SQL). The suggestion was the Exec DataBlade, which provides dynamic SQL on 9.x engines, and specifically its Exec_For_Rows() iterator UDF that returns multiple rows from a dynamically built SELECT. One reader confirmed using it and offered sample code by email, warning that before version 9.40 the result is limited by LVARCHAR's roughly 2K maximum. No true row-by-row FETCH alternative to FOREACH was demonstrated in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Paulo Cooker wrote: > What is exec data blade? It's a DataBlade that allows you to do dynamic SQL if your engine version is 9.x. -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche
What is exec data blade?
Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:<brt1fn$75tpp$2@ID-64669.news.uni-berlin.de>... > Paulo Cooker wrote: > > > What is exec data blade? > > It's a DataBlade that allows you to do dynamic SQL if your engine version is > 9.x. Ok, I get exec BladeLet, but I can't find nothing like fetch command.
Paulo Cooker wrote: > Obnoxio The Clown <obnoxio@hotmail.com> wrote in message > news:<brt1fn$75tpp$2@ID-64669.news.uni-berlin.de>... >> Paulo Cooker wrote: >> >> > What is exec data blade? >> >> It's a DataBlade that allows you to do dynamic SQL if your engine version >> is 9.x. > > Ok, I get exec BladeLet, but I can't find nothing like fetch command. <sigh> Did you read the documentation? -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche
Is this you talking about? How Can I read row by row without foreach?
EXEC_FOR_ROWS ( LVARCHAR ) RETURNS LVARCHAR WITH ( ITERATOR )
The Exec_For_Rows() UDF is an Iterator, which means that it can return
more than one result row. Of course, it only does so when it is asked
to execute a SELECT. Otherwise, it behaves exactly as the Exec() UDF.
For example:
EXECUTE FUNCTION Exec_For_Rows("SELECT * FROM Foo WHERE A IN (
1,2,3,4);") ;
(expression) ROW(1,'Zap!',SET{1,2,3})
(expression) ROW(2,'Zap!',SET{4,5,6})
(expression) ROW(3,'Stay Here',SET{7,8,9})
(expression) ROW(1,'Zero,Zero,One',SET{0,1})
(expression) ROW(2,'Zero,Zero,Two',SET{0,2})
(expression) ROW(3,'Zero,Zero,Three',SET{0,3})
(expression) ROW(4,'Zero,Zero,Four',SET{0,4})
(expression) ROW(1,'Zero,Zero,One',SET{0,1})
(expression) ROW(2,'Zero,Zero,Two',SET{0,2})
(expression) ROW(3,'Zero,Zero,Three',SET{0,3})
(expression) ROW(4,'Zero,Zero,Four',SET{0,4})
Paulo Cooker wrote:
> Is this you talking about? How Can I read row by row without foreach?
You tell me, mate.
> EXEC_FOR_ROWS ( LVARCHAR ) RETURNS LVARCHAR WITH ( ITERATOR )
>
> The Exec_For_Rows() UDF is an Iterator, which means that it can return
> more than one result row. Of course, it only does so when it is asked
> to execute a SELECT. Otherwise, it behaves exactly as the Exec() UDF.
> For example:
>
> EXECUTE FUNCTION Exec_For_Rows("SELECT * FROM Foo WHERE A IN (
> 1,2,3,4);") ;>
> (expression) ROW(1,'Zap!',SET{1,2,3})
> (expression) ROW(2,'Zap!',SET{4,5,6})
> (expression) ROW(3,'Stay Here',SET{7,8,9})
> (expression) ROW(1,'Zero,Zero,One',SET{0,1})
> (expression) ROW(2,'Zero,Zero,Two',SET{0,2})
> (expression) ROW(3,'Zero,Zero,Three',SET{0,3})
> (expression) ROW(4,'Zero,Zero,Four',SET{0,4})
> (expression) ROW(1,'Zero,Zero,One',SET{0,1})
> (expression) ROW(2,'Zero,Zero,Two',SET{0,2})
> (expression) ROW(3,'Zero,Zero,Three',SET{0,3})
> (expression) ROW(4,'Zero,Zero,Four',SET{0,4})
--
"C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule"
- Coluche
"Paulo Cooker" <larini@email.com> wrote in message news:8efe8056.0312200007.4d22ff3b@posting.google.com...
> Is this you talking about? How Can I read row by row without foreach?
>
> EXEC_FOR_ROWS ( LVARCHAR ) RETURNS LVARCHAR WITH ( ITERATOR )
>
> The Exec_For_Rows() UDF is an Iterator, which means that it can return
> more than one result row. Of course, it only does so when it is asked
> to execute a SELECT. Otherwise, it behaves exactly as the Exec() UDF.
> For example:
we use it. u can get in touch with me thru email for sample code.
remember there are some limitations with exec. for version prior to
9.40, the entire result has to be less than 2K bcos lvarchar can be
max of that size.
>
> EXECUTE FUNCTION Exec_For_Rows("SELECT * FROM Foo WHERE A IN (
> 1,2,3,4);") ;>
> (expression) ROW(1,'Zap!',SET{1,2,3})
> (expression) ROW(2,'Zap!',SET{4,5,6})
> (expression) ROW(3,'Stay Here',SET{7,8,9})
> (expression) ROW(1,'Zero,Zero,One',SET{0,1})
> (expression) ROW(2,'Zero,Zero,Two',SET{0,2})
> (expression) ROW(3,'Zero,Zero,Three',SET{0,3})
> (expression) ROW(4,'Zero,Zero,Four',SET{0,4})
> (expression) ROW(1,'Zero,Zero,One',SET{0,1})
> (expression) ROW(2,'Zero,Zero,Two',SET{0,2})
> (expression) ROW(3,'Zero,Zero,Three',SET{0,3})
> (expression) ROW(4,'Zero,Zero,Four',SET{0,4})