Re: Looking for C & SQL parm tips/experiences.

In <1993Feb11.003206.9136_at_dhw68k.cts.com> daveegan_at_dhw68k.cts.com (Dave Egan - N6XVZ) writes:

>We are designing a system that will be written in C and
>accesses a relational database (using embedded SQL or possibly
>a popular RDBMS API). This a a very large application that will
>involve hundreds of modules and approximately 36 tables.
>The sheer size of the task complicates what can normally be
>considered a straight-forward method of passing parameters.
>We want to keep our database calls in a lower layer and have
>been kicking around alternatives for how to set up the calling
>interface.

	Building a wrapper around SQL calls is generally a big mistake.
	I most commonly have seen it in shops with large, very unsophisticated
	staff. It used to be common (in the early days of commercial RDBMS)
	for consulting firms to contract to build large projects, but build
	"access layer" routines because their staff couldn't deal with the
	complexity of SQL.

	The result of this type of thing is terrible design mess. There is
	no functional gain between these and random access file - the main
	justification of DBMS is lost. Many have argued that centralizing the
	SQL calls allows wrapping business rules in a central place, allowing
	the most sophisticated programmers to create the best SQL, and put
	edits in a central place. Most practice shows this to be false - the
	same call tends to be used in multiple places, and the SQL writer
	is at a distance from the requirement.

	The only sane way to use this type of wrapper functionality is use
	one that is part of the base technology - either builtin trigger type
	mechanisms and referencial constraints in RDBMS or methods incorporated
	into OODBMS.

>We've come up with two different methods so far and corresponding
>issues:
 

>1) Call the low-level SQL routine with a pointer to a structure
> that was obtained by the caller. Or should the called routine
> do the malloc()? We've seen cases where the caller had the
> storage defined smaller than the SQL routine, so when the
> low-level routine moved data into it, other storage areas
> following the row definition were corrupted (makes for a
> debugging nightmare!) Yet, if the called SQL routine gets
> the storage, how can you guarantee that the caller gets to
> or remembers to free it later?
 

>2) Code our SQL statements to return only the columns that are
> needed -- everyone says that is more efficient than returning
> a whole row. The issue here is that with hundreds of SQL
> functions, we'll have #include's for every one -- a
> maintenance nightmare. We could pass a single structure
> that represents the whole row and just "fill in" the columns
> we need, but when the row changes, all the affected programs
> need to be recompiled.
 

>
>So what have you folks done? Any slick ideas? Any experiences
>and suggestions would be greatly appreciated.
 

>If there is sufficient interest, I'll post a response back to
>the net.
 

>--
>Dave Egan | Radio: N6XVZ
> uucp: ...{spsd,zardoz,felix}!dhw68k!daveegan | n6xvz.ampr.org[44.10.0.176]
> : daveegan_at_dhw68k.cts.com | n6xvz_at_wa6fwi.#SOCAL.CA.USA.NA

-- 

Aaron Werman		awerman_at_panix.com