Does anyone know of a C++ code generator to call stored procedures

[marlow...@googlemail.com]

{ Please limit your text to fit within 80 columns, preferably around 70,
so that readers don't have to scroll horizontally to read each line.
This article has been reformatted manually by the moderator. -mod }

Hello guys,

I am looking at various database libraries for C++ but have not been
able to find exactly what I am searching for. I hope someone can point
me in the right direction, if there is one.

I am after something that will enable a C++ program to call stored
procedures that have already been written. As you read on, you might
think of tools that enable stored procedures to be generated along with
corresponding CRUD code in C++ but the stored procs that I have in mind
already exist.

It is more than just the means of invoking the sp. It is also a way to
nicely automate the population of the result set so that a related object
is produced. Ideally the object should act like a vector of objects whose
type corresponds to a row from the result set. Since the classes have a
shape that is peculiar to the shape of the result set I anticipate that
what I am looking for will be some sort of code generator.

This is a tall order, I know, but here are some other requirements that
make it even harder:

o it should be vendor neutral and work with oracle, sybase and
postgres as a minimum.
o Ideally the code generator will use the data dictionary to get
the information it needs. This would avoid the user having to
redescribe the shape of the result set in a separate input to
the generator. This might make the generator extremely complex
since a strongly typed stored proc may be expressed in terms of
several user defined types.
o It should use the native API for certain well known proprietary
RDBMS, e.g. OCI for Oracle, CTLIB for sybase. This avoids the
ineffiencies of ODBC in these cases.
o Ideally the code generator should generate result set accessor
names derived from the names in the parameter list of the return
value. This would be better than using generically named
acessors. It enables the code that uses the output from the code
generator to be more readable since the reader can see which
parts of the result set are being used.

I have seen various database wrappers in my travels. Several are
multi-RDBMS, portable and useful. The OTL is the main one I am familiar
with. However, none of them have the code generator I am talking about.
They allow sps to be called but setting up the binding and creating a
suitable object type to hold the results is up to the caller.

I have only seen two code generators that come close. One was Sybase only
and completely exposed its use of CTLIB. So that would be no good for me
in my current environment, which uses Oracle. And the other is in C# and
is Oracle only. Deep sigh.


Regards,

Andrew Marlow


