Re: [boost] Interest in an ODBC library?

From: Paul (elegant_dice_at_[hidden])
Date: 2005-08-23 23:59:56

Corwin Joy wrote:
>>Jonathan Wakely wrote:
>>
>>
>>>How do you handle NULL values?  the postgresql::row_set_imp::get() 
>>>function
>>>doesn't seem to account for NULL, which would crash strlen(), atof() etc.
>>
> In DTL, Paul Harris actually just created a version of boost::optional to 
> handle NULL values which worked very well as a way to read and write types 
> that could be null.  He also did mappings of the boost::posix_time class and 
> we also used a version of the boost:variant class to provide dynamically 
> bound types (where you don't know the types until runtime). 
> 

Hi,  I'll attempt to be even as fraction as informative as Corwin was 
... I'm the guy responsible for the optional variant mentioned above.

I couldn't directly use boost::optional<> because binders need to be
given a memory address to write data to, even if its going to be NULL.
I wasn't sure how to do that with boost::optional, as it works with
uninitialised memory and other such stuff.  I assume you can do it, but
I didn't have the time to figure it out.

So I created dtl::nullable<>, where the underlying value is always
available at some memory address for binding, but the DTL must then go
back over all the nullables and set their is_null flag.   This is a key
part to performance - the advantage of binding to raw memory is speed,
but if you have more complex binders, then you are going to have go back
over what you have retrieved and do some processing.

Another example of this is my dtl_posix_time, which is a variant on
boost::posix_time::ptime.  You can't bind to ptime as it doesn't have
the right memory signature... so I embed both a ptime and an SQL date 
variable into dtl_posix_time, and then DTL will import/export the ptime 
to the SQL version before and after each SQL operation.

This is an intrusive solution, which is why I don't really like it.  I 
would rather use the real data types instead of "sql-enabled" types. 
But they work with the DTL, so mission accomplished :)

An alternative is to select into a buffer area and then copy-construct
(or whatever) into the real instances.  I assume this would work, but 
now you have to deal with bulk selects into a bound pre-allocated 
buffers, buffer management, etc.

Another problem that has cropped up is the handling of NULL or empty 
strings.  In the DTL, a NULL VARCHAR(10) can be bound to a 
dtl::nullable<dtl::tcstring<10> > field;  Then there is a distiction 
between an empty string and a NULL string.
But the distinction becomes a big blurred when you talk about NULL TEXT 
columns.  IIRC, dtl::nullable<std::string> doesn't currently work 
correctly, as either the DTL or ODBC drivers assume that an empty string 
means NULL (I forget what the problem was).

This will be fixed eventually, but its something you will have to be 
careful about too.

Oh, and I'd like to say that I always select everything thats available, 
so I'm not terribly interested in input_iterators (it seems that 
everyone else is).
This is because I usually do work on ALL the data in the database and 
produce some other set of data (that is usually not a 1-to-1 
transformation) and I need to do it fast and often.  I used to do more 
work in the SQL server, but that has evolved until the point where the 
SQL server is just a place to persist data.

I suppose I could just serialise the data instead of using a database, 
however I also need to support multiple networked users working on the 
same dataset.  Fortunately at this stage, the dataset isn't tremendous.

Hope this helps,
Paul