Subscribe to this thread
Home - General / All posts - Script returning table data from string table name
sga7 post(s)
#07-Apr-21 09:28

Hi all,

I'm trying to write a script that accepts a string as input parameter (a table name) and returns the corresponding table data.

So far, I tried this script

//C#

class Script

{

static Manifold.Context Manifold;

public static Manifold.Table getTableSchema(Manifold.Table t)

{

Manifold.Schema schema = t.GetSchema();

Manifold.Table output = Manifold.Application.CreateTable();

output.Design(schema);

return output;

}

public static Manifold.Table getTable(string tableName)

{

Manifold.Application app = Manifold.Application;

using (Manifold.Database db = app.GetDatabaseRoot())

{

using (Manifold.Table t = db.Search(tableName))

{

Manifold.Table output = getTableSchema(t);

Manifold.Schema schema = t.GetSchema();

Manifold.ValueSet fields = Manifold.Application.CreateValueSet();

int n = 0;

foreach (Manifold.Schema.Field field in schema.Fields)

{

fields.AddValue(field.Name);

++n;

}

using (Manifold.Sequence seq = t.SearchAll(fields.GetValueNames()))

{

// number records starting from zero

int num = 0;

while (seq.Fetch())

{

Manifold.ValueSet valuesNew = seq.GetValues().CreateCopy(true);

++num;

output.Insert(valuesNew);

}

}

return output;

}

}

}

static void Main()

{

}

}

And the call in SQL

FUNCTION getTable(@x NVARCHAR) TABLE AS SCRIPT [test]

ENTRY 'Script.getTable'

ENTRYSCHEMA 'Script.getTableSchema';

TABLE CALL getTable('mfd_root');

But I get an error "Can't run script, wrong number of parameters (Script.getTableSchema)."

I did not find much documentation on how to define a proper ENTRYSCHEMA function and I am wondering if there would be any way to write this function correctly or if it cannot be done this way.

Maybe a script is not even needed for this task, and I am reinventing the wheel...

Thanks in advance for your help

tjhb

9,627 post(s)
#07-Apr-21 10:04

It's quite hard to read, if you don't use the code button.

adamw


9,552 post(s)
#07-Apr-21 10:15

You seem to want to return an arbitrary table and you cannot do that.

You have to be able to determine the schema (fields + indexes) of the table you are going to return without knowing the values of the arguments you are going to be called with. In your case, you need to know what the name of the table is going to be to determine what fields you are going to return. The query engine cannot tell you what the name of the table is going to be, it needs to know what fields you are going to return before the names you are going to be called with are known.

The only info you can use to determine the schema of the result table is the types of all parameters. If your function takes a couple of strings and / or a couple of numbers, etc, you already know these types because you are coding the function, so the ENTRYSCHEMA function should take no parameters. If your function takes a table though, the query engine will let you know what fields it is going to contain when asking what the result table is going to be, the ENTRYSCHEMA function should take a TABLE parameter and the query engine will pass it a model table with the fields / indexes of the input. This is useful for things like adding a field to an existing table.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.