Hi Jean,

Each MV field must be attached to a 'assocName' (field <7> of the dict).

Then you'll have a secondary table for each assoc into the mainfile, the table is named 'mainfile_assocName' 

If you define, into the dict file, the assoc as a PH with fields list you limit the fields into the secondary table.

check the docs for details.

I hope this help.

ps : uvodbc can't adress subvalue level !

Hi Manu, 

Thanks for your reply. 

Sorry but I don't understand well, 

Indeed, I see the "assocName" into field 7 of the dict. 

But what does it mean "as a PH with fields list" ? If I modify assoc parameter with PH into the DICT I can read MV field with ODBC ? 

When you say "uvodbc can't adress subvalue level" . Does it mean is impossible to read multivalue field with ODBC ? 

Thanks for your help. 

Fundamentally, ODBC does not understand repeating or nested entities such as MultiValued fields, and clients like Excel would not know how to handle them. So UniVerse has to dynamically normalize these to create virtual sub-tables that they can understand.

If you have a standard UniVerse Orders table such as:

Order Id

Date

Time

Customer

Item]Item]Item

Price]Price]Price

Qty]Qty]Qty

It needs to separate these out into two tables to present to ODBC: Orders containing the Order Id, Date, Time and Customer; and Order Details containing Item, Price, Qty and Order Id - as you would find in a SQL database.

To follow up from what Manu said: each of these sub-tables is identified and named by an association of dictionaries. 

For the example above, you would have fields that looked something like:

Item

D

4

MCU

Item

10L

M

Details

and a phrase in the dictionary that also binds them together, like:

Details

PH

Item Price Qty

You would also need to make sure these are exposed for ODBC. This would then appear as a separate Orders_Details (table) to ODBC (the fields will not appear in Orders).

ODBC is a slow protocol, with lots of overheads in terms of column descriptions etc. so it is best only to expose what you need. The fields may not have been exposed, or may need to be refreshed.

Hi

Thank you Brian for the details. 

You can expose multi value (sep char(253),@VM) but not sub-value (sep char(252), @SVM).

Enjoy

Manu

thank you for your reply Brian, 

So I think I understand the idea.

When you deal with a single value field, it's easy to transform data for ODBC. 

However, when you deal with a multi value field, you need to identify the association with the dictionary and ODBC could take information/data with a sub-table and not in the same table.

Ok for that, but pratically, how I can do that ? I know how identify the association but in excel for exemple when I open a table with ODBC, I construct one sql request to open data (and only single value are accessible). So, to open a subtable with multi value data, do I need to construct a "special request" with a specific "association keyword" ? 

thank for your help 

Hi jean

If you read french I share a how to expose uv with odbc.

I hope this help. 

Many thanks Manu, I read that with great attention. 

Hi jean

Regarding the Excel's side (the client) you must see two table (table, table_assoc)  and you must built a sqlquery with a where table.id = table_assoc.id to extract info from the two tables. Odbc expose uv as relationnal.

Regards 

Subvalues can be exposed to ODBC by using the RAISE function in an I-descriptor. It converts subvalue separators to multivalue separators and multivalue separators to value marks, (attributes). However, like all I-descriptors, they will be read only.

It doesn't always work so well to ODBC for reasons I suspect to be related to the 255 character limit.

Multivalues don't actually require an association. A multivalued attribute included in the @SELECT phrase will be presented as a separate table via Dynamic Normalization whether it has an association or not. There is no point having an association if the multivalued attribute stands alone.

An association is used to present multiple multivalued  fields where there is a correspondence between the multivalues of separate attributes.

For example the details of an order might have the ProductCodes, Prices and Quantities as multivalues for each item in three separate attributes of the Order. The association will combine the corresponding multivalue for each attribute as columns in the normalized table with one row for each of the multivalues. The @SELECT need only refer to the association's PH DICT entry and the individual multivalues will be picked up as part of it.

------------------------------
Greg Clitheroe
Rocket Forum Shared Account
------------------------------