Re: [boost] SOCI 3.0.0 - The Database Access Library

From: Phil Endecott (spam_from_boost_dev_at_[hidden])
Date: 2008-07-11 09:07:36

Maciej Sobczak wrote:
> What about object length? Let's suppose that user does this:
>
> long long salary;
> sql << "select salary from ...", into(salary);
>
> and the server returns binary data of length 4. Still, the access 
> library would need to figure out that it is not what is expected, and do 
> the necessary conversion.

We were talking about query arguments, not results, before.  Many of 
the issues are the same, but I think that in the case of results it's 
more legitimate to worry about performance.  I hadn't actually noticed 
that results were also being transferred as text.

> It is much better with Oracle, where the client defines the expected 
> data types before executing the query and the server (or the native 
> client library) does the rest, so that no discovery and conversion is 
> needed in the client code.

When necessary, the approach that I've taken is for the client to 
define the expected data type, but to do this using a cast inside the 
SQL string:

"select salary::int64 from ..."

You can then be fairly certain that you'll actually get a 64-bit value 
back.  (I guess that SQL types with defined sizes are non-standard 
though.)  If the type of salary can't be converted to int64 for some 
reason I think you should get an error at preparation time.

Then at execution time, all I do is a quick check that the retuned 
column types are the expected ones, and throw if they aren't.  (This is 
per-query not per-row.)  There's no need for all the combinations of 
conversions that you suggest.

> In any case, the fact that PostgreSQL uses text for data transfer is an 
> implementation detail of this backend. It is possible to implement 
> alternative backend for binary transfer - the rest of the library is 
> prepared for this.

Right, yes.

Regards,  Phil.