Re: How to fetch array of strings in Pro*C?

Steve Tolkin (tolkin_at_ksr.com) wrote:
> Background: I am writing a Pro*C program that ought to be
> straightforward, but I am not sure the best way to go about it.
> The database table has two columns called name and value; both are of
> type varchar2. I want to fetch them into my Pro*C program, in order
> to build a kind of lookup table.
> My basic goal is to have each of these be an array of plain old C strings.
> I believe I want to use array fetch, for performance reasons.
> I need to have no trailing blanks. I am running Oracle 7.0.13.
 

> I believe I want to use Pro*C data type varchar.
> It seems I must myself insert the null terminator in the C string.
> (I could use Pro*c data type char, but then I would need to scan the
> string backwards, replacing each trailing blank with a null byte.)
 

> My questions:
> 1. How do I determine in advance the number of rows I will fetch?
> If I do a select count(*) first I am worried that the table
> might be changed change between that query and the one I am fetching
> from. Should I be prepared to malloc and realloc after fetching each
> batch.

First you lock the table. This has the advantage that no-one can change the data after you've loaded it into your array. Trust me, this makes things easier. Unfortunately, there's no way to release the lock apart from doing a commit or rollback, so everyone will curse you while your program is running.

> 2. Is there any standard way to create an array of char * from
> the array of varchar? This includes putting the null terminator in,
> and copying the address to the array.

If you declare your array as:
EXEC SQL BEGIN DECLARE SECTION;
char units[50][7];
EXEC SQL VAR units IS STRING;
EXEC SQL END DECLARE SECTION; Then you have an array of 50 null terminated strings. Each string can be upto 6 characters in length (plus the terminator). You can use this in your array fetch, and then copy the results into some malloc'd workspace.

Alternatively, this might work (I haven't tried it),

	typedef char[7] my_string;
	EXEC SQL TYPE my_string IS STRING(7);
	EXEC SQL BEGIN DECLARE SECTION;
	my_string *units;
	EXEC SQL END DECLARE SECTION;

	units=malloc(7*rows);

	EXEC SQL OPEN cursor;
	EXEC SQL FOR :rows FETCH cursor INTO :units;
	EXEC SQL CLOSE cursor;

Note that, despite what the manuals might imply, there is NO checking on the value used in the FOR clause: it just tries to fetch that many rows. So it's upto you to make sure that there's enough memory available. This does mean that the pre-compiler DOESN'T have to know how big the array is, but you do still need to convince it that it's an array of the right type.

--
  _________________________   _______________________________
 /  Tommy Wareing          \ /                               \
|  p0070621_at_brookes.ac.uk   X  'Blobby, blobby, blobby!'      |
 \  0865-483389            / \    said Mr. Blobby            /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~