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
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)
>
>
> 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

I find it funny that a supporter of static type checking should pose
this question. A code generator which is fed a schema description that
is automatically extracted from the database ensures that the db schema
and its description in C++ code are synchronized. Not to mention that
it also relieves you from the tedium of having to describe hundreds of
tables by hand!

Cheers,
Nicola Musatti

Roland Pibinger

unread,
Oct 7, 2005, 3:52:16 PM10/7/05
to
On 7 Oct 2005 09:07:35 -0400, Mirek Fidler <c...@volny.cz> wrote:
>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.

Then don't do it. If you are ambitious then implement a general cache
for dababase objects and load-on-demand for 'expensive' columns. The
point is that you design, implement and test it only once for n
tables.

Best wishes,
Roland Pibinger

Maciej Sobczak

unread,
Oct 7, 2005, 3:51:15 PM10/7/05
to
kanze wrote:

>>>>http://soci.sourceforge.net/
>
>>>What are the advantages of SOCI compared to OTL?

> 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.)

Indeed abuse - they are shift operators. IOStreams also abuses them by
using those shift operators for input/output, so we are in a good
company. ;-)


>>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 <<.

OK, also nice, although requires more parentheses (a bit "Lispish" :) ).

> I understand the idea that you are
> "outputting" to the sql session

Exactly. I "send" the query to the server.

> If I accept that the select
> statement is a string, what's wrong with:
>
> isqlstream in( sql, "select ..." ) ;
> while ( sql >> record ) {
> // ...
> }

What's wrong? The "record". What's that? An object? What's its type?

SOCI expects from the user to provide variables so that the library
knows where to store the data or where to get it from. If you exchange
more than one column, you provide more than one variable.

string name, phone;
sql << "select name, phone ...", into(name), into(phone);

There is no *additional* record variable and this is consistent with
what is known from embedded SQL.

> The question is rather: have you reduced it
> too far, and lost significant functionality (like static type
> checking).

We did not loose anything, because static type checking was never there
in the first place. We just choose not to gain it.

> And if not, why this "unnatural" (for C++) syntax,
> and not simply an isqlstream, as above? Or a pipestream to and
> from isql?

To get those nice "into" elements for data exchange. :)

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

I don't understand this. Could you elaborate?


>>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?

I was working in projects that do. One of those projects was a huge
billing system for some major telecom company, build on top of what is a
solution deployed in other telecoms as well. I think that in fact there
is quite a big legacy codebase like this.

>>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:-).

The INTO clause was never associated with anything, which is clear when
there is more than one column. In embedded SQL all "intos" are just
placed between "select" and "from". We only moved them to the end,
without loosing anything.


> 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 ] ;

sql << "...", into(x), into(y), into(z);

It reads three values.

Moreover, I like this, because the SOCI syntax does not suggest that the
operator>> is doing any data transmission (this is how I read your OTL
example). In SOCI, the data is placed in all three variables as a result
of a *single* database operation. That's why I don't see any place for
three separate extraction operators - as you said, OTL pushed the
iostream analogy too far.

It is more clear here:

Statement st = (sql.prepare << "select...", into(x), into(y), into(z));

st.execute();

The data is transmitted to x, y and z as a result of executing the
statement, which may happen later, as above. There are no three separate
transmissions, but *one* server operation. Your OTL example looks like
doing three intput operations, which is not true and therefore I treat
it as a misleading use of operators. It can be explained like doing one
transmission to "some internal buffer" and then executing three
extractions from that buffer, but I don't like it neither - it suggests
that there are more steps than necessary.


>>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.

Oracle was pushing its Pro*C (and related technologies for other
languages) quite heavily before, I think you can find lots of actual
code using 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.

Yes, exactly. The problem is that SQL grammar is part of the language
standard and that grammar cannot be implemented within the C++ grammar.
I don't consider Mirek's examples to be SQL. It is *some* query language
(he should name it somehow), but it is not SQL.

>>>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.

> And when it comes to reading the results of a select statement,


> what really is the difference between an input iterator and a
> cursor?

The type of *iterator, because there is only one expected, even if the
query retrieves many columns.

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

Mirek Fidler

unread,
Oct 8, 2005, 8:57:17 AM10/8/05
to
> Yes, exactly. The problem is that SQL grammar is part of the language
> standard and that grammar cannot be implemented within the C++ grammar.
> I don't consider Mirek's examples to be SQL. It is *some* query language

