Re: [boost] Interest in an ODBC library?

From: Corwin Joy (cjoy_at_[hidden])
Date: 2005-08-27 00:34:51

----- Original Message ----- 
From: "Brock Peabody" <brock.peabody_at_[hidden]>

>> Corwin Joy wrote:

>> 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
>
> Brock wrote:
>
> Unfortunately, there really isn't any way to guarantee at compile time
> what the data types in a database will be at run time.

Actually what I am talking about here is the situation where
the user specifies the type of the target data at compile
time.  But you're right, in the case of a variant row the assumption is
that we don't know the type until runtime.  In DTL we also support
binding to specific types. So, using DTL type sytax:

struct Employee {
    string Name;
    double Salary;
}

BindEmployee(Employee &Emp, Bindings &cols) {
    cols["Name"] >> emp.Name;
    cols["Salary"] >> emp.Salary;
}

In the binding syntax for DTL when we say

cols["Name"] >> emp.Name;

we are really specifying 3 things:

1. Bind a SQL column called "Name" to the member Name in the
Employee structure (or class).

2. Use the default SQL to C mapping.  I.e. assume the source SQL column
is of type SQL_VARCHAR. To do this,  have a class that contains a list of 
default mappings that
we specify like this:
void ETI_Map::build()

{

// add to these mappings as needed ...

(*this)[typeid(short).name()] =

TypeTranslation(typeid(short).name(), C_SHORT, SQL_INTEGER, SQL_C_SSHORT, 
TypeTranslation::TYPE_PRIMITIVE, sizeof(short));

(*this)[typeid(unsigned short).name()] =

TypeTranslation(typeid(unsigned short).name(), C_USHORT, SQL_INTEGER, 
SQL_C_USHORT, TypeTranslation::TYPE_PRIMITIVE, sizeof(unsigned short));

...

}

If I did it again, I would probably stay away from RTTI since some folks 
don't like it.

When we first designed DTL we didn't support stored procdures so I thought 
this was
enough.  Eventually, though, we came around to the point of view that 
supporting stored procedures
with an iterator is actually important so we generalized the binding sytax 
so that

3.
>>    Bind as an input parameter
<<    Bind as an output parameter
==    Bind as an input/output parameter

Personally, I quite like using operators for the binding syntax rather than 
function calls since I think this make it much easier to read what is going 
on.
You can find more examples here:
http://dtemplatelib.sourceforge.net/LocalBCA.htm

Here is a second quickie example

// simple example for Local bindings ... read a map

map<string, double> ReadDataLocalBCASingleField()
{
    typedef pair<string, double> rowtype;    rowtype row;  // prototype row 
object to guide binding process    // declare our bindings locally

    DBView<rowtype> view("EMPLOYEES",
        BCA(row, COLS["Name"] >> row.first && COLS["Salary"] >> row.second )
    );

    // copy the doubles from the view into the vector and return
    map<string, double> results(view.begin(), view.end());
    return results;
}

> Brock Wrote
>
> That's odd.  I've never used a native interface that cared about column
> order.
>
In fact, many ODBC drivers don't care either.  But some do.
To quote the relevant ODBC documentation:
"
Using SQLGetData
If the driver does not support extensions to SQLGetData, the function can 
return data only for unbound columns with a number greater than that of the 
last bound column. Furthermore, within a row of data, the value of the 
ColumnNumber argument in each call to SQLGetData must be greater than or 
equal to the value of ColumnNumber in the previous call"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgetdata.asp

It's sad but true.