[odb-users] Re: SQL Variant

[Boris Kolpackov]

Hi Dean,

I've CC'ed the odb-users mailing list to my reply since others may also
be interested in this topic.

Dean Throop <DThroop at PacificCabinets.com> writes:

> On April 8, 2012 you indicated that ODB does not support the MS SQL 
> Variant type.  Is this still the case?  I have a database I am accessing
> that utilizes this value type for something critical I need and I am
> unable to modify the database.

ODB now supports a feature called "extended database type mapping".
Essentially, it allows you to map any "extra" database type (spatial
types, user-defined types, arrays, XML, etc) to a C++ type using one
of the natively-supported types (normally string or binary) as an
interface type. This blog post has a nice overview of this feature:

http://www.codesynthesis.com/~boris/blog/2012/07/18/custom-database-to-cxx-type-mapping-in-odb/

In the ODB manual, the relevant section is 12.7, "Database Type Mapping
Pragmas".

I went ahead and added a test case to the mssql/custom test that shows
how to map SQL Server SQL_VARIANT type to a C++ variant class that can
store integer or string value. You can view the code here:

http://scm.codesynthesis.com/?p=odb/odb-tests.git;a=tree;f=mssql/custom;h=a1c1bd958f4e471a991b780d4092b09329e1f394;hb=HEAD

The idea is to represent SQL_VARIANT values as a string that contains
the base type name and value, separated by a space (e.g., 'bigint 123'
or 'varchar abc').

We implement two SQL Server functions (custom.sql): variant_to_string()
and string_to_variant() which convert between SQL_VARIANT and this string
representation.

With the help of these functions we tell ODB how to map SQL_VARIANT to 
VARCHAR(max) (test.hxx). We also implement the ODB value_traits
specialization for our variant C++ class that decodes our string
representation of SQL_VARIANT (traits.hxx, traits.cxx).

Overall, everything works great, even queries.

Boris