Answered Create and pass temp-table dynamically

[bigwill]

Hi all

I have a big problem that i can't find a solution to.

We use .NET as frontend and appserver to get data from db. To get this to work both DataTable in .NET and temp-table on appserver has to be defined identical. I would like to know if it is possible to create this temp-table dynamically.

Today it looks like this:
(.NET side)
- Strongly typed DataTable
- Building metadata for this table
- Passing datatable and metadata to appserver. (1 datatable for SearchCriteria and 1 datatable for the result)

using (OpenAppObject oao = new OpenAppObject(conn, ""))
{
    //Parameters
    ParamArray parms = new ParamArray(3);
    //Create metadata for activityfilter
    TempTableMetaData tempMeta = GenerateMetaData(searchCriteria); //DataTable: searchCriteria
    //Create metadata for searchresult.
    TempTableMetaData tempMetaOut = GenerateMetaData(searchResult); //DataTable: searchResult
 
    parms.AddTable(0, searchCriteria, ParamArrayMode.INPUT, tempMeta);
    parms.AddTable(1, searchResult, ParamArrayMode.INPUT_OUTPUT, tempMetaOut);
    parms.AddInteger(2, maxRowsToReturn, ParamArrayMode.INPUT);
 
    //Run program "searchResult"
    using (new PerformanceMonitor("RunSearch.p", parms, activeDatabase))
    {
        oao.RunProc("RunSearch.p", parms);
        //Get searchresult
        searchResult = (ActivityDS.searchResultDataTable)parms.GetOutputParameter(1);
    }
}

(Progress side)
-Define temp-table with all fields.... (defined like the table in .NET, same order, same name, etc)
-Define input-output parameter table for "table".

define temp-table SearchCriteria no-undo
  field DateOfRecord as datetime
  field ActivityStatus as char.
 
define temp-table SearchResult no-undo
  field ActivityNumber as int
  field ActivityStatus as char.
 
define input parameter table for SearchCriteria.
define input-output parameter table for SearchResult.
define input parameter ipiNumberOfRowsToReturn as int no-undo.
 
...

My problem is that i now want to create the searchresult and dynamically build columns for the searchresult. The user must be able to select the columns he want in the searchresult.

Can this be done using appserver and passing temp-tables as parametes ?
I can add more parameters to this procedure (tried to pass in xml of the expected returning datatable) , but can't figure this out.

Anyone with a good idea ?

 

[RealHeavyDude]

I don't know much about .NET but I do this with Java all the time. In fact the central data retrieval logic of one of our applications is based on a dynamic ProDataSet that gets returned to Java by the Progress AppServer. The Java OpenClient automatically converts it into a JavaSDO which not only contains the data itself but the schema information too. That way I can accomodate most of the application's data handling with two generic methods: fetchBatch and saveBatch. Of course I use an additional ProDataSet for the context management ( like filter criteria, batch and query positions, etc. ).

I would think that the same should be possible in .NET but maybe not with a DataTable but with an ADO .NET Dataset. If that's the case then the only thing you need to do on the Progress side is to stick the dynamic Temp-Table into a dynamic ProDataSet and the let the .NET OpenClient handle the conversion into an ADO .NET dataset.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Alternatively you can probably achieve something similar with JSON - if you're running 11.1+ then AFAIK JSON is handled very well by Progress.

 

[bigwill]

Thanks. I think i found a solution. Insted of returning a proDataTable or ProDataSet i just return the xml with the search result. Builing a temp-table dynamically and use the write-xml method.

Seems to work