I am using C# interface as described here: http://code.kx.com/wiki/Cookbook/InterfacingWithCSharp
I have a q table that has a composite foreign key and I want to bulk insert into it. How do I pass the composite key column values?
I tried Array of Array but that doesn’t work.
Here is an example of bulk insert via the qSharpinterface:
q.Sync("insert", "sp", new object[] { new string[] { "s1", "s1" }, new string[] { "p1", "p2" }, new int[] { 456, 333 } });
and c.cs:
c.k("insert", "sp", new object[] { new string[] { "s1", "s1" }, new string[] { "p1", "p2" }, new int[] { 456, 333 } });
Maciej Lach
exxeleron | a brand of DEVnet
I am already using that and it works indeed, my question was though for when your foreign key is composite. If the parent table has a primary key formed of 2 columns, how do insert values in a child table via c#? Are you implying that inserting the 2 columns separately as arrays should work?
Let’s define a table with compound primary key:
ktc:([lname:DentBeeblebroxPrefect; fname:ArthurZaphodFord]; iq:98 42 126)
A details table with a foreign key enumeration over ktc:
tdetails:([] name:ktc$(DentArthur;PrefectFord;DentArthur); sc:36 126 42; c: 1 2 1)`
Bulk insert in q:
``tdetails insert (ktc$(DentArthur;DentArthur); 11 34; 2 0)
While inserting from C#, simply using 2 columns to store foreign key values won’t work. You can use additional q function which takes care of enumerating the foreign key values over a parent table:
c.k("{[x;y;z] x insert (enlist y$first z),1_z}", "tdetails", "ktc", new object[] { new object[] { "Prefect", "Ford" }, 133, 2 });c.k("{[x;y;z] x insert (enlist y$first z),1_z}", "tdetails", "ktc", new object[] { new object[] { new string[] { "Prefect", "Ford" }, new string[] { "Prefect", "Ford" } }, new int[] { 156, 33 }, new int[] { 1, 2 } });
Maciej Lach
exxeleron | a brand of DEVnet
That example was very useful for me to learn more of q, thanks.
But when you have a table that has 10 composite foreign keys like that, I guess it will end up pretty ugly. This is external data shaped like that, which I am trying to bring into q, as a learning exercise at this stage.
Unless somebody has some better idea, I am thinking to insert the data first as pairs of symbols and then cast it to the parent table type by doing something like this:
q) update name:ktc$name from tdetails
Here is a shot at trying to get a generalized solution.
csert function is my first more complex Q function, so feedback for improvement/conciseness is gladly accepted.
q)ktc:([lname:DentBeeblebroxPrefectValerie; fname:ArthurZaphodFordJohan]; iq:98 42 126 133)
q)tdetails:( name:ktc$(); sc:int$())
q)csert:{[t;l]cs:cols t;t insert ?[flip cs!l;();0b;cs!each[{[f;c]$[`=f c;c;($;(enlist f c);c)]}fkeys t]cs]}
In C#:
c.k(“csert”, “tdetails”, new object {
new object { new string {“Beeblebrox”, “Zaphod”}, new string {“Prefect”, “Ford”}, new string {“Beeblebrox”, “Zaphod”} },
new int { 36, 126, 42 }});
This seems to work and unless more testing finds issue, it should work with multiple composite foreign keys, even simple keys, etc.
Maciej,
I gave qSharp a shot and I find out it outperforms kx c# interface. Thanks for pointing me to it.
One thing which I would hold as a minor bug though. While you can have columns with a runtime type of ‘type’, if you declare column with a compile time of say char, qSharp throws an exception saying it’s not able to handle char types. You are forced to use object type for that. kx’s c class doesn’t have that problem.
Thank you for pointing me to qSharp.
Hi Greg,
latest qSharp releaseprovides a convenience enhancement, which allows to seamlessly serialize char to q general list of strings.
Maciej Lach
M.Lach@devnet.de
exxeleron | a brand of DEVnet
On Saturday, March 21, 2015 at 10:20:18 AM UTC+1, Greg Borota wrote:
Maciej,
I gave qSharp a shot and I find out it outperforms kx c# interface. Thanks for pointing me to it.
One thing which I would hold as a minor bug though. While you can have columns with a runtime type of ‘type’, if you declare column with a compile time of say char, qSharp throws an exception saying it’s not able to handle char types. You are forced to use object type for that. kx’s c class doesn’t have that problem.
Thank you for pointing me to qSharp.