So you did not understand my examples after one minute of staring at
them? (Because that is what really matters).

> (he should name it somehow), but it is not SQL.

SqlExp is the name :)

Mirek

Mirek Fidler

unread,
Oct 8, 2005, 8:56:56 AM10/8/05
to
Nicola Musatti wrote:
> 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)
>>
>>
>>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
>
>
> I find it funny that a supporter of static type checking should pose
> this question. A code generator which is fed a schema description that
> is automatically extracted from the database ensures that the db schema
> and its description in C++ code are synchronized. Not to mention that
> it also relieves you from the tedium of having to describe hundreds of
> tables by hand!

Actually, you need to feed the database with schema. Normally, you have
these options:

- use CASE tool
- write database script

I always liked the later. And my funny macros are equivalent and I get
C++ interface in single file.

Of course, would I be required to interface with existing schemas, I
would probably created trivial tool to generate these macro based
schemas from database.... (trivial doing).

Mirek

Maciej Sobczak

unread,
Oct 9, 2005, 10:04:53 AM10/9/05
to
Mirek Fidler wrote:
>>Yes, exactly. The problem is that SQL grammar is part of the language
>>standard and that grammar cannot be implemented within the C++ grammar.
>>I don't consider Mirek's examples to be SQL. It is *some* query language
>
>
> So you did not understand my examples after one minute of staring at
> them? (Because that is what really matters).

What really matters is that each database environment comes with its own
SQL console. Developers routinely use that console for trying and
testing their queries. What really matters is whether you can
copy'n'paste the query from the C++ editor to SQL console and back.

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

Maciej Sobczak

unread,
Oct 9, 2005, 10:03:48 AM10/9/05
to
Mirek Fidler wrote:

>>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?

The example above was written with the knowledge that SSN is a primary
key (so there cannot be more rows) and with expectation that there is
such an entry. So - you *expect* exactly one result, and other
conditions will throw an exception.

Of course, there are cases where either you want to allow the "no such
object" condition and it is not considered to be an error - then use
"indicators" to get more information about the availability of each data
element:

http://soci.sourceforge.net/doc.html#indicators

(it was suggested to pack the indicator and data together, but it
requires more thought to get it right), ...

... or indeed you expect the whole rowset:

string name;
Statement st = (sql.prepare <<
"select name from person order by name",
into(name));
st.execute();
while (st.fetch())
{
cout << name << '\n';
}

More on statement preparation and repeated execution:

http://soci.sourceforge.net/doc.html#preparation

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

Steve Hutton

unread,
Oct 9, 2005, 10:46:25 PM10/9/05
to
On 2005-10-07, kanze <ka...@gabi-soft.fr> wrote:
>
> 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++.)

Yes, actually embedded SQL was part of the SQL-92 standard.
I recall studying it in school as part of a course in
"Fundamentals of Database Management Systems" - it was
covered in the textbook.

Oracle's PRO*C implementation also gained widespread use.
We used it in my first Oracle job in the late 90s and last
year when I was working for a major telecom, the 3rd party
billing system they used was PRO*C based. Oracle also provides
a Java embedded SQL implementation called SQLJ.

> All in all, my biggest complaint with OTL is that its SQL
> commands were simply strings, and that there was no compile time
> checking.

I agree the lack of SQL compile time checking can be unnerving
when coming from a C++ perspective, but the leading rdbms APIs for
almost all programming languages are based on SQL strings - JDBC
being the prime example, but also of course Perl and Python...

On the other hand I have had the experience of using what is probably
the leading non-sql based rdbms library - Rogue Wave's dbtools.
Initially I was quite impressed with the design and how it supported
compile time checking. However after using it for a while I came
to see some downsides as well:

- Compile time checking of this style only catches a limited set of
errors - it doesn't help if you mispell the column name or table name,
or if your dba renames things or makes other changes after you compile.

- A fairly large portion of sql syntax errors in code are due to
not escaping strings properly - but this problem is easily solved through
the use of bind variables, which are essential for performance anyway.

- When writing a new query, I found myself going through the exercise
of writing it and testing it in pure SQL, then translating it to the
language of the db api, anyway.

- Similarly, and perhaps more significantly, when maintaing a large system,
an essential step is to gain understanding of the queries in use.
So a reverse translation is needed. This need to translate the queries
back to pure SQL also comes up when working with other staff members or
departments who might be in charge of tuning or auditing queries.

