how to pass a multidimensional array when executin a lua script

majortk72
Contributor

Hi

maybe someone can help or has some ideas:

I created a lua script like this:

create or replace lua scriptreppsa.test (ARRAY x) RETURNS ROWCOUNT AS
  for i =1,#x do
    output(i)
    output(x[i])
    for j=1,#x[i] do
      output(j)
      output(x[i][j])
    end
  end
/

maybe calling the script like this:

execute script reppsa.test(ARRAY((1, 'A'),(2,'B')) with output;

Thanks for help or ideas

regards

Thomas

 

1 ACCEPTED SOLUTION

Accepted Solutions

exa-Peggy
Moderator
Moderator

HI Thomas;

Seems that Exasol supports only single dimensional array 
https://docs.exasol.com/database_concepts/scripting/db_interaction.htm#ScriptingParameters
Would a split  help as a workaround?

 

--https://stackoverflow.com/questions/1426954/split-string-in-lua
--/
create or replace lua script scriptreppsa.test (ARRAY x) RETURNS ROWCOUNT AS
  for i =1,#x do
    
    result = {};
      for match in (x[i]..','):gmatch("(.-)"..',') do
         table.insert(result, match);
      end
      for j =1,#result do
        output(i..' '..result[j])
      end
  end
/

execute script scriptreppsa.test(ARRAY('a,b','c,d')) with output;

 

 

View solution in original post

6 REPLIES 6

mwellbro
Xpert

Hi @majortk72 ,

maybe this would help you ?

https://community.exasol.com/t5/discussion-forum/how-to-call-another-lua-script-with-an-array-parame...

edit: I guess the keyword here is "multidimentional", right ? Will have to look into that once I´ve got an exasol available, currently on the move so I can´t play around with the syntax.

Cheers,
Malte

mwellbro
Xpert

Hi @majortk72 ,

after checking back with the docs and playing around a bit with the syntax I´d say you can´t pass a multi dimensional array as a single parameter because the ARRAY constructor only seems to be geared towards one dimension:

https://docs.exasol.com/database_concepts/scripting/db_interaction.htm#Return_Value_of_a_Script

mwellbro_0-1635191080687.png


You could however pass two different arrays as two separate parameters which share the same "id order" , which in effect would be rather similar to a single multi dim array  :

create or replace lua script scriptreppsa.test (ARRAY x,ARRAY y) RETURNS ROWCOUNT AS
  for i =1,#x do
    output(x[i].." "..y[i])
  end
/

execute script scriptreppsa.test(ARRAY(1,2),ARRAY('A','B')) with output;

mwellbro_2-1635191892032.png


What´s your use case in pushing an array to a script ? Just asking since you can´t do ( or I´m not aware of ) much a 
script that yields output via WITH OUTPUT in terms of further processing.
From what I can see it´s primarily aimed as a means of "passing a list in form of an ARRAY" to a script so the script can
work it´s way through said list. 

Cheers,
Malte

P.S.: @exa-MathiasHo , could you have someone look over this assumption of mine regarding the question ? Not feeling too sure since scripting isn´t my "main", but it´s what I get from a bit of testing.

majortk72
Contributor

Thanks for your tips

this is also I just worked out as a workaroud to pass multidimensional array

I like to pass from an API (graphical or scripting) a lsit of pairs (e.g. column_name, position) to update the position value for a column wihtin a repository table, and this array must be variable in count

so my workaround now has 2 ARRAY parameters, and I have to check e.g. if both arrays have the same count, and one array has the columns, the other one has the positions

greetings

Thomas

exa-Peggy
Moderator
Moderator

HI Thomas;

Seems that Exasol supports only single dimensional array 
https://docs.exasol.com/database_concepts/scripting/db_interaction.htm#ScriptingParameters
Would a split  help as a workaround?

 

--https://stackoverflow.com/questions/1426954/split-string-in-lua
--/
create or replace lua script scriptreppsa.test (ARRAY x) RETURNS ROWCOUNT AS
  for i =1,#x do
    
    result = {};
      for match in (x[i]..','):gmatch("(.-)"..',') do
         table.insert(result, match);
      end
      for j =1,#result do
        output(i..' '..result[j])
      end
  end
/

execute script scriptreppsa.test(ARRAY('a,b','c,d')) with output;

 

 

View solution in original post

majortk72
Contributor

Hi

This ist also a nice workaround for my problem

Thanks

regards

Thomas

exa-Peggy
Moderator
Moderator

Hi Thomas,

Hint: "Execute Scripts" should only be used for orchestrating statements. It only runs in one thread under one node.

a scripting program is a sequential program and only runs on a single cluster node (except the contained SQL statement). https://docs.exasol.com/database_concepts/scripting.htm 

What does your import format look like?
Is it a CSV file?
Then the following approach might also be an option?

CREATE schema test;

/*
 * create test tables
 */
CREATE TABLE
    test.testtable
    (
        column_name VARCHAR(10),
        "position" INT
    );
CREATE TABLE
    test.tmptable
    (
        column_name VARCHAR(10),
        "position" INT
    );

/*
 *  import from a csv into a table
 */
IMPORT INTO test.tmptable FROM LOCAL CSV FILE
'C:\Users\pesm\Documents\Exasol\SQL Beispiele\Import export\test.csv'
COLUMN SEPARATOR = ',' ROW SEPARATOR = 'CRLF';


/*
 * Merging the table updates
 */

MERGE
INTO
    test.testtable T
USING
    test.tmptable U
ON
    T."position" = U."position"
WHEN MATCHED
    THEN
UPDATE
SET
    T.column_name = U.column_name
WHEN NOT MATCHED
    THEN
INSERT
    VALUES
    (
        U.column_name,
        U."position");