Need fetch row by row, without foreach loop (SPL)
The poster asked whether SPL can fetch rows one at a time without a FOREACH loop (SQL Server-style explicit cursor OPEN/FETCH), because he was automatically converting thousands of SQL Server procedures, some of which fetch only one or two rows. Replies questioned the need, suggesting set-based SQL (INSERT...SELECT, temp tables) instead, and noted the Excalibur/exec DataBlade has its own fetch APIs. The practical workaround offered was to use FOREACH with EXIT FOREACH after the desired row(s), plus CONTINUE FOREACH and RETURN WITH RESUME. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
There is a way, any way to do this in SPL? Can be using external proc, C, anything... thanks.
"Paulo Cooker" <larini@email.com> wrote in message news:8efe8056.0312170801.2ebe400a@posting.google.com... > There is a way, any way to do this in SPL? > Can be using external proc, C, anything... Yes you can if you are using exec data blade. It comes with its own APIs on how to fetch rows. However I am curious why would anyone want to avoid FOREACH. This is like saying "can I insert into a table without INSERT statement" :-)
On Wed, 17 Dec 2003 11:06:47 -0500, "Ravi Krishna" <rkdba@sympatico.ca> wrote: > >"Paulo Cooker" <larini@email.com> wrote in message >news:8efe8056.0312170801.2ebe400a@posting.google.com... >> There is a way, any way to do this in SPL? >> Can be using external proc, C, anything... > >Yes you can if you are using exec data blade. It comes with its own >APIs on how to fetch rows. > >However I am curious why would anyone want to avoid FOREACH. This is like >saying "can I insert into a table without INSERT statement" :-) > > 'cat', perhaps? 8-) Disclaimer -- it's just a joke . . . . really . . . .
larini@email.com (Paulo Cooker) wrote in message news:<8efe8056.0312170801.2ebe400a@posting.google.com>...
> There is a way, any way to do this in SPL?
> Can be using external proc, C, anything...
I suspect, Sir, that you are up to no good!
Way, way back, when I was but a sprog with a fresh minted
bachelor's degree and an inflated sense of my importance and the
extent of my knowledge, my boss sent me along to an Ingres Developer
training course. I was unenthusiastic. If it couldn't be done in three
lines of Scheme, I reasoned, then it shouldn't be attempted in the
first place.
But the instructor was cute, so I gave the class material more
than my usual attention. And our cute instructor said something wise
and profound that has stuck with me ever since. (Actually, she was
reading from the notes, and it was 4:30pm on the last day of the
course, and I had a hangover, but I demand the right to mythologize
moments from my youth, thank you very much.)
"Think Relational", was what she told us. What she meant was that
we should eschew the programming trinkets of our childhood--the loop,
the branch, the else statement, variables, recursion--and the baubles
of our wasted education--data structures, algorithms, big-O notation.
Embrace instead, the Declarative Way.
Why do you want to loop over those rows? Is it to process them to
see if a) they match some criteria, or to b) prepare them for an
INSERT or UPDATE statement? Don't do it! It's just ain't right!
Instead, 'Think Relational', and compose a single query (or set of
queries) instead.
INSERT INTO Foo
( Col_1, Col_2, Col_3 )
SELECT Col_A, Col_B, Col_C
FROM Bar;
Use temporary tables, if you need to (to get around that pesky
limit on inserting into the same table you're reading from, for
example).
So: Post a little more detail, please. Give us some DDL, some
sample data, and some statement of what you're up to. With very few
exceptions what you want to do can probably be achieved with less code
and higher performance than the FOREACH A (SELECT) END FOREACH route.
KR
Pb
"Paul G. Brown" <paul_geoffrey_brown@yahoo.com> wrote in message news:57da7b56.0312171755.12749418@posting.google.com... > larini@email.com (Paulo Cooker) wrote in message news:<8efe8056.0312170801.2ebe400a@posting.google.com>... > > There is a way, any way to do this in SPL? > > Can be using external proc, C, anything... > > I suspect, Sir, that you are up to no good! > But the instructor was cute, so I gave the class material more > than my usual attention. And our cute instructor... I don't believe a word of it.
I know that my reasons are hard to understand, but I will try to explain. I have to get thousands of sql server procedures and translate to informix. In sql server I open a cursor, fetch first row, make a loop and fetch next... In some cases, I do not make a loop, I read only 1 or 2 rows. Doing this manually, is perfect possible using foreach loop, but doing automatic, I can fault in some logic. I know that foreach loop is more easy to use and more clean too, but in sql sever we have more flexibility. thanks
Paulo Cooker wrote: > I know that my reasons are hard to understand, but I will try to > explain. > I have to get thousands of sql server procedures and translate to > informix. > In sql server I open a cursor, fetch first row, make a loop and > fetch next... > In some cases, I do not make a loop, I read only 1 or 2 rows. > > Doing this manually, is perfect possible using foreach loop, but > doing automatic, I can fault in some logic. > > I know that foreach loop is more easy to use and more clean too, but > in sql sever we have more flexibility. If you're reading 2 rows, it's still a loop... FOREACH foo INTO bar EXIT FOREACH END FOREACH ? -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche
Perhaps EXIT FOREACH, RETURN WITH RESUME and maybe CONTINUE FOREACH are the missing pieces in your jigsaw? Andy larini@email.com (Paulo Cooker) wrote in message news:<8efe8056.0312180235.15124131@posting.google.com>... > I know that my reasons are hard to understand, but I will try to > explain. > I have to get thousands of sql server procedures and translate to > informix. > In sql server I open a cursor, fetch first row, make a loop and > fetch next... > In some cases, I do not make a loop, I read only 1 or 2 rows. > > Doing this manually, is perfect possible using foreach loop, but > doing automatic, I can fault in some logic. > > I know that foreach loop is more easy to use and more clean too, but > in sql sever we have more flexibility. > > thanks