I have an expression that takes in a dse and spits out the column names of everything in that dse. Unfortunately, the way it does this is by querying the dse and then running a!keys() on the first entry of the result to figure that all out. While this works perfectly well when the dse has values in it, it will error out if the table is empty. So is there any way to get the same information directly from a DSE that doesn't have data in it?
Discussion posts and replies are publicly visible
If this is static, why not just create a CDT from it? Then do a!keys() on that CDT.
Because it's not static. I basically have to figure out what the table looks like from the dse input so I can populate/query the table regardless of which table it is.
That is an interesting challenge :-) Thank you for the explanation.
If you can create a mapping of DSE to TABLE NAME, you may be able to use my solution to reference the columns via something such as INFORMATION_SCHEMA.COLUMNS - are you on MSSQL?
I'm not sure if it is possible to access table name dynamically via DSE input, but I have seen some work magic with XML on here for related situations..
Thanks. It works really well and saves us the trouble of configuring a new milestone each time we come up with a new activity or need to rework the flow of an existing activity. This one minor kink aside anyway.
Wouldn't I still need something in the table to do that? I'm good on the Appian side if something is in there.
No, INFORMATION_SCHEMA.COLUMNS is the database definition and does not rely on data in the tables. This is the MSSQL version, I'm not sure what you are using for an underlying database. Just a thought for how you might be able to return columns per DSE/table regardless on data present.
For instance, in this case we query for "chris_test_table" and are returned all column names, data types, etc in that table (more metadata is available). The table is empty.
We've got MariaDB. I guess I'd pull this info up through a stored procedure. Question is how do I get the table name from the dse?
Since each DSE has to map to a CDT, why not just make an expression rule that stores a key:value pairing with different DSEs and their matching CDT?
I was thinking creating a mapping as well - assuming new DSEs are not added that frequently?
Also, MariaDB should be similar for INFORMATION_SCHEMA.COLUMNS. I would create a VIEW, then map a CDT/DS to the view, vs a stored procedure.
I just whipped up this quick expression rule which, storing a list of data store entities and their accompanying CDTs, one can pass a DSE (like via constant) and it'll find the matching item in the list. If I were implementing this for real I'd probably store the list of maps in "local!pairs" in its own expression rule (for universality) and just reference it from this one.
a!localVariables( local!pairs: { a!map( entity: cons!APP_ENTITY_PERSON, type: 'type!{urn:com:appian:types:APP}APP_Person', typeId: tointeger('type!{urn:com:appian:types:APP}APP_Person'), keys: a!keys('type!{urn:com:appian:types:APP}APP_Person'()) ), a!map( entity: cons!APP_ENTITY_EQIP_SUBMISSION, type: 'type!{urn:com:appian:types:APP}APP_eQIPSubmission', typeId: tointeger('type!{urn:com:appian:types:APP}APP_eQIPSubmission'), keys: a!keys('type!{urn:com:appian:types:APP}APP_eQIPSubmission'()) ) }, local!index: where(local!pairs.entity = ri!dse), index(local!pairs, local!index, null()) )
As seen here, we can even have it directly provide us the CDT "keys" array as a member of the output set(!)
Nice! We are not on a version that supports a!keys() yet :(. I believe that was introduced in 21.4?
Yeah, plus or minus a version. AFAIK there might be a usable workaround if you need, like by populating an empty member of the data type then scraping the text for the property names.