Subscribe to this thread
Home - General / All posts - IronPython Script Returning a Table
joebocop
460 post(s)
#23-Mar-21 22:29

Is it possible for a script to return a "table", such that it could be called from SQL using the TABLE CALL syntax?

I have a directory structure of files, which I would like to query, joining to data within other tables inside my Manifold 9 project.

I've got IronPython scripts that return strings, which is easy enough. What I can't figure out is how to return a "table" where the script has produced, for example, a list, or a dict. Ideally, I'd call it as

FUNCTION dir_list(@root NVARCHAR) TABLE AS SCRIPT [drive_scripts] ENTRY 'DirList';

TABLE CALL dir_list('c:\\my files\\')

Each row in the CALLed dir_list() table is a list element that has been produced within the script.

I'm unclear on the syntax for converting a python list (or dict?) into a "table" object. Any pointers are very much appreciated. Thank you.

joebocop
460 post(s)
#23-Mar-21 23:31

I'd hoped the following (screencap) syntax would generally succeed, but I'm missing something (thanks).

Attachments:
tSchema.png

tjhb

9,700 post(s)
#24-Mar-21 05:16

I'm travelling but will try to look at this properly when I get on the ferry (Cook Strait).

The main thing is that when a script function returns a table, it needs a helper function that the Manifold compiler can interrogate to tell what the table's output schema will be. It needs to know this before calling the actual function.

The API gives examples and adamw has given many on the forum.

There are good examples in this thread. The helper functions there have the suffix "_table" but that is arbitrary.

The calling SQL needs to specify the template function name with the keyword ENTRYSCHEMA. You can search on that.

joebocop
460 post(s)
#24-Mar-21 11:59

Perfect, thank you very much! ENTRYSCHEMA is the exact term I needed to search in the manual. Safe crossing.

sga7 post(s)
#01-Apr-21 13:28

Have you managed to get any further with your problem? I'm actually trying to do pretty much the same thing as you (i.e. run a script that returns a table). I currently write my script in c# though I could change language if needed.

I tried to run example 3 of the afore-mentioned thread, but I'm stucked with an "invalid object reference" error. I did add an @ when defining the h function (so that it reads h(@x INT32)) as now required by the SQL syntax, but this does not solve the problem. Example 1 and 2 work perfectly well.

Any help on this problem is welcome! Thanks in advance!

tjhb

9,700 post(s)
#01-Apr-21 19:22

The last line of the calling query should now read

TABLE CALL h(5);

This syntax was changed in 9.0.168.0.

sga7 post(s)
#07-Apr-21 06:39

Oh yes indeed, I missed that one!

Thanks a lot, it now works!

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