So while the C++ programmer in me sees the appeal of compile time
checking, the db programmer and pragmatist in me appreciates having pure
SQL strings (preferably in a configuration file separate from the code
for large systems - I worked on such a system last year and it was by
far the best part of the design.)

Finally, if we were designing a mechanism for querying a relational
database from C++ in a world without SQL, the choice might be closer,
but SQL is of course in widespread use - there are many non-programmers
who know SQL (DBAs being the most relevant).

> 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.)

Ah, a point in favor of SQL-based libraries like OTL and SOCI? ;-)

Steve

Paschal Mushubi

unread,
Oct 9, 2005, 10:45:42 PM10/9/05
to
Maciej Sobczak (no....@no.spam.com) writes:
> 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.
>

nice but won't compile.

soci.h(1210) : error C2906: 'SOCI::UseType<double>' : explicit specialization
requires
'template <>' soci.cpp

using visual c++ 7.2

p.

Bronek Kozicki

unread,
Oct 10, 2005, 6:17:12 AM10/10/05
to
kanze wrote:
>>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.

Ther other advantage (very important to me) is to avoid string
manipulation when preparing (sometimes exteremely) complex SQL queries.
I created library slimilar to the one presented by Mirek just to gain
this particular ability. Parts of a the single select statement (ie. SQL
clauses) are manipulated by different parts of our software, and selects
are also valid arguments for some SQL clauses in other (outer) selects,
thus becoming subselects. Oh, and there are also expressions build in
Reverse Polish Notation, but that's different story (much less
compile-time checking).


B.

Mirek Fidler

unread,
Oct 10, 2005, 6:15:33 AM10/10/05
to
> ... or indeed you expect the whole rowset:
>
> string name;
> Statement st = (sql.prepare <<
> "select name from person order by name",
> into(name));
> st.execute();
> while (st.fetch())
> {
> cout << name << '\n';
> }
>
> More on statement preparation and repeated execution:
>
> http://soci.sourceforge.net/doc.html#preparation

Just for comparison (low-level):

Sql sql("select name from person order by name");
String name;
sql.Execute();

while(sql.Fetch(name))
DoSomething(name)

// or

while(sql.Fetch())
DoSomething(sql[0])

// or

while(sql.Fetch())
DoSomething(sql[NAME]);

Note that I especially do not like the idea of "binding", as coupling
between variable and actual place where value is fetched feels "broken".
You should not be required to specify target when defining statement (I
know that it is what OCI does, but it does not mean it is right).

Mirek

Steve Hutton

unread,
Oct 10, 2005, 6:09:50 AM10/10/05
to
On 2005-10-07, Nicola Musatti <nicola....@gmail.com> wrote:
>
> I find it funny that a supporter of static type checking should pose
> this question. A code generator which is fed a schema description that
> is automatically extracted from the database ensures that the db schema
> and its description in C++ code are synchronized. Not to mention that
> it also relieves you from the tedium of having to describe hundreds of
> tables by hand!

Yes, code generation can be very useful in the rdbms world.

It's also possible to write self adapting solutions which take
advantage of the fact that database objects can be described
at runtime. e.g. the SOCI documentation contains an example which
creates an xml document from an arbitrary query (using column names as
the names of elements):
http://soci.sourceforge.net/doc.html#dynamic

Steve

Mirek Fidler

unread,
Oct 10, 2005, 6:18:51 AM10/10/05
to
Maciej Sobczak wrote:
> Mirek Fidler wrote:
>
>>>Yes, exactly. The problem is that SQL grammar is part of the language
>>>standard and that grammar cannot be implemented within the C++ grammar.
>>>I don't consider Mirek's examples to be SQL. It is *some* query language
>>
>>
>>So you did not understand my examples after one minute of staring at
>>them? (Because that is what really matters).
>
>
> What really matters is that each database environment comes with its own
> SQL console. Developers routinely use that console for trying and
> testing their queries. What really matters is whether you can
> copy'n'paste the query from the C++ editor to SQL console and back.

My recent queries tend to be so complicated that they are almost
impossible to implement not using SqlExp :)

Anyway, I do test them using console if (when debugging) my application
does not work. In my application log they appear in "SQL" form.

Mirek

Steve Hutton

unread,
Oct 10, 2005, 6:11:02 AM10/10/05
to
On 2005-10-07, kanze <ka...@gabi-soft.fr> wrote:
>
> 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++.)

