ADO.net provider for AS400/VSAM files in OFFLINE mode
Just thought of writing a quick post on this new feature that ships with HIS 2009.
To give some introduction about this provider - HIS 2006 ships with first version of ADO.net provider for mainframe and AS400 files systems. At the time product required to have live connection to Host to read the data from VSAM files and AS400 files. Because of popular demand of supporting the SQL on predownloded binary files, HIS 2009 now supports this provider in OFFLINE mode.
1) ADVOFFLINE is a OFFLINE file ( VSAM or AS400 file that is FTP'd in BINARY format to a windows workstation ).
2) Lib_ADV.dll - Metadata assembly that defines record structure(s) that a ADVOFFLINE can have.
3) ADVCOPY - is a copybook for the original ONLINE VSAM file.
4) program.cs is a sample that does a SELECT ON a ADVOFFLINE.
program.cs code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.HostIntegration.MsHostFileClient;
using System.IO;
using System.Reflection;
using System.Data;
namespace OFFLINEUnions
{
class Program
{
static void Main(string[] args)
{
string cnstring = @"Local Folder=C:\HisTests\DataTests\OFFLINE;Metadata=C:\HISTests\DataTests\Binaries\HostFileProvider\Lib_ADV.DLL";
HostFileConnection cn = new HostFileConnection(cnstring);
try
{
Assembly HostFileMetadata = Assembly.LoadFile(@"c:\histests\DataTests\Binaries\HostFileProvider\Lib_ADV.DLL");
cn.Metadata = HostFileMetadata;
cn.Open();
HostFileCommand cm2 = new HostFileCommand(cn);
cm2.CommandText = "SELECT * from ADVOFFLINE as Lib_ADV.OUT_FILE_REC1";
HostFileRecordSet hfrs = cm2.ExecuteRecordSet();
StreamWriter ADVstream = new StreamWriter(@"c:\Histests\datatests\Scripts\SELECT_ADV.txt" );
while (hfrs.Read())
{
ADVstream.WriteLine(hfrs.GetString(0));
int lev88_colNumber = hfrs.GetOrdinal("OUT1_LEV88");
ADVstream.WriteLine(hfrs.GetInt16(lev88_colNumber));
if (hfrs.GetInt16(lev88_colNumber).Equals(1))
{
System.Console.WriteLine(hfrs.GetString(hfrs.GetOrdinal("UNION1")));
ADVstream.WriteLine(hfrs.GetString(hfrs.GetOrdinal("UNION1")));
}
if (hfrs.GetInt16(lev88_colNumber).Equals(2))
{
ADVstream.WriteLine(hfrs.GetDecimal(hfrs.GetOrdinal("UNION1")).ToString());
}
if (hfrs.GetInt16(lev88_colNumber).Equals(3))
{
HostFileRecordSet _unionshfrs = (HostFileRecordSet)hfrs.GetRecordSet(hfrs.GetOrdinal("UNION1"));
while (_unionshfrs.Read())
{
ADVstream.WriteLine(_unionshfrs.GetInt16(_unionshfrs.GetOrdinal("OUT1_SMALL1")).ToString());
ADVstream.WriteLine(_unionshfrs.GetString(_unionshfrs.GetOrdinal("OUT1_SMALL2")));
ADVstream.WriteLine(_unionshfrs.GetInt16(_unionshfrs.GetOrdinal("OUT1_SMALL3")).ToString());
}
_unionshfrs.Close();
}
HostFileRecordSet _unionsnumbershfrs = (HostFileRecordSet)hfrs.GetRecordSet(hfrs.GetOrdinal("OUT1_NUMBERS"));
while (_unionsnumbershfrs.Read())
{
string logvalue = _unionsnumbershfrs.GetInt32(_unionsnumbershfrs.GetOrdinal("OUT1_LEADING")).ToString();
ADVstream.WriteLine(logvalue);
logvalue = _unionsnumbershfrs.GetInt32(_unionsnumbershfrs.GetOrdinal("OUT1_TRAILING")).ToString();
ADVstream.WriteLine(logvalue);
logvalue = _unionsnumbershfrs.GetInt32(_unionsnumbershfrs.GetOrdinal("OUT1_SEPALEAD")).ToString();
ADVstream.WriteLine(logvalue);
logvalue = _unionsnumbershfrs.GetInt32(_unionsnumbershfrs.GetOrdinal("OUT1_SEPATRAI")).ToString();
ADVstream.WriteLine(logvalue);
}
}
hfrs.Close();
ADVstream.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
if (cn.State.Equals(ConnectionState.Open))
{
cn.Close();
}
}
}
}
}