Re: Pro*C - nice coding techniques

From: Stephane Hamel <shamel_at_mais.hydro.qc.ca>
Date: 7 Feb 92 12:40:00 GMT
Message-ID: <shamel.697466400_at_tdsb-s>

ccdavid_at_uqvax.cc.uq.oz.au writes:

>We are currently working on a fairly large project in Pro*C.
>I have been experimenting with *nice* ways to declare SQL variables
>but have yet to find a way that is easy to maintain, easy to debug,
>readable and reasonably efficient (not just speed, size as well i.e
>less code to write).

We have done the same thing here also...

>I don't like using explicit declarations e.g. char str[50];
>because magic nubers (i.e. 50) suk, esp. when they are likely to
>change often. I need to #define magic numbers for use in the code
>*and* have them hard coded in the SQL declaration. This becomes
>awkward when there are 200+ variables. Also, it doesn't lend itself
>to the use of structures very well (my main gripe).

If you explicitly declare the length of each (character) variable, you should at least keep a "dictionary-like" header file indicating the length of those vars. I also keep one header for the definition of each table.

>Putting a single database record into a structure is a great way to
>shuffle the data around in that you only have to deal with one
>item. Plus it seems more logical to be able to reference a sub-item
>in a record and use it to select related records from other tables.
>Only trouble is Pro*C hates structures.

That's the solution I have retained. Grouping each function related to a specific database into one source. In this source, I can declare all my "host" variables and use them as temporary storage and pass them to the structure given in parameter to the called function.

[stuff removed...]

Here is a basic example of what I have done (Each file name is indicated in comment):

/* ora_dict.h */
#define CODE_X 10
#define CODE_Z 12

/* some_table.h */
#include "ora_dict.h"

typedef struct {

  char	code_x[CODE_X+1];
  int	value_y;

  } SOME_TABLE; long OraFetchSomeTable(char *pt_code,SOME_TABLE *ps_some_table);

/* some_table.pc */
#include "some_table.h"

EXEC SQL BEGIN DECLARE SECTION

 VARCHAR	code_x[CODE_X];
 int		value_y;

EXEC SQL END DECLARE SECTION; long OraFetchSomeTable(char *pt_code,SOME_TABLE *ps_some_table)

   {

   CHAR_TO_ORA(code_x,pt_code);				/* Copy the key */
   memset(ps_some_table,NULL,sizeof(SOME_TABLE));	/* Erase the record */

   EXEC SQL						/* Fetch the record */
      SELECT value_y
      INTO   :value_y
      FROM   some_table
      WHERE  code_x = :code_x;

   ORA_TO_CHAR(ps_some_table->code_x,code_x);		/* Copy the key */
   ORA_TO_INT(ps_some_table->value_y,value_y;		/* And the value */

   }

NOTE: I have defined some macros to facilitate and make it easier to read the

      code where lot of fields are moved around:

     #define INT_TO_ORA(x,y)	x=y
     #define CHAR_TO_ORA(x,y)	x.len=strlen(y);memmove(x.arr,y,x.len);
     #define ORA_TO_CHAR(x,y)	strncpy(x,y.arr,y.len);x[y.len]='\0';
     #define ORA_TO_INT(x,y)	x=y;
     #define ORA_SETLEN(x)	x.arr[x.len]='\0';

     I also have defined what I think is a good way to indicate variable names.
     Each variable is "qualified" with it's abreviation type (i.e. before it's
     name, I put some letters indicating it's type).
	char x[n]	is t	-> t_x
	char *x		is pt	-> pt_x
	int x		is i	-> i_x
	struct x	is s	-> s_x
	struct *x	is ps	-> ps_x
	and so on...
     So when I look at the code, I always know what type is each variable...

     I usually don't hesitate to put names that are clear enough, even if it
     makes them longer...

If you want to share more on Pro*C or Oracle, don't hesitate!

--
Stephane Hamel - SHamel_at_Mais.Hydro.Qc.Ca	| AutoControle Inc.
Analyst/Programmer      			| Montreal, Quebec, Canada
"Traped in the inifinite loop of learning..."	| Tel.: 1 514 858 7882

Received on Fri Feb 07 1992 - 13:40:00 CET