Yes, actually embedded SQL was part of the SQL-92 standard.
I recall studying it in school as part of a "Fundamentals of
Database Management Systems" textbook.

Oracle's PRO*C implementation also gained widespread use.
We used it in my first Oracle job in the late 90s and last
year when I was working for a major telecom, the 3rd party
billing system they used was PRO*C based. Oracle also provides
a Java embedded SQL implementation called SQLJ.

> All in all, my biggest complaint with OTL is that its SQL


> commands were simply strings, and that there was no compile time
> checking.

I agree the lack of SQL compile time checking can be unnerving


when coming from a C++ perspective, but the leading rdbms APIs for
almost all programming languages are based on SQL strings - JDBC
being the prime example, but also of course Perl and Python...

On the other hand I have had the experience of using what is probably
the leading non-sql based rdbms library - Rogue Wave's dbtools.
Initially I was quite impressed with the design and how it supported
compile time checking. However after using it for a while I came
to see some downsides as well:

- Compile time checking of this style only catches a limited set of
errors - it doesn't help if you mispell the column name or table name,
or if your dba renames things or makes other changes after you compile.

- A fairly large portion of sql syntax errors in code are due to
not escaping strings properly - but this problem is easily solved through
the use of bind variables, which are essential for performance anyway.

- When writing a new query, I found myself going through the exercise
of writing it and testing it in pure SQL, then translating it to the
language of the db api, anyway.

- Similarly, and perhaps more significantly, when maintaing a large system,

an essential step is to gain understanding of all of the queries in use.


So a reverse translation is needed. This need to translate the queries
back to pure SQL also comes up when working with other staff members or
departments who might be in charge of tuning or auditing queries.

So while the C++ programmer in me sees the appeal of compile time
checking, the db programmer and pragmatist in me appreciates having pure
SQL strings (preferably in a configuration file separate from the code
for large systems - I worked on such a system last year and it was by
far the best part of the design.)

Finally, if we were designing a mechanism for querying a relational
database from C++ in a world without SQL, the choice might be closer,
but SQL is of course in widespread use - there are many non-programmers

who know SQL (DBAs being the most important).

> 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.)

Ah, a point in favor of SQL-based libraries like OTL and SOCI? ;-)

Steve

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

Steve Hutton

unread,
Oct 10, 2005, 6:10:28 AM10/10/05
to
On 2005-10-06, Roland Pibinger <rpb...@yahoo.com> wrote:
...

>
> 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).

O/R mapping doesn't neccessarily mean no sql strings. A common
approach in the Java world is store the sql strings in a configuration
file, e.g. iBatis is a very complete solution that does it this way.
http://ibatis.apache.org/

In practice I've found a continual need to know what the underlying
sql being executed is - when understanding, debugging, tuning, and
enhancing large systems. Having sql queries in a configuration file
has been the most maintainable approach in my experience.

Especially as you get into queries which join 4 or more tables, the
overhead of translating to and from something like Rogue Wave (which
I used before and found to be a solid library) becomes a hinderance.

IHMO complex queries are much easier to write and maintain when they are
stored in pure SQL text form - they can be easily tested and enhanced
using standard database tools, and in fact they can be audited and
tuned by non-programmers (DBAs).

Steve

Steve Hutton

unread,
Oct 10, 2005, 6:13:15 AM10/10/05
to
On 2005-10-07, kanze <ka...@gabi-soft.fr> wrote:
>
> 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++.)

Yes, actually embedded SQL was part of the SQL-92 standard.


I recall studying it in school as part of a "Fundamentals of
Database Management Systems" textbook.

Oracle's PRO*C implementation also gained widespread use.
We used it in my first Oracle job in the late 90s and last
year when I was working for a major telecom, the 3rd party
billing system they used was PRO*C based. Oracle also provides
a Java embedded SQL implementation called SQLJ.

> All in all, my biggest complaint with OTL is that its SQL


> commands were simply strings, and that there was no compile time
> checking.

I agree the lack of SQL compile time checking can be unnerving

> 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.)

Ah, a point in favor of SQL-based libraries like OTL and SOCI? ;-)

Steve

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

Steve Hutton

unread,
Oct 10, 2005, 6:12:10 AM10/10/05
to
On 2005-10-07, kanze <ka...@gabi-soft.fr> wrote:
>
> 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++.)

