Swimlane Relational Databases

the swimlane relational databases connector allows users to perform direct sql operations and procedure calls on various rdbms from within the swimlane platform the swimlane relational databases connector enables seamless interaction with various relational databases, such as ms sql server, postgresql, mysql, and oracle it allows users to execute sql queries, including data modification and retrieval, directly within swimlane turbine's low code automation platform by integrating this connector, end users can automate database operations, enhance security workflows with real time data, and streamline incident response by executing stored procedures or transforming query results into csv format for further analysis prerequisites to utilize the swimlane relational databases connector, ensure you have the following prerequisites custom authentication for databases with the following parameters database type specify the type of the relational database (e g , mysql, postgresql) username the username credential for database access password the password credential for database access host the hostname or ip address of the database server database name the name of the specific database to connect to capabilities this connector is compatible with the following databases microsoft sql server postgresql mysql oracle although the connector was tested against the mentioned databases, it is possible to use it with other databases as long as the engines are compatible to modify data in a database (i e , insert, etc ), use the task run modification query to retrieve data from a database (i e , select), use the task run query to execute stored procedures, use the task run stored procedure asset information the asset requires a username , password , host , port , and database name database type & database engine additionally, the asset requires an engine dialect to supply the database type if supplying a database type, the connector will use the default dialects for the corresponding database ex postgresql will default to postgresql+pg8000 available types are ms sql server, postgresql, mysql, oracle it is possible to provide the specific dialect to use in case its needed available dialects are database available recommended postgres postgresql, postgresql+pg8000 postgresql+pg8000 mysql mysqlconnector mysqlconnector oracle oracle, oracle+cx oracle oracle microsoft sql server mssql+pyodbc, mssql+pymssql mssql+pymssql note in order to use mssql+pyodbc, you must install the package "pyodbc" in turbine tasks setup and filtering the task "direct query" allows inputs to be passed as wild cards any "?" present will be replaced, in order, with supplied "parameters " example query = select id from person where id = %s or id < %s parameters = \["10","20"] the following query will be executed select id from person where id = 10 or id < 20 configurations asset authentication for databases configuration parameters parameter description type required database type database type available ms sql server, postgresql, mysql, oracle string required username username to connect to the database string required password password string required host server host string required database name name of the database string required port host port to use number optional database dialect database dialect this will override the database type supplied see readme for available example postgresql+pg8000 string optional ssl enable/disable ssl connection with database when needed boolean optional actions run modification query executes a non returning sql query such as insert, update, or delete on a database using the specified query string input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order input example {"parameters" \["idparam1","idparam2"],"query" "insert into example1 values (2, 'bob');"} output parameter type description result string result of the operation query executed string output field query executed row count number count value output example {"result" "success","query executed" "insert into example1 values (2, 'bob');","row count" 1} run query executes a specified sql query on the database and returns the results an input of 'query' is required input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order to csv boolean optional when true, returns a csv as an attachment input example {"parameters" \["idparam1","idparam2"],"query" "select table name from dba table","to csv"\ false} output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file file name string name of the resource file file string output field file file output example {"result" \[{"id" 1,"name" "adam"}],"query executed" "select from table1","row count" 1} run stored procedure executes a specified stored procedure in the relational database and retrieves the resulting data input argument name type required description stored procedure name string required name of the stored procedure to run stored procedure params array optional parameters in order that need to be executed with the stored procedure to csv boolean optional when true, returns a csv as an attachment input example {"stored procedure name" "s getperson","stored procedure params" \["1","2"],"to csv"\ false} output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file file name string name of the resource file file string output field file file output example {"result" \[{"id" 1,"name" "adam"}],"query executed" "select from table1","row count" 1} response headers header description example content type the media type of the resource application/json date the date and time at which the message was originated thu, 01 jan 2024 00 00 00 gmt