I'm a senior application designer and developer with educated skills on classical Windows client-server applications. Honestly, I just started my learning path in Appian.
Our use case:
We have a well developed and stable running legacy application that uses a MS SQL Server 2019 as database backend. We want to "migrate" some business processes covered by the legacy app to Appian (cloud) as a showcase and proof of concept for our customers (at the first step no productive environment). The Appian application should use the data from the existing SQL Server database on our demo server.
I already created a "Connected System" object that successfully connects to our SQL Server via JDBC. For setting this up I was using the SQL Server data source type and the JDBC connection string:jdbc:sqlserver://<our server public ip>:1433;databasename=<our database name>. The user and password properties are set to a valid SQL Server login that owns the target schema. The test of the connection passes successfully.
Using this connected system, I'm able to access tables and table data on our existing SQL Server database by defining "Record Type" objects in Appian.
So far, so good... now the challenge:
It would make sense to use existing views and also stored procedures in our DB during the Appian process, because nobody wants to re-invent everything in Appian. It seems that the normal "Connected System" object in Appian does not support access to these database objects. I learned from the Appian documentation that a "SQL Integration" object could be the solution to access views and stored procedures by .
When I try to create a new "Integration" of type "Connected System", Appian does not offer my already defined "Connected System" from above for usage. Why not?
When I try to define a new "Connected System" from within the "Integration" set up, Appian does not offer the MS SQL Server option. Why not?
When I try to use a new "Connected System" from within the "Integration" set up and try to use the available type "Custom JDBC Data Source" instead of the not available type "SQL Server Data Source", Appian is complaining that "No supported drivers are installed...". Why? The Microsoft JDBC SQL Server Driver is available for Appian in the environment obviously, so JDBC connection to SQL Server should be possible to configure.
I would really appreciate any support and hints from the community how we can address our challenge with success.
Thanks,
Manuel
Discussion posts and replies are publicly visible
To call stored procedures, you would use this:
https://docs.appian.com/suite/help/25.1/fnc_system_a_executestoredprocedureforquery.html
https://docs.appian.com/suite/help/25.1/Execute_Stored_Procedure_Smart_Service.html#a!executestoredprocedureonsave()
To access views, you will have to ignore Records and set up Custom Data Types + Data Stores.
Overall I am not sure whether you will be happy with not re-inventing your logic. Appian is about business processes, and not just a thin layer of UI on top of database logic. Let me hear what you think.
Thanks, Stefan, for the hint on Data Types + Data Stores to access views. I was able to set this up. Can you provide an example how this can be used then, let's say to populate a list object in an interface?
Concerning stored procs and re-inventing logic in Appian: I fully understand the Appian idea of abstraction from data sources to provide a unified view on data. May be we will follow this path with further development of an Appian application. But for the moment I can say for sure that we have some really sophisticated operations in stored procedures on database level which prevents me from re-invention on short term. Another concern is that - in our dream - we want to be able to keep a beneficial coexistence of the old legacy app and the new Appian approach.
When you use a gridfield in an interface, you can feed any data into it. Use queryEntity to fetch the data you need.
The only difference is, that when using record, Appian auto populates the grid.
In my experience, 15 years, many projects, training hundreds, real sustainable success with Appian means to let go the old. Not ultimately old technology, but old mindsets and ideas of what an "application" is. And once that mindset changes, tech will follow. We cover such aspects in our podcast https://appian.rocks.
100% agree with this.
It will be really hard to truly reengineer the process if you are stuck with legacy CRUD UI.
Don't get me wrong. I'm not questioning the Appian philosophy. It's only about a smooth migration of some business processes step by step from the legacy app. For the moment we need also to convince our internal team as well as the stakeholders from some customers. That's what the current activities are for. Unfortunately, we are a bit under time pressure. So it makes absolutely sense to use some of the things we have already on hand.
Be assured that I'm of course use the chance to rethink the presentation of things while we are preparing our showcase. Nonetheless, the questions I was asking about using existing database views and stored procs are in general valuable for integrations. Thanks for the answers, it makes me even more confident that Appian could be a way to go.