Yes, actually embedded SQL was part of the SQL-92 standard.


I recall studying it in school as part of a "Fundamentals of
Database Management Systems" textbook.

Oracle's PRO*C implementation also gained widespread use.
We used it in my first Oracle job in the late 90s and last
year when I was working for a major telecom, the 3rd party
billing system they used was PRO*C based. Oracle also provides
a Java embedded SQL implementation called SQLJ.

> All in all, my biggest complaint with OTL is that its SQL


> commands were simply strings, and that there was no compile time
> checking.

I agree the lack of SQL compile time checking can be unnerving

> 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.)

Ah, a point in favor of SQL-based libraries like OTL and SOCI? ;-)

Steve

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

Mirek Fidler

unread,
Oct 10, 2005, 7:14:42 AM10/10/05
to
Bronek Kozicki wrote:
> kanze wrote:
>
>>>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.
>
>
> Ther other advantage (very important to me) is to avoid string
> manipulation when preparing (sometimes exteremely) complex SQL queries.

Actually, the original reason for "SqlExp" was the same. Only lately we
started to use it for static SQL as well.

> I created library slimilar to the one presented by Mirek just to gain
> this particular ability. Parts of a the single select statement (ie. SQL
> clauses) are manipulated by different parts of our software, and selects
> are also valid arguments for some SQL clauses in other (outer) selects,
> thus becoming subselects.

Yes. Concurrent development :)

Mirek

kanze

unread,
Oct 10, 2005, 9:24:36 AM10/10/05
to
Mirek Fidler wrote:

>>> > Yes, exactly. The problem is that SQL grammar is part of the
>>> > language standard and that grammar cannot be implemented
>>> > within the C++ grammar. I don't consider Mirek's examples
>>> > to be SQL. It is *some* query language


>> So you did not understand my examples after one minute of
>> staring at them? (Because that is what really matters).


To be fair, that's not all that matters. Being able to write
new expressions is important too. The mapping seems rather
straight foreward, but it does take more than a minute to learn
it.

On the other hand, it doesn't look like it would take a long
time (once you get the documentation finished:-)). And IMHO,
the static syntax checking will more than pay for the effort.

--
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 10, 2005, 9:23:45 AM10/10/05
to
Maciej Sobczak wrote:

>> kanze wrote:


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


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


>>> > 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.)


>> Indeed abuse - they are shift operators. IOStreams also abuses
>> them by using those shift operators for input/output, so we
>> are in a good company. ;-)


You can get away with certain things when you are implementing
the reference:-). Seriously, in today's C++, << and >> are the
output and input operators. With a regrettable overload to mean
shifting, which we can't do away with for historical reasons.


>>>> >>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 <<.


>> OK, also nice, although requires more parentheses (a bit
>> "Lispish" :) ).


IMHO, it fits if you use >> to extract the data read.


>>> > I understand the idea that you are "outputting" to the sql
>>> > session


>> Exactly. I "send" the query to the server.


>>> > If I accept that the select
>>> > statement is a string, what's wrong with:
>>> >
>>> > isqlstream in( sql, "select ..." ) ;
>>> > while ( sql >> record ) {
>>> > // ...
>>> > }


>> What's wrong? The "record". What's that? An object? What's its
>> type?


Whatever you want, as long as you can write an operator>> for it
using existing operator>>, and that it reads the correct number
of columns (for the select statement) in the correct order.

If you don't want to define a new type, of course, you can just
write:

while ( sql >> name >> age ) { /* ... */ }


>> SOCI expects from the user to provide variables so that the
>> library knows where to store the data or where to get it from.
>> If you exchange more than one column, you provide more than
>> one variable.


That's the way OTL works by default. Except that you specify
the variable each time you read a column. Because of the way
operator overloading works, however, you can also write your own
overloads, for whatever types you want to define.


>> string name, phone;
>> sql << "select name, phone ...", into(name), into(phone);


>> There is no *additional* record variable and this is
>> consistent with what is known from embedded SQL.


The record variable is optional. If you have objects which
correspond to what you are reading, it is simple and
appropriate. If it doesn't fit, don't use it. Like with
istream, the flexibility is there to use it however you want.


>>> > The question is rather: have you reduced it too far, and
>>> > lost significant functionality (like static type checking).


>> We did not loose anything, because static type checking was
>> never there in the first place. We just choose not to gain it.


:-)

