Access Provider: Receive multiple tables in one command

Hi @Hans Andersen ,

I hope you are referring to the .net Access provider here. Can you please provide a sample sql that 

you are trying to execute? 

Thanks,

Kasun

@Kasun Balasooriya ,

Yes, It is the .net version.

I would like to do something like this:

select co.order_no, customer_info_api.get_name(co.customer_no) as "name" 
from customer_order co where co.order_no= :order_no;

select l.part_no 
from customer_order_line l where l.order_no= :order_no;

Two sets of records, but only one call. 

On the Ms sql provider you can do this

Can’t find anything similar in IFS provider.

Looking (as deeply as one can, anyway) at the Access Provider C# code, the ExecuteReader() method only ever returns FndDataTable:

public FndDataTable ExecuteReader(string recordType);
public FndDataTable ExecuteReader(int fetchsize);
public FndDataTable ExecuteReader();
public FndDataTable ExecuteReader(string recordType, int fetchsize);

FndDataTable is roughly equivalent to the .NET DataTable. A collection of tables in .NET is a DataSet, There does appear to be a FndDataSet type in the access provider:

    public class FndDataSet : Component, ISupportInitialize
    {
        public FndDataSet();
        public FndDataSet(IContainer container);

        [Browsable(false)]
        public bool EditMode { get; }
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
        [ListBindable(false)]
        public FndDataTableCollection Tables { get; }
        [Browsable(false)]
        public bool Dirty { get; }

        public event EventHandler DirtyStateChanged;
        public event FndDataEditEventHandler ColumnChanging;
        public event FndDataPathEventHandler ColumnChanged;
        public event FndRowCollectionChangedEventHandler RowAdded;
        public event FndRowCollectionChangedEventHandler RowRemoved;
        public event FndDataPathEventHandler DeferredPopulate;
        public event EventHandler EditModeChanged;

        [EditorBrowsable(EditorBrowsableState.Never)]
        public static void DisposeCachedDataSets(bool disposing);
        public void BeginInit();
        public void EndInit();
        public bool Validate();
        protected override void Dispose(bool disposing);
    }

It would be relatively easy to create your own method that took in a series of SQL Select statements (as a List<string>, for example) and then ran ExecuteReader() for each one. The resulting FndDataTable objects could then be added to a FndDataSet’s FndDataTableCollection and the FndDataSet could be returned.

I am not sure what you would gain by this, however, as I am not sure what you are trying to accomplish by having a collection of results instead of processing each result as it is generated. Each query is still going to take the same amount of processing on the server, and the results are still going to take as much memory in your .NET program whether they are separate tables or tables in a collection/set.

More sample code for the Access Provider would be handy, of course. We are trying to get more examples from IFS, but have not had any success with that yet.

Can you describe in more detail what you hope to gain by combining SELECT statements and executing them at the “same” time? If you really want to increase performance, running each query asynchronously via async and await might be more effective if you are looking for faster speeds. Running each query in its own thread can utilize Oracle’s multi-threaded capabilities more.

Thanks,

Joe Kaufman

Behind the scenes, Microsoft SQL Server allows a query or a stored procedure to return multiple record sets with dissimilar schemas. Oracle Database doesn’t work this way.

In my opinion, that’s not a technical limitation.

If you're familiar with SQL Server, you might want to not use SELECT aliases in quotes. In Oracle, that makes them case sensitive which can cause trouble downstream. The returning column names will be uppercase if you stick with the convention.

If you're trying to avoid round trips for latency, have you considered denormalizing the statement and parsing it for uniqueness downstream?

SELECT co.order_no,
       customer_info_api.get_name(co.customer_no) AS name,
       col.part_no
  FROM customer_order co
  JOIN customer_order_line col
    ON col.order_no = co.order_no
 WHERE co.order_no= :order_no;

Thanks you all,

I am converting an app for IFS that was using sql server. Trying to avoid some rewriting. 

Hans,

In that case, I would write an extension method of the IFS command object called “ExecuteReaderMult” that takes in a List of strings and returns a FndDataSet object containing all the FndDataTable results. I think you should be able to do that even without having any source code for the FndPLSQLSelectCommand class (which is one of the cool parts of extension methods). Here’s a rough cut at what I am thinking of:

public static FndDataSet ExecuteReaderMult(this FndPLSQLSelectCommand selectCommand, List<string> queries)
{
    if ((queries == null) || (queries.Count == 0))
    {
        // No query list passed in, or no queries in the list.
        return null;
    }
    bool success = true;
    FndDataSet fds = new FndDataSet();
    FndPLSQLSelectCommand tempCommand = new FndPLSQLSelectCommand(selectCommand.Connection);
    foreach (string query in queries)
    {
        try
        {
            tempCommand.CommandText = query;
            fds.Tables.Add(tempCommand.ExecuteReader());
        }
        catch (Exception ex)
        {
            // Decide whether to keep trying or bail out of the whole process
            // if any error is encountered. By default we will bug out.
            success = false;
        }
    }
    if (!success) { fds = null; }
    return fds;
}

You would call this via a command object already connected to the IFS instance you want (selectCommand in the following example), like so:

