Accessing MultiValued Data using ADO.NET without Mapping
Aug
10
Written by:
8/10/2010 2:41 PM
The FusionWare Direct DAS account “GAXOLEDB” includes a file named “CUST” which includes 2 MultiValued sets, each of which contains a SubValued set. It contains all the data types supported by your MultiValue system and also includes an example of a record with non-conforming data ( item-id “BAD.3”).
Here is a dump of an item from the CUST file:
>CT CUST 1
1
001 JOHN DOE
002 55
003 9567
004 10324753
005 13266
006 S]C
007 SAVINGS]CHEQUING
008 12335\23447]59866
009 1237\125618]325067
010 14001
011 DONT LOSE THIS
012 TRANSFER FROM ANOTHER\BRANCH
013 HERE'S A BUNCH OF TRAILING
014 STUFF THAT WE WANT TO MAKE
015 SURE WE DON'T LOSE.
016 14001\14001]14001
Consider this SORT command:
SORT CUST NE "BAD.3" BY AGE CUST.NAME AGE BIRTHDATE NET.WORTH BIRTHTIME ACCOUNT.TYPE ACCOUNT.TYPE.NAME ACCOUNT.BALANCE ACCOUNT.NUMBER
This command contains the following components:
|
SORT
|
This indicates that the records will be sorted. If no sort criteria are provided, we sort by item id.
|
|
CUST
|
The file name that we are processing against.
|
|
NE "BAD.3"
|
Selection criteria limits the records processed, in this case skipping our sample bad record.
|
|
BY AGE
|
Sort criteria. The records to process will be sorted by AGE, the by item-id.
|
Everything else is a dictionary definition item name, defining, in the order they will be displayed, the output columns of the report. In order, we are displaying single valued attributes 0, 1, 2, 3, 4, 5. Then we display multivalued attributes 6 and 7. Lastly we display subvalued attributes 8 and 9.
Here is the output of this command:
Note that the output is formatted for a screen or printer. Note also that text that exceeds the display column width wraps. Handling this with a screen scrape would be really difficult!
This same SORT statement can be simplified further by breaking it up into two steps:
SSELECT CUST NE "BAD.3" BY AGE
This command creates and activates a list of keys (referred to as item-ids) that are sorted and restricted to skip our sample bad data record. Any TCL command that results in the generation of a MultiValued SELECT list is acceptable here, including GET-LIST and others.
LIST CUST CUST.NAME AGE BIRTHDATE NET.WORTH BIRTHTIME ACCOUNT.TYPE ACCOUNT.TYPE.NAME ACCOUNT.BALANCE ACCOUNT.NUMBER
Having pulled out the sort and record limiting criteria, we now are left with a simple statement of the output to generate.
The list of fields is:
CUST.NAME AGE BIRTHDATE NET.WORTH BIRTHTIME ACCOUNT.TYPE ACCOUNT.TYPE.NAME ACCOUNT.BALANCE ACCOUNT.NUMBER
FusionWare has provided a BASIC subroutine that conforms to the requirements for the FusionWare Direct DAS ITEMPROC interface. The ITEMPROC command syntax is as follows:
|
EXEC ITEMPROC
|
This indicates that the rest of the command is an ITEMPROC command.
|
|
CUST
|
This is the table against which to process. If no filter command is provided, we process
|
|
"SSELECT CUST NE ""BAD.3""
|
This is the filter command and restricts and sorts the records to be processed.
|
|
FUSIONWARE.DICTCALL
|
The compiled and cataloged BASIC subroutine that conforms to our ITEMPROC interface, and that returns the result set.
|
|
Output Column Parameters
|
All the rest of the values displayed define the output. You can use ID-SUPP to exclude the item-id, which is included by default, as a single valued field.
|
You could simply specify this command as follows:
EXEC ITEMPROC CUST "SSELECT CUST NE ""BAD.3"" BY AGE" FUSIONWARE.DICTCALL CUST.NAME AGE BIRTHDATE NET.WORTH BIRTHTIME ACCOUNT.TYPE ACCOUNT.TYPE.NAME ACCOUNT.BALANCE ACCOUNT.NUMBER
The problem with this is that we can’t know without examining all of the data first whether any of the fields are multivalued or subvalued, or if the data is anything other than character data. Because of this, if you only specify it like this, we will make the following assumptions:
· We will assume subvalued data for everything except the item-id.
· We will make it one big subvalued group.
· We will find the largest number of multivalues and expand all fields to that many multivalues, padding with empty strings as needed.
· For each multivalue, we will find the largest number of subvalues and expand all fields to that many subvalues for that multivalue number, padding with empty strings as needed.
This is almost certainly not what you want to get.
In order to make the output more useful, we allow you to add metadata to each dictionary name you specify. Because we use the comma to separate the metadata parts, you have to enclose each dictionary name in quotes. In our case we use double quotes.
The format of this is:
dictid[,datatype[,scale[,level[,mvgroup[,svgroup[,columnname[,convcode]]]]]]]
Data types can include VARCHAR, INTEGER, DECIMAL, DATE or TIME. Default is VARCHAR.
If you use DECIMAL, then we will use scale, otherwise we ignore it.
Level must be one of the following:
· 0 Single valued field
· 1 MultiValued field (mvgroup must be specified)
· 2 SubValued field (both mvgroup and svgroup must be specified)
Columnname can be used to override the dictionary name as a column name.
Convcode allows you to specify that the raw data must have a PICK conversion code applied to it. We already handle correct conversion codes for DATE, TIME and DECIMAL fields.
The end result of all this is the following command:
EXEC ITEMPROC CUST "SSELECT CUST NE ""BAD.3"" BY AGE" FUSIONWARE.DICTCALL "CUST.NAME,,,0" "AGE,INTEGER,,0" "BIRTHDATE,DATE,,0" "NET.WORTH,DECIMAL,2,0" "BIRTHTIME,TIME,,0" "ACCOUNT.TYPE,,,1,AccountTypes" "ACCOUNT.TYPE.NAME,,,1,AccountTypes" "ACCOUNT.NUMBER,,,2,AccountTypes,Accounts" "ACCOUNT.BALANCE,DECIMAL,2,2,AccountTypes,Accounts"
Here is a breakout of the fields specified:
|
DictName
|
Data Type
|
Scale
|
Level
|
MV Group Name
|
SV Group Name
|
|
CUST.NAME
|
VARCHAR
|
|
0 (single valued)
|
|
|
|
AGE
|
INTEGER
|
|
0 (single valued)
|
|
|
|
BIRTHDATE
|
DATE
|
|
0 (single valued)
|
|
|
|
NET.WORTH
|
DECIMAL
|
2
|
0 (single valued)
|
|
|
|
BIRTHTIME
|
TIME
|
|
0 (single valued)
|
|
|
|
ACCOUNT.TYPE
|
VARCHAR
|
|
1 (multi valued)
|
AccountTypes
|
|
|
ACCOUNT.TYPE.NAME
|
VARCHAR
|
|
1 (multi valued)
|
AccountTypes
|
|
|
ACCOUNT.NUMBER
|
VARCHAR
|
|
2 (sub valued)
|
AccountTypes
|
Accounts
|
|
ACCOUNT.BALANCE
|
DECIMAL
|
2
|
2 (sub valued)
|
AccountTypes
|
Accounts
|
FusionWare Direct Product Family
The FusionWare Direct product family includes client providers that enable access to your MultiValue data from Java, .NET and Legacy COM/COM+ environments.
Client Platforms Supported
Client Platforms supported for .NET and COM clients include:
- Windows 2000, 2003 and 2008, 32 bit and 64 bit
- Windows XP, Vista and Windows 7, 32 bit and 64 bit
Client Platforms Supported for Java client include:
- Windows 2000, 2003 and 2008, 32 bit and 64 bit
- Windows XP, Vista and Windows 7, 32 bit and 64 bit
- IBM Platforms:
- IBM i (AS/400)
- System z (mainframe)
- System p (AIX)
- Certified "Ready for IBM Systems with Linux"
- Linux
- Unix
- and more...
MultiValue Platforms Supported
- Rocket (formerly IBM)
- U2 (Universe and Unidata) including Universe back to version 5
- PI Open
- Raining Data
- Northgate
- Temenos Group
- Ladybridge Systems (coming soon)
- Others (older or unsupported MultiValue Systems
- UltPlus
- Power95
- and more...
Copyright ©2010 Robert Houben, CTO