Re: [boost] [SQL-Connectivity] Is Boost interested in CppDB?

Subject: Re: [boost] [SQL-Connectivity] Is Boost interested in CppDB?
From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2010-12-14 15:33:08

On Tue, Dec 14, 2010 at 1:22 PM, Artyom <artyomtnk_at_[hidden]> wrote:
> I'm aware of this feature as SOCI supports this and as Orcale's OCI
> is only one API (at least onle one I'm aware off) that supports this.
>
> CppDB has nothing special for this as it does not have (yet) Oracle
> specific backend (you still can use ODBC with Oracle).
>
> Also this kind of feature has many limitations on what can be fetched
> and stored and is very "OCI" specific so I do not do anything like that.

I wasn't using SOCI but directly OCI, and I don't remember any
particular limitations compared to non-array binds.

> And not I do not support this as it is very Oracle specific feature
> and have quite lots of problems.

Again, I'm not sure what you mean by "lots of problems". Once we
understood how to do them (OCI is a very obtuse API), they worked just
fine, and were much faster than the equivalent non-array binds. In
fact Oracle array-binds were faster with a Win32 client talking to a
remote 11g Linux64 server (on the LAN over Gb-ethernet) than the
equivalent code against a local SQLite3 DB. With scalar binds SQLite
was faster, but not with array-binds.

> - What do you do with varchar(10000) types how do you do bulk operations?
>  Allocate for each entry huge amount of memory? Is this good?

Well, we'd probably use a LOB datatype, in which case you only get the
descriptor and later perform the IO on that descriptor (the new-in-11g
SecureFile LOB columns had surprisingly good IO performance).

> - What happens when you bind 100 strings when one of them is 10000 bytes
>  length and others 10? Allocate 100x10000 bytes, I'm not aware
>  if something better can be done but at least this is what SOCI does,
>  and I beleive they do the best.

Somehow I vaguely remember that it could be avoided (there are
different ways to do array binds), but I could be wrong. This stuff is
more than 18 months old for me.

> - What amount of data do you allocate? You do not know in advance
>  how many rows you fetch.

We experimented with various sizes, but experimentatlly I noticed that
once the combined amount of data in the arrays reached 32KB, that's
when performance would level off for my particular win32 client (both
client and server running 11g).

> So when writing Orcale backend it would be possible to do some optimizations
> for fetching data but they should be internal for the backend and not something generic.

Striving for genericity and simplicity is good, as long as the API
doesn't preclude doing more complex or proprietary things if the
backend supports it. That's the reason we had to abandon OCCI, since
it did not support many of the features OCI supported (XMLTYPE and
objects in general, CQN, etc...) and didn't allow going under-the-hood
to the OCI level when needed, to use what it (OCCI, the nice simple
JDBC-like C++ API) didn't provide wrappers for.

It's great to be able to use the nicely wrapped, typeseafe,
syntax-sugar enabled, DBMS-agostic API when you can, as long as you
can do proprietary things using the underlying API the back-end uses,
on the same connection, the few times you really need to.

I don't think what I describe above is incompatible with your stated
goals either, and I respect the fact that you don't see it as
something that needs to be supported in the generic API. If the
back-end can be accessed, let me and others shoot ourselves in the
foot when we really need to :) --DD