On Tue, Dec 14, 2010 at 1:22 PM, Artyom <artyomtnk@yahoo.com> 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