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

From: Maciej Sobczak (prog_at_[hidden])
Date: 2008-07-11 02:55:59

Phil Endecott wrote:

>> PostgreSQL always uses text transmission of query arguments, and they 
>> are also passed separately from the query.

>> I was meditating on the binary data transmission, but the interface is 
>> basically ill-designed and even PostgreSQL folks recommended the use 
>> of text format for portability (details: with PostgreSQL the binary 
>> format has to comply with *server* conventions not the client's and 
>> you never know what they are).
> 
> Hmm.  I was under the impression that ints were always sent in network 
> byte order (i.e. big endian), and I think I would have noticed if I'd 
> got it wrong because I've only ever used it on little-endian systems.  
> Is there some other convention that you're thinking of?

According to 
<http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html>, the 
format is:

"a zero-terminated text string (for text format) or binary data in the 
format expected by the server (for binary format)"

Nothing is said on what that format is and I understand that it might 
depend on how the server was compiled, what is the target platform, etc.
There is a commend at the bottom of this page, by Jason Lenthe, that the 
format is network byte order, so this might be some hint and also in 
agreement with what you say.
Is it worth the trouble? I'm not sure. 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.
Let's try something even more complex:

double salary;
sql << "select salary from ...", into(salary);

and the server returns the same as before. What now? The access library 
would need to figure out not only the size difference, but also *type* 
difference and the conversion would be inevitable anyway.

Why not:

std::string salary;
// ...

and again, the access library would need to handle the difference.
The number of different types that the server might return is quite big 
and all conversion combinations would need to be handled.

I don't think that it is worth the trouble with PostgreSQL.

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.

The difference between these two approaches can be shortly summarized 
thus: in Oracle you get what you ask for (and then binary makes sense), 
whereas in PostgreSQL you get whatever the server gives you (and you 
have to figure out how to digest it).

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.

-- 
Maciej Sobczak * www.msobczak.com * www.inspirel.com