Subscribe to this thread
Home - General / All posts - Help with CALL, EXECUTE, FUNCTION and TABLE parameters
KlausDE

6,335 post(s)
#22-Jul-19 08:12

I need some help how to call a functions that take a TABLE as parameter:

My aim is to build a table of all parmeters in default.ui.txt linked as table with a CSV link (not as comment/TXT).

What I have is a FUNCTION extract() that analyses a string and returns a table of all parameters enframed in angle brackets. What I have is a SELECT or INSERT that feeds this function with a static string and processes the result.

--SQL9

-- $manifold$

-- prepare result table for the final INSERT *** commented for tests

-- CREATE TABLE [Result] ([Value] NVARCHAR);

-- * FUNCTION extract()

-- * take a single string and extract all <parameters> into the return table, 

-- * no WHITESPACE allowed in parameter names

-- *

FUNCTION extrct(@txtparm NVARCHARTABLE AS CALL StringToRegexpMatches( @txtparm, '<\\w*>''c'END;

-- this works

-- *

-- * extracting all parameters of a static string example

-- *

SELECT [Value] FROM CALL extrct('ExampleString=ExampleFunction(<parm1>, <parm2>, <parm3>)');

-- this would work if not *** commented for tests

-- *

-- * extract and INSERT as separate records in [Result]

-- *

-- INSERT INTO [Result] SELECT [Value] FROM CALL extrct('ExampleString=ExampleFunction(<parm1>, <parm2>, <parm3>)'); 

How can I make this work on an NVARCHAR field of all records in a table? Missed the appropriate example in help for CALL, EXECUTE, FUNCTION.

I guess I can't use a CALL function() returning a TABLE in the fields list of a SELECT and don't manage to get it to work with all records of a table in a FROM clause.

adamw


8,634 post(s)
#22-Jul-19 08:53

How to launch extrct(...) on each record in a table, and insert all returned data (potentially multiple records for each call) into a second table?

Use SPLIT:

--SQL9

FUNCTION extrct(@txtparm NVARCHARTABLE AS

  CALL StringToRegexpMatches( @txtparm, '<\\w*>''c')

END;

VALUE @t TABLE = (VALUES

 ('ExampleString=ExampleFunction(<parm1>, <parm2>, <parm3>)'),

 ('ExampleString2=ExampleFunction2(<parm1>, <parm2>)')

);

SELECT SPLIT CALL extrct([result]FROM @t;

You'll frequently want to retain some data from the original table, too:

--SQL9

FUNCTION extrct(@txtparm NVARCHARTABLE AS

  CALL StringToRegexpMatches( @txtparm, '<\\w*>''c')

END;

VALUE @t TABLE = (VALUES

 ('ExampleString''ExampleFunction(<parm1>, <parm2>, <parm3>)'),

 ('ExampleString2''ExampleFunction2(<parm1>, <parm2>)')

   AS

 ([uistring][definition])

);

SELECT [uistring]SPLIT CALL extrct([definition]FROM @t;

Hope this helps.

KlausDE

6,335 post(s)
#23-Jul-19 12:09

Thx, that put my stumbling block away.

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