[Peter Muraya]

Hi,
I have a problem of transferring data from one odcbc-complilant database to another where the field types are known to be similar but not identical. I'm thinking of a function defined as follows:-

Code: Select all

coerce:(core::value SourceData, odbc_native::sqlDataType DestinationType)->core::value CoercedValue

What would be the best way of implementing it? I'm thinking of 2 approaches: brutal force and value2String.

The brutal force approach uses the fact that there are 12 codes of sql data types, viz.,

Code: Select all

sql_unknown_type : sqlDataType = 0.
    sql_char : sqlDataType = 1.
    sql_numeric : sqlDataType = 2.
    sql_decimal : sqlDataType = 3.
    sql_integer : sqlDataType = 4.
    sql_smallint : sqlDataType = 5.
    sql_float : sqlDataType = 6.
    sql_real : sqlDataType = 7.
    sql_double : sqlDataType = 8.
    sql_datetime : sqlDataType = 9.
    sql_varchar : sqlDataType = 12.

and there are 17 possible types of data sources as defined by the core::value domain, viz.,

Code: Select all

value =
        none();
        boolean(boolean Value);
        unsigned64(unsigned64 Value);
        integer64(integer64 Value);
        unsigned(unsigned Value);
        integer(integer Value);
        real(real Value);
        char(char Value);
        string(string Value);
        string8(string8 Value);
        binary(binary Value);
        binaryNonAtomic(binaryNonAtomic Value);
        object(object Value);
        gmtTimeValue(gmtTimeValue Value);
        localTimeValue(localTimeValue Value);
        pointer(pointer Value);
        handle(handle Value).
 

So I have 12*17=204 matches to make which I think is way too much.

The value2String method goes something like ...

Code: Select all

clauses
    coerce(Source, Type)=str_value(Str, Type):-
         core::value2String(Source)=Str.

Before I get bogged down with implementing the str_value predicate I would like to find out if there are better ways of achieving the intended result than these thoughts.

[Thomas Linder Puls]

I doubt that is necessary. ODBC performs all kinds of conversions itself.

[Peter Muraya]

Thank you Thomas.
My intention was to minimize possibilities of "Data conversion error" by ensuring correct data type matches, but I get your point. I will rely on ODBC to do the conversion; this should work well for database to database transfers.

I now have a similar question regarding spreadsheet to database transfers. I'm using COM to read the data that is returned as comDomains::variant. Has anybody converted variants to core::values?

[Peter Muraya]

Hi,
I have tried to map the comDomains::variant to core::value, but I'm having trouble with

Code: Select all

How to you convert:-
- oleCurrency to a real number?
- oldDate to either gmtTimevalue or localTime value?

[Thomas Linder Puls]

Do you actually receive such values? I have never seen any of them (but I have not retrieved dates from Excell).

Microsoft writes this about DATE value:

Type: DATE

A date and time value. Dates are represented as double-precision numbers, where midnight, January 1, 1900 is 2.0, January 2, 1900 is 3.0, and so on.

The date can be converted to and from an MS-DOS representation using VariantTimeToDosDateTime.

The mentioned function seem to be of little help unless you know how MS-DOS formats dates and times, but apparently there is also a VariantTimeToSystemTime function.

We have however never needed to convert such a value (hence the absence of the conversion functions).

I believe the currency value completely unused. Microsoft only spend these few words on it:

Type: CY

A currency value.

[Peter Muraya]

Thanks Thomas.

Do you actually receive such values?

I'm working on a data integration project whose sources of raw data are (thousands of) files in Excel, delimited texts, word documents, access database, etc. By far, most of the data is in Excel and I use COM to read them directly (and ODBC to write them into the integrating database that has corresponding date type fields).

[Thomas Linder Puls]

Yes, but do you actually receive these two kind of values

Code: Select all

comDomains::oleCurrency(oleCurrency Value);
comDomains::oleDate(oleDate Value);

from Excel?