How can be execute sql server table valued functions from appian ? Are there any plug-ins available for the same ?
Thanks and regards,
If you can share an example of what you are needing to do, we may be able to provide a more specific answer....However, here are two approaches to performing functions on data being pulled /displayed / captured / etc in Appian:
1. Use Appian functions to perform the manipulations (https://docs.appian.com/suite/help/19.3/Appian_Functions.html), combine with a!forEach to apply to arrays
2. Use DB functions in views to manipulate the data before pulling in into Appian
Hope this helps
Thank you for the help !
I'm not sure about it's limitations, but you could also look into the execute stored procedure plugin on the App Market.
What are the DB functions? would you provide the link?
Would you elaborate how does #2 works?
The DB functions are functions of whatever DB you are using, not Appian. There are many great SQL tutorials online, but here is a MYSQL listing of some functions: https://www.w3schools.com/sql/sql_ref_mysql.asp
Regarding #2, how to pull the data into Appian?
The SQL I want to execute is SELECT SEQ.NEXTVAL SEQ FROM DUAL. DUAL is a special table in Oracle which does not show up in the table dropdown list when creating the CDT.
You can not access functions like this or the DUAL table directly in CDTs. You need to create a view directly in the DB to use functions or the DUAL table.
1. Why do you need the nextval? If for a PK, then, you should be using the sequence automatically as part of the trigger on insert and this is not needed.
2. If you need a nextval for another reason, you can us that select (just use an alias on the column) in a select with another table (use an inner select or other to combine with the table you are getting the main part of your data from).
Thx for the quick reply. On one form, we allow the user to enter multiple contacts and each contact could have multiple addresses. Since there is only one submit button on the form, we need the GUIDs of the contacts to associate the contacts with their address before the user click the submit button (aka before launching the process model). Otherwise the process model does not know which addresses belong to which contacts.
Which Appian function we should use to do #2 below?
2. If you need a nextval for another reason, you can us that select (just use an alias on the column) in a select with another table (use an inner select or other to combine with the table you are getting the main part of your data from)
Do you have an example?
I am not sure you are addressing this in the best way. It sounds like you have a one to many situation and I am not sure any of this has been written to the DB yet.You may want to look at your UI and your CDTs for the parent and child. If you write the parent first, you will get the PK that can then be used as a FK for the child (this is just one of many approaches) and then the nextval is not needed.There may be some tutorials for building grids, etc. that might help..
I would like to write an expression rule to call SELECT SEQ.NEXTVAL SEQ FROM DUAL and return the GUID before launch the process model to save the data. What Appian function can i use to call the SQL statement you suggested that calls SELECT SEQ.NEXTVAL SEQ FROM DUAL as an inner select?
In other word, is there an Appian function that can execute raw SQL statements?
Discussion posts and replies are publicly visible
© 2020 Appian. All rights reserved.