Return multiple records from subroutine and parse into datatable [Unidata][U2.NET]

I am working with Unidata, and ADO.NET using the U2 .NET Provider. This may be a shot in the dark as there are not many resources for Unidata and .NET these days.

Currently I can only return a single MV record 153926þIþ and parse it using MV_To_DataTable. I'd like to return multiple records like 153926þIþÿ153926þIþÿ. Is there any built in mechanism for doing this? I fear I will have to write the extension to best accomodate me.

I retrieve a single record in a unidata subroutine this way:

SUBROUTINE GETITEMS(results)
EXECUTESQL "SELECT ID, STATUS, DESC FROM ITEMS TO GETITEM_LIST;"

DONE = 0
RECCNT = 0
LOOP
    RECCNT += 1
    READNEXTTUPLE REC FROM "GETITEM_LIST" ELSE DONE = 1

    results := REC

    IF RECCNT EQ 1 THEN EXIT
UNTIL DONE
REPEAT

results

CLEARSQL

RETURN

Simple subroutine that returns one record without any record marks. This works when I use the U2Parameter method called MV_To_DataTable to parse it into an existing datatable.

However when I change the subroutine line: results:= REC to results:= REC : @RM to append the record marks and remove the limit of 1, the MV_To_DataTable no longer is able to parse it correctly. In fact it will throw System.IndexOutOfRangeException: Cannot find column 3.

VB.NET Code:

' ... Open database connection called U2Connection ...

Dim cmd = U2Connection.CreateCommand
cmd.CommandText = "CALL GETITEMS(?)"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Clear()
cmd.Parameters.Add(New U2Parameter("@arg1", "") With {.Direction = ParameterDirection.InputOutput})

cmd.ExecuteNonQuery()

Dim tb As New DataTable
tb.Columns.Add("ID")
tb.Columns.Add("STATUS")
tb.Columns.Add("DESC")
cmd.Parameters.Item(0).MV_To_DataTable(tb) ' Error happens here

' System.IndexOutOfRangeException: Cannot find column 3.

It appears the method does not separate records. I could be interpretting this incorrectly.

*****UPDATE 2/9/2019

I went ahead and wrote my own extension method to support my return format with the record markers. It populates a datatable with records allowing me to continue as I normally would.

Solution

You are kind of straddling the Multivalue/System.Data divide here. If you have not already done so, I would suggest looking into the U2 Toolkit for .NET, which I believe is generally readily available if you are current on maintenance. It comes with some samples of how to do things like this in C# and VB as well some Entity Framework stuff.

But as to what is going on here, You are trying to put a U2Type.DynArray into a System.DataTable is kind of tricky as the DynArray is a Record state, which could contain multiple rows from multiple tables within a DataSet. As @RM is the terminator for a record so it turns DynArray into DynArray[] and you can't have that as a parameter as such.

To fix this with the minimum refactoring, you can still use MV_To_DataTable, but note that it is expecting your data to be tabular and in in a single record. These example assume a newline is an Attribute mark (@FM/@AM)

Here is the contents of what you are returning

Row1Column1
Row1Column2
Row1Column3:@RM
Row2Column1
Row2Column2
Row2Column3:@RM
Row13olumn1
Row13olumn2
Row13olumn3:@RM

And here is what MV_To_DataTable expects

Row1Column1:@VM:Row1Column2:@VM:Row1Column3
Row2Column1:@VM:Row2Column2:@VM:Row2Column3
Row3Column1:@VM:Row3Column2:@VM:Row3Column3

If you adjust your U2 sub to output that, it should work.

Additionally, you could try using your SQL command directly for .net, but that becomes perilous for other reasons depending on your data.

Good Luck!