>>> > And if not, why this "unnatural" (for C++) syntax, and not
>>> > simply an isqlstream, as above? Or a pipestream to and from
>>> > isql?


>> To get those nice "into" elements for data exchange. :)


I'm not sure I understand. What is the advantage of forcing the
user to specify a single variable for each column, and ensuring
that all data goes into that variable? It seems like lost
flexibility to me.


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


>> I don't understand this. Could you elaborate?


Well, the equivalent might be something along the lines of:

otl_stream out( 1000,
"insert into t values( :a, :b, :c )",
sql ) ;
for ( int i = 0 ; i < 20 ; ++ i ) {
out << a[ i ] << b[ i ] << c[ i ] ;
}

Note that the values being inserted are expressions: a[i], and
not explicit variables.

(Of course, if you've defined a record type, and overloaded the

>>>> operator correctly, you might just write:


std::copy( v.begin(), v.end(), otl_output_iterator( out ) ) ;

and let STL take care of the loop.)

As I said, while I don't like the abstraction of << being used
to position the select statements bound variables (although I
can live with it), and I think that more static typecheck is the
way to go in the long run, OTL remains exceptionally flexible
and, at least for someone as imbued in the C++ world as I am,
incredibly easy to use.


>>>> >>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?


>> I was working in projects that do. One of those projects was
>> a huge billing system for some major telecom company, build on
>> top of what is a solution deployed in other telecoms as well.
>> I think that in fact there is quite a big legacy codebase like
>> this.


Could be. I've used SQL on telecom's projects (both operations
and customer care) and in banks, and I've not seen it, but my
sample space is admittedly pretty small.

[...]

>>> > 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 ] ;


>> sql << "...", into(x), into(y), into(z);


>> It reads three values.


I don't think you quite understood (or maybe I misunderstand
you). In my case, the select statement selected one (and only
one) column. The where clause selected three rows. The three
values were from three different rows.

More generally, the OTL syntax allows reading each column,row
couple where ever is desired. It would be quite possible to
call a function, for example, which extracts the values into
local variables -- in fact, this is exactly what I did in my
case, where I had:

otl_stream&
operator>>( otl_stream& source, MyData& dest )
{
source >> dest.fieldA >> dest.fieldB ...
return source ;
}

(Modulo some error checking, of course. But you get the idea.)


>> Moreover, I like this, because the SOCI syntax does not
>> suggest that the operator>> is doing any data transmission
>> (this is how I read your OTL example). In SOCI, the data is
>> placed in all three variables as a result of a *single*
>> database operation. That's why I don't see any place for
>> three separate extraction operators - as you said, OTL pushed
>> the iostream analogy too far.


>> It is more clear here:


>> Statement st = (sql.prepare << "select...", into(x), into(y), into(z));


>> st.execute();


>> The data is transmitted to x, y and z as a result of executing
>> the statement, which may happen later, as above. There are no
>> three separate transmissions, but *one* server operation.
>> Your OTL example looks like doing three intput operations,
>> which is not true and therefore I treat it as a misleading use
>> of operators. It can be explained like doing one transmission
>> to "some internal buffer" and then executing three extractions
>> from that buffer, but I don't like it neither - it suggests
>> that there are more steps than necessary.


Sounds to me like you are exposing too many low level details.
I don't care how many "operations" there are, at some lower
level. Just as I don't care about the fact that filebuf is
reading 1024 byte blocks and buffering them when I do std::cin

>>>> someInt.


At one point in my code, for example, I had something like:

std::ostringstream cmd ;
cmd << "select " << MyData::columnNames
<< " from tableX "
<< "where i >= " << min << " and i < " << max ;
otl_stream in( 1024, cmd.str().c_str(), sql ) ;
MyData obj ;
while ( in >> obj ) {
v.push_back( obj ) ;
}

With a modern version of OTL (and a modern compiler and a modern
implementation of the STL:-)), the last loop would be a simple
invocation of std::copy.

(Of course, this example also shows up some of the weak points
of OTL. Like having to use an ostringstream to generate the
command, with no static checking of the SQL syntax. Or that
silly 1024 that is needed as the first parameter -- OTL is not
able to determine the correct buffer size on its own, so you
must give it. But once you have your otl_stream, using it is
pure 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.


>> Oracle was pushing its Pro*C (and related technologies for
>> other languages) quite heavily before, I think you can find
>> lots of actual code using it.


Maybe. As I say, I'm not an expert in the field.

</