List<string> queries = new List<string>();
queries.Add("SELECT * FROM Customer_Info WHERE UPPER(Name) LIKE 'A%'");
queries.Add("SELECT * FROM Customer_Info WHERE UPPER(Name) LIKE 'B%'");
queries.Add("SELECT * FROM Customer_Info WHERE UPPER(Name) LIKE 'C%'");
FndDataSet fds = selectCommand.ExecuteReaderMult(queries);

At that point the fds object should have three FndDataTable objects in its collection.

It would be relatively simple to change ExecuteReaderMult() into an extension that returns a straight .NET DataSet object containing .NET DataTable objects instead, if that is more in line with what you were getting back from the SQL Server version of the code. Here is an extension method I wrote for FndDataTable to convert it to a DataTable, including a supporting routine that translates the values in FndDataTable:

public static DataTable ToDataTable(this FndDataTable fdt)
{
    DataTable dt = new DataTable();
    // Create the structure (Columns).
    foreach (FndDataColumn col in fdt.Columns)
    {
        dt.Columns.Add(col.Name, Type.GetType(col.PropertyType.FullName));
    }
    // Now fill the DataTable with data from the FndDataTable.
    dt.Clear();
    foreach (FndDataRow row in fdt.Rows)
    {
        DataRow newRow = dt.NewRow();
        foreach (DataColumn col in dt.Columns)
        {
            newRow[col.ColumnName] = row[col.ColumnName].GetDataValue();
        }
        dt.Rows.Add(newRow);
    }
    dt.AcceptChanges();
    return dt;
}

public static object GetDataValue(this FndAttribute attr)
{
    object value;
    if ((attr.IsNull) || (attr.IsInvalidValue))
    {
        // Attribute is already null or is invalid, so we will just return a null value.
        return DBNull.Value;
    }
    // If we make it here, cast the attribute into the appropriate FndAttributeType and use GetValue() to return value of correct type. 
    FndAttributeType fndType = attr.Column.Type;
    switch (fndType)
    {
        case FndAttributeType.Alpha:
            value = ((FndAlphaAttribute)attr).GetValue();
            break;
        case FndAttributeType.Binary:
            value = ((FndBinaryAttribute)attr).GetValue();
            break;
        case FndAttributeType.Boolean:
            value = ((FndBoolAttribute)attr).GetValue();
            break;
        case FndAttributeType.Decimal:
            value = ((FndDecimalAttribute)attr).GetValue(0.00m);
            break;
        case FndAttributeType.Integer:
            value = ((FndIntegerAttribute)attr).GetValue(0);
            break;
        case FndAttributeType.Number:
            value = ((FndNumberAttribute)attr).GetValue(0);
            break;
        case FndAttributeType.Text:
            value = attr.ToString();
            break;
        case FndAttributeType.TimeStamp:
            value = ((FndTimeStampAttribute)attr).GetValue(new DateTime());
            break;
        case FndAttributeType.LongText:
            value = attr.ToString();
            break;
        default:
            value = DBNull.Value;
            break;
    }
    return value;
}

There are probably better ways of doing all that -- I stick with regular looping constructs, etc. But LINQ might offer a better way. The above performs adequately, especially if data results are small (which they usually are in focused client programming).

With some modularity via extension methods, I think you can make code conversion a fairly easy search and replace to get back data sets instead of single tables. You could even just pass your semi-colon-delimited string to ExecuteReaderMult() and use Split() to create the individual queries, or write a function that takes a semi-colon-separated string and generates List<string>.

Hope this helps!

Thanks,

Joe Kaufman

@sutekh137, did you just write this, or did you have some code up your sleeve? 

I am coming to this next week, then I will have a look at it. I let you know.

Hans,

Everything I posted was done except the first extension method ExecuteReaderMult() -- that I was throwing together and it gelled so easily I finished it up and made it work. Might come in useful for me down the road. I really enjoy writing extension methods and prosthelytize about them whenever I can. If you are getting into .NET development and are given time to explore and play around, I recommend discovering their power and straightforward nature.

I started my own data migration (and integration) framework a little over a year ago in .NET (C#) using the Oracle Instant Client (OIC). Works great. Then I ran into limitations with LOB fields, so also have implemented some package calls with Oracle MDAC. That works well, too, but I did not get a framework around it yet for standards like calling New, Modify, and Remove. I have a pretty good framework going with OIC, though it does not utilize much inheritance and is just a simple Reflection-based system and data objects that roughly match IFS’s logical units.

Then I discovered how well the .NET Access Provider worked and now kind of wish I had started with that. However, I would prefer the Access Provider used straight DataSet and DataTable objects -- not sure why IFS decided to “roll their own” data structures on those two fronts, especially since FndDataTable shares a lot of similarities with DataTable, and it isn’t difficult to pull System.Data into a .NET project. Folks who are better with IEnumerables and binding sources probably determine FndDataTable to be just as functional, but I am pretty set in my DataTable ways...

The nice thing about MDAC and the Access Provider is that you can bundle the run-times right inside your executable to avoid the need to install OIC. I have OIC installing in a modular way, and it mainly just requires a folder copy, but getting the ODBC driver fully operable requires Admin privileges on the client machine.

Anyway, as with most things IFS-related, there are at least 2-3 ways of doing things, and they all tend to work pretty well! I hope it all works well for you!

Thanks,

Joe Kaufman