RemObjects.DataAbstract.Schema.Schema.DeriveDataReaderSchema with duplicates in column names

Remoting SDK
1

I have a tool which allows execution of arbitrary SQL statements.
This tool uses DeriveDataReaderSchema and Bin2DataStreamer to put the dataset in RO Binary data.
The DeriveDataReaderSchema throws a RODuplicateNameException however in case the SQL contains 2 select fields with the same name.
How can this be avoided?
Note: The user can enter an arbitrary select statement.

2

Hi,

You can use as and specify new name like field1 as myfield
By other hand, you can use DA SQL

3

Hi,
Option 1 is not valid since I want to give the user full control over the SQL.

Can something be done with SchemaColumnMappingCollection of the WriteDataReader function?

public void WriteDataReader(IDataReader reader, SchemaDataTable tableSchema, string fields, int maxRecords, bool includeSchema, SchemaColumnMappingCollection columnMapping)

A solution would problaby be to write my own DeriveDataReaderSchema

4

Hi,

Why you can’t execute user SQL with DA SQL ?

pls review DASQL sample that shows usage of this technology.

5

The code gets executed in a RO application. It does not contain a DA service.
I do not have a RemoteDataAdapter.
Furthermore: A sequence of SQL statements get executed and some context info is persisted in between them (db selection).
Their is already quite some code that already exists, it was my assesment that the fastest/best solution would be to only use RO for the binary serialization. Is this possible?

6

Hi,

I can suggest to review your sql (or received data) and make sure that field names in it are unique:

  • you can parse sql and add as field_1, as field1_2, etc manually
  • check fields of received data and serialize it into binary with unique names.
7

re 1: At that level it is not always possible.
We sometimes have

select lefttable.*,righttable.* from
join mytable lefftable
join mytable righttable

I had the impression it should be possible to achieve this by using a slightly modified version of DeriveDataReaderSchema (using a modified reader.GetSchemaTable() )

re 2: What exactly do you propose

8

Hi,

If you rename field instead of raising exception it may work.

when you serialize data to binary, you can store field names to somewhere and if you have duplicate field - just rename it to other one.

Note: you don’t use DA so I don’t know how you serialize your data to Binary .

9

I use WriteDataReader which requires SchemaDataTable, that is why I use DA.
I want a DataTable with schema to be serialized and then on some client side it needs to get deserialized with table schema info back into a DataTable

10

Hi,

Try to rename fields in your SchemaDataTable if they contain the same name.

You can rename fields names like

        for each fld in table.Fields index i do begin
          writeLn($'old name {fld.Name}');
          fld.Name := 'fld'+i.ToString;
          writeLn($'new name {fld.Name}');          
        end;
11

OK, thanks, this works
For me it’s .NET code but using a variant of DeriveDataReaderSchema works

string columnName = dataRow[“ColumnName”].ToString();
string uniqueColumnName=uniqueColumnNames.AddColumnAndReturnUniqueName(columnName);
SchemaField schemaField = schemaDataTable.Fields.Add(uniqueColumnName);

public class UniqueColumnNames
{
Dictionary<string, string> mapping_;
public UniqueColumnNames()
{
mapping_ = new Dictionary<string, string>();
}

public string AddColumnAndReturnUniqueName(string columnName)
{
string basename = columnName;
string name = basename;
int seqidx = 1;
do
{
if (!mapping_.ContainsKey(name))
{
mapping_[name] = basename;
return name;
}
seqidx++;
name = basename + $“_{seqidx}”;
} while (seqidx < 1000);
return basename;
}
} // class UniqueColumnNames