--
[ See http://www.gotw.ca/resources/clcm.htm for info about ]
[ comp.lang.c++.moderated. First time posters: Do this! ]

[James K. Lowden]

On Fri, 3 May 2013 10:51:38 -0700 (PDT)
marlow...@googlemail.com wrote:

> It is more than just the means of invoking the sp. It is also a way to
> nicely automate the population of the result set so that a related
> object is produced. Ideally the object should act like a vector of
> objects whose type corresponds to a row from the result set. Since
> the classes have a shape that is peculiar to the shape of the result
> set I anticipate that what I am looking for will be some sort of code
> generator.

I like my library: http://dbstreams.schemamania.org/.

Efficiency is not a big concern with a database client library because
network I/O and server execution dominates any time spent in the call
stack.

You're correct that it's possible to generate data structures and code
to call a stored procedure on an array of structures or populate an
array of structures from a SELECT statement. (However, it's not
possible to portably or reliably ascertain the columns produced by a
strored procedure.) That said, for several reasons it's not much of a
win.

1. The names used for the struct members would either be taken from the
column/parameter names, or would have to be supplied to the generator.
Column names are in general too long and clunky to be used
conveniently in a struct. If a mapping is supplied, it's hardly
"automatic".

2. The generated code serves only as a template. Changes made to it
with a text editor are hard to preserve if the code is re-generated
later as the database changes. Iterative development virtually assures
database changes, often significant ones, as the work progresses. To
keep things fully automatic, then, the development rule must be: Don't
touch the generated code, leading to #3,

3. Generated code becomes Just Another interface to the application.
Internal to the application, you will have your "real" structures, the
ones manipulated by your application logic. You will be forced to copy
the data to/from the internal structures to the generated ones. That
effort wipes out any convenience of using generated code.

The trick, then, is not code generator, but a library that is easy to
use. I'm not surprised you haven't found one. The couple dozen
I've looked at range from clunky to awful.

For my money, Stroustrup's stream metaphor is easy to use and
understand. Automatic type conversion in C++ eliminates the fiddly
column bindings needed in C.

In dbstreams (and similarly in OTL), you write only the I/O functions
that map your internal structures to the database. Each function is
hardly any more lines than the number of members of the structure.
The mapping is explicit and plastic, easy to modify as the database
changes, and can be neatly segregated from the rest of the code.

--jkl

[woodb...@googlemail.com]

{ Please remove the double spacing when quoting.
Manually reformatted. -mod }

On Friday, May 3, 2013 12:51:38 PM UTC-5, marlow...@googlemail.com wrote:
> Hello guys,
>
> I am looking at various database libraries for C++ but have not been
> able to find exactly what I am searching for. I hope someone can point
> me in the right direction, if there is one.
>
> I am after something that will enable a C++ program to call stored
> procedures that have already been written. As you read on, you might
> think of tools that enable stored procedures to be generated along with
> corresponding CRUD code in C++ but the stored procs that I have in mind
> already exist.
>
> It is more than just the means of invoking the sp. It is also a way to
> nicely automate the population of the result set so that a related object
> is produced. Ideally the object should act like a vector of objects whose
> type corresponds to a row from the result set. Since the classes have a
> shape that is peculiar to the shape of the result set I anticipate that
> what I am looking for will be some sort of code generator.
>
> This is a tall order, I know, but here are some other requirements that
> make it even harder:

If you decide to work on this and decide to make
it an on line code generator, you're welcome to
use the code available here --
http://webEbenezer.net/build_integration.html --
to help build a distribution system.

I use a three-tier architecture to help with the
distribution. The back tier is the code generator.
The front tier is a command line interface that can
be called from makefiles. The middle tier is used
to funnel requests from front tier instances through
one machine in order to simplify network administration
and help with efficiency.

I believe you when you say it's a big project. I've
been working on a code generator for over 10 years.

Brian
Ebenezer Enterprises

[marlow...@googlemail.com]

On Sunday, May 5, 2013 6:56:07 AM UTC+1, James K. Lowden wrote:

> I like my library: http://dbstreams.schemamania.org/.
>

First, thank you for your very detailed reply :-)

> 1. The names used for the struct members would either be taken from the
> column/parameter names, or would have to be supplied to the generator.
> Column names are in general too long and clunky to be used
> conveniently in a struct.

This is a danger but not in C# env I am currently in.
I reckon this danger was anticipated by the designer and
caused them to create tables with very suitable names for
the code generator. This is obviously not a general purpose
solution since other projects may find that the names in the
database can be legacy names and not suitable to be used verbatim
in C++ (or C#).

> If a mapping is supplied, it's hardly "automatic".

Indeed. And this was the case for the only time I have
seen such a code generator for C++. It was because the table and
column names were totally unsuitable.

I think you make the point well. This really has made me think again
about where or not a code generator would be a good thing in the
general case. It happens to be good for the C# I am working with at
the moment.

> 2. The generated code serves only as a template. Changes made to it
> with a text editor are hard to preserve if the code is re-generated
> later as the database changes. Iterative development virtually assures
> database changes, often significant ones, as the work progresses. To
> keep things fully automatic, then, the development rule must be: Don't
> touch the generated code, leading to #3,

I don't agree with this one. Having seen a C++ solution (for sybase only)
I can atest that the generated code was not a template but really
was the code that was used verbatim.

> 3. Generated code becomes Just Another interface to the application.
> Internal to the application, you will have your "real" structures, the
> ones manipulated by your application logic. You will be forced to copy
> the data to/from the internal structures to the generated ones.

I disagree. The C++ code generator I saw generated the structures and
there was no need to copy to other ones.

> The trick, then, is not code generator, but a library that is easy to
> use. I'm not surprised you haven't found one. The couple dozen
> I've looked at range from clunky to awful.

You may have a point there. :-)

> For my money, Stroustrup's stream metaphor is easy to use and
> understand. Automatic type conversion in C++ eliminates the fiddly
> column bindings needed in C.
> In dbstreams (and similarly in OTL), you write only the I/O functions
> that map your internal structures to the database.

Indeed. This is what I like about OTL. I will take a look at your
library due its similar stream based approach.

-Andrew Marlow