Re: ProC SQL Question Regarding Cursors

Melissa wrote:
> I need some advice - I was taught that when using cursors, I should
> follow this format:
>
> EXEC SQL
> DECLARE cursor_name CURSOR FOR
>
> <SQL Statement inserted here>
>
> EXEC SQL OPEN cursor_name;
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> for (;;)
> {
> EXEC SQL FETCH cursor_name INTO
> :variable1 :nvariable1,
> :char1 :nchar1,
> :int1 :nint1;
>
> }
>
> It was my understanding for each field, you needed another place
> holder that was defined as a "short" (:nvariable1, :nchar1, :nint1 are
> defined as short values).
>
> What bothers me is that I have seen the above fetch done without these
> integer values associated with the variables. Is what I learned
> wrong?
>
> Thanks!
>
> Melissa

No, you training is incomplete. You are trying to interface two very different programming languages, C, and SQL.

One crucial concept in SQL is that of NULL. In SQL a variable can have a NULL value, meaning it doesn't have ANY value. It's the all consuming nothing! 8^)

In C programming there is no such thing. Every variable has a value even if the value is zero, which for character data, in ACSII is referred to as nul(0).

So when fetching values from SQL into C variables, you need two parts

• a place to hold that value (if any)
• a place to hold the indicator (those "short" variables) The indicator value lets you know whether you can really use the value, ie whether it is NULL or not.

Now your question can be rephrased as:
when can I ignore the possibility of a NULL being returned from SQL?

Well, if you take a step back and think about it there are a few cases. The simpliest one is when the field being fetched is a primary key. In SQL a primary key, by definition CANNOT be NULL. So you know that if any row is returned, then that field must have a value. So you could optionally leave out the indicator. I'll leave you to think up some of the other cases where you can leave off the indicator.

Final note: All of these concepts are in the manual which you should have read before asking this question. But sometimes a different presentation makes things clear.

HTH

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost