Database library: SOCI 1.2.0 available

54 views
Skip to first unread message

Maciej Sobczak

unread,
Oct 3, 2005, 9:23:07 AM10/3/05
to

The SOCI library is a database access library that provides the look and
feel of "embedded SQL", but staying withing the standard C++:


http://soci.sourceforge.net/


We are pleased to announce that the new version of SOCI library is
available for download.

The important new feature in the library allows to operate on whole
arrays of values (interfaced via std::vector), which can greatly
increase application performance by reducing network round-trips in
those queries that insert or retrieve many rows at once.


Special thanks go to Steve Hutton, whose involvement and contribution
made this new release possible.

You can visit Steve at <http://featurecomplete.com/>


Your comments are highly welcome.


--
Maciej Sobczak : http://www.msobczak.com/
Programming : http://www.msobczak.com/prog/

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

Mirek Fidler

unread,
Oct 3, 2005, 5:06:21 PM10/3/05
to
Maciej Sobczak wrote:
> The SOCI library is a database access library that provides the look and
> feel of "embedded SQL", but staying withing the standard C++:
>
>
> http://soci.sourceforge.net/
>
>
> We are pleased to announce that the new version of SOCI library is
> available for download.

> Your comments are highly welcome.

You could do better.

Our baby allows this:

int id = ...;
String name;
int salary;

sql * Select(NAME, SALARY).From(PERSON).Where(ID == id);
sql.Fetch(name, salary);

Note that this way SQL syntax is checked by C++ compiler...

What is more important, you can create SQL queries part by part:

SqlBool where;
if(!IsNull(findname))
where = NAME == findname;
if(!IsNull(findsurname))
where = where && SURNAME == findsurname;
sql * Select(PERSONID).From(PERSON).Where(where);

Mirek

Roland Pibinger

unread,
Oct 4, 2005, 9:39:38 AM10/4/05
to
On 3 Oct 2005 09:23:07 -0400, Maciej Sobczak <no....@no.spam.com>
wrote:

>The SOCI library is a database access library that provides the look and
>feel of "embedded SQL", but staying withing the standard C++:
>http://soci.sourceforge.net/
>
>Your comments are highly welcome.

1. SQL statements in code as char strings and fancy operator
overloadings are not something I want to have in my application.

2. Take a look at RogueWave's DBTools, now touted 'SourcePro',
probably the best 'database interface' libraray available in C++:
http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/index.html
An example comparable with your 'motivating code example' can be found
at:
http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/5-3.html#531

3. IMO, the best way for database access is to generate classes out of
table definitions (and maybe additonal informations) with a code
generator. For that you don't need a specific SQL-library. Users work
with objects, not bind variables and SQL becomes mostly 'invisible'.

Best wishes,
Roland Pibinger

Mirek Fidler

unread,
Oct 4, 2005, 2:48:45 PM10/4/05
to
> 2. Take a look at RogueWave's DBTools, now touted 'SourcePro',
> probably the best 'database interface' libraray available in C++:
> http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/index.html
> An example comparable with your 'motivating code example' can be found
> at:
> http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/5-3.html#531

Well, I followed that link and what is presented there is even more ugly
than SOCI... :)

> 3. IMO, the best way for database access is to generate classes out of
> table definitions (and maybe additonal informations) with a code

If you mean by this that there for any table there will be struct or
class that maps SQL record to member variables (or methods), then while
this approach is sometimes useful, it is not the "solution", nor the
"best way".

SQL is about that little part after "where", not about fetching data.

> generator.

Code generator? What for?

You can easily describe schema using C macros like this: (actual code
snippet from application I am working on right now)


TABLE_(SOCI_TRIDA) DOC("Třída sociální dávky")
INT_ (SOCI_TRIDA_SEQ) PRIMARY_KEY DOC("Sekvenční klíč")
STRING (TEXT, 200) DOC("Třída")
END_TABLE

TABLE_(SOCI_DRUH) DOC("Definice druhu druhého typu rozhodnutí")
INT_ (SOCI_DRUH_SEQ) PRIMARY_KEY DOC("Sekvenční klíč")
STRING (NAZEV, 200) DOC("Název druhu")
STRING (TEXT, 2000) DOC("Definice textu rozhodnutí ve
formátu QTF s vyuľitím maker ze SOCI_MAKRO")
INT (SOCI_TRIDA_SEQ) REFERENCES(SOCI_TRIDA) DOC("Třída
sociální dávky, odkaz SOCI_TRIDA")
STRING (ROZHODNUTI, 1) DOC("Příznak rozhodnutí")
STRING (FORMA, 1) DOC("Forma dávky")
INT (BARVA)
INT (PISMO)
END_TABLE

(SOCI_ in table names is accidental as the actual application deals with
SOCIal security system.)

Now this 'schema' is used to

- generate SQL scripts that synchronize schema with SQL server

- create C++ structs like you suggest

- define column names as special C++ constants (of type SqlId) so that
they can be safely used in SQL expressions

- generate schema documentation (based on those DOC remarks).

Now with schema defined you can do things like

String something = ....;
S_SOCI_DRUH foo;
sql * Select(foo).From(SOCI_DRUH)
.Where(SOCI_TRIDA_SEQ ==
Select(SOCI_TRIDA_SEQ).From(SOCI_TRIDA).Where(TEXT == something));

while(sql.Fetch(foo)) {
DoSomething(foo.TEXT);
}

Mirek

Roland Pibinger

unread,
Oct 5, 2005, 10:56:41 AM10/5/05
to
On 4 Oct 2005 14:48:45 -0400, Mirek Fidler <c...@volny.cz> wrote:
>> 2. Take a look at RogueWave's DBTools, now touted 'SourcePro',
>> probably the best 'database interface' libraray available in C++:
>> http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/index.html
>> An example comparable with your 'motivating code example' can be found
>> at:
>> http://www.roguewave.com/support/docs/sourcepro/html/dbcoreug/5-3.html#531
>
>Well, I followed that link and what is presented there is even more ugly
>than SOCI... :)

You haven't looked very carefully ... :(

>> 3. IMO, the best way for database access is to generate classes out of
>> table definitions (and maybe additonal informations) with a code
>
>If you mean by this that there for any table there will be struct or
>class that maps SQL record to member variables (or methods), then while
>this approach is sometimes useful, it is not the "solution", nor the
>"best way".
>
>SQL is about that little part after "where", not about fetching data.

... but mostly you read by key.

>> generator.
>
>Code generator? What for?

Among others, to avoid MFC-style macros like the following TABLE_ ...
END_TABLE. People seriously interested in code generation may take a
look at http://www.codegeneration.net/

Best wishes,
Roland Pibinger

Maciej Sobczak

unread,
Oct 5, 2005, 4:57:50 PM10/5/05
to
Mirek Fidler wrote:

>>http://soci.sourceforge.net/

> You could do better.

Except that we have a different opinion on what is better. :)

> Our baby allows this:

> sql * Select(NAME, SALARY).From(PERSON).Where(ID == id);
> sql.Fetch(name, salary);

Wow! I'm impressed, really (actually, this is what I wanted to do at the
very beginning, but I dropped this idea).

Let's raise the bar a little bit:

sql << "select e.ename, e.sal, e.deptno "
"from emp e, "
" (select sal "
" from (select distinct sal from emp order by sal desc) "
" where rownum <=2) s "
"where e.sal = s.sal",
into(name), into(salary), into(depno);

Of course, why stop here? order by, having, unions, correlated queries,
calls to stored procedures, ...

Does you baby allow this?

If yes, is this still similar to C++ and to SQL, or maybe to none of
them? What about the learning curve? Can you hire a developer with
"standard" education background and expect him to write and maintain the
code?
If not (ie. your baby does not allow this), then I find it to be a
showstopper. Do you privide some alternative channel for those more
advanced? How does it look like?

The point is that today's SQL servers are, well, SQL servers in the
sense that they parse and understand SQL queries. There is a standard
for SQL, for that matter. What's the point in bypassing it?

Moreover, the database server accepts not only queries, but also a whole
lot of DDL statements plus any number of administrative commands that
manipulate storage or session parameters. SOCI can be used for them as
well and yes, we have users that use SOCI for administrative stuff.

sql << "alter session set optimizer_mode=RULE";

> Note that this way SQL syntax is checked by C++ compiler...

Which is good and I acknowledge it. But I don't think that the DB
library can use this as a justification for limiting what the user can
do with his DB server. ;)

[ See http://www.gotw.ca/resources/clcm.htm for info about ]

Allan W

unread,
Oct 5, 2005, 5:04:27 PM10/5/05
to
> Maciej Sobczak wrote:
> > The SOCI library is a database access library that provides the look
> > and feel of "embedded SQL", but staying withing the standard C++:
> > http://soci.sourceforge.net/
> > We are pleased to announce that the new version of SOCI library is
> > available for download.

Mirek Fidler wrote:
> You could do better.
>
> Our baby allows this:

What is your "baby"? Did you intend to give a link to some product's
home page? Is it free?

> int id = ...;
> String name;
> int salary;
>
> sql * Select(NAME, SALARY).From(PERSON).Where(ID == id);
> sql.Fetch(name, salary);
>
> Note that this way SQL syntax is checked by C++ compiler...

It looks like you have an object named "sql" which has an operator*().
Is this a singleton object? Or is it possible to create two different
objects, connected to two different databases?

"name" (lower-case) is a String variable, but what is NAME (capital)?
Similarly with "salary" and "SALARY"?

Can you select computed fields, like MIN(NAME)? Does it look as much
like standard SQL as the example above does?

Can "your baby" also handle free-form SQL text, for instance to
interpret text typed in by the user, or a WHERE clause read from some
database?

> What is more important, you can create SQL queries part by part:
>
> SqlBool where;
> if(!IsNull(findname))
> where = NAME == findname;
> if(!IsNull(findsurname))
> where = where && SURNAME == findsurname;
> sql * Select(PERSONID).From(PERSON).Where(where);

It looks to me as if "where" can be uninitialized when it's passed to
the .Where function... for instance, if findname and findsurname are
both null.

Is there anything in standard SQL that isn't in your "baby"? I've seen
some systems that can't handle the HAVING clause... How about non-
standard SQL, such as the SQL extensions that most vendors have?
How about statements that tend to differ from one vendor to the next,
such as CREATE PROCEDURE or GRANT or ALTER TABLE?

kanze

unread,
Oct 5, 2005, 5:23:12 PM10/5/05
to
Maciej Sobczak wrote:
> The SOCI library is a database access library that provides
> the look and feel of "embedded SQL", but staying withing the
> standard C++:

> http://soci.sourceforge.net/

> We are pleased to announce that the new version of SOCI
> library is available for download.

A quick glance shows that it seems to follow the same philosophy
as OTL; in fact, it seems less systematic than OTL, since it
also uses the comma operator and requires binding variables.

I've used OTL in earlier projects, and while it wasn't perfect,
it worked pretty well. What are the advantages of SOCI compared
to OTL? (Note that OTL already supports Oracle, DB2, and ODBC.)

I rather like the Mirek's idea of using special types and
overloaded operators so that the language can verify at least
part of the SQL syntax. The part that pleased me least in OTL
was the fact that they used the stream operators to insert
variable control fields; IMHO, these are logically closer to
seek operations, and in any case, are *not* output data, as they
appear to be with OTL (and from a quick glance at the
documentation, in SOCI as well). (It would be interesting to
see what his "baby" allows in the case of more complex SQL
statements, with multiple joins and nested selects, for
example.)

Another idea would be to use STL-like iterators to return the
values (although given the nature of STL, I suspect that the
iterator value_type would have to be something like
std::map<???,boost::any>, and of course, it would be an
input_iterator). I'm not sure how to integrate this, though.

--
James Kanze GABI Software
Conseils en informatique orientée objet/
Beratung in objektorientierter Datenverarbeitung
9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34

Maciej Sobczak

unread,
Oct 5, 2005, 5:36:30 PM10/5/05
to
Roland Pibinger wrote:

>>http://soci.sourceforge.net/


>
> 1. SQL statements in code as char strings and fancy operator
> overloadings are not something I want to have in my application.

cout << "I do not like char strings and operator overloading!";

But seriously - what's wrong with it? SQL statements as strings is what
the server likes crunching anyway. Operator overloading is used so that
users need not mess with unrelated stuff in their code.

> 2. Take a look at RogueWave's DBTools

Done. Doesn't look like SQL. Too bad for those who will have to learn it.

I have already addressed the above two points when answering Mirek
Fidler, so let me focus on this one:

> 3. IMO, the best way for database access is to generate classes out of
> table definitions (and maybe additonal informations) with a code
> generator.

a) One project had some tables with more than 100 (hundred) columns. The
main business entity table had ~150 columns. Obviously, every existing
query was interested in only a small subset of the columns, like here:

select name, phone from clients where id = 123456

Obviously, there are 2^N-1 ways to choose a subset of N columns.
Obviously, nobody likes to transfer the whole row with 150 columns over
the network just to pick up few fields on the client side.


b) One project used a stiff policy that client applications can't touch
tables directly. Instead, every query had to go through a stored
procedure, which did the actual data extraction and possibly additional
processing.


c) It is possible to construct a query which result will not be related
to the "profile" of any single table in the schema. Table joins are one
way to do this, but not the only way in SQL. In other words, there are
queries which "return type" cannot be extracted from the schema.


How do you approach these three issues?

[ See http://www.gotw.ca/resources/clcm.htm for info about ]

Mirek Fidler

unread,
Oct 6, 2005, 4:36:55 AM10/6/05
to
>>>3. IMO, the best way for database access is to generate classes out of
>>>table definitions (and maybe additonal informations) with a code
>>
>>If you mean by this that there for any table there will be struct or
>>class that maps SQL record to member variables (or methods), then while
>>this approach is sometimes useful, it is not the "solution", nor the
>>"best way".
>>
>>SQL is about that little part after "where", not about fetching data.
>
>
> ... but mostly you read by key.

No. I do a lot of SQL and most of my queries have quite complicated
where conditions. Note that this is a good thing as it tends to reduce
the number of round-trips between the server and client.

>>Code generator? What for?
>
>
> Among others, to avoid MFC-style macros like the following TABLE_ ...
> END_TABLE. People seriously interested in code generation may take a
> look at http://www.codegeneration.net/

Do you insist that by replacing C macroprocessor with another one you
gain less ugly solution?

Mirek

Mirek Fidler

unread,
Oct 6, 2005, 9:35:43 AM10/6/05
to
> documentation, in SOCI as well). (It would be interesting to
> see what his "baby" allows in the case of more complex SQL
> statements, with multiple joins and nested selects, for
> example.)

Well, it was designed to solve the problem, gradually adding things we
needed in our projects, so at the moment it probably does not support
ANY SQL syntax you will throw at it, but of course nested selects and
joins are supported.

In very short description, basic types are

SqlId - those NAME, SURNAME - SQL constants
SqlCol - name of column, implicit constructor from SqlId
SqlVal - represents values - column, date, number, text.... and has
implicit construcors from them
SqlBool - represents boolean conditions
SqlSet - represents "sets" - that includes sets of columns, values AND
SQL statements as well (because Select is set).

SqlBool, SqlVal and SqlSet have internal "priority value" in order to
add parenthesis where needed. Alternatively, those could be added
everywhere, but that makes it nightmare to debug.... (logs of SQL
statements look too ugly).

LOW-LEVEL:

SqlSession - database connection
Sql - cursor - you can execute commands on it and fetch results from it
(if any). Constructs from SqlSession or default constructs from default
session.

Mirek

Mirek Fidler

unread,
Oct 6, 2005, 9:37:24 AM10/6/05
to
Allan W wrote:
>>Maciej Sobczak wrote:
>>
>>>The SOCI library is a database access library that provides the look
>>>and feel of "embedded SQL", but staying withing the standard C++:
>>>http://soci.sourceforge.net/
>>>We are pleased to announce that the new version of SOCI library is
>>>available for download.
>
>
> Mirek Fidler wrote:
>
>>You could do better.
>>
>>Our baby allows this:
>
>
> What is your "baby"? Did you intend to give a link to some product's
> home page? Is it free?

http://upp.sf.net

I must admit that at the moment, documentation for SQL packages is
mostly missing... (so far, we have hard time to describe all 100 GUI
widgets :)

>>sql * Select(NAME, SALARY).From(PERSON).Where(ID == id);
>>sql.Fetch(name, salary);
>>
>>Note that this way SQL syntax is checked by C++ compiler...
>
>
> It looks like you have an object named "sql" which has an operator*().
> Is this a singleton object? Or is it possible to create two different
> objects, connected to two different databases?

OracleSession oracle1, oracle2;
oracle1.Open(...);
oracle2.Open(...);

Sql sql1(oracle1);
Sql sql2(oracle2);

sql1 * Select(ID, NAME, SURNAME).From(PERSON);
String id, name, surname;
while(sql1.Fetch())
sql2 * Insert(PERSON)(ID, sql1[ID])(NAME, sql1[NAME])(SURNAME,
sql1[SURNAME]);


> "name" (lower-case) is a String variable, but what is NAME (capital)?
> Similarly with "salary" and "SALARY"?

Constant of SqlId type. Usually generated using scheme files (see other
post). But basically defined as

SqlId NAME("NAME");

> Can you select computed fields, like MIN(NAME)? Does it look as much
> like standard SQL as the example above does?

Select(SqlMin(NAME))....

> Can "your baby" also handle free-form SQL text, for instance to
> interpret text typed in by the user, or a WHERE clause read from some
> database?

Sure, but that is uninteresting low-level :)

>>What is more important, you can create SQL queries part by part:
>>
>>SqlBool where;
>>if(!IsNull(findname))
>> where = NAME == findname;
>>if(!IsNull(findsurname))
>> where = where && SURNAME == findsurname;
>>sql * Select(PERSONID).From(PERSON).Where(where);
>
>
> It looks to me as if "where" can be uninitialized when it's passed to
> the .Where function... for instance, if findname and findsurname are
> both null.

Then the "where ..." clause is missing in the final SQL statement.

> Is there anything in standard SQL that isn't in your "baby"? I've seen
> some systems that can't handle the HAVING clause... How about non-
> standard SQL, such as the SQL extensions that most vendors have?

Actually, this is area of research. Actual library deals mostly with
Oracle, which mostly works for other servers as well, but sometimes
syntax differs (e.g. outer joins in oracle have different syntax from
anything else). Therefore we plan to virtualize the thing so that actual
SQL syntax generation is deffered to the point of execution (when actual
SQL server used is known).

> How about statements that tend to differ from one vendor to the next,
> such as CREATE PROCEDURE or GRANT or ALTER TABLE?

Not implemented. But that is what is low-level for, is not it?

Mirek

Mirek Fidler

unread,
Oct 6, 2005, 9:32:02 AM10/6/05
to
>
> sql << "select e.ename, e.sal, e.deptno "
> "from emp e, "
> " (select sal "
> " from (select distinct sal from emp order by sal desc) "
> " where rownum <=2) s "
> "where e.sal = s.sal",
> into(name), into(salary), into(depno);
>
> Of course, why stop here? order by, having, unions, correlated queries,
> calls to stored procedures, ...
>
> Does you baby allow this?

Actually, to be fair, I am afraid you got me on that subquery in 'from'
clause - actuall implementation is not able to make that rename to 's'.
(I even suspect that it will not run on many SQL servers now... and code
portability is my other concern). But that could be easily fixed and
code then would look like

SQL * Select(ENAME.Of(E), SAL.Of(E), DEPTNO.Of(E))
.From(EMP.As(E),
Select(Distinct(SAL).From(
Select(Distinct(SAL)).From(EMP).OrderBy(Descending(SAL)))
.Where(SqlRowNum() <= 2)
.As(S) // not implemented yet
)
.Where(SAL.Of(E) = SAL.Of(S));

Other than that, I think that you can imagine pretty well that "order
by, having, unions, correlated queries" are all possible.

> If yes, is this still similar to C++ and to SQL, or maybe to none of
> them?

I guess it is pretty similar to both C++ AND SQL, plus compiler will
guide you as many invalid statements are simply reject in compile time.

> What about the learning curve? Can you hire a developer with
> "standard" education background and expect him to write and maintain the
> code?

Same applies to any advanced solution. I guess even SOCI has its
learning curve.

> The point is that today's SQL servers are, well, SQL servers in the
> sense that they parse and understand SQL queries. There is a standard
> for SQL, for that matter. What's the point in bypassing it?

The actual reason is to turn SQL into 'functional metalanguage'. Have
you ever created dynamic SQL queries? Like you have a dialog full of
query options and you have to build the right 'where' condition out of
them. Then this approach really shines.

Example:

SqlBool where;
if(!IsNull(select.cislo))
where = where && CISLO == ~select.cislo;
if(!IsNull(select.rok))
where = where && ROK == ~select.rok;
if(!IsNull(select.prijmeni)) {
SqlBool b;
b = OSOBA_SEQ ==
Select(OSOBA_SEQ).From(OSOBA).Where(Like(SqlId("PRIJMA"),
(String)~select.prijmeni + '%'));
where = where &&
(SO_PODANI_SEQ == Select(SO_PODANI_SEQ).From(SO_OSOBA).Where(b) ||
SO_PODANI_SEQ == Select(SO_PODANI_SEQ).From(SO_DOC)
.Where(SO_DOC_SEQ ==
Select(SO_DOC_SEQ).From(SO_ROZDELOVNIK)
.Where(b)));
}

podani.Query(where);


> Moreover, the database server accepts not only queries, but also a whole
> lot of DDL statements plus any number of administrative commands that
> manipulate storage or session parameters. SOCI can be used for them as
> well and yes, we have users that use SOCI for administrative stuff.
>
> sql << "alter session set optimizer_mode=RULE";

Actually, we have "low-level" path too. I can write

SQL.Execute("select NAME, SURNAME from PERSON where ID = ?", key);
SQL.Fetch(name, surname);

and DDL statements are performed this way. But you usually do not see
too many DDL statements in client code (if for nothing else, end-users
are usually not allowed to perform DDL statements). And having your SQL
partialy checked by compiler is not bad either...

BTW, speaking about OCI:

static Sql x("select NAME, SURNAME from PERSON where ID = ?");
x.Run(key);
...
x.Run(key);

is a way how to create and use "pre-parsed" cursor, but frankly, with
recent hardware and Oracle > 8, I have not noticed any performance gains.

BTW, maybe by DDL you actually meant DML:

SqlSequence SeqPerson(SEQ_PERSON);

SQL * Insert(PERSON)
(ID, ++SeqPerson)
(NAME, name)
(SURNAME, surname)
;

Not that this 'syntax' is even better than original SQL :) (I had always
problems to match the columns with 'values').

Mirek

igivanov

unread,
Oct 6, 2005, 9:40:22 AM10/6/05
to
Mirek Fidler wrote:
> Code generator? What for?
>
> You can easily describe schema using C macros like this: (actual code
> snippet from application I am working on right now)

[snip]

>
> Now this 'schema' is used to
>
> - generate SQL scripts that synchronize schema with SQL server
>
> - create C++ structs like you suggest
>
> - define column names as special C++ constants (of type SqlId) so that
> they can be safely used in SQL expressions
>
> - generate schema documentation (based on those DOC remarks).

If I understood you correctly, you have these clever macros as the
master description of your database structure. There are several
problems with this approach

- It doesn't scale beyond a few simple tables with a few fields that
you can list on a single page (or two). Once you go beyond that it
becomes a maintenance nightmare.

- You probably cannot express all the various constraints on the
database structure using macros. Well, if you can, I bet you won't be
able to make the compiler generate error messages understandable to a
human if constraints are violated.

- A database design rarely belongs solely to the programmer. There are
often other parties involved, and you cannot expect them reading or
modifying esoteric C++ code to make a simple change to the database
structure.

AFAICT the generally accepted idea is to use a dedicated database
design tool with a human-friendly inteface and all the services
(checking constraints, spelling, reserved words, graphical
representation, versioning, data dictionaries and what not).

Igor

Branimir Maksimovic

unread,
Oct 6, 2005, 9:44:37 AM10/6/05
to

Maciej Sobczak wrote:
> The SOCI library is a database access library that provides the look and
> feel of "embedded SQL", but staying withing the standard C++:
>
>
>
>
> Your comments are highly welcome.
>
>

I like library but you have to sepparate oracle oci from interface.
Lib needs generic functionality which will select specific
db implementations (either classical oo or via templates)
either by function or template parameter .
In that way one could add support for eg mysql c api or sybase open
client without changing source code, but just adding one ore more
source files.

Greetings, Bane.

Sean Kelly

unread,
Oct 6, 2005, 9:43:26 AM10/6/05
to
kanze wrote:
>
> Another idea would be to use STL-like iterators to return the
> values (although given the nature of STL, I suspect that the
> iterator value_type would have to be something like
> std::map<???,boost::any>, and of course, it would be an
> input_iterator). I'm not sure how to integrate this, though.

I did something like this this for a project a while back. I didn't
attempt to make it quite so generic, but the result was still extremely
easy to use. Every query was an object, and 'get' queries were derived
from a template class that exposed begin and end methods. Ultimately,
iterating through a dataset populated a user-defined object via a
static fill() method defined for each query:

struct MyQuery : public get_base<MyResult, MyQuery>
{
// methods to set query parameters, if neccessary

private:
static fill( MyResult& val, sql::resultset const& rs )
{
val.parm1 = rs[0];
val.parm2 = rs[1];
}
};

(sql::resultset is part of a simple ADO/ODBC wrapper library I wrote a
while back)

Using the query is just like using a container--calling begin()
executes the query and returns an iterator to the new resultset:

for( MyQuery::const_iterator i( qry.begin() ); i != qry.end(); ++i )
std::cout << *i;

I suspect it would be fairly straightforward to use tuples for
destination objects and possibly auto-generate the fill code as well
with a bit of work (as your std::map idea suggests), but I haven't
found writing the 5-10 straightforward lines of code to be so onerous
as to warrant the task.

For what it's worth, I personally don't believe in writing ad-hoc SQL
in code as it tends to be both slow to execute and difficult to
maintain, so the ability to assemble a query using C++ features is not
something I'm particularly interested in. Stored procedures and views
tend to be a preferable alternative in every case I've encountered.


Sean

Maciej Sobczak

unread,
Oct 6, 2005, 11:09:53 AM10/6/05
to
kanze wrote:

>>http://soci.sourceforge.net/

> What are the advantages of SOCI compared
> to OTL?

As in any other domain, the beauty is in the eye of beerholder and
especially interface choices are subjective and difficult to compare.
But I try to give you some objective answer.

The idea behind SOCI was to minimize the amount of unrelated code on the
user side, leaving only three things:

1. what has to be done (expressed in SQL)

2. where the data should go or where it should come from (if data
exchange is involved, which is not always the case)

3. of course, some way to identify the session is also needed, since the
session need not be a singleton.

I think that the following interface:

sql << "select ...", into(object);
sql << "insert ...", use(object);
sql2 << "some SQL with no data exchange";

allows to achieve these assumptions, since indeed there is nothing else
in sight except the things that are necessary. In other words this
interface cannot be reduced further without loss of functionality.

And, what's most important, the interface was modeled on what I've seen
in environments supporting "embedded SQL". One of the examples taken
from Google:

{
int a;
/* ... */
EXEC SQL SELECT salary INTO :a
FROM Employee
WHERE SSN=876543210;
/* ... */
printf("The salary is %d\n", a);
/* ... */
}

This is Pro*C, which is an Oracle preprocessor-based extension to C (it
requires special preprocessor for converting the above to what is
regular C).
Interestingly, there are also full RAD environments with embedded SQL
support (like PowerBuilder), where the interface is *strikingly* simiral.

The SOCI version of the above example would be:

int a;
sql << "SELECT salary "
"FROM Employee "
"WHERE SSN=876543210",
into(a);

cout << "The salary is " << a << '\n';

The difference is that SOCI is standard C++ and there is no special
preprocessor involved.

Whether this is better than OTL or not is a matter of objectives and
personal preferences. I've been working in some projects where embedded
SQL was extensively used and this obviously influenced how the SOCI
library looks like. I expect that other programmers with embedded SQL
experience will find the SOCI library extremely easy to use, exactly
because its interface fits into the embedded SQL "culture" (if there is
anything like this). This minimizes the learning curve and the time
needed to port applications.

Compared to this, other libraries (including OTL) impose steep learning
curve because they always try to create new ways of thinking instead of
trying to fit into existing practice. And yes, I consider embedded SQL
to be an existing practice when it comes to database interfacing.


> I rather like the Mirek's idea of using special types and
> overloaded operators so that the language can verify at least
> part of the SQL syntax.

I again acknowledge that this approach has its merits. And I again
stress that this approach bypasses the existing standards (namely, SQL
as a language for queries) and potentially limits what can be *really*
done. Not mentioning, again, that it imposes steep learning curve even
on those who already think that they know how to talk to SQL server.
Note also that some applications need to build SQL queries dynamically.
It was true in all the projects I've seen. SOCI supports this in a
natural way, not as a separate interfacing channel.

> Another idea would be to use STL-like iterators to return the
> values

I disagree. I think there is some "impedance mismatch" between the STL
way and SQL way when it comes to data exchange. In fact, I'm comfortable
with STL as a tool for data *processing* and SQL as a tool for data
*retrieval*. It is hard to elaborate on this (probably some math would
be needed to back it), but the failure of existing libraries to
integrate one with another in a really natural way shows that this claim
is not just taken from the air. At least, *I* have no idea how to do this.

[ See http://www.gotw.ca/resources/clcm.htm for info about ]

Maciej Sobczak

unread,
Oct 6, 2005, 11:17:36 AM10/6/05
to
Branimir Maksimovic wrote:

> I like library but you have to sepparate oracle oci from interface.
> Lib needs generic functionality which will select specific
> db implementations (either classical oo or via templates)
> either by function or template parameter .
> In that way one could add support for eg mysql c api or sybase open
> client without changing source code, but just adding one ore more
> source files.

And this is exactly the plan for the nearest future. The Oracle support
is there, because this is the environment where SOCI was born (hence the
name), but Oracle is by no means the only target.

[ See http://www.gotw.ca/resources/clcm.htm for info about ]

kanze

unread,
Oct 6, 2005, 12:15:44 PM10/6/05
to
Maciej Sobczak wrote:
> Mirek Fidler wrote:

> >>http://soci.sourceforge.net/

> > You could do better.

> Except that we have a different opinion on what is better. :)

Compile time checking is better than runtime checking.

> > Our baby allows this:

> > sql * Select(NAME, SALARY).From(PERSON).Where(ID == id);
> > sql.Fetch(name, salary);

> Wow! I'm impressed, really (actually, this is what I wanted to
> do at the very beginning, but I dropped this idea).

> Let's raise the bar a little bit:

> sql << "select e.ename, e.sal, e.deptno "
> "from emp e, "
> " (select sal "
> " from (select distinct sal from emp order by sal desc) "
> " where rownum <=2) s "
> "where e.sal = s.sal",
> into(name), into(salary), into(depno);

> Of course, why stop here? order by, having, unions, correlated
> queries, calls to stored procedures, ...

Yes. Simple examples are, well, simple. A priori, I think that
the same techniques he's using could be used to do this -- think
of everything that Boost's lambda class does, to get an idea
just how flexible meta-programming can be. But it isn't easy,
and it's a lot of work.

> Does you baby allow this?

> If yes, is this still similar to C++ and to SQL, or maybe to
> none of them? What about the learning curve? Can you hire a
> developer with "standard" education background and expect him
> to write and maintain the code?
> If not (ie. your baby does not allow this), then I find it to
> be a showstopper. Do you privide some alternative channel for
> those more advanced? How does it look like?

Valid questions. The most valid question, of course, is where
or how can I get this baby? That would allow me to see for
myself what it can and cannot do. (Ideally, it would even
verify the data types into which the data was read.
Practically, I don't think that this is possible; only the data
base knows the types of the columns, and in fact, the table may
have not yet been created when I'm compiling the C++ code to
access it.)

> The point is that today's SQL servers are, well, SQL servers
> in the sense that they parse and understand SQL queries.
> There is a standard for SQL, for that matter. What's the
> point in bypassing it?

To get an error at compile time, rather than at run time. The
fact that SQL is a standard is an advantage here.

> Moreover, the database server accepts not only queries, but
> also a whole lot of DDL statements plus any number of
> administrative commands that manipulate storage or session
> parameters. SOCI can be used for them as well and yes, we
> have users that use SOCI for administrative stuff.

Certainly. A complete implementation of what he is proposing is
a LOT a work. That doesn't mean that it isn't interesting.

> sql << "alter session set optimizer_mode=RULE";

> > Note that this way SQL syntax is checked by C++ compiler...

> Which is good and I acknowledge it. But I don't think that the
> DB library can use this as a justification for limiting what
> the user can do with his DB server. ;)

It depends. Obviously, the less limitations the better.
Support for select, insert and update are essential, as is
probably creating and dropping tables and managing
transactions, and invoking stored procedures. I can imagine
that the code would still be useful, however, even if it didn't
support all managerial operations (e.g. things like creating new
users).

--
James Kanze GABI Software
Conseils en informatique orientée objet/
Beratung in objektorientierter Datenverarbeitung
9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34

Mirek Fidler

unread,
Oct 6, 2005, 5:48:38 PM10/6/05
to
> - It doesn't scale beyond a few simple tables with a few fields that
> you can list on a single page (or two). Once you go beyond that it
> becomes a maintenance nightmare.

Schema of one of our system has more than 700 tables. And it is very
easy to maintain. And yes, some tables have quite a lot columns,
although in fact I do not consider such fat tables to be a good design.

> - You probably cannot express all the various constraints on the
> database structure using macros. Well, if you can, I bet you won't be

I am able to express all basic conatraints I need. 90% it is foereight
key reference anyway.

> able to make the compiler generate error messages understandable to a
> human if constraints are violated.

This part is even better using this aproach, as I am able to generate
contraint names based on actuall column names and table names. Much less
work to get good error messages this way.

> - A database design rarely belongs solely to the programmer. There are
> often other parties involved, and you cannot expect them reading or
> modifying esoteric C++ code to make a simple change to the database
> structure.

That is valid complaint. I am happy to have control over everything now,
but I undestand that in some environments you have to follow schema
designed by somebody else.

In that case, my C++ schema for tables will simply not be used to sync
database server, just to interface with another model.

> AFAICT the generally accepted idea is to use a dedicated database
> design tool with a human-friendly inteface and all the services
> (checking constraints, spelling, reserved words, graphical
> representation, versioning, data dictionaries and what not).

Generally accepted idea does not have to be optimal. I have found this
approach very productive, much more than when we were using all those
funny graphical tools.

Moreover, I think that in future I will provide some graphical tools in
TheIDE as well (but I will always keep this text representation).

Mirek

Mirek Fidler

unread,
Oct 6, 2005, 5:45:11 PM10/6/05
to
> Valid questions. The most valid question, of course, is where
> or how can I get this baby?

http://upp.sf.net

Problem is that SQL part is not very well documented....

> That would allow me to see for
> myself what it can and cannot do. (Ideally, it would even
> verify the data types into which the data was read.

> Practically, I don't think that this is possible; only the data
> base knows the types of the columns, and in fact, the table may
> have not yet been created when I'm compiling the C++ code to
> access it.)

Unfortunately, you are right... While we all understand advantages of
static compile time checking (something that advocates o "true" OOP are
unable to grasp), somethimes dynamic typing is unavoidable.

> Certainly. A complete implementation of what he is proposing is
> a LOT a work. That doesn't mean that it isn't interesting.

Not that complicated, really...

That "high-level" (we call it "SqlExp") has 500 lines of .h and 1000
lines of .cpp...

Of course, planned refactoring (to resolve problems with SQL dialects)
will drive these line counts up, but I hope no more than 50%...

Mirek

Roland Pibinger

unread,
Oct 6, 2005, 5:46:01 PM10/6/05
to
On 5 Oct 2005 17:36:30 -0400, Maciej Sobczak <no....@no.spam.com>
wrote:

>Roland Pibinger wrote:
>
>>>http://soci.sourceforge.net/
>>
>> 1. SQL statements in code as char strings and fancy operator
>> overloadings are not something I want to have in my application.
>
>cout << "I do not like char strings and operator overloading!";
>
>But seriously - what's wrong with it? SQL statements as strings is what
>the server likes crunching anyway.

It's error prone (typo in sql-string), untestable, and unmaintainable
for larger databases.

>Operator overloading is used so that
>users need not mess with unrelated stuff in their code.
>
>> 2. Take a look at RogueWave's DBTools
>
>Done. Doesn't look like SQL. Too bad for those who will have to learn it.

It isn't embedded SQL but a C++ library. :-)

>I have already addressed the above two points when answering Mirek
>Fidler, so let me focus on this one:
>
>> 3. IMO, the best way for database access is to generate classes out of
>> table definitions (and maybe additonal informations) with a code
>> generator.
>
>a) One project had some tables with more than 100 (hundred) columns. The
>main business entity table had ~150 columns. Obviously, every existing
>query was interested in only a small subset of the columns, like here:
>
>select name, phone from clients where id = 123456
>
>Obviously, there are 2^N-1 ways to choose a subset of N columns.
>Obviously, nobody likes to transfer the whole row with 150 columns over
>the network just to pick up few fields on the client side.

150 columns? A database access library cannot (should not) compensate
for bad, non-normalized database designs.

>b) One project used a stiff policy that client applications can't touch
>tables directly. Instead, every query had to go through a stored
>procedure, which did the actual data extraction and possibly additional
>processing.
>
>c) It is possible to construct a query which result will not be related
>to the "profile" of any single table in the schema. Table joins are one
>way to do this, but not the only way in SQL. In other words, there are
>queries which "return type" cannot be extracted from the schema.
>
>How do you approach these three issues?

These issues boil down to what has been discussed as O/R Mapping in
the last 10 or so years (not in C++ but but in 'newer technologies').
The purpose is to raise the level of abstraction for database access
thereby increasing usability, maintainability and testability,
especially when large DBs area involved. And it works very well in
practice (what I can confirm from personal experience).

Best wishes,
Roland Pibinger

Mirek Fidler

unread,
Oct 7, 2005, 9:07:35 AM10/7/05
to
>>Obviously, there are 2^N-1 ways to choose a subset of N columns.
>>Obviously, nobody likes to transfer the whole row with 150 columns over
>>the network just to pick up few fields on the client side.
>
>
> 150 columns? A database access library cannot (should not) compensate
> for bad, non-normalized database designs.

a) you are correct that generally, database full of tables with more
than say 15 columns is a bad design. OTOH, sometimes things like this
are needed and are benefical.

b) even if your table has less than 15 columns, some of them can store
huge data. Passing these data through network each time, even if you do
not need them, is suboptimal.

Mirek

kanze

unread,
Oct 7, 2005, 9:50:58 AM10/7/05
to
Maciej Sobczak wrote:
> kanze wrote:

> >>http://soci.sourceforge.net/

> > What are the advantages of SOCI compared to OTL?

> As in any other domain, the beauty is in the eye of beerholder
> and especially interface choices are subjective and difficult
> to compare. But I try to give you some objective answer.

What struck me was a certain superficial similarity -- both use
<< and >>:-) (And in both cases, to do something that really
isn't insertion or extraction -- in sum, operator overloading
abuse.)

> The idea behind SOCI was to minimize the amount of unrelated
> code on the user side, leaving only three things:

> 1. what has to be done (expressed in SQL)

> 2. where the data should go or where it should come from (if
> data exchange is involved, which is not always the case)

> 3. of course, some way to identify the session is also needed,
> since the session need not be a singleton.

> I think that the following interface:

> sql << "select ...", into(object);
> sql << "insert ...", use(object);
> sql2 << "some SQL with no data exchange";

I could just as easily imagine using sql( "select ..." )
(overloaded operator()()), rather than <<.

I'll admit that I look at this from the point of view of a C++
programmer, who uses some SQL, and not from the point of view of
a data base specialist, who occasionally uses some C++.
Logically, to me at least, a select statement is input. So <<
doesn't seem to fit. I understand the idea that you are
"outputting" to the sql session (and that a select statement is
output to this session), but it seems to me at least that the
abstraction is too low -- the abstraction which seems to fit for
me is that the select statement is like an open, and I later
read. In a certain way, a select statement is just a filename
with a very particular syntax.

Supposing, of course, we accept the principle that the select
statement is nothing but a string, and that the compiler can't
do any syntax checking on it. If I accept that the select
statement is a string, what's wrong with:

isqlstream in( sql, "select ..." ) ;
while ( sql >> record ) {
// ...
}

> allows to achieve these assumptions, since indeed there is
> nothing else in sight except the things that are necessary.
> In other words this interface cannot be reduced further
> without loss of functionality.

Sure it can: just use a pipestream to and from isql:-).

The question is one of added value. You're last statement above
is certainly true. The question is rather: have you reduced it
too far, and lost significant functionality (like static type
checking). And if not, why this "unnatural" (for C++) syntax,
and not simply an isqlstream, as above? Or a pipestream to and
from isql?

Just a nit, too, but your syntax doesn't seem to allow using
arbitrary expressions for output data.

> And, what's most important, the interface was modeled on what
> I've seen in environments supporting "embedded SQL".

Interesting. I've never encountered a customer using "embedded
SQL". Do people actually use it? (By embedded SQL, I presume
you mean systems in which SQL is more or less embedded in C++
source code, and a preprocessor is used to convert it to real
C++.)

> One of the examples taken from Google:

> {
> int a;
> /* ... */
> EXEC SQL SELECT salary INTO :a
> FROM Employee
> WHERE SSN=876543210;
> /* ... */
> printf("The salary is %d\n", a);
> /* ... */
> }

> This is Pro*C, which is an Oracle preprocessor-based extension
> to C (it requires special preprocessor for converting the
> above to what is regular C).
> Interestingly, there are also full RAD environments with
> embedded SQL support (like PowerBuilder), where the interface
> is *strikingly* simiral.

> The SOCI version of the above example would be:

> int a;
> sql << "SELECT salary "
> "FROM Employee "
> "WHERE SSN=876543210",
> into(a);

> cout << "The salary is " << a << '\n';

> The difference is that SOCI is standard C++ and there is no special
> preprocessor involved.

And that the into clause has been moved to the end, and
dissociated from the column specification:-).

> Whether this is better than OTL or not is a matter of
> objectives and personal preferences.

Well, the binding of a variable like that IS less flexible.
With OTL, if I knew that my select would read three values, for
example, I could write:

select >> v[ 0 ] >> v[ 1 ] >> v[ 2 ] ;

With the current version, I can even write something like:

std::copy( otl_input_iterator( select ),
otl_input_iterator(),
std::back_iterator( v ) ) ;

or
std::vector< double > v( (otl_input_iterator( select )),
(otl_input_iterator()) ) ;

(According to the documentation, at least. This feature wasn't
present when I used it three and a half years ago.)

All in all, my biggest complaint with OTL is that its SQL
commands were simply strings, and that there was no compile time
checking. Another complaint, more on stylistic grounds, is that
it used the << operator for binding variable values in the where
clause of a select statement (for example). IMHO, this is not
output, in the classical sense; over all, I think that they
pushed the iostream idiom too far. On the other hand, reading
the results of a select statement, or writing to an insert or an
update statement, seems like a very good use of the idiom; the
fact that they've now added otl stream iterators just makes it
better. It looks and feels like C++.

> I've been working in some projects where embedded SQL was
> extensively used and this obviously influenced how the SOCI
> library looks like. I expect that other programmers with
> embedded SQL experience will find the SOCI library extremely
> easy to use, exactly because its interface fits into the
> embedded SQL "culture" (if there is anything like this). This
> minimizes the learning curve and the time needed to port
> applications.

I came to OTL with absolutely no data base experience. I did
have to learn the syntax of select, insert and update
statements:-). But knowing C++, and being familiar with the C++
"culture", meant that the learning curve was minimal. (Less
than a day, in fact, for simple SQL requests.)

> Compared to this, other libraries (including OTL) impose steep
> learning curve because they always try to create new ways of
> thinking instead of trying to fit into existing practice.

If you consider iostreams and STL iterators a new way of
thinking, yes. I would argue, however, that if they are new way
of thinking, you should probably learn C++ before trying to
learn how to do SQL in C++.

> And yes, I consider embedded SQL to be an existing practice
> when it comes to database interfacing.

Well, I'm no expert in the field, but I've yet to see anyone who
uses it.

> > I rather like the Mirek's idea of using special types and
> > overloaded operators so that the language can verify at
> > least part of the SQL syntax.

> I again acknowledge that this approach has its merits. And I
> again stress that this approach bypasses the existing
> standards (namely, SQL as a language for queries)

This is what I don't understand about your criticism. SQL is a
language standard, not an implementation. He has managed to
implement the language standard, modulo a few quirks, by means
of C++ meta-programming techniques, so that the compiler
enforces this standard. It's only the fact that SQL is a
standard that makes his technique viable.

> and potentially limits what can be *really* done.

That's a question of the implementation. From later postings,
I gather he also offers a lower level interface for things his
high level interface doesn't yet support.

> Not mentioning, again, that it imposes steep learning curve
> even on those who already think that they know how to talk to
> SQL server. Note also that some applications need to build
> SQL queries dynamically. It was true in all the projects I've
> seen. SOCI supports this in a natural way, not as a separate
> interfacing channel.

> > Another idea would be to use STL-like iterators to return
> > the values

> I disagree. I think there is some "impedance mismatch"
> between the STL way and SQL way when it comes to data
> exchange.

Well, I'm no fan of STL iterators myself -- I think that there
is a definite "impedance mismatch" between them and the concept
of iteration, to begin with:-). Not to mention all of the
gotcha's concerning validity of iterators. BUT... they are the
standard. If you know C++, you know how to use STL iterators.
And when it comes to reading the results of a select statement,
what really is the difference between an input iterator and a
cursor?

Of course, neither are particularly clean or elegant (although
the STL has a great deal of internal coherence, which is sadly
lacking in SQL), but both get the job done, if you know how to
use them, and both are "standard" techniques, in both senses of
the word standard. So they match at those levels as well.

--
James Kanze GABI Software
Conseils en informatique orientée objet/
Beratung in objektorientierter Datenverarbeitung
9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34

kanze

unread,
Oct 7, 2005, 9:50:15 AM10/7/05
to
Mirek Fidler wrote:
> > Valid questions. The most valid question, of course, is
> > where or how can I get this baby?

> http://upp.sf.net

> Problem is that SQL part is not very well documented....

The first problem is that the SQL part isn't even mentionned at
the site. And what is mentionned isn't relevant to my work:
large scale server software. Interfacing with SQL is.

If I download the lot, can I extract and use the SQL stuff
without installing the works?

> > That would allow me to see for myself what it can and cannot
> > do. (Ideally, it would even verify the data types into
> > which the data was read.

> > Practically, I don't think that this is possible; only the
> > data base knows the types of the columns, and in fact, the
> > table may have not yet been created when I'm compiling the
> > C++ code to access it.)

> Unfortunately, you are right... While we all understand
> advantages of static compile time checking (something that
> advocates o "true" OOP are unable to grasp), somethimes
> dynamic typing is unavoidable.

There's a saying in French: "À l'impossible nul n'est tenu".
Roughly, no one can be required to do the impossible. Even if
you could check it statically, it wouldn't give any guarantees
concerning program execution, since someone could have dropped
the table and recreated it since compilation.

--
James Kanze GABI Software
Conseils en informatique orientée objet/
Beratung in objektorientierter Datenverarbeitung
9 place Sémard, 78210 St.-Cyr-l'École, France, +33 (0)1 30 23 00 34

Mirek Fidler

unread,
Oct 7, 2005, 11:48:00 AM10/7/05
to
> The first problem is that the SQL part isn't even mentionned at
> the site.

Many things are not mentioned yet... There is too much of it.

> If I download the lot, can I extract and use the SQL stuff
> without installing the works?

Unlikely. I am afraid that the best you can do now is to download
sources, go for "Sql" (or directory and be inspired by contents....).

Mirek

Mirek Fidler

unread,
Oct 7, 2005, 2:25:43 PM10/7/05
to
> {
> int a;
> /* ... */
> EXEC SQL SELECT salary INTO :a
> FROM Employee
> WHERE SSN=876543210;
> /* ... */
> printf("The salary is %d\n", a);
> /* ... */
> }
>
> int a;
> sql << "SELECT salary "
> "FROM Employee "
> "WHERE SSN=876543210",
> into(a);
>
> cout << "The salary is " << a << '\n';

Just a question: What if there is more rows or none?

>>I rather like the Mirek's idea of using special types and
>>overloaded operators so that the language can verify at least
>>part of the SQL syntax.
>
>

> Note also that some applications need to build SQL queries dynamically.

Dynamic queries is THE REASON of that "SQL expressions" feature of our
"baby".

Mirek

Maciej Sobczak

unread,
Oct 7, 2005, 3:48:45 PM10/7/05
to
Roland Pibinger wrote:

>>>>http://soci.sourceforge.net/
>>>
>>>1. SQL statements in code as char strings and fancy operator
>>>overloadings are not something I want to have in my application.

>>But seriously - what's wrong with it? SQL statements as strings is what


>>the server likes crunching anyway.
>
> It's error prone (typo in sql-string), untestable, and unmaintainable
> for larger databases.

Some programmers copy'n'paste queries from the SQL console or some other
tool where it was tested.
Others keep them externally (in a file or in... database) and have
automated tests that periodically assert that the queries are at least
syntactically correct.

No matter what's the approach, good management and discipline is what
you need in large systems. Generators alone will not give you that.


>>>2. Take a look at RogueWave's DBTools
>>
>>Done. Doesn't look like SQL. Too bad for those who will have to learn it.
>
> It isn't embedded SQL but a C++ library. :-)

As you see, there are different approaches to the problem. :)


>>a) One project had some tables with more than 100 (hundred) columns. The
>>main business entity table had ~150 columns.

> 150 columns? A database access library cannot (should not) compensate


> for bad, non-normalized database designs.

Do you claim that this database was badly designed? On what basis?

Is there anything in the DB theory and normalization principles that
puts a limit on the number of attributes (+ relations to other entities)
of any given entity? I'm not aware of such limits.

More to this, there are projects (this applies to most big ones, I
think) that are built on existing foundations or use existing frameworks
- they tend to grow with time in every direction. Even more to this, you
might not be able to modify the schema, for whatever (usually political)
reason. If you have to write an application against such database, you
need tools that will not prevent you from doing this.

Whatever the number of columns, there are still 2^N-1 ways of choosing a
subset of them (N=15 is OK?) and I don't want to transmit all of them
all of the time.

[ See http://www.gotw.ca/resources/clcm.htm for info about ]

Nicola Musatti

unread,
Oct 7, 2005, 3:44:59 PM10/7/05