KDB - getting error when I wrap working code in a function

Question

I wrote the following function which fetches data from a sql table.

get_sql_table:{[usr;psswd;server;db;tbl]
    usr: "'", usr, "'";
    psswd: "'", psswd, "'";
    new_tbl: "'", tbl, "'\"";
    map:(`date`datetime`bigint`char`decimal`double`int`varchar)!("DZJCFFIS");
    schema:exec column_name!native from (update native:map[data_type] from (2#"S";enlist"\t") 0: 
        schema:system "mysql --user=",usr," --password=",psswd," -h ",server, " ", db, " --execute=\"select column_name, data_type from information_schema.columns WHERE table_name = ", new_tbl) 
        where not null native;
    query: system "mysql --user=",usr," --password=",psswd," -h ",server, " ", db, " --execute=\"select * from ", tbl, ";\"";
    data:(count["\t" vs query 0]#"S";enlist"\t") 0: query;
    :![data;();0b;k!{($;schema[x];(string;x))} each k:key[schema] inter cols data];
 };

When I load the script and try to run the function, I sometimes get a type error on the last line (the return statement). The last line is there to map the appropriate data types to the kdb table.

However, the code sometimes succeeds. It seems the tables it succeeds with and fails with are arbitrary and have nothing to do with the column types (in fact it's failed on tables it's succeeded with before, and vice versa).

Additionally, there's nothing wrong with the code itself. When I write it outside of a function it works every time, with the exception of the table having a data type that's not in map (but in that case I can just add it to map). This code only fails when it's wrapped in a function.

What am I missing?

I think when you are reading data from mysql maybe sometimes it returns type which is not compatible to type you are using for casting. Maybe kdb is trying to interpret type in some cases based on the result. I would suggest that you insert show statements to print meta of schema. That will help you with debugging the issue. — Rahul
– Rahul, Commented Dec 14, 2018 at 17:16

terrylynch · Accepted Answer · 2018-12-14 17:24:58Z

2

Your schema variable is defined within the function, not globally, so it is not accessible from within this lambda

{($;schema[x];(string;x))}

You would have to pass it in like so

{($;y[x];(string;x))}[;schema] each k:key[schema] inter cols data

It has probably worked outside the function because you've accidentally globally defined schema

answered Dec 14, 2018 at 17:24

terrylynch

14k1 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JesusMonroe Over a year ago

Makes a lot of sense. Thank you.

Collectives™ on Stack Overflow

KDB - getting error when I wrap working code in a function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related