Re: [boost] Interest in an ODBC library?

From: Corwin Joy (cjoy_at_[hidden])
Date: 2005-08-24 19:55:45

"Brock Peabody" <brock.peabody_at_[hidden]> wrote in message 
news:000c01c5a8bb$7b148420$a70a10ac_at_npci.com...
> I'd rather make the whole system templated
> on the type of string that is used than force everyone to use a wide
> string type.

Agreed.  Just wanted to point this out.  We did everything as a "tstring" 
with a
compile switch to say whether you wanted wide or narrow strings and whether
to link to the wide or narrow ODBC libraries.

>> One major difference, though, is that you're going to want "sticky
types".

> I think this is more of an issue for a binding system.  Boost.Database
> is more of simple uniform interface to a database.  This type of
> functionality could be built on top of Boost.Database.

I guess you could do this in the bindings.  I'm not sure how you would get
compile time checks that you are assigning an illegal value to a field if 
you
just expose a collection of boost::variant fields tho'.

> Arbitrary length strings are a common type in databases.  I understand
> from your post that fixed length strings can be a problem, but I don't
> see why arbitrarily long ones are.

Actually if you re-read my earlier post my point is exactly the reverse.
Fixed length strings are easy to support from an implementation point of 
view,
it is arbitrary length strings that are hard. Supporting arbitrary length 
strings
under ODBC comes down to using SQLGetData and SQLPutData.  Using
these functions creates two problems:
1. Efficiency.  These are slower than binding a small fixed length string.
2. More problematic, if you call SQLGetData  and SQLPutData on a column then
ODBC says it may not be supported unless that string column comes last.  So, 
a simple
query like "select name, salary from employees" will fail if you want to 
support arbitrary
length strings because the string column does not come last.

I'm not saying that supporting arbitrary length strings is not the right way 
to go, I'm just
saying that it has some potentially unlpeasent consequences down the line. 
Arbitrary
length strings was the way we went in DTL but it does not work so great when 
users
want to run arbitrary queries at runtime.  For this reason we have an 
alternate compile
switch that makes the library bind to a fixed length buffer rather than call 
SQLGetData
and SQLPutData.

> 2. Database data type and related information.  You can't just assume
this
> from the C++ type.  Sometimes you will need to specify extra
information
> about the SQL type to bind nondefault values for use in things like
> mapping to larger strings, specifying non-default precision for dates
etc.
> The database datatype will be driver layer specific, i.e. different
for
> ODBC and various native driver layers.

>>If the user is using the non-C++ data type information, what they're
>>doing won't be database-independent.  I don't want to have to worry
>>about what the underlying type is.

I'm not saying that you should default construct this inside private class 
members,
but what I am saying is that you will need to allow it to be overriden. 
I'll give a
couple examples:
1. In microsoft access there are two kinds of text fields, text and memo.
text can be up to 255 bytes long, memo can be much larger (I think up to 
4k).
When you bind a text column, you have to specify a SQL column length.
If you specify >255 for a text column you will get an error.
If you specify 255 for a memo column you will get truncated.
When the user binds a std::string you don't know what kind of SQL type they 
are binding to.

The same thing goes in Oracle when you bind a column, again you have VARCHAR
for short columns and LONG VARCHAR for long text columns so again you have 
two
different source data types.

2. Dates and precision.  By default when we bind a date we set the precision 
to ignore
milliseconds.  But, for some applications they want a different precision 
down to the milliseconds read.

Anyway, not having a class to hold the source SQL column type is just wrong 
IMO since
there are all kinds of conversion rules that may be desired and the default 
may not be the
right one and have to be overriden.

I would recommend that you take a look at this conversion table to better 
understand
what I am talking about with SQL type versus C++ type and the choice of 
conversion
rules:
http://www.uic.rsu.ru/doc/db/DB2CLI/db2l022.htm#HDRHDDTDCN

>> 3. Mapping between 1 <--> 2.  Can include things like the address of
>> where to put the data, intermediate buffers that may be used to
>> manage the mappings, possibly any conversion policy.  Here is
>> an example snippet from DTL where we ended up having all 3 of these
>> things in a single class:

>These are the types of things I'd prefer remain hidden.  Aside from the
>complexity they add to the interface (I don't want to have to deal with
>buffers, I just want it to work!), it would be hard to make all this
>portable.

Yes, definitely they should be hidden as private members, I was just 
pointing
out that this association needs to be made.

_______________________________________________
Unsubscribe & other changes: 
http://lists.boost.org/mailman/listinfo.cgi/boost