Path: utzoo!attcan!uunet!zaphod.mps.ohio-state.edu!mips!wdl1.wdl.fac.com!wdl47!eric
From: eric@wdl47.wdl.fac.com (Eric Kuhnen)
Newsgroups: comp.databases
Subject: Re: SQL Question record by record processing
Message-ID: <1990Oct26.155138.6646@wdl1.wdl.fac.com>
Date: 26 Oct 90 15:51:38 GMT
References: <1731@exodus.Eng.Sun.COM>
Sender: root@wdl1.wdl.fac.com (SUPER USER)
Organization: Ford Aerospace
Lines: 34
Nntp-Posting-Host: wdl47

bm@bike2work.Eng.Sun.COM (Bill Michel) writes:

>I want to be able to process each record in one of my tables, one by one.
>Something similar, say, in a shell script, to setting a variable to the
>contents of a directory, and then using *shift* to process each file
>one by one.

Define a cursor that selects all rows of the table you wish to loop through.
Use a fetch loop to get each row from cursor, one row at a time.  If you
have included the SQL Communications Area in your program, you could do
something like the following:

declare cursor fetch_em for 
select * from <table-name>

open cursor fetch_em

fetch * into <variable-list>

while (sqlca.sqlcode == 0)
{
  [various and sundry processing statements]
  fetch * into <variable-list>
}

close cursor fetch_em

The sqlca structure is fairly common across vendor implementations of SQL.
When sqlca.sqlcode < 0, an error has occurred in the preceeding SQL statement.
When sqlca.sqlcode > 0, the preceeding SQL statement completed execution but
some special condition came up; i.e. 100 = "no rows returned," etc.  A 0 
means that the preceeding SQL statement went off without a hitch.

"Q"